MySQL主从复制原理深入讲解

lx86

　　利用MySQL自身提供的主从复制技术，在企业生产场景中，可以很好的对数数据进行多处自动备份，并且实现数据库的扩展。比如：在做定时备份时，备份的过程可能需要锁表操作，在备份锁表期间，用户无法访问数据，虽然可以选择在业务低谷期进行备份，但是多少都会有影响，这时可以通过主从复制的从库进行锁表备份。在主从复制的基础上通过读写分离技术还能提升数据库的负载性能（主库写，从库读）。
　　主从复制模型
　　一主一从
　　一主多从
　　双主
　　线性级联
　　环状级联
　　这次用来讲解的是一主一从模型
　　主从复制原理图

　　主从复制过程存在三个线程，Master端的I/O线程，Slave的I/O线程与SQL线程。Master端需要开启binlog日志，Slave端需要开启relay日志。
　　1、Slave端的I/O读取master.info文件，获取binlog文件名和位置点，然后向Master端的I/O线程请求，该binlog文件名和位置点的binlog信息。
　　（master.info文件在配置主从复制时使用change master命令来指定生成）
　　2、Master端的I/O线程会根据Slave端的I/O线程请求的信息来读取Master的binlog日志信息与及读取到最新的binlog文件名和位置点一同返回给Slave的I/O线程。
　　3、Slave端的I/O线程会把获取到的binlog日志写入relay日志（中继日志）文件中，并且更新master.info文件信息。（把读取到Master最新的binlog日志文件名和位置点更新到master.info文件中，下一次当前位置去读取Master的binlog日志）
　　4、Slave端的SQL线程会定期读取relay日志，把二进制的日志解析成SQL语句，并执行这些SQL语句，同步数据到从库中。
　　主从复制实战配置
　　配置小结
　　Master端
　　1、同步Master端的原始数据到所有Slave端
　　2、开启binlog日志，保持server-id唯一
　　3、配置Slave验证授权用户，权限replication slave
　　Slave端
　　1、执行change master语句，生成master.info文件
　　2、开启relay日志，保持server-id唯一
　　3、启动Slave复制（start slave）
　　Master端全备数据库同步到Slave端
　　在开始做主从复制之前，需要把Master原有的数据都先同步到所有的Slave，否则在做同步复制之时，因为原有数据不一致导致同步失败。（注意，如果使用原来备份时间点比如昨天凌晨的全备数据同步所有Slave数据时，还需要把当前时间点之前的所有binlog增量备份同步，使在截取主从复制时间点时，Master和所有Slave的数据保持一致）
　　原有数据不一致导致主从复制失败
　　

mysql> show slave status\G　　
Last_Error: Error 'Table 'ricky.test' doesn't exist' on query. Default database: 'ricky'. Query: 'insert into test values(987654,9,'gogo',30)'
　　

　　并且全备时，要锁表备份，并导入所有Slave，保证截取的时间点数据一致，同时刷新binlog日志。
　　

[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B -R --flush-logs --lock-all-tables --events >/tmp/mysql_bak_$(date +%F).sql　　
[root@db02 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock  show databases;
　　

　　Master端配置
　　Master端开启binlog日志功能
　　

[root@db02 ~]# egrep "log-bin|server-id" /data/3306/my.cnf　　
log-bin = /data/3306/mysql-bin
　　
server-id = 1
　　

　　Master配置slave复制授权用户
　　

#权限replication slave
　　
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysql52'@'172.16.1.52'>　　
mysql> flush privileges;
　　

　　检查Master端当前binlog日志文件名和位置点
　　当前binlog文件名和位置点，在配置Slave端时，需要告诉Slave端
　　

mysql> show master status\G　　
*************************** 1. row ***************************
　　File: mysql-bin.000004
　　Position: 337
　　Binlog_Do_DB:
　　
Binlog_Ignore_DB:
　　
1 row in set (0.00 sec)
　　

　　Slave端配置
　　Slave端开启relaylog日志功能
　　

[root@db02 ~]# egrep "relay-bin|server-id" /data/3307/my.cnf　　
relay-log = /data/3307/relay-bin
　　
server-id = 3
　　

　　告诉Slave端主从复制时连接的Master（ip+端口【默认可以不需要】）、授权连接用户和密码、binlog文件名和位置点。这里的binlog文件名和位置点还有另一种解决办法，在mysqldump全备时加入--master-data参数，把change master【1，不注释；2，注释】语句嵌入到备份文件中（mysqldump逻辑备份，以SQL语句形式导出数据），把备份文件复制到Slave端导入时，如果参数选项为“1”，则会自动执行change master语句，把binlog文件名和位置点写入Slave端的master.info文件中。其它的master端信息、授权用户和密码则需要手动change master写入master.info文件。
　　执行change master语句
　　

mysql> CHANGE MASTER TO MASTER_HOST='172.16.1.52',　　-> MASTER_PORT=3306,
　　-> MASTER_USER='mysql52',
　　-> MASTER_PASSWORD='123456',
　　-> MASTER_LOG_FILE='mysql-bin.000004',
　　-> MASTER_LOG_POS=337;
　　

　　启动Slave
　　

mysql> start slave;　　

　　查看Slave端状态
　　

mysql> show slave status\G　　
*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 172.16.1.52
　　Master_User: mysql52
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000004
　　Read_Master_Log_Pos: 337

　　Relay_Log_File:>　　Relay_Log_Pos: 253
　　Relay_Master_Log_File: mysql-bin.000004
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　Replicate_Do_DB:
　　Replicate_Ignore_DB: mysql
　　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: 337
　　Relay_Log_Space: 403
　　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端向test表中插入一条数据
　　

mysql> insert into test values(987654,9,'gogo',30);　　

　　Slave端对应的test会同步了该条数据
　　

mysql> select * from ricky.test;