Mysql之基于日志的主从复制
1. Mysql主从复制
1. 什么是mysql主从复制？
mysql主从复制是mysql自带的功能，通过逻辑的binlog日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的SQL语句，重新应用到mysql数据库中。
2. 主从复制的结构
单向复制：一主一从 一主两从 一主多从 多主一从 多主多从
双向复制：互为主从
链式复制：类似A-->B-->C
环式复制：生产场景很少用
在生产工作中，mysql主从复制都是异步的复制方式。不是严格的实时数据同步。
3. 主从复制的应用场景
1. 从服务器作为主服务器的实时数据备份
2. 主从服务器实现读写分离，从服务器实现负载均衡LVS。
从服务器仅仅处理用户的select查询请求，降低用户查询响应时间。
对于更新的数据(update,insert,delete语句)，交给主服务器处理。
结构图：
3. 把多个从服务器根据业务重要性进行拆分访问
有为外部用户提供查询服务的从服务器，
有内部DBA用来数据备份的从服务器，
为内部人员提供访问的日志分析及供开发人员查询的从服务器。
结构图：
4. 实现mysql主从读写分离的方案
1. 通过程序代码实现(PHP和java)，生产应用场景最多。
2. 通过开源的软件(mysql-proxy,amoeba)实现读写分离，这种方式稳定性差，生产不建议使用。
3. 大型门户网站自己开发dbproxy。
2. mysql主从复制原理
1. 介绍
mysql的主从复制是一个异步的复制过程，数据将从master复制到slave，由三个线程参与完成。其中sql线程和I/O线程在slave，另外一个I/O在master.
要实现mysql的主从复制，首先必须打开master的binlog记录功能，否则无法实现。因为整个复制过程就是slave从master获取binlog日志，然后在slave上以相同顺序执行获取的binlog日志中所记录的各种sql操作。
要打开mysql的binlog记录功能，通过在mysql配置文件my.cnf中的mysqld模块增加"log-bin"参数选项来实现，具体如下：
[mysqld]
log-bin=/data/mysql/mysql-bin
2. mysql主从复制原理过程详细描述
1) master服务器上打开binlog记录功能。
2) 在slave服务器执行start slave命令开启主从复制开关，开始进行主从复制。
3) slave的I/O线程会通过在master上已经授权的复制用户权限请求连接到master服务器，并请求从指定binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行change master命令指定的)之后开始发送binlog日志内容。
4) master服务器接收到来自slave服务器的I/O线程的请求后，master上负责复制的I/O线程会根据slave服务器的I/O线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给slave端的I/O线程。返回的信息中除了binlog日志内容外，还有在master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
5) 当slave服务器的I/O线程获取到master服务器上I/O线程发送的日志内容，日志文件及位置后，会将binlog日志内容依次写到slave自身的relay log，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master新binlog日志时能够告诉master服务器从新binlog日志的指定文件及位置开始请求新的binlog日志内容。
6) slave的sql线程会实时监测relay log中的内容，把它解析成sql语句。
3. mysql主从复制的配置过程
1. 在主服务器上打开log-bin，在my.cnf配置文件的mysqld模块下。
log-bin=/data/3306/mysql-bin
log-bin=/data/3307/mysql-bin（如果只是从数据库，不需要配置log-bin；如果是要做级联，需要打开log-bin）
2. 配置主从的server-id，要不同啊。
server-id=3306
server-id=3307
3. 配置好以后，要重启mysql服务。
/data/3306/mysql restart
/data/3307/mysql restart
4. 检查配置参数后的结果。
egrep "log-bin|server-id" /data/3306/my.cnf
egrep "log-bin|server-id" /data/3307/my.cnf
5. 登录数据库查看
mysql -uroot -p -S /data/3306/mysql.sock
mysql> show variables like 'server_id'; 查看server-id +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 3306 | +---------------+-------+ 1 row in set (0.94 sec) mysql> show variables like 'log_bin'; 查看binlog是否开启 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.03 sec)
6. 在主库创建用于主从复制的帐号
grant replication slave on *.* to 'rep'@'192.168.0.%' identified by '123';
flush privileges;
查看一下select user,host from mysql.user;
show grants for 'rep'@'192.168.0.%';
7. 实现对主数据库锁表
flush table with read lock;
8. 备份主数据库服务器
mysqldump -uroot -p -S /data/3306/mysql.sock --events -A -B | gzip >/opt/mysql_bak.$(date +%F).sql.gz
-A 备份所有的库
-B 直接覆盖原有的库
mysqldump -uroot -p -A --master-data=2 > /tmp/crm2.sql 文件中会出现带有注释的-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=6245095;
mysqldump -uroot -pJn9V2pI5\!0R8wY1L -A --master-data=1 > /tmp/crm1.sql 文件中会出现不带有注释的CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=6247969;
9. 解锁主库
unlock tables;
查看主库的binlog的position
show master status;
show master logs;
10. 把备份的数据库导入到从服务器上。
使用scp或者rsync命令复制到从服务器上。
rsync -avz mysql_bak.2018-07-05.sql.gz /data/3307/
gzip -d mysql_bak.2018-07-05.sql.gz
11. 在从库上的操作，修改配置文件
server-id 一定不能跟主相同，一定是唯一的。
如果不做级联，就不用开启binlog,因为会消耗磁盘I/O
有两种情况需要开启binlog：
1. 级联
2. 从库是备份服务器
12. 在从服务器上还原备份的数据库
mysql -uroot -p -S /data/3307/mysql.sock <mysql_bak.2018-07-05.sql
13. 在从服务器上，登录mysql,执行change master
CHANGE MASTER TO MASTER_HOST='192.168.0.200', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=406;
14. 在从服务器上，也可以不登陆mysql，执行下面的命令
mysql -uroot -p -S /data/3307/mysql.sock<<EOF CHANGE MASTER TO MASTER_HOST='192.168.0.200', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=406; EOF
15. 在从服务器上，登录mysql，执行
start slave;
show slave status\G;
查看最关键的3个参数
Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0
16. 在从服务器上，也可以不登陆mysql
mysql -uroot -p /data/3307/mysql.sock -e "start slave;"
mysql -uroot -p /data/3307/mysql.sock -e "show slave status\G;"
4. 一主多从 主宕机 从切换为主 继续同步
1. 检查所有从库
show processlist;
mysql> show processlist \G
*************************** 1. row ***************************
Id: 10
User: system user
Host:
db: NULL
Command: Connect
Time: 529
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 528
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
2. 检查所有从库的master.info
看哪个更新，选择最大的bin-log和位置点
[root@nfs-server data]# cat master.info 23 mysql-bin.000011 464 192.168.0.200 rep 123 3306 60 0 0 1800.000 0 a027a03c-7df9-11e8-92fc-000c296cbf3b 86400 0
3. 确保所有relay log全部更新完毕
在每个从库上执行stop slave io_thread;
show processlist \G; 直到看到下面的信息
Id: 11
User: system user
Host:
db: NULL
Command: Connect
Time: 833
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
4. 登录从库
stop slave;
reset master
5. 进入数据库数据目录，删除master.info relay-log.info
检查授权表，清除read-only参数
6. 开启bin-log，如果存在log-slave-updates read-only一定要注释掉
/data/3307/mysql restart
7. 如果主库没有宕机，需要去主库拉取binlog补全提升主库的从库
8. 其他从库配置
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.0.200', 指向新的主库
start slave;
show slave status \G;
9. 修改web前端代码的数据库连接串地址
10. 修理损坏的主库，作为新主库的从库.
5. 主从复制忽略数据库和表
1. 在主库上指定主库二进制日志记录的库或忽略的库
vim /etc/my.cnf
...
binlog-do-db=xxxx 二进制日志记录的数据库
binlog-ignore-db=xxxx 二进制日志中忽略数据库
以上任意指定其中一行参数就行，如果需要忽略多个库，则添加多行
重启mysql
2. 在从库上指定复制哪些库或者不负责哪些库
#编辑my.cnf，在mysqld字段添加如下内容： replicate-do-db=test 设定需要复制的数据库 replicate-ignore-db 设定需要忽略的复制数据库 replicate-do-table 设定需要复制的表 replicate-ignore-table 设定需要忽略的复制表 replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符 replicate-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符
一库一行，一表一行
重启mysql服务
3. 以上方法选其中之一即可。
浙公网安备 33010602011771号