Mysql

MySQL笔记：持续更新

一些关于MySQL的笔记

MySQL主从复制参数

Master端：

  1. –binlog-do-db：指定数据库的操作事件被记录
  2. –binlog-ignore-db：指定某个数据库的操作事件不被操作
  3. set sql_log_bin = 0：会话级别所作操作不被记录

注意：在Master段进行复制过滤惠导致主服务器上的二进制日志记录不完整，一旦主服务器崩溃将无法做到还原所有数据。

Slave端：

  1. –replicate-do-db：需要复制的数据库名，如果复制多个数据库，重复设置这个选项
  2. –replicate-ignore-db：不需要复制的数据库名，如果复制多个数据库，重复设置这个选项
  3. –replicate-do-table：同理，表级别的操作
  4. –replcate-ignore-table：同理，表级别的操作
  5. –replicate-wild-do-table=foo%.bar%：复制以foo开头的数据库，bar结尾的表
  6. –replicate-wild-ignore-table=foo%.bar%：不复制以foo开头的数据库，bar结尾的表

注意：Slave端在接受Master端传过来的日志时，是没有选择权的，Master端写过的日志Slave端全部都接收并写到磁盘，保存在中继日志中。但是Slave端在应用那些数据时是有选择权的。

配置方式：

第一：配置文件方式

第二：在线配置，例如

change replication filter replicate_do_db=(db1,db2)

MySQL半同步复制原理

默认情况下，MySQL复制是异步的，主库执行完Commit之后，主库写入binlog后就可以返回给客户端，无需等待binlog发送给从库。当主库的一个写入事务并提交成功，而从库尚未得到主库的binlog时，主库宕机，从而可能导致从库丢失该事务，造成主从不一致。

因此，MySQL引入了半同步复制。半同步复制保证主库的每一个binlog都能可靠得发送到从库上，主库在每次提交事务时，并不及时反馈给客户端，而是等待其中一个从库也接收到binlog并成功写入中继日志后，主库才返回给客户端。此时，至少有两份日志记录，一份在主库的binlog，另外一份至少一个从库的中继日志上，从而保证了数据的一致性。

配置

rpl_semi_sync_wait_point：AFTER_COMMIT和AFTER_SYNC

  1. 半同步复制AFTER_COMMIT(5.6默认值)：Master将每个事务写入binlog(sync_binlog=1)，传递到Slave刷新到磁盘(sync_relay=1)，同时Master提交事务。Master等待Slave反馈收到relay log，只有收到ACK后Master才将commit ok结果反馈给客户端。

    存在的问题：在使用AFTER_COMMIT的模式下，客户端事务在存储引擎提交后，在得到从库确认的过程中，主库宕机了。此时主句在等待从库ACK的时候，虽然没有返回当前客户端，但是事务已经提交了，其他客户端惠读取到已提交的事务。如果从库还没读到该事务的events，同时主库发生了crash，然后切换到备库，那么之前督导的事务就不见了，出现幻读。

  2. 无损复制AFTER_SYNC(5.7默认值，5.6没有)：Master将每个事务写到binlog中，发送给Slave并刷新到磁盘(relay log)。Master等待Slave反馈接受到relay log之后，再提交事务并返回commit ok给客户端。

半同步复制和无损复制的对比

  1. 半同步复制时先engine commit再ACK
  2. 无损复制是先ACK再engine commit
  3. 半同步复制意味着在Master节点上，刚刚提交的事务对数据库的修改，对其他事务是可见的。所以，如果等待Slave ACK的时候crash了，对其他事务惠造成幻读，丢失数据。
  4. 无损复制在Master写完binlog之后，就传输给Slave，但还没去commit，意味着当前这个事务对数据库的修改。其他事务是不可见的。所以不会出现幻读和数据丢失风险。

配置

在MySQL的lib/plugin里面有两个插件：semisync_master.so和semisync_slave.so。分别在Master和Slave安装；

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

#在主库安装
install plugin rpl_semi_sync_master soname ‘semisync_master.so’;
show plugin;
set global rpl_semi_sync_master=on;
show variables like '%semi%';

#在从库安装
install plugin rpl_semi_sync_slave soname ‘semisync_master.so’
show plugin;
set global rpl_semi_sync_slave=on;
show variables like '%semi%';
stop slave;
start slave;

关键参数：

rpl_semi_sync_master_enabled =on 开启半同步功能

rpl_semi_sync_master_timeout 单位毫秒，表示主库等待从库回复消息的时间超过该值，就自动切换为异步复制模式

rpl_semi_sync_master_wait_no_slave 默认on。表示主库每个事务提交都要等待从库的ACK信号。

rpl_semi_sync_master_wait_for_slave-count 控制主库接受多少个从库写事务成功反馈，才返回commit ok给客户端

rpl_semi_sync_master_wiat_point 默认值AFTER_SYNC，含义是使用无损复制。

手动切换主从

正常切换

  1. 对主库进行全表锁定：flush tables with read lock;

  2. 在Master执行：show processlist;

    显示：Master has sent all binlog to slave;waiting for binlog to be update

  3. 在Slave执行：show processlist;

    显示：Slave has read all relay log;waiting for more updates

  4. 停止slave io线程：stop slave io_thread;

  5. 将Slave提升为Master

    1
2
3
4
5
6
7

    stop slave;
#删除所有binlog日志文件，并将日志索引清空，重新开始所有新的日志文件。
reset master;
#删除所有master.info和relay-log.inf文件以及所有的relay log文件，并重新启用一个新的relay log文件
reset slave;
#比reset slave，还会删除内存中的连接信息
reset slave all;

  6. 查看salve是否只读模式

    1
2
3
4

    # 只读模式需要修改配置文件，注释read-only=1,并重启mysql服务
# 不重启mysql,直接使用命令。不过下次重启mysql就会失效
set global read_only=1;
show variables like '%read only%';

  7. 将Master变为Slave

     1
 2
 3
 4
 5
 6
 7
 8
 9
10

    # 在新的Master上创建同步使用的用户
grant replication slave on *.* to repl@192.168.79.150 indentified by '123456';
# 将新的slave设置为只读：1、配置文件修改并重启mysql。2、命令修改
set global read_only=on;
# 在新的slave上释放全局锁
unlock tables;
# 重置binlog
reset master;
# master连接信息
change master to master_host='192.168.79.150',master_port='3306',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=599,master_connect_retry=10;

MySQL同步的一些故障处理

  1. 在master上删除一条记录，而在slave上找不到：Error_code:1032;handler error HA_ERR_KEY_NOT_FOUND;the event’s master log ….

    解决方法：由于master要删除一条记录，而slave上找不到所以报错，这种情况master上都将其删除了，那么slave可以直接跳过。

    1
2
3
4
5
6

    stop slave;
# id
set global sql_slave_skip_counter=1;
start slave
#或者在my.cnf中配置，并重启mysql
slave-skip-errors=1032

  2. 主键重复。在slave上已经有了该记录，又在master上插入了同一条记录。

    解决方法：登录slave，删除那条重复的数据。

    1
2
3

    stop slave;
delete from tabls_name where id=id_num;
start slave;

  3. 在master上更新一条记录，而slave上找不到，丢失数据。

    解决方法：在master上用mysqlbinlog分析下出错的binlog日志在干什么。在slave上找一下更新后的那条记录，应该是不存在。最后把丢失的数据补上。

    1
2
3
4

    mysqlbinlog --no-defaults --base64-output-DECODE_RWOS -v -v mysql-bin.000001 | grep -A 30 'end_log_pos 4709';
stop slave;
insert into ...;
start slave;

  4. slave中的中继日志relay-log损坏

    解决方法：确认同步的binlog和pos位置。重新同步。

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

    show slave status \G;
...
Slave_IO_Running:#接手master的binlog信息
Master_Log_File/Read_Master_Log_Pos:#显示当前读取的Master的binlog的文件和pos位置

Slave_SQL_Running:#执行写操作
Relay_Log_File/Relay_Log_Pos:#显示当前Slave正在处理的中继日志文件和pos位置
Relay_Master_Log_File/Exce_Master_Log_Pos:#显示当前slave正在处理的中继日志文件和pos位置，在Master中对应的binlog文件和pos位置
...
stop slave;
#重新同步
change master to master_log_file='mysql-binlog.000008',master_log_pos=1233;

持续更新…

TODO

Licensed under CC BY-NC-SA 4.0