MySQL主从复制

目录

MySQL主从复制

将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上，然后将这些日志重新执行（重做）一遍；从而使得从数据库的数据与主数据库保持一致。

1、MySQL 主从复制的基本介绍

MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。

MySQL复制是基于主服务器在二进制日志中跟踪所有对数据库的更改。因此，要进行复制，必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器已经记录到日志的数据。

当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。

2、什么是主从复制

简单来说，是使用两个或两个以上相同的数据库，将一个数据库当做主数据库，而另一个数据库当做从数据库。在主数据库中进行相应操作时，从数据库记录下所有主数据库的操作，使其二者一模一样。

MySQL数据库主从复制主要分为三步：

  1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）。

  2. slave的io线程将master的binary log events拷贝到它的中继日志(relay log)。

  3. slave的sql线程解析中继日志中的事件并在从库执行，保持与主库一致。

Binary log：主数据库的二进制日志。

Relay log：从服务器的中继日志。

注意：复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

1、从库准备

  1. 从库change master to 时，ip port user password binlog position写入到master.info进行记录。
  2. 从库 start slave 时，会启动IO线程和SQL线程。

2、同步的过程

  1. 从库的IO线程，读取master.info信息，获取主库信息并连接主库。

  2. 主库接收从库的链接请求后，会生成一个准备binlog DUMP的线程，来响应从库。

  3. 主库一旦有新的日志生成，会发送“信号”给主库的binlog dump线程，然后binlog dump线程会读取binlog日志的更新。

  4. 通过binlog dump线程j将数据传送给从库的IO线程。

  5. IO线程将收到的日志存储到了TCP/IP 缓存。

  6. 写入TCP/IP缓存后，立即返回ACK消息给主库 ，此时主库工作完成。

  7. IO线程更新master.info文件、binlog 文件名和postion定位。

  8. IO线程将缓存中的数据，存储到relay-log日志文件,此时io线程工作完成。

  9. 从库SQL线程读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点。

  10. 从库SQL线程基于从步骤9中获取到的起点，去中继日志relay-log.000001获取后续操作，在从库回放relay-log中继日志之中内从主库复制过来的数据。

  11. SQL线程回放完成之后，会更新relay-log.info文件，把当前操作的位置记入，作为下一次操作的起点。

  12. relay-log会有自动清理的功能。

注：在tcp协议中通讯之前都要经过三次握手，请求方发出一个syn信号请求连接，对方收到并接受的时候就会发出ack消息，ack就是回应的意思。

3、主从复制的方式

MySQL的主从复制有两种复制方式，分别是异步复制和半同步复制。

1、异步复制

我们之前介绍的就是异步复制，即客户端线程提交一个写操作，写入主库的binlog日志后就立即返回，并不需要等待从库完成同步操作，而主库的dump线程会监测binlog日志的变量然后主动将更新推送给从库。

MySQL 主从复制默认是异步的模式。

1.1、主从复制实现

要实现主从复制，需要如下几步：

1、在主库上创建一个用于复制的账号。

2、修改主库配置文件，开启主库的Binlog，并设置server-id和重启。

3、导出主库中所有的数据，先导给从库

4、修改从库配置文件并重启

5、配置主从复制

6、开启主从复制

  1. 在主库上创建一个用于复制的账号
mysql> grant replication slave on *.* to 'shanhe'@'%' identified by '123456';
mysql> flush privileges;
  1. 修改主库配置文件
[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
  1. 导出主库中所有的数据，先导给从库
[root@mysql-1 ~]# mysqldump -uroot -p123456 -A -E -R --triggers --master-data=2 --single-transaction > /tmp/all.sql
  1. 将数据导入从库
[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
  1. 修改从库配置文件并重启
[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
  1. 配置主从复制

配置主从复制，首先得在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的一个插件来实现的，主从库使用的插件不一样。

  1. 先确认主从的MySQL服务器是否支持动态增加插件
mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.08 sec)
  1. 分别在主从库上安装对用插件
-- 主库安装插件
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)
  1. 在主库开启半同步复制
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
  1. 在从库开启半同步复制
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
  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双主（主主）架构方案思路

  1. 两台mysql都可读写，互为主备，默认只使用一台（masterA）负责数据的写入，另一台（masterB）备用。

  2. masterA是masterB的主库，masterB又是masterA的主库，它们互为主从。

  3. 两台主库之间做高可用,可以采用keepalived等方案（使用VIP对外提供服务）。

  4. 所有提供服务的从服务器与masterB进行主从同步（双主多从）。

  5. 建议采用高可用策略的时候，masterA或masterB均不因宕机恢复后而抢占VIP（非抢占模式）。

这样做可以在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台主库上（尽可能减少主库宕机对业务造成的影响），减少了主从同步给线上主库带来的压力；但是也有几个不足的地方:

  1. masterB可能会一直处于空闲状态。
  2. 主库后面提供服务的从库要等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 ~]# grep password /var/log/mysqld.log 

-- 使用临时密码登陆数据库
[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)

# 'masterB节点操作与上述操作一致~~，当然除了初始密码不一样。。。'

3.6、开始配置主从复制

-- 1. 先查看master1上的信息
'如果masterB上需要连接，则需要此处的file以及position信息'
mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000002
         Position: 1616
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set: f6be1c0e-30d0-11ec-afbb-000c29e0e250:1-7
1 row in set (0.00 sec)

-- 2. 接着查看master2上的信息
mysql> show master status\G
*************************** 1. row ***************************
             File: binlog.000002
         Position: 1459
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set: fe9529ac-30d0-11ec-8a3c-000c297a2694:1-6
1 row in set (0.00 sec)

-- 在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、查看连接结果

-- 查看masterB的信息  
mysql> show slave status\G
'基本上IO线程和SQL线程都出现yes即为ok'

3.8、测试双主

-- 1. masterA执行如下操作
mysql> create database db01;
Query OK, 1 row affected (0.00 sec)

mysql> use db01;
Database changed
mysql> create table t1(id int primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(100);
Query OK, 1 row affected (0.07 sec)

mysql> select * from t1;
+-----+
| id  |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)

-- 2. masterB查看t1表是否有内容
mysql> select * from db01.t1;
+-----+
| id  |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)


-- 3. masterB插入一条新数据
mysql> insert into db01.t1 values(200);
Query OK, 1 row affected (0.01 sec)


-- 4. masterA查看数据是否同步完成
mysql> select * from t1;
+-----+
| id  |
+-----+
| 100 |
| 200 |
+-----+
2 rows in set (0.00 sec)
'END'

题解决
#在第八步查看连接结果时，最初显示状态，两个线程的状态都为NO不可用

1. 错误日志并没有error信息
2. 经过网络上看，发现大多数是由于数据库的uuid相同，而导致触发此异常
3. 而我一检查，发现我两台数据库uuid并不一样，但是 ！！！ 本机由于前面的原因，有两个auto.cnf文件(这两个文件的uuid一致...)
	'test02机器也是一样的~'
    [root@test01 ~]# find / -name auto.cnf
    /usr/local/mysql-5.7.35/data/auto.cnf
    /data/auto.cnf
4. 对比一下/etc/my.cnf，确定新的auto.cnf文件是在data目录下
5. 删除了旧的auto.cnf文件，重启数据库再次查看即为yes

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
    }
}

# 两台机器开启keepalived
 systemctl start keepalived

# navicat连接设置的vip进行测试

posted @ 2021-10-19 08:53  小丶凡  阅读(345)  评论(0)  编辑  收藏  举报
1