mysql主从复制案例

主从复制案例

主从同步：

1）主节点做配置

vim /etc/my.cnf
[mysqld]
server-id=1
log-bin
port=3306
basedir=/opt/mariadb
datadir=/data/mariadb
socket=/data/mariadb/run/mysql.sock
log-error=/data/mariadb/run/mysql.log
pid-file=/data/mariadb/run/mysql.pid
skip-name-resolve=1
event-scheduler=1
userstat=1
query_cache_type=1
innodb_flush_log_at_trx_commit=2	#二进制日志记录模式改成数据型
sync_binlog=0		#二进制异步写磁盘

systemctl restart mariadb 

#导出全库，并把备份传给从节点
mysqldump -A --single-transaction --master-data=1 -F > all.sql
scp all.sql 2.2.2.22:/data

2）从节点配置

vim /etc/my.cnf
[mysqld]
server-id=2
log-bin
port=3306
basedir=/opt/mariadb
datadir=/data/mariadb
socket=/data/mariadb/run/mysql.sock
log-error=/data/mariadb/run/mysql.log
pid-file=/data/mariadb/run/mysql.pid
skip-name-resolve=1
read-only
innodb_flush_log_at_trx_commit=2	
sync_binlog=0	

systemctl restart mariadb

3）在从节点绑定master节点，并开启主从同步

方法1：适用于已经在使用中的主、从服务器，在文件中配置

#在刚刚传过来的备份文件中，找到：CHANGE MASTER TO MASTER_LOG_FILE..这一行，添加同步的相关信息
vim /data/all.sql
CHANGE MASTER TO
 MASTER_LOG_FILE='mysql-bin.000003',
 MASTER_LOG_POS=385,
 MASTER_HOST='2.2.2.12',
 MASTER_USER='copy',
 MASTER_PASSWORD='123456',
 MASTER_PORT=3306;

#注意临时关闭二进制日志记录
mysql
->set sql_log_bin=0;
  \. /data/all.sql
  start slave;
  show slave status\G;
  set sql_log_bin==1;

方法2：适用于新安装的主、从服务器，在命令行配置

mysql
->set sql_log_bin=0;
  CHANGE MASTER TO
   MASTER_LOG_FILE='mysql-bin.000003',
   MASTER_LOG_POS=385,
   MASTER_HOST='2.2.2.12',
   MASTER_USER='copy',
   MASTER_PASSWORD='123456',
   MASTER_PORT=3306;
  start slave;
  show slave status\G; 
  set sql_log_bin=1;

主从节点切换

master挂掉，slave上线
使用于一主多从的场景，要择优上任

1）选一个从节点，要求其数据与主节点最接近

方法1: 命令行查看

#每个从节点需要查看，选择与主节点数据差异最小的，也就是对比Read_Master_Log_Pos
show slave status\G;

方法2：文件中查看

cat /var/lib/mysql/relay-log.info

2）在选出来的最新从节点操作，上任master

#允许新数据写入
vim /etc/my.cnf
[mysqld]
read-only=0	#把原有的只读关掉

#清除原从节点信息
stop slave;
reset slave all;

3）新master备份数据，并整理旧master的数据

分析旧master的二进制日志，把未同步到新master的数据，导出来，恢复到新master，做到尽可能恢复最新数据

4）其他slave节点改连接信息，指向新masrer

#在新master节点查看当前二进制日志记录到哪里了
show master logs;		#记录新主节点的日志信息

#在所有slave节点更改连接
mysql
->set sql_log_bin=off;
  stop slave
  reset slave all;
  CHANGE MASTER TO
   MASTER_HOST='1.1.1.25',
   MASTER_USER='copy',
   MASTER_PASSWORD='123456',
   MASTER_PORT=3306
   MASTER_LOG_FILE='新主节点日志文件',
   MASTER_LOG_POS=889;
  set sql_log_bin=1;

主主复制：

两个节点，都可以更新数据，且互为主从

易出现数据不一致问题：

A为主，B也配置为主。当B作为A的从节点同步时，如果A有自增为：10，新插入成11。B主机发生意外写入，自增为：10，也是成11，那么两者的数据就相同，导致重复

解决方法：奇偶数分开写入

A节点使用奇数id

auto_increment_offset=1 #起点
auto_increment_increment=2 #步长

B节点使用偶数id

auto_increment_offset=2
auto_increment_increment=2

主主复制配置注意要点：

各节点使用一个惟一server_id
都启动binary log和relay log
创建拥有复制权限的用户账号
定义自动增长id字段的数值范围各为奇偶
均把对方指定为主节点，并启动复制线程

1）A主机修改配置

vim /etc/my.cnf
[mysqld]
server-id=1
log-bin
auto_increment_offset=1         #开始点
auto_increment_increment=2      #增长幅度

systemctl restart mariadb

方法一：用于新安装的数据库做主主复制

mysql -e 'show master logs'
mysql -e 'grant replication slave,replication client on *.* to copy@"%" identified by "123456"'

方法二：用于已有数据的数据库做主主复制

mysqldump -A -F --single-transaction --master-date=1 > /opt/all.sql
mysql -e 'grant replication slave,replication client on *.* to copy@"%" identified by "123456"'
scp -a /opt/all root@2.2.2.22:/opt

2）B主机修改配置，并作为A的从节点

vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld] 
server-id=2
log-bin
auto_increment_offset=2    
auto_increment_increment=2    	

systemctl restart mariadb

方法一：用于新安装的数据库

mysql
->CHANGE MASTER TO
   MASTER_HOST='2.2.2.12',
   MASTER_USER='copy',
   MASTER_PASSWORD='123456',
   MASTER_PORT=3306,
   MASTER_LOG_FILE='A主机的二进制日志',
   MASTER_LOG_POS=字节位置;
  start slave;
  show master logs;

方法二：用于已经使用的

#找到：CHANGE MASTER TO...
vim /opt/all.sql
CHANGE MASTER TO
 MASTER_HOST='2.2.2.12',
 MASTER_USER='copy',
 MASTER_PASSWORD='123456',
 MASTER_PORT=3306,
 ...;

mysql 
->set sql_log_bin=0;
  \. /opt/all.sql;
  start slave;
  show master logs;		#查看B主机的日志

3）再把A主机配置为B主机的slave

有时候为了留有余地，配置完成后，不会立即启动同步

mysql
->CHANGE MASTER TO
   MASTER_HOST='2.2.2.22',
   MASTER_USER='copy',
   MASTER_PASSWORD='123456',
   MASTER_PORT=3306,
   MASTER_LOG_FILE='B主机的二进制日志',
   MASTER_LOG_POS=字节位置;
  start slave;		#根据实际需要，是否立即启动

级联复制：

master-->slave--slaves（多台）
中间B节点由于本身不会记录master的二进制记录到自己的二进制日志中，所以要开启一个选项，接收master二进制日志，并把内容同步到自己二进制日志，方便C、D节点获取B节点的二进制日志能直接得到master节点的数据

log_slave_updates=1

1）在A主机配置

vim /etc/my.cnf
[mysqld]
server-id=1
log-bin

systemctl restart mariadb
mysql -e 'show master logs'
mysql -e 'grant replication slave,replication client on *.* to copy@"%" identified by "123456"'

2）B主机配置

vim /etc/my.cnf
[mysqld]
server-id=2
log_bin
log_slave_updates	#开启master日志记录
read-only

systemctl restart mariadb

mysql
->CHANGE MASTER TO
   MASTER_HOST='2.2.2.12',
   MASTER_USER='copy',
   MASTER_PASSWORD='123456',
   MASTER_PORT=3306,
   MASTER_LOG_FILE='A主机的日志',
   MASTER_LOG_POS=字节位置;
  start slave;
  show master logs;

3）C主机配置

vim /etc/my.cnf
[mysqld]
server-id=3
log-bin
read-only

systemctl restart mariadb

mysql
->CHANGE MASTER TO
   MASTER_HOST='2.2.2.22',
   MASTER_USER='copy',
   MASTER_PASSWORD='123456',
   MASTER_PORT=3306,
   MASTER_LOG_FILE='B主机的日志',
   MASTER_LOG_POS=字节位置;
  start slave;

半同步复制：

官方文档： https://mariadb.com/kb/en/library/semisynchronous-replication/

半同步复制时会在异步机制、半同步机制之间切换

原理：

master主机对应多台slave，当主节点的数据修改时，默认留给从节点有10s的二进制日志复制超时时间
预留时间内，从节点复制主节点的二进制日志完成（无需等待中继日志也写完），并通知master，master会返回请求者，操作完成。此为半同步
预留时间外，从节点的复制时间超过10s，master也会返回请求者操作完成，但实际并没有完成，数据停留在master有一定的风险。此时是变成默认的异步复制

配置选项：

plugin-load-add = semisync_master	加载主节点模块
rpl_semi_sync_master_enabled=ON		启用功能
rpl_semi_sync_master_timeout=3000	#同步的超时时间改为3秒，默认10s，单位ms
plugin_load_add = semisync_slave	加载从节点模块
rpl_semi_sync_slave_enabled=ON 		启用功能

搭建半同步复制：

1）master主机配置

vim /etc/my.cnf
[mysqld]
server-id=1
log-bin
plugin-load-add = semisync_master
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000

systemctl restart mariadb

mysql
->show master logs;
  show variables like '%semi%'; #查看是否启动
  grant replication slave,replication client on *.* to copy@"%" identified by "123456";

2）slave主机配置

vim /etc/my.cnf
[mysqld]
server-id=2
log-bin
plugin_load_add = semisync_slave
rpl_semi_sync_slave_enabled=ON

systemctl restart mariadb

mysql
->CHANGE MASTER TO
   MASTER_HOST='2.2.2.12',
   MASTER_USER='copy',
   MASTER_PASSWORD='123456',
   MASTER_PORT=3306,
   MASTER_LOG_FILE='master主机的日志',
   MASTER_LOG_POS=字节位置; 
  start slave;

主从复制数据过滤：

筛选出备份的库、表

两种实现方法：

master服务端：

主服务器仅向二进制日志中记录与特定数据库相关的事件

注意：此项和 binlog_format相关

弊端：二进制备份还原将无法实现；不建议使用

参数:

binlog-do-db=db1 	二进制日志中记录库的白名单，不支持同时指定多个值，如果想实现多个数据库需多行实现
binlog-do-db=db2
binlog-ignore-db	二进制日志中记录库的黑名单

slave服务端：

在relay log日志中仅读取特定的数据

弊端：此方法存在的问题：会造成网络及磁盘IO浪费

参数：

replicate_do_db=db1,db2,..	复制库白名单，只能在做变量时可以多个值
replicate_ignore_db= 		复制库黑名单
replicate_do_table= 		复制表白名单
replicate_ignore_table=		复制表黑名单
replicate_wild_do_table=foo%.bar%	库和表的白名单，支持通配符
replicate_wild_ignore_table=	库和表的黑名单，支持通配符

从master端控制：

要在主从复制的基础上，再配置

1）主节点配置

vim /etc/my.cnf
[mysqld]
binlog-do-db=hellodb

systemctl restart mariadb

2)从节点重启同步服务

mysql -e 'stop salve'
mysql -e 'start slave;'

从slave端控制：

1）所有从节点配置

vim /etc/my.cnf
[mysqld]
replicate_do_db=db1
replicate_do_db=hj

systemctl restart mariadb

2）重启同步服务

mysql -e 'stop salve'
mysql -e 'start slave;'

主从复制加密：

加密主要是加密登录
mysql本身通信时明文的，需要基于tls/ssl协议来进行加密
master、slave都需要自己的证书。需要三个证书： CA,主,从

1）生成证书，并分发各个slave节点

用我之前写好的shell脚本生成证书，如果不知道我写的脚本在哪里，点击跳转：生成CA证书及自签脚本

rsync -av {*.pem,*.key,*.crt) root@2.2.2.22:/etc/my.cnf.d/ssl/
chown mariadb.mariadb /etc/my.cnf.d/ssl/

2）master开启ssl，配置证书位置

vim /etc/my.cnf
[mysqld]
log-bin
server_id=1
ssl
ssl-ca=/etc/my.cnf.d/cacert.pem
ssl-cert=/etc/my.cnf.d/master.crt
ssl-key=/etc/my.cnf.d/master.key

systemctl restart mariadb

mysql
->show master logs;
  show variables like '%ssl%';	#查看状态
  #创建一个必须使用ssl登录的账号，非证书登录会拒绝
  grant replication slave,replication client on *.* to copy@'%' identified by '123456' require ssl;

3）slave开启ssl

#测试连接master，使用证书登录，验证主节点ssl是否正常
mysql -ucopy -p123456 -h2.2.2.12 --ssl-ca=/etc/my.cnf.d/ssl/cacert.pem --ssl-cert=/etc/my.cnf.d/ssl/slave.crt --ssl-key=/etc/my.cnf.d/ssl/slave.key -e '\s'

#上面测试连接OK，就改配置文件
vim /etc/my.cnf
[mysqld]
server-id=2
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave.crt  
ssl-key=/etc/my.cnf.d/ssl/slave.key

systemctl restart mariadb

方法一：命令行绑定master节点

mysql
->CHANGE MASTER TO
   MASTER_HOST='2.2.2.12',
   MASTER_USER='copy',
   MASTER_PASSWORD='123456',
   MASTER_LOG_FILE='主节点日志文件',
   MASTER_LOG_POS=字节位置,
   MASTER_SSL=1;		#强制要求加密
  start slave;

方法二：编辑备份的sql文件，改change master

vim /backup/all.sql
CHANGE MASTER TO
 MASTER_HOST='2.2.2.12',
 MASTER_USER='copy',
 MASTER_PASSWORD='123456',
 MASTER_LOG_FILE='主节点日志文件',
 MASTER_LOG_POS=字节位置,
 MASTER_SSL=1,
 MASTER_SSL_CA='/etc/my.cnf.d/ssl/cacert.pem',
 MASTER_SSL_CERT='/etc/my.cnf.d/ssl/slave.crt',
 MASTER_SSL_KEY='/etc/my.cnf.d/ssl/slave.key';

mysql
->\. /backup/all.sql;
  start slave;

主从复制之基于GTID复制：

GTID复制：（Global Transaction ID 全局事务标识符）
MySQL的5.6版本开始支持。只需知道master的ip、端口、账号、密码，不用手动给定binlog和post
每次事务开始前，都会记录： gtid-->begin-->...--->commit

详情1：https://www.cnblogs.com/kevingrace/p/5569753.html
详情2：http://mysql.taobao.org/monthly/2016/02/08/

GTID架构：

二进制日志记录内容变成：

版本信息、日志元数据、配置
前一个gtid的事件
gtid事件
...（数据记录）

GTID的组成：

GTID=server_uuid:transaction_id ，在一组复制中，全局唯一
server_uuid 来源于：/var/lib/mysql/auto.cnf

配置选项

注意：

使用gtid后，就不能在slave上做任何数据变更，否则会直接出现数据不一致

mysql 5.6以上配置：

gtid_mode		 #gtid模式
enforce_gtid_consistency 	 #保证GTID安全的参数

启动同步的执行sql：

#根据全局事物id自动寻找文件、字节位置并同步
change master to master_auto_postion=1

mariadb 10.2以上配置参数：

默认启动了GTID，所以不在需要手动配置

gtid_strict_mode		严格模式下

启动同步的执行sql：

change master to MASTER_USE_GTID=参数;
参数：
	current_pos		用于新安装的数据库，或者之前是master，被下线后，重新上线作为slave使用，因为gtid_slave_pos是空的
	slave_pos		只从最后一个事件id开始同步，也就是主节点的gtid_slave_pos之后开始
	no

基于GTID复制示例：

1）master配置

mysql -e 'grant replication slave,replication client on *.* to copy2@"%" identified by "123456"'

2）slave配置

mysql
->CHANGE MASTER TO
   MASTER_HOST='2.2.2.12',
   MASTER_USER='copy',
   MASTER_PASSWORD='123456',
   MASTER_PORT=3306,
   #MASTER_AUTO_POSITION=1;	#mysql版本使用
   MASTER_USE_GTID=slave_pos;
  start slave;
  show slave status\G;		查看是否成功

posted @ 2022-02-10 00:02 suyanhj 阅读(170) 评论(0) 收藏举报

刷新页面返回顶部

suyanhj

mysql主从复制案例

主从复制案例

主从同步：

1）主节点做配置

2）从节点配置

3）在从节点绑定master节点，并开启主从同步

方法1：适用于已经在使用中的主、从服务器，在文件中配置

方法2：适用于新安装的主、从服务器，在命令行配置

主从节点切换

1）选一个从节点，要求其数据与主节点最接近

方法1: 命令行查看

方法2： 文件中查看

2）在选出来的最新从节点操作，上任master

3）新master备份数据，并整理旧master的数据

4）其他slave节点改连接信息，指向新masrer

主主复制：

易出现数据不一致问题：

解决方法： 奇偶数分开写入

主主复制配置注意要点：

1）A主机修改配置

方法一： 用于新安装的数据库做主主复制

方法二：用于已有数据的数据库做主主复制

2）B主机修改配置，并作为A的从节点

方法一： 用于新安装的数据库

方法二： 用于已经使用的

3）再把A主机配置为B主机的slave

级联复制：

1）在A主机配置

2）B主机配置

3）C主机配置

半同步复制：

原理：

配置选项：

搭建半同步复制：

1）master主机配置

2）slave主机配置

主从复制数据过滤：

两种实现方法：

master服务端：

注意：此项和 binlog_format相关

弊端：二进制备份还原将无法实现；不建议使用

参数:

slave服务端：

弊端： 此方法存在的问题：会造成网络及磁盘IO浪费

参数：

从master端控制：

1）主节点配置

2)从节点重启同步服务

从slave端控制：

1）所有从节点配置

2）重启同步服务

主从复制加密：

1）生成证书，并分发各个slave节点

2）master开启ssl，配置证书位置

3）slave开启ssl

方法一：命令行绑定master节点

方法二： 编辑备份的sql文件，改change master

主从复制之基于GTID复制：

GTID架构：

GTID的组成：

配置选项

mysql 5.6以上配置：

启动同步的执行sql：

mariadb 10.2以上配置参数：

启动同步的执行sql：

基于GTID复制示例：

1）master配置

2）slave配置

公告

方法2：文件中查看

解决方法：奇偶数分开写入

方法一：用于新安装的数据库做主主复制

方法一：用于新安装的数据库

方法二：用于已经使用的

弊端：此方法存在的问题：会造成网络及磁盘IO浪费

方法二：编辑备份的sql文件，改change master