一、原理
主从复制架构图:
主从复制原理:
Mysql 中有一种日志叫做 bin 日志(二进制日志)。这个日志会记录下所有修改了数据库的SQL 语句(insert,update,delete,create/alter/drop table, grant 等等)。
主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍,这样从服务器上的数据就和主服务器上的数据相同了。所以主服务器要开启二进制bin.log日志文件
主从复制过程:
MySQL使用3个线程来执行复制功能(其中1个在主服务器上,另2个在从服务器上)
1)主服务器log Dump线程。Slave上面的IO线程连接上 Master,请求从指定日志文件的指定位置之后的日志内容,Master 接收请求内容后,返回给 Slave 端的 IO 线程。
2)从服务器I/O线程。slave服务器收到bin-log日志内容,将bin-log日志内容写入relay-log中继日志,创建一个master.info的文件,该文件记录了master ip 用户名 密码 master bin-log名称,bin-log position。
3)从服务器SQL线程。Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后,会马上解析该 Log 文件中的内容,并在自身执行这些 Query。
主从复制好处:
1、数据分布(data distribution)
2、负载均衡(load balancing)
3、备份(backups)
4、高可用和和故障切换(high availability and failover)
Binlog日志的三种模式
1、Row Level模式:日志中会记录成每一行数据修改的形式,然后在slave端再对相同的数据进行修改。
优点:1、记录详细。2、解决statement level模式无法解决的复制问题。
缺点:日志量大,因为是按行来拆分。
2、Statement Level模式:每一条修改数据的sql都会记录到master的bin_log中,slave在复制的时候sql进程会解析成master端执行过的相同的sql在slave库上再次执行。
优点:1、解决了row level的缺点,不需要记录每一行的变化。2、日志量少,节约IO,从库应用日志块。
缺点:一些新功能同步可能会有障碍,比如函数、触发器等。
3、Mixed模式(混合模式):实际上就是前两种模式的结合,在mixed模式下,mysql会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也是在statement和row之间选择一种。
新版本中的mysql中对row level模式也做了优化,并不是所有的修改都会以row level来记录,像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,那么还是会记录所有行的变更。
显示当前模式
mysql> show variables like "%binlog_format%";
更改当前模式立即生效
mysql> set global binlog_format='MIXED';
主从架构模式
一主一从、主主、一主多从、多主一从
主从同步复制方式
1、同步复制,master的变化,必须等待slave-1,slave-2,...,slave-n完成后才能返回。
2、异步复制,master只需要完成自己的数据库操作即可,至于slaves是否收到二进制日志,是否完成操作,不用关心。MYSQL的默认设置。
3、半同步复制,master只保证slaves中的一个操作成功,就返回,其他slave不管。这个功能,是由google为MYSQL引入的。
二、实战
测试环境:vmware、centos7、mysql5.7.33
要求:
1、数据库版本一致,或者从数据库不低于主数据库版本
2、从数据库初始化最好与主库数据一致,不然容易出现问题(可以把主库数据的完全备份拷贝到本机执行恢复)
主节点:192.168.1.34
从节点:192.168.1.35
配置主节点
1)主节点数据库master配置文件修改:
server-id=1 #必须,并且主从数据库id不能相同 log-bin=master-bin #必须,开启二进制日志
skip_name_resolve=ON #可选,启动动mysqld来禁用DNS主机名查找。只可以使用MySQL中的授权表中的IP,比如‘localhost’要更改为127.0.0.1,不然域名无法解析。
innodb-file-per-table =ON
binlog_format=mixed #可选,二进制文件的格式
binlog-do-db=test #可选,设置master对哪些表不做记录(我没选)
binlog-ignore-db=mysq #可选,设置master对哪些库不做记录(我没选,这两个参数不选代表全库都复制)
sync-binlog=1 #可选,每次执行写入就与硬盘同步...
2)登录从节点上数据库
mysql>GRANT REPLICATION SLAVE ON *.* to master@'%' identified by '123456'; #建立帐户并授权远程登录 mysql>flush privileges; #刷新权限
mysql>show master status; #显示主机状态。记住,二进制文件的名称和位置,从数据库连接要用!
systemctl restart mysqld 重启mysql服务器
主服务器配置完成。
注意:确保主服务器的iptables没有阻断3306的访问端口。
配置从节点
1)从节点数据库master配置文件修改:
server-id=2 #必须,并且主从数据库id不能相同
relay-log=relay-log #必须,开启中继日志
read_only = ON #可选,此限制对拥有SUPER权限 的用户均无效
skip_name_resolve=ON
master-connect-retry=30 #这个选项控制重试间隔,默认为60秒
slave-skip-errors=1007,1008,1053,1062,1213,1158,1159 #可选,这个是在同步过程中忽略掉的错误,这些错误不会影响数据的完整性,有事经常出现的错误,一般设置忽略。其中1062为主键重复错误。
2)登录主节点上数据库中创建有复制权限的用户。
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.34',MASTER_USER=master,MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=0;
# 执行同步SQL语句(主服务器ip地址,密码,二进制文件的名称,位置)
mysql>start slave; #启动从数据库
mysql>show slave status\G; #查看slave状态
replicate-do-db=test #可选,对slave不需要复制的数据库(我没选)
replicate-ignore-db=mysq #可选,对slave不需要复制的数据库(我没选)
重要的指标为:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Log_File: bin-log.003
Relay_Master_Log_File: bin-log.003
Read_Master_Log_Pos: 4
Exec_master_log_pos: 4
Seconds_Behind_Master: 0(5.0之前版本没有这个选项)
以上选项是两两对应的,只要结果是一致的,就说明主从同步成功。
可能遇到的问题
1、主从复制失败原因查找顺序
- 检查主节点防火墙,selinux,数据库状态。
- 网络通信问题,使用ping命令检查;
- 复制授权的用户名、密码、端口号、地址有问题,在从节点服务器检测能否连接数据库 [root@mysql-slave ~]# mysql -umaster -h192.168.1.34 -p
- MySQL自动解析,会将连接的IP解析成主机名(skip-name-resolve=0)写入my.cnf文件即可。
- 从库IO异常关闭,通过show slave status\G;进行查看错误码
2、从库同步出错,有时跳过某个无法执行的命令, 设置参数 set global sql_slave_skip_counter=N中的N是指跳过N个event。
STOP SLAVE; SET GLOBAL sql_slave_skip_counter =1; #表示跳过一步错误,后面的数字可变 START SLAVE;
说明:N最好设置1,效果跳过是下一个事务。假如跳过第N个event后,位置若刚好落在一个事务内部,则会跳过这整个事务,一个insert/update/delete不一定只对应一个event,由引擎和日志格式决定,最好能在master的binglog上查看一下跳过的evnet到底做了写什么。
3、mysql5.7及以上版本增加了个uuid值,默认情况下在data目录下有个auto.cnf文件中,如果用镜像方式安装的mysql服务器,server-uuid应该是一样的,所以需要将auto.cnf删掉,再重启自动生成一个新的uuid值。uuid相同主从复制会出现问题
4、从节点要设置某些限定使得它不能进行写操作,才能保证复制当中的数据一致。限制从服务器为只读,在从服务器上设置:read_only = ON,但是此限制对拥有SUPER权限 的用户均无效。
阻止所有用户:mysq>FLUSH TABLES WITH READ LOCK;
5、master_info_repository和relay_log_info_repository:把主从服务器的信息存储到innodb表中,默认情况下是存储到文件系统中的,这样如果从服务器出现宕机,则很容易出现文件记录和实际同步信息不同步的情况。而把相关信息存储到表中,可以利用innodb丰富的恢复机制保证记录数据的一致性
6、如何保证主从复制时的事物安全
- 在主节点设置参数
MySQL提供一个sync_binlog=N 参数来控制数据库执行N次写入后,与硬盘同步。
sync_binlog=0,表示MySQL不控制binlog的刷新,由文件系统自己控制它的缓存的刷新。这时候的性能是最好的,风险最大。一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。
sync_binlog=1,最安全,表示每次事务提交,MySQL都会把binlog刷下去,是最安全但是性能损耗最大的设置。对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。
所以很多MySQL DBA设置的sync_binlog并不是最安全的1,而是100或者是0。这样牺牲一定的一致性,可以获得更高的并发和性能。
如果用到innode 存储引擎:
innodb_flush_logs_at_trx_commit=ON(刷写日志:在事务提交时,要将内存中跟事务相关的数据立即刷写到事务日志中去。)
innodb_support_xa=ON (分布式事务:基于它来做两段式提交功能)
- 在每个slave节点
skip_slave_start =ON (跳过自动启动,使用手动启动。)
relay_log也会在内从中先缓存,然后在同步到relay_log中去,可以使用下面参数使其立即同步。
sync_relay_log =1 ,默认为10000,即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新,交由OS的cache控制。
sync_relay_log_info=1每间隔多少事务刷新relay-log.info,如果是table(innodb)设置无效,每个事务都会更新
注: 在从节点中 master.info是记录在主节点复制位置的文件。
...