9.主从复制

主从复制

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的复制

基于binary log的复制是指主库将修改操作写入到bin log中，从库负责读取主库的bin log，并在本地复制一份，然后将里面的操作在从库执行一遍

每个从库会保存目前读取主库日志的文件名和日志位置

主库和每个从库都必须有一个唯一ID，叫server-id配置在配置文件中

配置方法：

主库需要开启bin-log，并且指定一个唯一的server-id，重启数据库

[mysqld] 
log-bin=mysql-bin 
server-id=1

在同一个复制组下的所有实例的server_id都必须是唯一的，而且取值必须是正整数，取值范围是1~(232)−1确保主库的my.cnf中skip-networking参数为非开启状态，否则会导致主从库不能通信而复制失败(MySQL8.0中binlog是默认开启的，且server_id默认为1：)

注意：配置文件中的server-id参数的是横线，不是下划线，在数据库中对server_id参数进行查询时，是下划线，不是横线

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 |

mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value  |
+----------------+-------+
| server_id 	| 1 	 |

查看server_id的第二种方式
mysqladmin var | grep server_id

在主库创建一个专门用来做复制的数据库用户，这样所有从库都可以用这个用户来连接主库，也可以确保这个用户只有复制的权限

虽然可以用任何拥有复制权限的MySQL用户来建立复制关系，但由于被使用的用户名和密码会明文保存在备库的master.info文件中，所以为安全起见，最好是使用仅有复制权限的独立用户

CREATE USER 'repl'@'192.168.237.%' IDENTIFIED BY 'mysql';
 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.237.%';

从slave库验证远程连接主库是否正常

mysql -u repl -p -P 3308 -h 192.168.237.128

获取主库的日志信息

为了确保建立的备库能从正确的bin log位置开启复制，要首先获取主库的bin log信息，包括当前的日志文件名和日志文件内的位置

 FLUSH TABLES WITH READ LOCK; ##主库上所有表加锁，停止修改
 SHOW MASTER STATUS; ##获取主库的日志信息，file表示当前日志，position表示当前日志里的位置
 +------------------+----------+--------------+------------------+-------------------+
| File 				| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 619 	  |   			 | 					| 					 |
+------------------+----------+--------------+------------------+-------------------+

mysql> drop table temp;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

mysql> create database temp2;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

mysql> insert into temp values(now());
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock

主库数据生成镜像并上传到从库

两种方式生成镜像，一种是用mysqldump，是innodb存储引擎推荐的方式；另一种是将数据文件从主库拷贝到从库，这种方式效率更高(省去了dump/import过程中insert语句执行导致的更新index的行为)，但innodb不推荐使用

a) shell> bin/mysqldump --all-databases --master-data -u root -p -P 3308 > dbdump.db 
##mysqldump方式导出所有数据库数据到dbdump.db文件，--master-data表示导出数据直接加上change master to参数以便备库使用
a1)mysqldump -f -h -u --default-character-set=utf8 --single-transaction -R --triggers -q --all-databases --master-data | gzip >master.dmp.gz

b) 如果使用文件拷贝的办法
shell> tar cf /tmp/db.tar ./data

sftp 192.168.237.128

The authenticity of host '192.168.237.128 (192.168.237.128)' can't be established.
ECDSA key fingerprint is 64:db:17:91:08:ca:77:29:57:ac:b1:ff:b5:81:3d:db.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.237.128' (ECDSA) to the list of known hosts.
root@192.168.237.128's password: 
Connected to 192.168.237.128.
sftp> cd /usr/local/mysql/
sftp> lcd /usr/local/mysql
sftp> get dbdump.db
Fetching /usr/local/mysql/dbdump.db to dbdump.db
/usr/local/mysql/dbdump.db 100% 785KB 784.9KB/s 00:01 

sftp> exit

# 主库释放锁
UNLOCK TABLES;
#从库配置唯一server-id，并重启mysql实例
[mysqld] 
server-id=2

从库应用主库的数据镜像

Mysqldump的镜像，通过source命令执行原始文件拷贝的镜像，将文件复制到和主库相同的目录下

从库指定主库的日志信息和链接信息

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.237.128',
MASTER_PORT=3308,
MASTER_USER='repl',
MASTER_PASSWORD='mysql',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=801;

从库启动复制进程

 START SLAVE;

查看主备库复制是否正常：
- 在slave上执行show slave status\G命令

show slave status\G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
             Master_Host: 192.168.237.128
             Master_User: repl
             Master_Port: 3308
             Connect_Retry: 60
             Master_Log_File: mysql-bin.000001
             Read_Master_Log_Pos: 2667
             Relay_Log_File: vmware1-relay-bin.000003
             Relay_Log_Pos: 2186
             Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
            ……
             Last_Errno: 0
             Last_Error: 
            ……
             Last_IO_Errno: 0
             Last_IO_Error: 
             Last_SQL_Errno: 0
             Last_SQL_Error: 
            Seconds_Behind_Master: 0
…..
1 row in set (0.00 sec)

验证复制工作正常：基本方法是在主库添加任意数据，查看从库是否能查询到
在Master上执行：

 create table temp(id int,name varchar(10));
 
  insert into temp values(1,'a'),(2,'b');

在Slave上验证：

 select * from temp;

简单排错：
- 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
- - 解决办法：备库连接主库失败，检查防火墙，用户密码端口是否设置正确
- - 复制过程中由于备份之前没有lock全表而导致的数据复制异常
当第一个slave创建好之后，如果还想创建其他的slave，则可以直接使用先前使用的备份文件，分别执行：
- a) 在slave的my.cnf上分配新的server_id
- b) 从库应用主库的数据镜像
- a) 利用相同的change master命令将从库指定主库的日志信息和链接信息
- c) Slave start
这样第二个slave也就创建起来了

# 主库上执行
show processlist;
# 查看是否有从库的复制信息

MySQL基于binlog的多slave环境
- 如果想在事后再增加一个slave，但之前的备份文件已经不存在，或者主库的日志文件已经被清除了的情况下，考虑使用如下办法：
- - 在已经建立好的复制环境中新增一个从库，则不需要关闭主库复制数据，而是用已有的从库复制数据即可

a) 关闭现有的从库
mysqladmin shutdown
b) 拷贝从库的文件到新的从库，包括log文件和relay log文件，其中如果relay log使用了从库的主机名，则需要调relay-log-index参数

[root@vmware1 mysql]# tar -zcvf data.tar.gz data
[root@vmware1 mysql]# sftp 192.168.237.130
Connected to 192.168.237.130.
sftp> get data.tar.gz
tar –zxvf data.tar.gz
rm -rf data/auto.cnf

b) 拷贝master info和relay log info文件到新的从库
c) 为新的从库分配一个唯一的server-id
d) 新的从库启动slave进程

复制相关系统变量

server_id：是必须设置在master和每个slave上的唯一标识ID，其取值范围是1~4294967295之间，且同一个复制组之内不能重复
server_uuid：server_uuid会在GTID复制中使用。当MySQL启动之后，会首先到数据文件目录下的auto.cnf中寻找是否有指定的server_uuid，如果没有找到，则自己生成一个server_uuid并保存到这个文件中

[auto]
server_uuid=8a94f357-aab4-11df-86ab-c80aa9429562

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进程只会应用在本数据库下的对象相关的语句。如果有多个数据库需要复制，则这个参数要使用多次。但如果是涉及到跨库操作语句，则复制会丢失，比如：

replicate-do-db=sales
USE prices;
UPDATE sales.january SET amount=amount+1000;

- 在基于行复制的环境中，只要数据库对象是指定的库，则复制都能正常，比如上述update语句由于january表是属于sales库的，则slave会复制并应用，同样下面的语句在基于行复制的环境中也不会执行：

USE sales;
UPDATE prices.march SET amount=amount-25;

在slave的my.cnf上设置replicate-do-db=test，重启mysql
查看从库的状态信息：

show slave status\G;

*************************** 1. row ***************************
• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Replicate_Do_DB: test
• 在主库上执行: • mysql> use test;
• mysql> insert into temp values(5,'e');
• mysql> use test2;
• mysql> insert into temp values(13,'dd');
• 在备库上查看复制情况：
• mysql> use test;
• mysql> select * from temp; ##数据有复制
• +------+------+ 
• | id | name |
• +------+------+ 
• | 1 | a |
• | 2 | b |
• | 3 | c |
• | 4 | d |
• | 5 | e |
• +------+------+ • 5 rows in set (0.00 sec)
• mysql> use test2;
• mysql> select * from temp; ##数据没有复制
• +------+------+ 
• | id | name |
• +------+------+ 
• | 10 | aa |
• | 11 	 | bb |
• | 12 | cc |

在语句复制环境下查看对指定数据库的修改操作：

在语句复制环境下查看对指定数据库的修改操作：
• [mysqld] 
• binlog-format=statement
• 主库上执行：
• mysql> use test;
• mysql> update test2.temp set name='ddd';
• mysql> use test2;
• mysql> update test.temp set name='eee';
• 在从库上查看复制结果：
• mysql> use test;
• Reading table information for completion of table and column names
• You can turn off this feature to get a quicker startup with -A • Database changed
• mysql> select * from temp; ##虽然是指定的同步数据库但并没有同步
• +------+------+ 
• | id | name |
• +------+------+ 
• | 1 | abc | 
• | 2 | abc | 
• | 3 | abc | 
• | 4 | abc | 
• | 5 | abc | 
• +------+------+ 

• mysql> use test2;
• mysql> select * from temp; ##虽然不是指定的同步数据库但数据有同步
• +------+------+ 
• | id | name |
• +------+------+ 
• | 10 | ddd | 
• | 11 | ddd | 
• | 12 | ddd |

在行复制环境下查看对指定数据库的修改操作：

mysql> show variables like '%binlog_format%';
• +---------------+-------+ 
• | Variable_name | Value |
• +---------------+-------+ 
• | binlog_format | ROW |
• +---------------+-------+ 

• 主库上执行：
• mysql> use test;
• mysql> update test2.temp set name='bcd';
• mysql> use test2;
• mysql> update test.temp set name='abc';
• 在从库上查看复制结果：
• mysql> use test;
• mysql> select * from temp; ##数据已复制
• +------+------+ 
• | id | name |
• +------+------+ 
• | 1 | abc | 
• | 2 | abc | 
• | 3 | abc | 
• | 4 | abc | 
• | 5 | abc | 
• +------+------+ 

• mysql> use test2;
• mysql> select * from temp; ##数据未复制
• +------+------+ 
• | id | name |
• +------+------+ 
• | 10 | aa |
• | 11 | bb |
• | 12 | cc |
• +------+------+

另一个基于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-do-table=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过滤掉符合匹配的表
设置replicate-do-table参数，重启mysql：

[mysqld]
replicate-do-db=test
replicate-do-table=test.temp
mysql> show slave status\G
*************************** 1. row ***************************
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB: 
Replicate_Do_Table: test.temp
在主库上操作：
mysql> use test;
mysql> insert into temp values(6,'f');
mysql> insert into test2.temp values(14,'ff');
mysql> insert into temp2 values(4,'d'); 在从库上查看复制情况：
mysql> use test;
mysql> select * from temp; ##数据被复制过来
+------+------+
| id | name |
+------+------+
| 1 | abc |
| 2 | abc |
| 3 | abc |
| 4 | abc |
| 5 | abc |
| 6 | f |
mysql> select * from test2.temp; ##数据没有复制
+------+------+
| id | name |
+------+------+
| 10 | ddd |
| 11 | ddd |
| 12 | ddd |
mysql> select * from temp2; ##数据没有复制
+------+------+
| id | name |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |

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*中：

USE prices;
UPDATE sales.january SET amount=amount+1000; 而以下语句则会被记录到bin log中：
USE sales; UPDATE prices.discounts SET percentage = percentage + 10;
而基于行复制的环境下，只有属于指定数据的语句才会被记录到bin log中。比如下面的语句
会被记录：
USE prices; UPDATE sales.february SET amount=amount+100;
而下面的语句则不会被记录：
USE sales; UPDATE prices.march SET amount=amount-25;
针对跨库的语句来说，行为和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 status来检查复制是否正常工作
SHOW SLAVE STATUS\G

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> SHOW PROCESSLIST \G;
*************************** 4. row ***************************
 Id: 10
 User: root
 Host: slave1:58371
 db: NULL
Command: Binlog Dump
 Time: 777
 State: Has sent all binlog to slave; waiting for binlog to be updated
 Info: NULL

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表的行复制有可能会导致复制异常

基于语句复制的二进制文件内容：

log-bin=mysql-bin
server_id=1
binlog-format=statement
执行命令：/bin/mysqlbinlog data/mysql-bin.000002
# at 154
#170609 9:31:43 server id 1 end_log_pos 219 CRC32 0xe503603e Anonymous_GTID last_committed=0 sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#170609 9:31:43 server id 1 end_log_pos 302 CRC32 0x6c714c85 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1497000703/*!*/;
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=1342177280/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 302
#170609 9:31:43 server id 1 end_log_pos 414 CRC32 0x2962b9af Query thread_id=4 exec_time=0 error_code=0
use `course`/*!*/;
SET TIMESTAMP=1497000703/*!*/;
insert into temp values('a','abc')
/*!*/;
# at 414
#170609 9:31:43 server id 1 end_log_pos 445 CRC32 0x82c53726 Xid = 55
COMMIT/*!*/

基于行复制的二进制文件内容：

log-bin=mysql-bin
server_id=1
binlog-format=row
执行命令：bin/mysqlbinlog -v data /mysql-bin.000003
BEGIN
/*!*/;
# at 293
#170609 9:42:05 server id 1 end_log_pos 347 CRC32 0xa130fe8f Table_map: `course`.`temp` mapped to number 
141
# at 347
#170609 9:42:05 server id 1 end_log_pos 389 CRC32 0x8deeb544 Write_rows: table id 141 flags: STMT_END_F
BINLOG '
bW06WRMBAAAANgAAAFsBAAAAAI0AAAAAAAEABmNvdXJzZQAEdGVtcAACDw8ECgAKAAOP/jCh
bW06WR4BAAAAKgAAAIUBAAAAAI0AAAAAAAEAAgAC//wBYgNiY2REte6N
'/*!*/;
### INSERT INTO `course`.`temp`
### SET
### @1='b'
### @2='bcd'
# at 389
#170609 9:42:05 server id 1 end_log_pos 420 CRC32 0x0bf24c6a Xid = 51
COMMIT/*!*/;

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可以查看三个线程当前的状态信息：

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
 Id: 2
 User: root
 Host: localhost:32931
 db: NULL
Command: Binlog Dump
 Time: 94
 State: Has sent all binlog to slave; waiting for binlog to
 be updated
 Info: NULL
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
 Id: 10
 User: system user
 Host:
 db: NULL
Command: Connect
 Time: 11
 State: Waiting for master to send event
 Info: NULL
*************************** 2. row ***************************
 Id: 11
 User: system user
 Host:
 db: NULL
Command: Connect
 Time: 11
 State: Has read all relay log; waiting for the slave I/O
 thread to update it
 Info: NULL

在slave暂停复制的方法：
整体停止的方法：mysql> STOP SLAVE;
停止指定线程的方法：
mysql> STOP SLAVE IO_THREAD; 
mysql> STOP SLAVE SQL_THREAD;
整体开启的方法：mysql> START SLAVE;
开启指定线程的方法：
mysql> START SLAVE IO_THREAD; 
mysql> START 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复制可以用来做负载均衡功能的水平扩展，最主要是将数据库的读压力分担到多个MySQL slave实例上，这种情况适用在读多写少的环境中。比如一个基本的WEB架构：

MySQL复制可以用在当需要将主库上的不同数据库复制到不同的slave上，以便在不同的slave上执行不同的数据分析任务时。可以在每个slave上配置不同的参数来约束复制过来的数据，通过replicate-wild-do-table参数或者replicate-do-db参数

slave1上应该配置参数replicate-wild-do-table=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值表明了设置的延迟时长

在slave上执行：
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 60;
mysql> start slave;

在主库上执行：
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------
-----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
+------------------+----------+--------------+------------------+--------------------------------------
-----+
| mysql-bin.000003 | 3039 | | | 9eae8f34-47b6-11e7-8087-000c298d7ee3:1-23 |
+------------------+----------+--------------+------------------+--------------------------------------
-----+
1 row in set (0.00 sec)
mysql> insert into temp values(4,'d');
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------
-----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
+------------------+----------+--------------+------------------+--------------------------------------
-----+
| mysql-bin.000003 | 3318 | | | 9eae8f34-47b6-11e7-8087-000c298d7ee3:1-24 |
+------------------+----------+--------------+------------------+--------------------------------------
-----+

复制主从切换

如果是使用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.237.130',
 -> MASTER_PORT=3308,
 -> 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名字打头

# 主库上安装插件：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
# 在每个从库上安装插件：
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%';
 
# 在主库上开启半同步复制：
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;

# 主库执行update数据操作，需要等待10秒才能返回
mysql> update temp2 set name='ddd' where id=12;

# 超时返回之后，从库的半同步状态变成OFF状态
 show status like '%Rpl_semi%';
# 当从库同步正常后，半同步状态显示正常
START SLAVE IO_THREAD;

 show status like '%Rpl_semi%';
 
# 当有两个从库都开启半同步复制时，停止其中一个的slave IO线程，再在主库上执行插入，操作很快返回
mysql> insert into temp2 values(131,'a');

# 当把第二个从库的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，第十个提交的事务就是10

GTID集合代表一组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表中

CREATE TABLE gtid_executed ( source_uuid CHAR(36) NOT NULL, interval_start

BIGINT(20) NOT NULL, interval_end BIGINT(20) NOT NULL, PRIMARY KEY

(source_uuid, interval_start) )

 show master status;
 
 select * from mysql.gtid_executed;

创建复制流程

假定两个数据库实例间的主从关系已经通过传统模式创建好了
• 将主库和从库都设置为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');

# Slave上执行：
mysql> select * from temp;

mysql> show slave status\G

使用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变量

posted @ 2024-12-16 17:03 水一RAR 阅读(8) 评论(0) 编辑收藏举报

刷新页面返回顶部

水一RAR