MySQL主从复制

MySQL主从复制

环境

hostname	IP	OS
master	192.168.220.128	centos7
slave	192.168.220.129	centos7

MySQL复制原理大致如下：

1. 首先，MySQL主库在事务提交时会把数据变更为Events记录在二进制日志文件Binlog中；MySQL主库上的sync_binlog参数控制Binlog日志刷新到磁盘。

2. 主库推送二进制日志文件Binlog中的事件到从库的中继日志Relay Log,之后从库根据中继日志Relay Log重做数据变更操作，通过逻辑复制一次来达到主库和从库的数据一致。

MySQL通过3个线程来完成主从库间的数据复制：其中Binlog Dump线程在主库上，I/O线程和SQL线程跑在从库上。当在从库上启动复制(START SLAVE)时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用。

二进制日志文件Binlog格式

Statement	基于SQL语句级别的Binlog，每条修改数据的SQL都会保存到Binlog中。
Row	基于行级别，记录每一行数据的变化。不记录原始SQL;
Mixed	混合Statement和Row模式，默认情况下采用Statement模式记录。

主从配置

============master============
[mysqld]
log-bin=mysql-bin	#启用二进制日志
server-id=128	#id标识
binlog-do-db=world	#可被服务器复制的数据库
#binlog-ignore-db=mysql	#不可被服务器复制的数据库
[root@master ~]# mysql -uroot -p world < world.sql
Enter password: 
[root@master ~]# systemctl restart mysqld
授权
mysql> grant replication slave on *.* to slave@192.168.220.129 identified by '000000';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      492 | world        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MySQL用server-id来唯一的标识某个数据库实例，并在链式或双主复制结构中用它来避免sql语句的无限循环。简单来说，server_id有两个用途：
1.用来标记binlog event的源产地，就是SQL语句最开始源自于哪里；
2. 用于IO_thread对主库binlog的过滤。对于在同一个集群中，server-id不能重复。

=================slave================
[root@slave ~]#  vim /etc/my.cnf
  4 [mysqld]
  5 server-id=129	

mysql> change master to master_host='192.168.220.128', master_user='slave', master_password='000000';
Query OK, 0 rows affected (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.220.128
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 492
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.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
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 492
              Relay_Log_Space: 556
              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: 128
1 row in set (0.00 sec)