12-mysql主从同步复制-01

mysql主从同步复制

MySQL的主从复制

读写分离
复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
复制的功用
- 负载均衡读操作
- 备份
- 高可用和故障切换
- 数据分布
- MySQL升级

主从复制架构

一主一从架构一主多从架构

主主复制架构主主+级联复制架构

主从复制原理

主从复制相关线程

主节点：
- dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
- I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
- SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
- master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
- relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
- mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志
注意：MySQL8.0 取消 master.info 和 relay-log.info文件

复制需要考虑二进制日志事件记录格式

STATEMENT（5.0之前）, Mariadb5.5 默认使用此格式
ROW（5.1之后，推荐）,MySQL 8.0 默认使用此格式
MIXED: Mariadb10.3 默认使用此格式

实现主从复制配置

官网参考

https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.7/en/replication-configuration.html
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html
https://mariadb.com/kb/en/library/setting-up-replication/

主节点配置：

启用二进制日志

[mysqld]
log_bin

为当前节点设置一个全局惟一的ID号

[mysqld]
server-id=#
log-basename=master  #可选项，设置datadir中日志名称，确保不依赖主机名

说明：

server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1
0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此
slave的连接

查看从二进制日志的文件和位置开始进行复制

SHOW MASTER STATUS;
show master logs;

创建有复制权限的用户账号

GRANT REPLICATION SLAVE  ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
#MySQL8.0 分成两步实现
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';

从节点配置

启动中继日志

[mysqld]
server_id=# #为当前节点设置一个全局惟的ID号，需与主节点不同
log-bin
read_only=ON #设置数据库只读，针对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index  #默认值hostname-relay-bin.index

使用有复制权限的用户账号连接至主服务器，并启动复制线程

CHANGE MASTER TO MASTER_HOST='masterhost', 
MASTER_USER='repluser',
MASTER_PORT=3306, 
MASTER_PASSWORD='replpass', 
MASTER_LOG_FILE='mariadb-bin.xxxxxx', 
MASTER_LOG_POS=#;

START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS\G;

新建主从复制与级联复制

主从复制

主节点56.11

#主节点
dnf -y install mariadb-server
vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin
systemctl restart mariadb
mysql
#查看二进制文件和位置
MariaDB [(none)]> show master logs;
#创建复制用户
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.56.%' identified by 'zhongguo'; 
#如果是MySQL 8.0 需要分成下面两步实现
mysql>create user 'repluser'@'192.168.56.%';
mysql>grant replication slave on *.* to 'repluser'@'192.168.56.%';

从节点56.13

dnf -y install mariadb-server
vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18  
systemctl restart mariadb
mysql

MariaDB [(none)]>CHANGE MASTER TO MASTER_HOST='192.168.56.11',
MASTER_USER='repluser',
MASTER_PORT=3306,
MASTER_PASSWORD='zhongguo',
MASTER_LOG_FILE='mysql-bin.000013',
MASTER_LOG_POS=154;

start slave;
show slave status\G;

级联复制

需要在中间的从服务器启用以下配置，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制

[mysqld]
server-id=18
log_bin
log_slave_updates    #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加
read-only

master主节点56.15

#master：192.168.56.15
#级联slave：192.168.56.14
#slave：192.168.56.18

[root@web15 ~]#vim /data/mysql3306/etc/my.cnf
[mysqld]
server-id=15
log-bin  
[root@web15 ~]#systemctl restart mysql3306.service
[root@web15 ~]#mysql -u root -S /data/mysql3306/var/mysql.sock -P 3306
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.56.%' identified by 'zhongguo';
[root@web15 ~]#mysqldump -u root -S /data/mysql3306/var/mysql.sock -P 3306 -A -F --single-transaction --master-data=1 >/data/all.sql
[root@web15 ~]#scp /data/all.sql 192.168.56.14:/data
[root@web15 ~]#scp /data/all.sql 192.168.56.18:/data

级联slave节点56.14

[root@web14 ~]#vim /data/mysql3306/etc/my.cnf
[mysqld]
server-id=14
log-bin
read-only
log_slave_updates  #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加
[root@web14 ~]#systemctl restart mysql3306.service
#还原数据库
[root@web14 ~]#vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.56.15',
MASTER_USER='repluser',
MASTER_PASSWORD='zhongguo',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=523;

[root@web14 ~]#mysql -u root -S /data/mysql3306/var/mysql.sock -P 3306
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /data/all.sql
MariaDB [(none)]> show master logs;  #记录二进制位置，给第三个节点使用  
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> start slave;

slave从节点56.18

[root@web18 ~]#vim /data/mysql3306/etc/my.cnf
[mysqld]
server-id=18
read-only
[root@web18 ~]#systemctl restart mysql3306.service
[root@web18 ~]#vim /data/all.sql 
CHANGE MASTER TO
MASTER_HOST='192.168.56.14',
MASTER_USER='repluser',
MASTER_PASSWORD='zhongguo',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344; 
[root@web18 ~]#mysql -u root -S /data/mysql3306/var/mysql.sock -P 3306</data/all.sql
[root@web18 ~]#mysql -u root -S /data/mysql3306/var/mysql.sock -P 3306 -e 'start slave;'

范例：主服务器非新建时,主服务器运行一段时间后，新增从节点服务器

如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点
通过备份恢复数据至从服务器，复制起始位置为备份时，二进制日志文件及其POS
主节点56.15

#在主服务器完全备份
mysqldump -u root -S /data/mysql3306/var/mysql.sock -P 3306 -A -F --single-transaction --master-data=1 >/home/nameke/backup/fullbackup_`date +%F_%H`.sql
ll /home/nameke/backup/

#将完全备份拷贝到新的从节点
scp -P 52113 /home/nameke/backup/fullbackup_`date +%F_%H`.sql nameke@192.168.56.14:/home/nameke/backup/

#建议优化主、从节点服务器的性能
set global innodb_flush_log_at_trx_commit=2;
set global sync_binlog=0;
show variables like 'innodb_flush_log_at_trx_commit';
show variables like 'sync_binlog';

从节点56.16

dnf -y install mariadb-server
vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=16
read-only
systemctl restart mariadb
#配置从节点，从完全备份的位置之后开始复制
grep '^CHANGE MASTER' /home/nameke/backup/fullbackup_`date +%F_%H`.sql

#编辑从节点的备份文件，添加POS还原位置信息
vim /home/nameke/backup/fullbackup_`date +%F_%H`.sql
CHANGE MASTER TO MASTER_HOST='192.168.56.15',
MASTER_USER='repluser',
MASTER_PORT=3306,
MASTER_PASSWORD='zhongguo',
MASTER_LOG_FILE='mariadb-bin.000003',
MASTER_LOG_POS=389;

#从节点导入还原数据
mysql -u root -S /data/mysql3306/var/mysql.sock -P 3306 </home/nameke/backup/fullbackup_`date +%F_%H`.sql
#启动从节点sql线程
start slave;
show slave status\G;

注意：如果新节点是虚拟机克隆的，新增节点做主从同步复制，可能有server_uuid/server_id重复的报错问题，解决办法：http://www.wjhsh.net/kiko2014551511-p-11648521.html

主从复制相关

1、限制从服务器为只读
read_only=ON
#注意：此限制对拥有SUPER权限的用户均无效

2、以下命令会阻止所有用户, 包括主服务器复制的更新
FLUSH TABLES WITH READ LOCK;

3、在从节点清除信息
#以下都需要先 STOP SLAVE
RESET SLAVE;
#从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE  ALL;
#清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和 PASSWORD 等

4、在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID
注意: Centos 8.1以上版本上的MariaDB10.3主从节点同时建同名的库和表不会冲突，建主键记录会产生冲突
#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL

START SLAVE 语句，指定执到特定的点

START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS =
log_pos
START SLAVE [SQL_THREAD] UNTIL   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS =
log_pos
thread_types:
    [thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD

保证主从复制的事务安全

参看https://mariadb.com/kb/en/library/server-system-variables/

1、在master节点启用参数：
sync_binlog=1
#每次写后立即同步二进制日志到磁盘，性能差
#如果用到的为InnoDB存储引擎：
innodb_flush_log_at_trx_commit=1
#每次事务提交立即同步日志写磁盘
sync_master_info=#
#次事件后master.info同步到磁盘

2、在slave节点启用服务器选项：
skip-slave-start=ON #不自动启动slave

3、在slave节点启用参数：
sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘

实战案例：当master服务器宕机，提升一个slave成为新的master

#1、找到哪个从节点的数据库是最新，让它成为新master
[root@centos8 ~]#cat /var/lib/mysql/relay-log.info 
5
./mariadb-relay-bin.000002
1180
mysql-bin.000002
996
0

#2、新master修改配置文件，关闭read-only配置
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
read-only=OFF
log-bin=/data/mysql/logbin/mysql-bin 
#3、清除旧的master复制信息
MariaDB [hellodb]>set global read_only=off;
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;

#4、在新master上完全备份
[root@slave1 ~]#mysqldump -A --single-transaction --master-data=1 -F >backup.sql
[root@slave1 ~]#scp backup.sql 10.0.0.28:
#5、分析旧的master 的二进制日志，将未同步到至新master的二进制日志导出来，恢复到新master,尽可能恢复数据

#6、其它所有 slave 重新还原数据库，指向新的master
[root@slave2 ~]#vim backup.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=371;

7、还原数据库并进行主从复制
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;
MariaDB [hellodb]>set sql_log_bin=off;
MariaDB [hellodb]>source backup.sql;
MariaDB [hellodb]>set sql_log_bin=on;
MariaDB [hellodb]>start slave;

主主复制

主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id

auto_increment_offset=1   #开始点
auto_increment_increment=2 #增长幅度

另一个节点使用偶数id

auto_increment_offset=2
auto_increment_increment=2

主主复制的配置步骤：

(1) 各节点使用一个惟一server_id

(2) 都启动binary log和relay log

(3) 创建拥有复制权限的用户账号

(4) 定义自动增长id字段的数值范围各为奇偶

(5) 均把对方指定为主节点，并启动复制线程

在第一个master节点上实现

[root@master1 ~]#vim /data/mysql3306/etc/my.cnf 
[mysqld]
server-id=11
log-bin
auto_increment_offset=1         #开始点
auto_increment_increment=2      #增长幅度   
[root@master1 ~]#systemctl start mysql3306.service
[root@master1 ~]#mysql -u root -S /data/mysql3306/var/mysql.sock -P 3306
#登录数据库后执行
show master logs;
grant replication slave on *.* to repluser@'192.168.56.%' identified by 'zhongguo';

在第二个master节点上实现

[rootmaster2 ~]#vim /data/mysql3306/etc/my.cnf 
[mysqld]                                                                         
server-id=13
log-bin
auto_increment_offset=2         #开始点
auto_increment_increment=2      #增长幅度
[root@master2 ~]#systemctl start mysql3306.service
[root@master2 ~]#mysql -u root -S /data/mysql3306/var/mysql.sock -P 3306

#登录数据库后执行
CHANGE MASTER TO MASTER_HOST='192.168.56.11',
MASTER_USER='repluser',
MASTER_PASSWORD='zhongguo',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000xxx',
MASTER_LOG_POS=386;

start slave;
show master logs; #查看二进制位置

在第一个master节点上实现

CHANGE MASTER TO MASTER_HOST='192.168.56.13',
MASTER_USER='repluser',
MASTER_PASSWORD='zhongguo',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000xxxx',
MASTER_LOG_POS=344;

start slave;
create database db1;
use db1;
create table t1(id int auto_increment primary key,name char(10));
#两个节点分别插入数据
#在第一个节点上执行insert t1 (name) values('user1');
#在第二个节点上执行
insert t1 (name) values('user2');
#两个节点同时插入数据
insert t1 (name) values('userX');
select * from t1;


#两个节点同时创建数据库，发生复制冲突
create database db2;
show slave status\G;

posted @ 2022-08-02 01:56 西瓜的春天阅读(65) 评论(0) 收藏举报

刷新页面返回顶部

西瓜的春天

斯人若彩虹，遇上方知有

12-mysql主从同步复制-01

mysql主从同步复制

MySQL的主从复制

主从复制架构

一主一从架构 一主多从架构

主主复制架构 主主+级联复制架构

主从复制原理

主从复制相关线程

复制需要考虑二进制日志事件记录格式

实现主从复制配置

主节点配置：

从节点配置

新建主从复制与级联复制

主从复制

级联复制

范例：主服务器非新建时,主服务器运行一段时间后，新增从节点服务器

主从复制相关

START SLAVE 语句，指定执到特定的点

保证主从复制的事务安全

实战案例：当master服务器宕机，提升一个slave成为新的master

主主复制

主主复制的配置步骤：

一主一从架构一主多从架构

主主复制架构主主+级联复制架构