MySql 复制介绍
MySQL复制允许将主实例(master)上的数据同步到一个或多个从实例(slave)上，默认情况
下复制是异步进行的，从库也不需要一直连接到主库来同步数据
MySQL复制的数据粒度可以是主实例上所有的数据库，也可以是指定的一个或多个数据库
，也可以是一个数据库里的指定的表
MySQL复制所带来的优势在于：
扩展能力：通过复制功能可以将MySQL的性能压力分担到一个或多个slave上。这要求所有
的写操作和修改操作都必须在Master上完成，而读操作可以被分配到一个或多个slave上。
将读写分离到不同服务器执行之后， MySQL的读写性能得到提升
数据库备份：由于从实例是同步主实例的数据，所以可以将备份作业部署到从库
数据分析和报表：同样，一些数据分析和报表的实现可以在从实例执行，以减少对主库的
性能影响
容灾能力：可以在物理距离较远的另一个数据中心建立一个slave，保证在主实例所在地区
遭遇灾难时，在另一个数据中心能快速恢复
MySQL复制有两种方法：
传统方式： 基于主库的bin-log将日志事件和事件位置复制到从库，从库再加以应用来达到主从同步的目的
Gtid方式： global transaction identifiers是基于事务来复制数据，因此也就不依赖日志文件，同时又能更好的保证主从库数据一致性
MySQL复制有多种类型：
异步复制：一个主库，一个或多个从库，数据异步同步到从库
同步复制：在MySQL Cluster中特有的复制方式
半同步复制：在异步复制的基础上，确保任何一个主库上的事务在提交之前至少有一个从库已经收到该事务并日志记录下来
延迟复制：在异步复制的基础上，人为设定主库和从库的数据同步延迟时间，即保证数据延迟至少是这个参数
复制的工作原理简单来说是数据库修改事件记录到bin log中并传递到slave，然后slave在本地还原的过程。 而事件记录到bin log的格式会有所不同。
MySQL复制有三种核心格式:
基于语句的复制(statement based replication)： 基于主库将SQL语句写入到bin log中完成复制
基于行数据的复制(row based replication)： 基于主库将每一个行数据变化的信息作为事件写入到bin log中完成日志 (默认使用)
混合复制(mixed based replication)： 上述两者的结合。 默认情况下优先使用基于语句的复制，只有当部分语句如果基于语句复制不安全的情况下才会自动切换为基于行数据的复制
MySQL基于binlog的复制：
主从复制构建
mysql 5.7 版本
| ip | host |
| 192.168.44.128 | mysql-master01 |
| 192.168.44.131 | mysql-slave01 |
从以下步骤
1. 主库开启bin-log，并且指定一个唯一的server-id，重启数据库
在配置文件/etc/my.cnf中添加
[mysqld] log-bin=mysql-bin server-id=1
在同一个复制组下的所有实例的server_id都必须是唯一的，而且取值必须是正整数，取值范围是1~(232)−1确保主库的my.cnf中skip-networking参数为非开启状态， 否则会导致主从库不能通信而复制失败
mysql> show variables like 'skip_networking'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | skip_networking | OFF | +-----------------+-------+ 1 row in set (0.01 sec) # 跳过网络通信 mysql> show variables like '%log_bin%'; +---------------------------------+---------------------------------------+ | Variable_name | Value | +---------------------------------+---------------------------------------+ | log_bin | ON | | log_bin_basename | /usr/local/mysql/data/mysql-bin | | log_bin_index | /usr/local/mysql/data/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+---------------------------------------+ 6 rows in set (0.00 sec)
2. 在主库创建一个专门用来做复制的数据库用户，这样所有从库都可以用这个用户来连接主库，也可以确保这个用户只有复制的权限
虽然可以用任何拥有复制权限的MySQL用户来建立复制关系，但由于被使用的用户名和密码
会明文保存在备库的master.info文件中，所以为安全起见，最好是使用仅有复制权限的独立用户
mysql> CREATE USER 'repl'@'192.168.44.%' IDENTIFIED BY '123456'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.44.%';
3.从slave库验证远程连接主库是否正常
4.获取主库的日志信息
为了确保建立的备库能从正确的bin log位置开启复制，要首先获取主库的bin log信息，
包括当前的日志文件名和日志文件内的位置
5. 配置从库
在/etc/my.cnf 文件 跟主库的配置一致， 把server-id 该变一下就可以
6. 主库数据生成镜像并上传到从库
两种方式生成镜像，一种是用mysqldump，是innodb存储引擎推荐的方式；另一种是将数据文件从主库拷贝到从库，这种方式效率更高(省去了dump/import过程中insert语句执行导致的更新index的行为)， 但innodb不推荐使用
第一种:
shell> bin/mysqldump –all-databases –master-data -u root -p > dbdump.db ##mysqldump方
式导出所有数据库数据到dbdump.db文件， –master-data表示导出数据直接加上change master to参数以便备库使用
#进行锁表，防止写入数据 mysql> FLUSH TABLES WITH READ LOCK; ##主库上所有表加锁，停止修改 mysql> SHOW MASTER STATUS; ##获取主库的日志信息， file表示当前日志， position表示当前日志里的位置 mysql> drop table t; # ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock #在另一个会话窗口打开，锁表哪个窗口不要关闭 shell> mysqldump --all-databases --master-data -u root -p > dbdump.db #通过scp 方式拷贝到备库 ，可以看看这个文件vi 是否备份成功，里面都是sql语句 shell> scp dbdump.db 192.168.44.131:/root/
第二种：
如果使用文件拷贝的办法： 将主库临时关闭，并将相关文件拷贝到从库上
7. 从库应用主句的数据镜像
Mysqldump的镜像，通过source命令执行
mysql> source dbdump.db
8. 配置从库，搭建主从关系
从库配置唯一server-id，并重启mysql实例 ，其他配置文件一致 从库的bin log属性可以打开也可以不打开
从库指定主库的日志信息和链接信息
mysql> CHANGE MASTER TO MASTER_HOST=‘master_host_name’, ##主库的主机名 MASTER_PORT=port_number ##主库的端口号 MASTER_USER=‘replication_user_name’, ##复制的数据库用户名 MASTER_PASSWORD=‘replication_password’, ##复制的用户密码 MASTER_LOG_FILE=‘recorded_log_file_name’, ##主库的日志文件名 MASTER_LOG_POS=recorded_log_position; ##主库的日志文件位置 CHANGE MASTER TO MASTER_HOST='192.168.44.128', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154; 从库启动复制进程 mysql> START SLAVE;
9. 查看主备库复制是否正常：
在slave上执行show slave statusG命令
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.44.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 540
Relay_Log_File: mysql02-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes # IO 线程
Slave_SQL_Running: Yes # sql 线程
。。。。。。
10. 主库释放锁，验证主从复制， 主库随意添加数据
mysql> UNLOCK TABLES; #主库添加数据 mysql> insert init t1 values(4,456); mysql> select * from t1; +---+---------+ | a | message | +---+---------+ | 1 | testing | | 2 | table | | 3 | t1 | | 4 | 456 | +---+---------+ #在slave验证： mysql> select * from t1; +---+---------+ | a | message | +---+---------+ | 1 | testing | | 2 | table | | 3 | t1 | | 4 | 456 | +---+---------+ #成功
主从简单排错
Last_IO_Errno: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. 解决办法：删除备库的auto.cnf文件，重启mysql，生成新的UUID Last_IO_Errno: 2003 Last_IO_Error: error connecting to master 'repl@192.168.237.128:3306' - retry-time: 60 retries: 19 解决办法：备库连接主库失败，检查防火墙，用户密码端口是否设置正确
MYSQL 基于binlog的多slave环境
当第一个slave创建好之后，如果还想创建其他的slave，则可以直接使用先前使用的备份文件，
分别执行：
a) 在slave的my.cnf上分配新的server_id
b) 从库应用主库的数据镜像
a) 利用相同的change master命令将从库指定主库的日志信息和链接信息
c) Slave start
这样第二个slave也就创建起来了：
主库上执行：show processlist #就能看到几个复制关系
如果想在事后再增加一个slave，但之前的备份文件已经不存在，或者主库的日志文件已经被清除了的情况下，考虑使用如下办法：
在已经建立好的复制环境中新增一个从库，则不需要关闭主库复制数据，而是用已有的从库复制数据即可
a) 关闭现有的从库
service mysql stop
b) 拷贝从库的文件到新的从库，包括log文件和relay log文件，其中如果relay log使用了从库的主机名， 则需要调relay-log-index参数,master info和relay log info文件到新的从库
[root@mysql-slave01 mysql]# tar zcf data131.tar.gz data/ [root@mysql-slave01 mysql]# scp data131.tar.gz 192.168.44.132 #slave-02 #在slave02从库解压到mysql 数据目录 [root@mysql-salve03 data]# tar zxvf data131.tar.gz -C /usr/local/mysql/ [root@mysql-salve03 data]# rm -rf /usr/local/mysql/data/auto.cnf
c) 为新的从库分配一个唯一的server-id
vim /etc/my.cnf [mysqld] ... server-id=3 ... 启动mysql service mysql start
d) 新的从库启动slave进程
mysql> start slave; ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository #因为主机名一致，修改主机名，删除auto.cnf 是从mysql-slave01 拷贝需要 重置一下salve ，然后与主库建立关系 mysql> reset slave; Query OK, 0 rows affected (0.03 sec) mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.44.128', -> MASTER_PORT=3306, -> MASTER_USER='repl', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysql-bin.000003', -> MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.02 sec)
复制相关系统变量
相应的系统变量也可在配置文件修改让其生效
server_id：是必须设置在master和每个slave上的唯一标识ID，其取值范围是1~4294967295之间，且同一个复制组之内不能重复
server_uuid：server_uuid会在GTID复制中使用。当MySQL启动之后，会首先到数据文件目录下的auto.cnf中寻找是否有指定的server_uuid，如果没有找到，则自己生成一个server_uuid并保存到这个文件中 log_slave_updates：该参数用来控制是否将收到的主库的更新数据的语句也记录在slave自己的bin log中。正常情况下是不需要记录的，但如果是想创建级联复制关系，比如A -> B -> C，这其中B既要作为A的从库，也要作为C的主库，则需要既开启log-bin参数，也要开启log_slave_updates参数
relay-log：该参数用来指定relay-log文件的基础名称，默认的名称为host_name-relay-bin.xxxx，其中的xxxx结尾是依次递增的数字 replicate-do-db：该参数用来指定需要复制的数据库。
在基于语句复制的环境中，指定该参数之后，则slave的SQL thread进程只会应用在本数据库(当前session连接的当前所在的库)的对象相关的语句。如果有多个数据库需要复制，则这个参数要使用多次。但如果是涉及到跨库操作语句，则复制会丢失。
比如：
#基于语句的复制 replicate-do-db=sales # 只复制sales 这个数据库 USE prices; #用户切换prices库 UPDATE sales.january SET amount=amount+1000; #在prices库下修改 sales库下数据，主从复制会失败，丢失
在基于行复制的环境中，只要数据库对象是指定的库，则复制都能正常，比如上述update语句由于january表是属于sales库的，则slave会复制并应用，同样下面的语句在基于行复制的环境中也不会执行：
# 行的复制 USE sales; #切换到sales库 UPDATE prices.march SET amount=amount-25; #修改prices库下的数据 ，但因为只复制sales这个库下的数据，所以从库不会复制
在slave的my.cnf上设置replicate-do-db=test，重启mysql
查看从库的状态信息
• mysql> show slave statusG; • *************************** 1. row *************************** • Slave_IO_Running: Yes • Slave_SQL_Running: Yes • Replicate_Do_DB: test
查看当前数据库是基于什么语句的复制
mysql> show variables like '%binlog_format%'
-> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
另一个基于SQL语句复制和基于行复制的区别在于当语句中包含对多个数据库的表进行
操作时。比如设置replicate-do-db=db1,
例：
USE db1; UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20; 基于SQL语句的复制会将table1和table2都在备库修改，而基于行的复制只会在备库修改 table1表
USE db4; UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20; 而对于上述语句来说，基于SQL语句的复制不会在备库修改任何表，而基于行的复制会 在备库修改table1表
如果希望跨库的update语句在多个库上都起作用，可以使用replicate-do-table=db_name.tbl_name
replicate-ignore-db：该参数决定了忽略指定数据库的复制，其行为和replicate-do-db 正好相反
replicate-do-table=db_name.tbl_name： 通过该参数告知slave的SQL thread仅复制指定表上的数据。如果有多个表，则该参数要使用多次 例如联表修改
replicate-ignore-table=db_name.tbl_name：通过该参数告知slave的SQL thread将指定表上的数据过滤掉
replicate-wild-do-table=db_name.tbl_name： 通过该参数告知SQL的SQL thread仅复制符合匹配的表，可以使用_和%作为通配符。比如replicate-wild-dotable=foo%.bar%表示复制以foo打头的数据库下所有bar打头的表数据。如果是replicate-wild-do-table=foo%.%,则表示即复制foo打头的所有表的数据，也复制create/drop/alter database foo打头的命令
replicate-wild-ignore-table=db_name.tbl_name：通过该参数告知SQL的SQL thread过滤掉符合匹配的表
slave-parallel-workers: 该参数决定了slave上启动多个SQL thread线程来并行应用数据的。默认值是0代表不允许并行，取值范围可以是0~1024
[mysqld] slave-parallel-workers=5
skip-slave-start ：该参数决定了在MySQL启动时是否先不启动slave线程，即暂停复制
[mysqld] skip-slave-start=1
slave-parallel-type=type ：该参数决定了当启动了并行之后，采用什么粒度的并行方式。默认值database表示按照不同的数据库执行并行， LOGICAL_CLOCK则表示按照在binlog中的一组提交的事务作为并行粒度
slave-skip-errors=[err_code1,err_code2,…|all|ddl_exist_errors]：该参数决定了当slave的SQL thread执行过程中碰到何种错误时可以忽略并继续接下来的数据复制。正常情况下当有错误发生时，复制会停止而需要人工干预修复才能继续进行。 除非非常自信可以忽略某些错误，否则不要使用这个参数，不然会导致虽然复制执行正常，但其实内部的数据已经完全不一致
sql_slave_skip_counter代表在非GTID复制环境下，通过设置此参数来跳过多少个复制事件。设置完该参数并非立即生效，而是要等待下次start slave命令的执行生效，并将该参数再次设置为0
log-bin[=base_name] ：该参数表示是否开启binary log。默认情况下MySQL会使用host_name-bin.xxxx作为文件的名字，其中xxxx是以数字递增的后缀。如果该参数指定了base_name，则二进制文件会以base_name.xxxx来命名
binlog-do-db=db_name： 该参数决定了哪些库下的修改会被记录到bin log中。其行为与
replicate-do-db类型，在基于SQL语句复制的环境下，只记录在当前数据库下的修改。比如指定binlog-do-db=sales，一下语句不会被记录到bin log中：
binlog-do-db=sales USE prices; UPDATE sales.january SET amount=amount+1000;
而以下语句则会被记录到bin log中：
USE sales; UPDATE prices.discounts SET percentage = percentage + 10; 修改的是prices库
而基于行复制的环境下，只有属于指定数据库的语句才会被记录到bin log中。比如下面的语句会被记录：
USE prices; UPDATE sales.february SET amount=amount+100;
而下面的语句则不会被记录：
USE sales; UPDATE prices.march SET amount=amount-25; 修改的是price库的里面的东西
针对跨库的语句来说，行为和replicate-do-db相同
binlog-ignore-db=db_name：该参数决定了在bin log中忽略的数据库，其行为与replicate-ignore-db类型
binlog_format：该参数决定了bin log中记录的格式，可以是statement,row,mixed，分别
代表基于SQL语句的复制，基于行复制和基于混合复制。在5.7.7版本之前的默认设置是
statement， 在5.7.7及以后，则默认是row。 当设置为混合模式时，则优先使statement，只有当基于语句的复制无法保证复制的准确时会自动替换为row
检查复制状态方法
通过在slave上执行show slave statusG 来检查复制是否正常工作
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.44.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 154
Relay_Log_File: master02-relay-bin.000024
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
.....
Slave_IO_State：代表当前slave的状态
Slave_IO_Running：代表负责读取主库bin log的IO线程是否是运行状态， 正常情况下应
该是YES
Slave_SQL_Running：代表负责执行备库relay log的SQL线程是否是运行状态， 正常情
况下应该是YES
Last_IO_Error, Last_SQL_Error: 分别代表最后一次IO线程和SQL线程所发生的错误，
正常情况下应该是空代表没有错误
Seconds_Behind_Master：代表备库的SQL线程比主库的bin log晚多少秒。0代表目前
没有复制延迟
(Master_Log_file, Read_Master_Log_Pos)：表示IO线程在主库bin log中的坐标位置
(Relay_Master_Log_File, Exec_Master_Log_Pos)：表示SQL线程在主库bin log中的坐
标位置
(Relay_Log_File, Relay_Log_Pos)：表示SQL线程在备库relay log中的坐标位置
在主库可以通过执行show processlist命令查看主库的bin log日志生成进程
MySQL 复制格式
基于语句复制:
优势：
属于比较成熟的技术，得到广泛使用
当SQL语句会修改很多数据时，使用语句复制会比较节省空间
由于二进制文件中包含了所有的修改语句，所以可以用来做审计功能
劣势：
某些特定的修改语句在基于语句复制的环境中复制会有问题，比如：
语句中包含自定义函数或者不确定性的存储过程
某些特定(限定指定复制库或者表)的修改语句在基于语句复制的环境中复制会有问题，比如：
语句中包含自定义函数或者不确定性的存储过程
update/delete语句中包含Limit语句但不包含order by语句属于不确定性语句
一些函数比如rand(), sysdate()，version()等由于不确定性也会导致复制异常
每个导致复制异常的语句都会产生一个告警信息
[Warning] Statement is not safe to log in statement format.
基于行复制：
优势：
所有的数据库修改都可以被复制，是一种安全的方式
由于是行复制，所以某些语句在主库和从库上执行需要较少的lock
劣势：
当DML语句涉及到多行的修改时，则由于行复制会把每行的修改信息都记录下来，所以
bin log会很大，有可能会导致复制的延迟相比较语句复制要大
不能直接查看在备库中执行的SQL语句 建议仅使用InnoDB表做行复制，对MyISAM表的行复制有可能会导致复制异常
基于语句复制的二进制文件内容：
my.cnf下修改
log-bin=mysql-bin server_id=1 binlog-format=statement
执行命令：
[root@mysql-master01 data]# mysqlbinlog -v mysql-bin.000008 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #180829 15:37:52 server id 1 end_log_pos 123 CRC32 0x438bc252 Start: binlog v 4, server v 5.7.22-log created 180829 15:37:52 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' UE2GWw8BAAAAdwAAAHsAAAABAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABQTYZbEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AVLCi0M= '/*!*/; # at 123 #180829 15:37:52 server id 1 end_log_pos 154 CRC32 0xc585cc2f Previous-GTIDs # [empty] # at 154 #180829 15:39:04 server id 1 end_log_pos 219 CRC32 0xc977dddf Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #180829 15:39:04 server id 1 end_log_pos 335 CRC32 0x84bae1ca Query thread_id=2 exec_time=0 error_code=0 use `test`/*!*/; #进入到test库 SET TIMESTAMP=1535528344/*!*/; SET @@session.pseudo_thread_id=2/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=192/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table temp (id int , name char(45)) # 创建了一个表temp /*!*/; # at 335 #180829 15:39:39 server id 1 end_log_pos 400 CRC32 0xa34052ac Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 400 #180829 15:39:39 server id 1 end_log_pos 479 CRC32 0x89729188 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1535528379/*!*/; BEGIN /*!*/; # at 479 #180829 15:39:39 server id 1 end_log_pos 588 CRC32 0xcca6b322 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1535528379/*!*/; insert into temp values (1,'xixi') #插入了一条数据 /*!*/; # at 588 #180829 15:39:39 server id 1 end_log_pos 619 CRC32 0x4eeb446c Xid = 36 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
记录的都是操作的sql语句
基于行复制的二进制文件内容：
修改my.cnf 重启
log-bin=mysql-bin server_id=1 binlog-format=row
执行命令：mysqlbinlog -v 二进制文件
[root@mysql-master01 data]# mysqlbinlog -v mysql-bin.000009 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #180829 15:46:24 server id 1 end_log_pos 123 CRC32 0x31a27974 Start: binlog v 4, server v 5.7.22-log created 180829 15:46:24 at startup # Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ' UE+GWw8BAAAAdwAAAHsAAAABAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAABQT4ZbEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AXR5ojE= '/*!*/; # at 123 #180829 15:46:24 server id 1 end_log_pos 154 CRC32 0xd0cf8740 Previous-GTIDs # [empty] # at 154 #180829 15:47:40 server id 1 end_log_pos 219 CRC32 0x0adc70d3 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #180829 15:47:40 server id 1 end_log_pos 291 CRC32 0x1919b077 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1535528860/*!*/; SET @@session.pseudo_thread_id=4/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=192/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 291 #180829 15:47:40 server id 1 end_log_pos 341 CRC32 0xa90bf3dd Table_map: `test`.`temp` mapped to number 111 # at 341 #180829 15:47:40 server id 1 end_log_pos 384 CRC32 0x45a2384c Write_rows: table id 111 flags: STMT_END_F BINLOG ' nE+GWxMBAAAAMgAAAFUBAAAAAG8AAAAAAAEABHRlc3QABHRlbXAAAgP+Av6HA93zC6k= nE+GWx4BAAAAKwAAAIABAAAAAG8AAAAAAAEAAgAC//wCAAAAAnhpTDiiRQ== '/*!*/; ### INSERT INTO `test`.`temp` ### SET ### @1=2 ### @2='xi' # at 384 #180829 15:47:40 server id 1 end_log_pos 415 CRC32 0x2040ef76 Xid = 31 COMMIT/*!*/; # at 415 #180829 15:47:58 server id 1 end_log_pos 480 CRC32 0x76b138e4 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 480 #180829 15:47:58 server id 1 end_log_pos 552 CRC32 0x882090f6 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1535528878/*!*/; BEGIN /*!*/; # at 552 #180829 15:47:58 server id 1 end_log_pos 602 CRC32 0x1b285959 Table_map: `test`.`temp` mapped to number 111 #会记录表和库的映射关系而语句复制不会 # at 602 #180829 15:47:58 server id 1 end_log_pos 649 CRC32 0x3fc542ff Write_rows: table id 111 flags: STMT_END_F BINLOG ' rk+GWxMBAAAAMgAAAFoCAAAAAG8AAAAAAAEABHRlc3QABHRlbXAAAgP+Av6HA1lZKBs= rk+GWx4BAAAALwAAAIkCAAAAAG8AAAAAAAEAAgAC//wCAAAABnhpYW94af9CxT8= '/*!*/; ### INSERT INTO `test`.`temp` ### SET ### @1=2 ### @2='xiaoxi' # 插入数据 字段的第几列会通过@number 显示， @1表示第一列 # at 649 #180829 15:47:58 server id 1 end_log_pos 680 CRC32 0x72dce01d Xid = 34 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
基于语句复制缺点演示：
假如主库的temp表数据展示如下：temp 表没主键
#temp 表没主键 mysql> select * from temp; +------+------+ | id | name | +------+------+ | 2 | eee | | 3 | eee | | 4 | eee | | 5 | eee |
备库的temp数据展示如下：
mysql> select * from temp; +------+------+ | id | name | +------+------+ | 3 | eee | | 2 | eee | | 4 | eee | | 5 | eee |
主库执行delete from temp limit 1;
则主库删掉的数据是id=2的行，而备库删掉的是id=3的行。 导致主备库数据不一致
1.在statement环境下实验主从一个表数据不一致的情况下复制是否还能继续：
mysql> show variables like '%format%'; +---------------------------+------------------- | Variable_name | Value | +---------------------------+------------------- | binlog_format | STATEMENT |
从库执行：
mysql> delete from temp where id<10000;
主库执行：
mysql> delete from temp where id=1；
查看从库的同步状态依然正常：
查看从库的同步状态依然正常： mysql> show slave statusG *************************** 1. row *************************** Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Seconds_Behind_Master: 0
这时候主从数据复制已近不一致了
2. 在行复制环境下实验主从一个表数据不一致的情况下复制是否还能继续：
mysql> show variables like '%format%'; +---------------------------+-------------------+ | Variable_name | Value | +---------------------------+-------------------+ | binlog_format | ROW|
从库执行：
mysql> delete from temp where id=2；
查看从库的同步状态不正常：
mysql> show slave statusG *************************** 1. row *************************** Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: test Last_Errno: 1032 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 4 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 386. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
解决 办法：手工在备库增加这条数据，则同步再次正常：
Insert into temp values(2,’mike’); mysql> show slave statusG *************************** 1. row *************************** Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Skip_Counter: 0 Seconds_Behind_Master: 0
如果从库数据丢失的比较多，这时候mysqldump 单独恢复丢失的那几表 ，在进行同步
MySQL复制线程
MySQL复制涉及三个线程，其中一个在主库，另两个在从库
binlog dump thread：在主库创建，用来在从库链接过来时发送bin log的内容
slave io thread：在备库创建，用来连接主库并请求发送新的bin log内容。该线程读取主库的bin log dump线程发送的更新内容并将此内容复制到本地的relay log中
Slave sql thread：在备库创建，读取slave io线程在本地relay log中的内容并在本地执行内容中的事件
通过show processlist可以查看三个线程当前的状态信息：
在slave暂停复制的方法：
整体停止的方法： mysql> STOP SLAVE; 停止指定线程的方法： mysql> STOP SLAVE IO_THREAD; mysql> STOP SLAVE SQL_THREAD;
MySQL 复制使用场景
MySQL复制可以作为数据库备份的一种解决方案，由于主库的数据会复制到
备库，所以可以在备库执行数据库备份作业而不用影响主库的性能
在备库的备份通常有两种选择：
当数据库比较小时，可以采用mysqldump的方式。由于mysqldump出来的文
件内容是SQL语句，所以可以很方便的将其中的一部分复制出来应用到其他数
据库里。在执行mysqldump之前，为了保证数据的一致性，最好是把slave进
程停掉。
shell> mysqladmin stop-slave或者shell> mysql -e 'STOP SLAVE SQL_THREAD;’ shell> mysqldump --all-databases > fulldb.dump shell> mysqladmin start-slave
当数据库比较大时，采用mysqldump方式的效率不高，所以可以使用物理文件拷贝的方式。为了保证数据的一致性，物理备份需要将备库关闭
shell> mysqladmin shutdown shell> tar cf /tmp/dbbackup.tar ./data /etc/init.d/mysql.server start
MySQL复制可以用在主库和从库采用不同的存储引擎的情况下。这样做的目
的通常是在`主库和从库可以分别利用不同存储引擎的优势，比如在主库使用
InnoDB是为了事务功能，而从库使用MyISAM因为是只读操作而不需要事务
功能
当使用mysqldump方式来创建备库时，改变备库的表存储引擎的方式就是在应用dump文件之前先修改文件里的所有关于表存储引擎的地方
如果是使用文件拷贝的方式来创建备库时，则唯一修改备库表存储引擎的方式就是在启动备库之后使用alter table命令修改
mysql> STOP SLAVE; 执行ALTER TABLE ... ENGINE=‘engine_type'命令 mysql> START SLAVE;
在从库执行： mysql> alter table temp engine='myisam'; 在主库执行： mysql> insert into temp values(1,'a'); 在从库查看数据复制情况： mysql> select * from temp; +------+------+ | id | name | +------+------+ | 3 | eee | | 4 | eee | | 5 | eee | | 6 | f | | 1 | a | | 2 | b |
MySQL复制可以用来做负载均衡功能的水平扩展，最主要是将数据库的读压 ，这种情况
力分担到多个MySQL slave实例上适用在读多写少的环境中。比如
一个基本的WEB架构：
MySQL复制可以用在当需要将主库上的不同数据库复制到不同的slave上，以便在
不同的slave上执行不同的数据分析任务时。
可以在每个slave上配置不同的参数来约束复制过来的数据，通过replicate-wilddo-table参数或者replicate-do-db参数
slave1上应该配置参数replicate-wild-do-table=databaseA.% # databaseA下的所有表
slave2上应该配置参数replicate-wild-do-table=databaseB.%
slave3上应该配置参数replicate-wild-do-table=databaseC.%
每个slave其实是接收到完整的bin log日志，但在应用环节中会进行过滤，仅应用符合参数配置的事件
在配置完参数之后，通过mysqldump的方式将对应数据库在slave应用起来，再启动slave线程
MySQL延迟复制
改个参数就能实现延迟复制
延迟复制是指定从库对主库的延迟至少是指定的这个间隔时间，默认是0秒。
可以通过change master to命令来指定
CHANGE MASTER TO MASTER_DELAY = N;
其原理是从库收到主库的bin log之后，不是立即执行，而是等待指定的秒数之后再执行
延迟复制的使用场景比如： 确保在主库上被错误修改的数据能及时找回
测试在从库IO集中在恢复bin log过程中对应用程序的访问影响
保留一份若干天前的数据库状态，和当前状态可以做对比
show slave status中SQL_Delay值表明了设置的延迟时长
mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_Running: Yes Slave_SQL_Running: Yes SQL_Delay: 60
在slave上执行：
mysql> stop slave; mysql> CHANGE MASTER TO MASTER_DELAY = 60; mysql> start slave;
MySQL复制主从切换
如果是使用GTID的复制方式，可以使用mysqlfailover工具做主从复制状态的监控和自动切换；如果是使用非GTID模式，则需要使用其他的方式做监控和切换
当新的master产生之后，需要通过在其他slave上执行change master to语句来对应到新的master上。 slave不会检查自己的数据库和新的master上是否一致，而是直接获取master上的二进制日志并继续自己的复制功能新当选master的实例需要运行在log_bin模式下
如果是使用GTID的复制方式，可以使用mysqlfailover工具做主从复制状态的
监控和自动切换；
如果是使用非GTID模式，则需要使用其他的方式做监控和切换
当新的master产生之后，需要通过在其他slave上执行change master to语句来对应到新的master上。 slave不会检查自己的数据库和新的master上是否一致，而是直接获取master上的二进制日志并继续自己的复制功能 新当选master的实例需要运行在log_bin模式下
新的master上开启log-bin=mysql-bin
Master上查看bin log信息
mysql> show master status; +------------------+----------+--------------+------------------+---------------- ---+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+ ------------------+----------+--------------+------------------+---------------- ---+ | mysql-bin.000001 | 154 | | |
在slave上执行：
mysql> reset slave all; Query OK, 0 rows affected (0.01 sec) mysql> stop slave; mysql> mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.44.130', -> MASTER_PORT=3306, -> MASTER_USER='repl', -> MASTER_PASSWORD='mysql', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=154; mysql> start slave;
MySQL半同步复制
默认创建的MySQL复制是异步的，意味着主库将数据库修改事件写入到自己的bin log，而并不知道从库是否获取了这些事件并应用在自己身上。 所以当主库崩溃导致要主从切换时，有可能从库上的数据不是最新的
从5.7版本开始MySQL通过扩展的方式支持了半同步复制
当主库执行一个更新操作事务时，提交操作会被阻止直到至少有一个半同步的复制slave确认已经接收到本次更新操作，主库的提交操作才会继续
半同步复制的slave发送确认消息只会在本次更新操作记录已经记录到本地的relay log之后
如果没有任何slave发送确认消息而导致超时时，半同步复制会转换成异步复制
半同步复制会对MySQL性能产生影响，因为主库的提交动作只有在收到至少一个从库的确认消息之后才能执行。但这个功能是性能和数据可靠性方面的权衡
搭建半同步复制
rpl_semi_sync_master_wait_point参数用来控制半同步复制的行为：
AFTER_SYNC：默认值
AFTER_COMMIT
需要配置的系统参数包括：
rpl_semi_sync_master_enabled：在主库配置，确保主库的半同步复制功能开启
rpl_semi_sync_master_timeout：配置主库等待多少毫秒时间来保证接收备库的确认消息，当超过这个时间时，半同步变成异步方式
rpl_semi_sync_slave_enabled：在从库配置，确保从库的半同步复制功能开启
半同步复制是通过插件的方式建立，要分别在主库和从库安装一个插件
前提条件：
5.5版本及以上 have_dynamic_loading参数必须是YES代表可以安装插件并动态加载事先建立好异步复制关系
相关的插件安装文件会在plugin_dir文件夹下，并以semisync_master和semisync_slave名字打头 可以find 一下这个名字
主库上安装插件： MYSQL> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so'; 在每个从库上安装插件： MYSQL> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 查看插件的安装情况： mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%'; +----------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +----------------------+---------------+ | rpl_semi_sync_master | ACTIVE | +———————————+---------------+ 在主库上开启半同步复制： SET GLOBAL rpl_semi_sync_master_enabled = 1; SET GLOBAL rpl_semi_sync_master_timeout = N; ##N是毫秒，默认是10000，代表10秒 在备库上开启半同步复制： SET GLOBAL rpl_semi_sync_slave_enabled =1； 在备库上重启slave进程: STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
半同步复制监控参数：
Rpl_semi_sync_master_clients：检查半同步的slave个数
Rpl_semi_sync_master_status：1表示主库的半同步功能开启并且运行正常,0表示主库的半同步功能关闭或者半同步复制已经变成了异步复制
Rpl_semi_sync_master_no_tx：表示有多少提交没有收到slave的确认消息
Rpl_semi_sync_master_yes_tx：表示有多少个提交收到了slave的确认消息
Rpl_semi_sync_slave_status：1表示备库上slave功能开启并且运行正常，0表示功能为开启或者运行异常
通过命令mysql> SHOW STATUS LIKE ‘Rpl_semi_sync%’;查看各个参数的状态
从库关闭IO线程 mysql> STOP SLAVE IO_THREAD; Query OK, 0 rows affected (0.00 sec) • 主库执行update数据操作，需要等待10秒才能返回 mysql> update temp2 set name='ddd' where id=12; • 超时返回之后，从库的半同步状态变成OFF状态 mysql> show status like '%Rpl_semi%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | OFF | • 当从库同步正常后，半同步状态显示正常 mysql> START SLAVE IO_THREAD; Query OK, 0 rows affected (0.00 sec) mysql> show status like '%Rpl_semi%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Rpl_semi_sync_slave_status | ON | +----------------------------+-------+
当有两个从库都开启半同步复制时，停止其中一个的slave IO线程，再在主
库上执行插入，操作很快返回
mysql> insert into temp2 values(131,'a'); Query OK, 1 row affected (0.00 sec)
当把第二个从库的slave IO线程关闭时，则主库插入数据需要等待10秒才能返回
mysql> insert into temp2 values(132,‘a’); ##等待10秒
MySQL基于GTID的复制
GTID(global transaction identifiers)复制是完全基于事务的复制，即每个在主库上执行的事务都会被分配一个唯一的全局ID并记录和应用在从库上
这种复制方式简化了建立slave和master/slave之间切换的工作，因为其完全不需要找当前执行的bin log和log中的位置完成切换
一个GTID是master上执行的任何commit事务所分配的全局唯一ID标示，其由两部分组成
GTID = source_id:transaction_id
Source_id代表主库的server_uuid，transaction_id代表事务按顺序提交的ID，比如第一个提交则是1，第十个提交的事务就是10GTID集合代表一组GTID
- 当一个事务在主库提交时，该事务就被赋予了一个GTID，并记录在主库的binary log
- 主库的binary log会被传输到从库的relay log中，从库读取此GTID并生成gtid_next系
统参数 - 从库验证此GTID并没有在自己的binary log中使用，则应用此事务在从库上
MySQL5.6的GTID复制模式， slave必须开启bin-log和log_slave_updates参数，否则启动就报错，因为需要在binlog找到同步复制的信息（UUID:事务号）
注：开启log_slave_updates参数，是把relay-log里的日志内容再记录到slave本地的binlog里。
但在MySQL5.7里， 官方做了调整，用一张gtid_executed系统表记录同步复制的信息
（UUID:事务号），这样就可以不用开启log_slave_updates参数，减少了从库的压力
从MySQL5.7.4版本开始， GTID会存放在mysql系统库的gtid_executed表中
mysql> desc gtid_executed; +----------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+------------+------+-----+---------+-------+ | source_uuid | char(36) | NO | PRI | NULL | | | interval_start | bigint(20) | NO | PRI | NULL | | | interval_end | bigint(20) | NO | | NULL | | +----------------+------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
mysql> show master status; +------------------+----------+--------------+------------------+-------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | + ------------------+----------+--------------+------------------+-------------------------------------------+ | mysql-bin.000004 | 475 | | | 9eae8f34-47b6-11e7-8087- 000c298d7ee3:1-25 | +------------------+----------+--------------+------------------+-------------------------------------------+ mysql> select * from mysql.gtid_executed; +--------------------------------------+----------------+--------------+ | source_uuid | interval_start | interval_end | +--------------------------------------+----------------+--------------+ | 9eae8f34-47b6-11e7-8087-000c298d7ee3 | 1 | 1 | | 9eae8f34-47b6-11e7-8087-000c298d7ee3 | 2 | 2 | ……… | 9eae8f34-47b6-11e7-8087-000c298d7ee3 | 23 | 23 | | 9eae8f34-47b6-11e7-8087-000c298d7ee3 | 24 | 24 | | 9eae8f34-47b6-11e7-8087-000c298d7ee3 | 25 | 25 | +--------------------------------------+----------------+--------------+
搭建GTIB的复制
创建复制流程
假定两个数据库实例间的主从关系已经通过传统模式创建好了
将主库和从库都设置为read only，确保两者之间的数据都完全同步
mysql> SET @@global.read_only = ON;
关闭主库和从库
shell> mysqladmin -uusername -p shutdown
设置主从库GTID后启动 并暂时关闭slave进程
[mysqld] gtid-mode=on enforce-gtid-consistency=on skip-slave-start=1 Enforce-gtid-consistency参数是确保只有对gtid复制机制安全的语句才会被log
重新设置主从库的复制关系
mysql> CHANGE MASTER TO MASTER_HOST = host, MASTER_PORT = port, MASTER_USER = user, MASTER_PASSWORD = password, MASTER_AUTO_POSITION = 1;
启动slave进程
mysql> START SLAVE;
关闭主库和从库的read only模式
mysql> SET @@global.read_only = OFF;
Master上执行： mysql> insert into temp values(3,'c'); Query OK, 1 row affected (0.00 sec) Slave上执行： mysql> select * from temp; +------+------+ | id | name | +------+------+ | 1 | a | | 2 | b | | 3 | c | mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.44.128 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 414 Relay_Log_File: vmware1-relay-bin.000002 Relay_Log_Pos: 627 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Last_Errno: 0 Last_Error: Retrieved_Gtid_Set: 9eae8f34-47b6-11e7-8087-000c298d7ee3:1 Executed_Gtid_Set: 9eae8f34-47b6-11e7-8087-000c298d7ee3:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
使用GTID复制的限制条件：
由于GTID复制是依赖于事务的，所以MySQL的一些属性不支持
当一个事务中既包含对InnoDB表的操作，也包含对非事务型存储引擎表(MyISAM)的操作时，就会导致一个事务中可能会产生多个GTID的情况；或者是当master和slave的表使用的存储引擎不一样时，都会导致GTID复制功能不正常
create table…select语句在基于语句复制的环境中是不安全的，在基于行复制
的环境中，此语句会被拆分成两个事件，一是创建表，二是insert数据，在某
些情况下这两个事件会被分配相同的GTID，而导致insert数据的操作被忽略，
所以GTID复制不支持create table … select语句
create/drop temporary table语句在GTID复制环境中不能放在事务中执行，
只能单独执行，并且autocommit要开启
sql_slave_skip_counter语句是不支持的，如果想要跳过事务，可以使用gtid_executed变量
如下：
mysql> create table temp2 select * from temp; ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT. mysql> create table temp2(id int,name varchar(10)) engine=myisam; Query OK, 0 rows affected (0.02 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into temp2 select * from temp; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update temp set name='abc'; Query OK, 3 rows affected (0.02 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> insert into temp2 select * from temp; ERROR 1785 (HY000): Statement violates GTID consistency: Updates to nontransactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.