MySQL主从复制的实现

MySQL主从复制原理
#

从节点的I/O线程向主节点请求binlog事件，主节点Dump线程读取binlog事件并发送事件至从节点I/O线程，从节点将事件写到relay log（中继日志）文件中；之后从节点的SQL 线程，会读取relay log文件中的日志，重放事件来实现主从数据一致。

事实上，主节点是可以并行写的(mysql的并发)，而binlog是串行写(每次写一个事件) ，所以主从复制中，mysql是单线程复制，也就不可避免的造成了主从数据同步有延迟，所以主从复制也成为异步复制

主从复制配置
#

本文以一主一从为例。

在配置之前确保时间同步，并且从节点的版本号与主节点相同或者高于主节点的版本号

配置主服务器

开启二进制日志记录binlog

$ vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id = 1
log_bin = master-log
skip_name_resolve = ON                                            <-- Centos6不支持
innodb_file_per_table = ON

创建用于复制的账号并授权

$ systemctl start mariadb
$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rpuser'@'192.168.196.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW MASTER STATUS;                                 <-- 查看binlog日志position
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 |      495 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

配置从服务器

开启中继日志relay-log

$ vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id = 2
relay_log = relay-log
skip_name_resolve = ON
innodb_file_per_table = ON
read_only = ON

配置slave连接至主节点

$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.196.129',MASTER_USER='rpuser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=495;
Query OK, 0 rows affected (0.09 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.196.129
                  Master_User: rpuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000001
          Read_Master_Log_Pos: 495
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-log.000001
             Slave_IO_Running: Yes                                   <-- I/O线程开启
            Slave_SQL_Running: Yes                                   <-- SQL线程开启
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0            
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 495
              Relay_Log_Space: 818
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

查看从节点的两个文件master.info 、relay-log.info

$ cat /var/lib/mysql/master.info                              <-- 储存有master信息，重启也会生效  
18
master-log.000001
495
192.168.196.129
rpuser
123456
3306
60
0

0
1800.000
0

$ cat  /var/lib/mysql/relay-log.info                          <-- 记录有当前数据库对应日志位置
./relay-log.000002
530
master-log.000001
495

更新主节点服务器数据

$ mysql -uroot -p123456
MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use testdb
Database changed
MariaDB [testdb]> create table tb1 (id int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [testdb]> insert into tb1 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 |      863 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

从节点查看数据已经同步

$ mysql -uroot -p123456
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb             |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> select * from tb1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
MariaDB [testdb]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.196.129
                  Master_User: rpuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-log.000003
          Read_Master_Log_Pos: 863
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 898
        Relay_Master_Log_File: master-log.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0                                     <-- 参考 1)
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 863
              Relay_Log_Space: 1186
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.01 sec)