mysql 复制原理与实践应用详解
本文实例讲述了mysql 复制原理与实践应用。分享给大家供大家参考，具体如下：
复制功能是将一个mysql数据库上的数据复到一个或多个mysql从数据库上。
复制的原理：在主服务器上执行的所有ddl和dml语句都会被记录到二进制日志中，这些日志由连接到它的从服务器获取，并复制到从库，并保存为中继日志，
这个过程由一个称为 io线程 的线程负责，还有一个称为 sql线程 的则按顺序执行中继日志中的语句。
复制有多种拓扑形式：
1、传统复制，一主多从，一个主服务器多个从服务器。
2、链式复制，一台服务器从主库复制，而另一台服务器又从这台复制，中间服务器又叫中继主库。
3、主主复制，两个主库互相接受写入和复制。
4、多源复制，一个从库，从多个主库复制。
一、复制如何操作
1、在主库上启用二进制日志记录。
2、在主库上创建一个复制用户。
3、在从训上设置唯一的 server_id。
4、从主库中备份数据。
5、在从库上恢复主库备份的数据。
6、执行change master to命令。
7、开始复制。
二、具体的操作步骤如下：
1、在 主库 上，启用二进制日志并设置server_id。
#设置server_id server_id = 1 #开启binlog日志 log-bin = mysql-bin
2、在主库上创建复制用户
create user '用户名'@'%' identified by '密码'; grant replication slave on *.* to '用户名'@'%';
3、在从库上设置server_id
#设置server_id server_id = 10
4、备份主库上的数据
mysqldump -u root -p --all-databases --routines --events --triggers --single-transaction --master-data > 导出路径
5、在从库上恢复主库导出的数据
mysql -u root -p -f < 主库备份文件.sql
6、在从库上执行 change master to 命令
change master to master_host='主库ip', master_user='主库复制用户', master_password='密码', master_log_file='二进制日志名称', master_log_pos=二进制日志位置;
二进制日志名称和二进制日志位置，已经在备份主库文件中包含了，类似如下所示：
change master to master_log_file='mysql-bin.000016', master_log_pos=47845;
7、从库上运行 start slave，然后 show slave status\g; 查看复制状态;
三、设置主主复制
假设主库分别是master1和master2。
1、设置master2为只读
set @@global.read_only = on;
2、在master2上创建复制用户，如果存在，则不用创建了
create user '用户名'@'%' identified by '密码'; grant replication slave on *.* to '用户名'@'%';
3、确保master2已开启二进制日志，检查master2上的二进制日志的坐标
show master status;
4、根据第2步的信息，在master1上执行 change master to 命令
change master to master_host='master2主机ip', master_user='master2复制用户', master_password='密码', master_log_file='二进制日志名称', master_log_pos=二进制日志位置;
5、在master1上开启 slave 模式
start slave;
6、设置master2为可读写
set @@global.read_only = off;
四、设置多源复制
设置 server3 为 server1 和 server2 的从库。
1、设置 server1 和 server2 的二进制日志和server_id，具体操作可参考上面。
2、在 server1 和 server2 上创建复制用户，具体操作可参考上面。
3、在 server3 上设置 server_id。
4、备份 server1 和 server2 的数据。
5、在 server3 上恢复 server1 和 server2 上备份的数据。
6、在 server3 上，将复制存储库从 file 改为 table，
stop slave; set global master_info_repository = 'table'; set global relay_log_info_repository = 'table';
还需要在配置文件中修改：
[mysqld] master-info-repository = table relay-log-info-repository = table
7、在 server3 上，执行 change master to 命令，并命名通道名
change master to master_host='server1主机ip', master_user='server1复制用户', master_password='密码', master_log_file='server1二进制日志名称', master_log_pos=server1二进制日志位置 for channel 'server1'; change master to master_host='server2主机ip', master_user='server2复制用户', master_password='密码', master_log_file='server2二进制日志名称', master_log_pos=server2二进制日志位置 for channel 'server2';
8 、在 server3 上，为每个通道执行 start slave for channel 语句
start slave for channel 'server1'; start slave for channel 'server2';
9、查看同步状态，show slave status\g;
要获取指定通道的从库状态，show slave status for channel '通道名称'\g;
五、设置复制筛选器
可以选择要复制哪些表或数据库，在主库上，可以使用--binlog-do-db 和 --binlog-ignore-db 选项来选择要记录变更的数据库，以控制二进制日志。更好的方法是控制从库。
1、复制指定数据库
change replication filter replicate_do_db = (db1, db2);
2、复制指定表
change replication filter replicate_do_table = ('db1.table1');
3、如果想使用通配符来选择表
change replication filter replicate_wild_do_table = ('db1.tb_%');
4、忽略数据库
change replication filter replicate_ignore_db = (db1, db2);
5、忽略指定表
change replication filter replicate_ignore_table = ('db1.table1');
六、将从库由主从复制切换到链式复制
比如现在服务器a为主库，服务器b和服务器c为从库，复制于服务器a。现在想把服务器c作为服务器b的从库。
1、在服务器c上停止从库运行
stop slave; show slave status\g;
记录下relay_master_log_file和exec_master_log_pos的值
2、在服务器b上停止从库运行
stop slave; show slave status\g;
记录下relay_master_log_file和exec_master_log_pos的值
3、将服务器b的日志位置与服务器c的进行比较，找出哪一个是服务器a最新同步，通常，服务器c先停止从库运行，服务器b的日志会更靠前。
4、在服务器c上，使用 start slave until 语句将其同步到服务器b的日志位置：
start slave until master_log_file='上一步中服务器b日志名称', master_log_pos=上一步中服务器b日志位置;
5、在服务器c上，检查 show slave status 中 exec_master_log_pos 和 until_log_pos 两者应该相同。
6、在服务器b上，查看主库状态，启动从库。
show master status; start slave; show slave status\g;
7、在服务器c上，停止从库运行，执行 change master to 命令。
stop slave; change master to master_host='服务器b的ip', master_user='服务器b复制用户', master_password='密码', master_log_file='上一步中通过show master status获取日志名称', master_log_pos=上一步中通过show master status获取日志位置;
8、在服务器c上，启动复制并查看状态
start slave; show slave status\g;
七、将链式复制切换到主从复制
服务器a->服务器b->服务器c，如果想让服务器c直接作为服务器a的从库，该怎么做？
1、在服务器b上，停止从库运行，并记录主库状态
stop slave; show master status\g;
2、服务器c上，确保从库的延迟已被追上，relay_master_log_file和exec_master_log_pos应该等于服务器b上主库状态。
一旦延迟被追上，就停止从库的运行。
stop slave;
3、在服务器b上，从 show slave status 中获取服务器a的日志坐标值(relay_master_log_file和exec_master_log_pos)，并启动从库
show slave status\g; start slave;
4、在服务器c上，停止从库运行，并执行 change master to 命令，指向服务器a
stop slave; change master to master_host='服务器a的ip', master_user='服务器a的复制用户', master_password='密码', master_log_file='上一步中获取的日志', master_log_pos=上一步中获取的日志位置;
5、在服务器c上，开启从库，并查看状态。
start slave; show slave status\g;
八、设置延迟复制
为什么需要延迟复制，有可能主库上执行了一条灾难性语句，你必须通过备份中的时间点恢复，如果数据库大小过大，这将导致长时间停机。
为了避免出现这种情况，可以使用一个延迟的从库，如果发生了灾难，并且延迟的从库还没有执行这条灾难性语句，则可以先停止复制，让从库跳过该灾难语句，最后把从库提升为主库。
1、停止从库运行
stop slave;
2、设置延迟时间，以秒为单位
change master to master_delay = 3600; start slave;
3、检查从库状态
show slave status\g;
sql_delay: 从库延迟于主库的秒数。
sql_remaining_delay：延迟还剩余的秒数，当保持延迟时，这个值是null。
slave_sql_running_state：sql线程的状态
九、设置 gtid 复制
全局事务标识符 gtid 是在程序中创建的唯一标识符，并与主库上提交的每个事务相关联。该标识符是唯一的，不仅在主库上，在其他从库上，它都唯一。
上面描述的所有复制，都需要指明二进制文件和复制起点的位置，如果将一个从库的主库切换到另一个，就必须重新获取二进制文件位置，这会很麻烦。
为了避免，可以使用基于 gtid 的复制，mysql 使用 gtid 自动检测二进制日志的位置。
1、在所有数据库中 my.cnf 中启动 gtid
[mysqld] gtid_mode = on enforce-gtid-consistency = 1 skip_slave_start
2、将主库设置为只读，确保主库与从库数据一致。
set @@global.read_only = on;
3、重新启动所有从库，使 gtid 生效。
4、重新启动主库。
5、在从库上执行 change master to 命令来设置 gtid 复制
change master to master_host='主库ip', master_port=3306, master_user='复制用户', master_password='密码', master_auto_position=1;
6、在所有从库上执行 start slave; 并查看状态。
十、设置半同步复制
默认情况下，复制是异步的，主库不知道写入操作是否到达从库，如果主库与从库间存在延迟，主库崩了，尚未到达从库的那些数据就会丢失。
为了解决这种问题，半同步复制，主库会一直等待，直到至少有一个从库接收到写入的数据。
1、在主库上，安装 rpl_semi_sync_master 插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
windows下请使用如下：
install plugin rpl_semi_sync_master soname 'semisync_master.dll';
2、确认插件已激活
select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';
3、开启半同步复制并调整超时时间
set @@global.rpl_semi_sync_master_enabled=1; set @@global.rpl_semi_sync_master_timeout=100;
4、在从库上，安装 rpl_semi_sync_slave 插件
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
windows下请使用如下：
install plugin rpl_semi_sync_slave soname 'semisync_slave.dll';
5、确认插件已激活
select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';
6、在从库上，启用半同步复制，并重新启动从库io线程
set global rpl_semi_sync_slave_enabld = 1; stop slave io_thread; start slave io_thread;
7、通过如下方式查看半同步状态
show status like 'rpl_semi_sync_master_clients';
查看以半同步连接到主库的客户端数量
show status like 'rpl_semi_sync_master_status';
主库在异步和半同步复制之间切换，on表示半同步，off表示异步。