【0.3】mysql复制的日常管理维护，mysql复制常见问题处理

【1】复制的日常管理

#复制的日常管理与维护

【1.1】show slave status\G ：在从库查看从库线程状态

【1.2】flush tables with read lock; 　：主从不一致后锁表。

　　　　然后 show master status\G

　　　　然后 show slave status\G 来查看从库同步状态 或者重新 change master to....

　　　　然后 select master_pos_wait('mysql-bin.00002','389'); （即刚刚show master status找到的文件及位置），如果为1 表示超时退出 ，如果为0 则标识主从同步。

　　　　最后再主库 unlock tables; 解锁

【1.3】跳过错误

跳过错误有两种方式：
1.跳过指定数量的事务：（建议如果已经出现了错误，使用这种办法）
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳过一个事务
mysql>slave start

2.修改mysql的配置文件，通过slave_skip_errors参数来跳所有错误或指定类型的错误（建议配置时使用这种办法）
vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误，DDL错误类型包含 1007,1008,1050,1051,1054,1060,1061,1068,1091,1146（5.6可以用这个）
#slave-skip-errors=ddl_exist_errors #跳过DDL错误，all：跳过所有错误(mysql5.7才有ddl_exist_errors)

【1.4】大对象blog ,text 传输

　　　　

【1.5】多主复制时，自增变量冲突问题

　　　　当有多主对应一个从库时（这里以2主1从为例），如果主库的表使用自动增长，那么多主复制该表到从时，可能会有主键冲突问题；

　　　　因为默认的系统auto_increment，其 auto_increment_increment 和 auto_increment_offset 均为1.

　　　　（1.5.1）为了解决这个问题，可以在两个主库交错设置主键初始值，然后设置相同步长。比如：

　　　　　　A主库，设置ID=1为初始值，步长设置为2，那么自增下来就是 1,3,5,7

　　　　　　B主库，设置ID=2为初始值，步长设置为2，那么自增下来就是 2,4,6,8

　　　　这样就不会导致从库主键冲突了。

　　　　（1.5.2）使用分布式主键处理方案

　　　　（1.5.3）分库分表方案

1. select @@identity;
2. select last_insert_id();

多人同时写的话，不会相互影响。LAST_INSERT_ID();  是 connection 级的变量，各个不同连接相互之间不影响

其实不加事务也可以。
案例：
start transaction;
insert into login_log_tb(server_name) values ('服务器');
select LAST_INSERT_ID(); 
commit;

【1.6】如何查看上一个同步事务距离现在有多久？

　　　　查看SQL线程中的time字段即可

　　　　

　　　　我在主库立马插入一条之后，过了20多秒时间就又变成24了。这里的time字段数据，以秒为单位

　　　　　　

 　　

【1.7】如何查看主从延迟？

　　　　show slave status 下的 Seconds_behind_Master..

　　　　注意，这只是一个预估值，预估从库落后主库的描述。单位是毫秒

　　　　

【1.8】如何提高复制性能？

　　　　【1.8.1】根据database拆分复制

　　　　　　

　　　　　　

 

【1.8.2】并行复制（5.6之后才有）

#如果业务正在运行，那么直接在从库运行可能

#5.7加入参数
slave-parallel-type=LOGICAL_CLOCK #5.7新增。这里设置的是基于组条件的（同一数据库内的也可以用多个SQL 重做线程）
slave-parallel-workers=4　　#默认为4

#5.6加入参数
#因为5.6的slave-parallel-type 参数，只能为DATABASE，是基于不同数据库之间的（也就是schema）并行，不用设置

slave-parallel-workers=2

 

【1.9】主从切换

　　　　【1.9.1】一主一从切换

　　　　　　这里指的是，主库挂了，把从库作为主库，把原主库作为从库。

　　　　　　

步骤【1】：查看当前同步情况
步骤【2】：清理Binlog信息，在old slave构建复制账户
步骤【3】：old master 设置为 new slave

## mysql主从切换为从主：
#从库变主库（注意，如果old slave没开binlog，在reset master前开启Binlog）
old slave: 
show processlist；#  确保Slave has read all relay log;
STOP SLAVE IO_THREAD
show slave status \G;检查IO及SQL线程是否正常，如果为NO表明同步不一致

stop slave;
reset master;
reset slave all;(看版本号)

grant replication slave on *.* to 'repl'@'192.168.135.%' identified by '123456';
flush privileges;
show master status;#记住这里列出的log_file和log_pos信息
------------------------------------------------------------------------------
## 主库变从库
old master:
Reset master;(新的slave，老的master)
Reset slave;

change master to master_host="IP",
master_port=3306,
master_user="repl",
master_password="123456",
master_log_file="...",#上一步中列出的log_file
master_log_pos="...";#上一部中列出的log_pos

start slave;
show slave status\G;

#通知所有应用程序，指向新主库

 　　【1.9.2】一主两从切换

　　　　

假设M，S1,S2 一主两从，M为主，S1/S2为从
主从切换，S1为主，M为从、S2为从

操作步骤
（1）确保所有的从数据库已经执行完了relay log中的全部更新。
all slave:
    stop slave io_thread; --#关闭IO线程，不再从主区获取日志到relay log
    show processlist; -- 确认状态是has read all relay log
    
（2）把S1设置为主做准备
    stop slave;
    reset master;
    reset slave;
（3）S2重新设置为S1新主库的从库
    stop slave;
    change master to master host='S1_ip';
    -- 如果S1也有相关的复制账号，这样就可以了。否则就需要重新change所有信息;
（4）连接转移到S1
　　　-- 把所有的应用程序连接指定到S1上去
（5）原M 设置为 S1（先主库）的从库
　　直接从mysql-bin.000001 的154位置开始就好。
    

 

 

 【1.10】网络无法连接怎么办？

　　（1）网络可以互相ping通：ping 192.168.135.158

　　（2）双方端口是否侦听开启：netstat -an|grep 3306

　　（3）防火墙策略：service iptables stop;（这里只是测试，我就直接关闭了哈）

　　（4）selinux策略：getenforce（查看状态） setenforce 0 （关闭）

 

 

参考自：

　　《mysql深入浅出开发、优化与管理维护》第二版--唐汉明