MySQL主从复制
将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上,然后将这些日志重新执行(重做)一遍;从而使得从数据库的数据与主数据库保持一致。
1、MySQL 主从复制的基本介绍
MySQL支持单向、异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。
MySQL复制是基于主服务器在二进制日志中跟踪所有对数据库的更改。因此,要进行复制,必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到日志的数据。
当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,并在本机上执行相同的更新。然后*并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器,在备份过程中主服务器可以继续处理更新。
2、什么是主从复制
简单来说,是使用两个或两个以上相同的数据库,将一个数据库当做主数据库,而另一个数据库当做从数据库。在主数据库中进行相应操作时,从数据库记录下所有主数据库的操作,使其二者一模一样。
MySQL数据库主从复制主要分为三步:
-
master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events)。
-
slave的io线程将master的binary log events拷贝到它的中继日志(relay log)。
-
slave的sql线程解析中继日志中的事件并在从库执行,保持与主库一致。
Binary log:主数据库的二进制日志。
Relay log:从服务器的中继日志。
注意:复制过程有一个很重要的限制——复制在slave上是串行化的,也就是说master上的并行更新操作不能在slave上并行操作。
1、从库准备
- 从库change master to 时,ip port user password binlog position写入到master.info进行记录。
- 从库 start slave 时,会启动IO线程和SQL线程。
2、同步的过程
-
从库的IO线程,读取master.info信息,获取主库信息并连接主库。
-
主库接收从库的链接请求后,会生成一个准备binlog DUMP的线程,来响应从库。
-
主库一旦有新的日志生成,会发送“信号”给主库的binlog dump线程,然后binlog dump线程会读取binlog日志的更新。
-
通过binlog dump线程j将数据传送给从库的IO线程。
-
IO线程将收到的日志存储到了TCP/IP 缓存。
-
写入TCP/IP缓存后,立即返回ACK消息给主库 ,此时主库工作完成。
-
IO线程更新master.info文件、binlog 文件名和postion定位。
-
IO线程将缓存中的数据,存储到relay-log日志文件,此时io线程工作完成。
-
从库SQL线程读取relay-log.info文件,获取到上次执行到的relay-log的位置,作为起点。
-
从库SQL线程基于从步骤9中获取到的起点,去中继日志relay-log.000001获取后续操作,在从库回放relay-log中继日志之中内从主库复制过来的数据。
-
SQL线程回放完成之后,会更新relay-log.info文件,把当前操作的位置记入,作为下一次操作的起点。
-
relay-log会有自动清理的功能。
注:在tcp协议中通讯之前都要经过三次握手,请求方发出一个syn信号请求连接,对方收到并接受的时候就会发出ack消息,ack就是回应的意思。
3、主从复制的方式
MySQL的主从复制有两种复制方式,分别是异步复制和半同步复制。
1、异步复制
我们之前介绍的就是异步复制,即客户端线程提交一个写操作,写入主库的binlog日志后就立即返回,并不需要等待从库完成同步操作,而主库的dump线程会监测binlog日志的变量然后主动将更新推送给从库。
MySQL 主从复制默认是异步的模式。
1.1、主从复制实现
要实现主从复制,需要如下几步:
1、在主库上创建一个用于复制的账号。
2、修改主库配置文件,开启主库的Binlog,并设置server-id和重启。
3、导出主库中所有的数据,先导给从库
4、修改从库配置文件并重启
5、配置主从复制
6、开启主从复制
- 在主库上创建一个用于复制的账号
mysql> grant replication slave on *.* to 'shanhe'@'%' identified by '123456';
mysql> flush privileges;
- 修改主库配置文件
[root@mysql-1 ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data
port=3306
socket=/usr/local/mysql/mysql.sock
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/tmp/mysqld.pid
server-id=1
log-bin=/data/log-bin/binlog
sync_binlog=1
binlog_format=row
expire_logs_days=7
max_binlog_size=100m
binlog_cache_size=4m
max_binlog_cache_size=512m
binlog-ignore-db=mysql
auto-increment-offset=1
auto-increment-increment=1
slave-skip-errors=all
[mysql]
socket=/usr/local/mysql/mysql.sock
[client]
socket=/usr/local/mysql/mysql.sock
# 重启
[root@mysql-1 ~]# systemctl restart mysqld
- 导出主库中所有的数据,先导给从库
[root@mysql-1 ~]# mysqldump -uroot -p123456 -A -E -R --triggers --master-data=2 --single-transaction > /tmp/all.sql
- 将数据导入从库
[root@mysql-1 ~]# scp /tmp/all.sql root@192.168.15.62:/tmp/
root@192.168.15.62''s password:
all.sql 100% 853KB 3.5MB/s 00:00
[root@mysql-2 mysql]# mysql -uroot -p123456 < /tmp/all.sql
- 修改从库配置文件并重启
[root@mysql-2 mysql]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data
port=3306
socket=/usr/local/mysql/mysql.sock
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/tmp/mysqld.pid
server-id=2
# 中继日志
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
[mysql]
socket=/usr/local/mysql/mysql.sock
[client]
socket=/usr/local/mysql/mysql.sock
[root@mysql-2 mysql]# systemctl restart mysqld
- 配置主从复制
配置主从复制,首先得在MySQL Master节点查出binlog日志状态,然后配置主从复制
- 在MySQL Master节点查出binlog日志状态
mysql> show master status ;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 154 | | mysql | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 在从库配置主从复制
# 首先测试主库是否连接正常
[root@mysql-2 mysql]# mysql -uroot -p123456 -h192.168.15.61
[root@slave1 ~]# mysql -uroot -p123456 # 登录然后执行
change master to
master_host='192.168.15.61', -- 库服务器的IP
master_port=3306, -- 主库端口
master_user='shanhe', -- 主库用于复制的用户
master_password='123456', -- 密码
master_log_file='binlog.000001', -- 主库日志名
master_log_pos=154; -- 主库日志偏移量,即从何处开始复制
- 查看主从复制结果
2、半同步复制
介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。
半同步复制超时则会切换回异步复制,正常后则切回半同步复制
在半同步复制时,如果主库的一个事务提交成功了,在推送到从库的过程当中,从库宕机了或网络故障,导致从库并没有接收到这个事务的Binlog,此时主库会等待一段时间(这个时间由rpl_semi_sync_master_timeout的毫秒数决定),如果这个时间过后还无法推送到从库,那MySQL会自动从半同步复制切换为异步复制,当从库恢复正常连接到主库后,主库又会自动切换回半同步复制。
2.1、部署半同步复制
半同步模式是作为MySQL5.5的一个插件来实现的,主从库使用的插件不一样。
- 先确认主从的MySQL服务器是否支持动态增加插件
mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.08 sec)
- 分别在主从库上安装对用插件
-- 主库安装插件
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.28 sec)
-- 从库安装插件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.20 sec)
- 在主库开启半同步复制
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.09 sec)
mysql> set global rpl_semi_sync_master_timeout=30000;
Query OK, 0 rows affected (0.00 sec)
# 添加到配置文件
[root@mysql-1 ~]# vim /etc/my.cnf
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
- 在从库开启半同步复制
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.07 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.09 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.06 sec)
# 添加到配置文件之中
[root@mysql-2 ~]# vim /etc/my.cnf
rpl_semi_sync_slave_enabled =1
- 在主库上查看半同步复制的状态
-- 主库查看
mysql> show status like 'Rpl_semi_sync_master_status';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
-- 从库查看
mysql> show status like 'Rpl_semi_sync_slave_status';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)
这两个变量常用来监控主从是否运行在半同步复制模式下。
3、多主多从
在企业中,数据库高可用一直是企业的重中之重,中小企业很多都是使用mysql主从方案,一主多从,读写分离等,但是单主存在单点故障,从库切换成主库需要作改动。因此,如果是双主或者多主,就会增加mysql入口,增加高可用。不过多主需要考虑自增长ID问题,这个需要特别设置配置文件,比如双主,可以使用奇偶,总之,主之间设置自增长ID相互不冲突就能完美解决自增长ID冲突问题。
3.1、MySQL双主(主主)架构方案思路
-
两台mysql都可读写,互为主备,默认只使用一台(masterA)负责数据的写入,另一台(masterB)备用。
-
masterA是masterB的主库,masterB又是masterA的主库,它们互为主从。
-
两台主库之间做高可用,可以采用keepalived等方案(使用VIP对外提供服务)。
-
所有提供服务的从服务器与masterB进行主从同步(双主多从)。
-
建议采用高可用策略的时候,masterA或masterB均不因宕机恢复后而抢占VIP(非抢占模式)。
这样做可以在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台主库上(尽可能减少主库宕机对业务造成的影响),减少了主从同步给线上主库带来的压力;但是也有几个不足的地方:
- masterB可能会一直处于空闲状态。
- 主库后面提供服务的从库要等masterB先同步完了数据后才能去masterB上去同步数据,这样可能会造成一定程度的同步延时。
3.2、修改主节点的配置文件
[mysqld]
basedir=/usr/local/mysql
datadir=/data
port=3306
socket=/usr/local/mysql/mysql.sock
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/tmp/mysqld.pid
# 节点ID,确保唯一
server-id = 1
#开启mysql的binlog日志功能
log-bin=binlog
#控制数据库的binlog刷到磁盘上去 , 0 不控制,性能最好,1每次事物提交都会刷到日志文件中,性能最差,最安全
sync_binlog=1
#binlog日志格式
binlog_format=row
#binlog过期清理时间
expire_logs_days=7
#binlog每个日志文件大小
max_binlog_size=100m
#binlog缓存大小
binlog_cache_size=4m
#最大binlog缓存大小
max_binlog_cache_size=512m
#不生成日志文件的数据库,多个忽略数据库可以用逗号拼接,或者 复制黏贴下述配置项,写多行
binlog-ignore-db=mysql
# 表中自增字段每次的偏移量
auto-increment-offset=1
# 表中自增字段每次的自增量
auto-increment-increment=2
#跳过从库错误
slave-skip-errors=all
#将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=file
relay-log-info-repository=file
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
max_binlog_size=1024M
# 忽略同步的数据库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
max_connections=3000
max_connect_errors=30
#忽略应用程序想要设置的其他字符集
skip-character-set-client-handshake
#连接时执行的SQL
init-connect='SET NAMES utf8'
#服务端默认字符集
character-set-server=utf8
#请求的最大连接时间
wait_timeout=1800
#和上一参数同时修改才会生效
interactive_timeout=1800
#sql模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_allowed_packet=10M
bulk_insert_buffer_size=8M
query_cache_type=1
query_cache_size=128M
query_cache_limit=4M
key_buffer_size=256M
read_buffer_size=16K
# 禁用反向解析
skip-name-resolve
slow_query_log=1
long_query_time=6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=16M
[mysql]
socket=/usr/local/mysql/mysql.sock
[client]
socket=/usr/local/mysql/mysql.sock
3.3、修改备节点的配置文件
[mysqld]
basedir=/usr/local/mysql
datadir=/data
port=3306
socket=/usr/local/mysql/mysql.sock
character-set-server=utf8
log-error=/var/log/mysqld.log
pid-file=/tmp/mysqld.pid
# 节点ID,确保唯一
server-id=2
#开启mysql的binlog日志功能
log-bin=binlog
#控制数据库的binlog刷到磁盘上去 , 0 不控制,性能最好,1每次事物提交都会刷到日志文件中,性能最差,最安全
sync_binlog=1
#binlog日志格式
binlog_format=row
#binlog过期清理时间
expire_logs_days=7
#binlog每个日志文件大小
max_binlog_size=100m
#binlog缓存大小
binlog_cache_size=4m
#最大binlog缓存大小
max_binlog_cache_size=512m
#不生成日志文件的数据库,多个忽略数据库可以用逗号拼接,或者 复制黏贴下述配置项,写多行
binlog-ignore-db=mysql
# 表中自增字段每次的偏移量
auto-increment-offset=2
# 表中自增字段每次的自增量
auto-increment-increment=2
#跳过从库错误
slave-skip-errors=all
#将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=file
relay-log-info-repository=file
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
max_binlog_size=1024M
# 忽略同步的数据库
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
max_connections=3000
max_connect_errors=30
#忽略应用程序想要设置的其他字符集
skip-character-set-client-handshake
#连接时执行的SQL
init-connect='SET NAMES utf8'
#服务端默认字符集
character-set-server=utf8
#请求的最大连接时间
wait_timeout=1800
#和上一参数同时修改才会生效
interactive_timeout=1800
#sql模式
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_allowed_packet=10M
bulk_insert_buffer_size=8M
query_cache_type=1
query_cache_size=128M
query_cache_limit=4M
key_buffer_size=256M
read_buffer_size=16K
# 禁用反向解析
skip-name-resolve
slow_query_log=1
long_query_time=6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=16M
[mysql]
socket=/usr/local/mysql/mysql.sock
[client]
socket=/usr/local/mysql/mysql.sock
3.4、两个master阶段都必须重新初始化数据库
[root@mysql-1 ~]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data
[root@mysql-2 ~]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data
3.5、分别登陆数据库并创建复制账号(每个机器上都必须执行)
-- 使用临时密码登陆数据库
[root@mysql-2 ~]# mysql -uroot -p'_<r,zjMpG6-.'
-- 修改数据库临时密码
mysql> alter user root@localhost identified by 'Test123!';
Query OK, 0 rows affected (0.00 sec)
-- 利用数据库临时密码创建登陆
[root@mysql-2 ~]# mysql -uroot -p'Test123!'
-- 创建远程连接账号
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.08 sec)
-- 删除其他默认密码
mysql> delete from mysql.user where host = 'localhost';
Query OK, 3 rows affected (0.29 sec)
-- 刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.09 sec)
-- 重新登陆并创建远程复制账号
[root@mysql-2 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'shanhe'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.6、开始配置主从复制
-- 在master2上执行
mysql> change master to
-> master_host='192.168.15.61',
-> master_port=3306,
-> master_user='shanhe',
-> master_password='123456',
-> master_log_file='binlog.000002',
-> master_log_pos=1459;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
-- 在master1上执行
mysql> change master to
-> master_host='192.168.15.62',
-> master_port=3306,
-> master_user='shanhe',
-> master_password='123456',
-> master_log_file='binlog.000002',
-> master_log_pos=1459;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
3.7、查看连接结果
3.8、测试双主
3.9、双主高可用
高可用是使用keepalived实现VIP。从而实现一个IP无感知操作两个主节点。
1、安装keepalived高可用软件(两个节点上全都安装)
[root@mysql-1 ~]# yum install keepalived -y
[root@mysql-2 ~]# yum install keepalived -y
2、修改keepalived的配置文件
[root@mysql-1 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id LVS_DEVEL
}
vrrp_script chk_kubernetes {
script "/etc/keepalived/check_kubernetes.sh"
interval 2
weight -5
fall 3
rise 2
}
vrrp_instance VI_1 {
state MASTER
interface eth0
mcast_src_ip 192.168.15.61 # 所在节点的IP
virtual_router_id 51
priority 100
advert_int 2
authentication {
auth_type PASS
auth_pass K8SHA_KA_AUTH
}
virtual_ipaddress {
192.168.15.60
}
}