MYSQL主从复制

post thumb
Mysql
作者 Louis 发表于 2018年6月14日

摘要:mysql的主从复制,级联复制,加密复制,半同步复制,cluster,

Mysql复制

扩展方式: Scale UpScale Out

MySQL的扩展

读写分离 复制:每个节点都有相同的数据集 向外扩展 二进制日志,实现主从的主要原理 单向

复制的功用:

数据分布 负载均衡读 备份 高可用和故障切换 MySQL升级测试

主从复制线程:

1529412272031

  • 主节点:

dump Thread:为每个SlaveI/O Thread启动一个dump线程,用于向其发送binary log events

  • 从节点:

I/O Thread:向Master请求二进制日志事件,并保存于中继日志中 SQL Thread:从中继日志中读取日志事件,在本地完成重放

  • 跟复制功能相关的文件:

master.info:用于保存slave连接至master时的相关信息,例如账号、密码、服务器地址等。 relay-log.info:保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系

  • 主从复制特点:

异步复制 主从数据不一致比较常见

  • 复制架构:

Master/Slave, Master/Master, 环状复制 一主多从 从服务器还可以再有从服务器 一从多主:适用于多个不同数据库

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

STATEMENT(5.0之前),ROW(5.1之后,推荐),MIXED

搭建主从复制

主服务器

修改配置文件/etc/my.cnf,启动服务,并授权slave账户同步,同时,记录二进制日志的位置

$ vim /etc/my.cnf
[mysqld]
server_id=1
log-bin
log-basename=master
innodb_file_per_table
$ systemctl restart mariadb
$ mysql
-- 启动服务
grant replication slave on *.* to repluser@'192.168.1.%' identified by 'centos';
show master status;
-- master-bin.000003  399
source hellodb.sql
CALL pro_testlog();

从服务器

修改配置文件/etc/my.cnf,在[mysqld]添加server_id=2,必须和主服务器不一样。修改完配置文件,重启服务即可systemctl restart mariadb

HELP CHANGE MASTER TO
CHANGE MASTER TO   
	MASTER_HOST='192.168.1.8',   
	MASTER_USER='repluser',   MASTER_PASSWORD='centos',   
	MASTER_PORT=3306,   
	MASTER_LOG_FILE='master-bin.000003',   
	MASTER_LOG_POS=399;
SHOW SLAVE STATUS\G
-- 启动同步,后续会自动启动
START SLAVE;
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 0 这个为0,说明同步完成

报错学习

做实验的时候,从服务器同步不了,还报错了。

show slave status\G:
  Slave_IO_Running: yes
 Slave_SQL_Running: No
    Last_SQL_Error: Error 'Duplicate entry '%-test-' for key 'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0'
        Last_Error: Error 'Table 'testlog' already exists' on query. Default database: 'hellodb'. Query: 'create table testlog (id int auto_increment primary key,name char(10),age int default 20)'

报错从日志查询,查询了一下报错,1062的报错,应该是主键冲突。

tail -f /var/log/mariadb/mariadb.log
180614 17:36:05 [ERROR] Slave SQL: Error 'Table 'testlog' already exists' on query. Default database: 'hellodb'. Query: 'create table testlog (id int auto_increment primary key,name char(10),age int default 20)', Error_code: 1050
180614 17:46:48 [Note] Slave I/O thread: connected to master 'repluser@192.168.1.8:3306',replication started in log 'FIRST' at position 4
180614 17:46:48 [ERROR] Slave SQL: Error 'Duplicate entry '%-test-' for key 'PRIMARY'' on query. Default database: 'mysql'. Query: 'INSERT INTO db SELECT * FROM tmp_db WHERE @had_db_table=0', Error_code: 1062
180614 17:46:48 [Warning] Slave: Duplicate entry '%-test-' for key 'PRIMARY' Error_code: 1062

​ 然后查询了一下mariadb服务器的binlog_format,居然是select @@binlog_format;,STATEMENT ,心中忽然一喜,应该是这个问题,网上也查询了蛮多了资料,最后解决了这个报错如下: 在主服务器上,在/etc/my.cnfbinlog_format=row,重启服务

-- 确认binlog_format为row
select @@binlog_format;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 记录位置
master-bin.000005  245  
UNLOCK TABLES;

​ 在从服务器上,重新重置即可解决。

STOP SLAVE;
reset slave all;
CHANGE MASTER TO   
	MASTER_HOST='192.168.1.8',   
	MASTER_USER='repluser',   MASTER_PASSWORD='centos',   
	MASTER_PORT=3306,   
	MASTER_LOG_FILE='master-bin.000005',   
	MASTER_LOG_POS=245;
START SLAVE;

主从复制plus:

场景:从服务器的大数据备份,随着业务的增加,要求增加从服务器

主节点备份

$ mysqldump -A -F --single-transaction --master-data=1 >full.sql
#将全备份文件复制到新增的从服务器上。
$ scp full.sql 192.168.1.10:/backup

新增从节点的配置

配置/etc/my.cnf加入下面几行即可。然后启动服务。

tips:/etc/my.cnf,文件损坏,想恢复默认设置,yum install - y mariadb-libs

$ vim /etc/my.cnf
server_id=2
read_only
relay_log=relay-log   
relay_log_index=relay-log.index
$ systemctl start mariadb

将全备份的/backup/full.sqlchange master to子句改为最初起点。然后实现sql语句全备份,并自动同步

$ vim /backup/full.sql
···
CHANGE MASTER TO
  MASTER_HOST='192.168.1.7',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master-bin.000001',
  MASTER_LOG_POS=245,
···
$ mysql < /backup/full.sql
配置完成

级联复制

master:192.168.1.7slave1:192.168.1.8;slave2:192.168.1.18

主节点配置

$ vim /etc/my.cnf
[mysqld]
server_id=1
log-bin
log-basename=master
innodb_file_per_table
$ systemctl restart mariadb
$ mysql
-- 启动服务
grant replication slave on *.* to repluser@'192.168.1.%' identified by 'centos';
show master status;
-- master-bin.000001  245
source hellodb.sql
CALL pro_testlog();

slave1的配置

ip为192.168.1.8,配置过程和主从复制差不多。

$ vim /etc/my.cnf
server_id=2
log_bin
log_slave_updates
read_only
$ systemctl restart mariadb
$ mysql
CHANGE MASTER TO
  MASTER_HOST='192.168.1.7',
  MASTER_USER='repluser',
  MASTER_PASSWORD='centos',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master-bin.000001',
  MASTER_LOG_POS=245,
start slave;
stop slave;
-- 重置slave的日志,但是不破坏数据库
reset slave all;

salve2的配置

ip为192.168.1.18,此时,slave2的主是slave1,故而master的ip应该为192.168.1.8

HELP CHANGE MASTER TO
CHANGE MASTER TO   
	MASTER_HOST='192.168.1.8',   
	MASTER_USER='repluser',   MASTER_PASSWORD='centos',   
	MASTER_PORT=3306,   
	MASTER_LOG_FILE='master-bin.000001',   
	MASTER_LOG_POS=245;
SHOW SLAVE STATUS\G
-- 启动同步,后续会自动启动
START SLAVE;
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 0 这个为0,说明同步完成

半同步复制

​ 默认情况下,MySQL的复制功能是异步的,异步复制可以提供最佳的性能,主库把binlog日志发送给从库即结束,并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时,有可能从服务器没有接收到主服务器发送过来的binlog日志,这就会造成主服务器和从服务器的数据不一致,甚至在恢复时造成数据的丢失。

1529412418043

在配置好mysql主从复制后,可以安装某些插件,只要有一台从服务器同步完成,即可认为数据同步。具体如下:

在主服务器上:

install plugin rpl_semi_sync_master soname 'semisync_master.so';
show global variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
set global rpl_semi_sync_master_enabled=ON;

从服务器:

stop slave;
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=ON;
start slave;

加密复制

ssh证书安装,相关的ca证书知识,源于openssl,具体原理可以参考博主前面的博文。

在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的,外网里访问数据或则复制,存在安全隐患。通过SSL/TLS加密的方式进行复制的方法,来进一步提高数据的安全性

官方配置查看

  • 主服务器开启SSL:[mysqld] 加一行ssl
  • 主服务器配置证书和私钥;并且创建一个要求必须使用SSL连接的复制账号
  • 从服务器使用CHANGER MASTER TO 命令时指明ssl相关选项

具体证书生成:

$ mkdir /etc/my.cnf.d/ssl/
$ ls /etc/my.cnf.d/ssl/
$ cd /etc/my.cnf.d/ssl/
$ openssl pwd
$ openssl genrsa 2048
$ openssl genrsa 2048 > cakey.pem
#自签名证书
$ openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650
 	Country Name (2 letter code) [XX]:CN
    State or Province Name (full name) []:beijing
    Locality Name (eg, city) [Default City]:beijing
    Organization Name (eg, company) [Default Company Ltd]:mysql
    Organizational Unit Name (eg, section) []:opt
    Common Name (eg, your name or your server's hostname) []:ca.mysql.com
#生成master证书申请,最后的server's和自签名证书不同即可
$ openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr
#制作master的crt证书
$ openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
#生成slave证书申请,最后的server's和master证书不同即可
$ openssl req -newkey rsa:2048 -days 365 -nodes -keyout salve.key > slave.csr
#制作slave的crt证书
$ openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt
#确认证书是否成功
$ openssl verify -CAfile cacert.pem master.crt slave.crt
cacert.pem: OK
master.crt: OK
slave.crt: OK
#将证书和key分别复制到相应的主机。
$ scp -r cacert.pem master.crt master.key 192.168.1.8:/etc/my.cnf.d/
$ scp -r cacert.pem slave.crt slave.key 192.168.1.18:/etc/my.cnf.d/

主服务器配置

$ vim /etc/my.cnf
[mysqld]
log_bin
server_id=1
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key
$ systemctl restart mariadb
grant replication slave on *.* to repluse@'192.168.1.%' identified by 'centos' require ssl;

从服务器配置

$ mysql -urepluser -preplpass -h192.168.1.8 --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
STOP SLAVE;
reset slave all;
CHANGE MASTER TO   
	MASTER_HOST='192.168.1.8',   
	MASTER_USER='repluser',   MASTER_PASSWORD='centos',   
	MASTER_PORT=3306,   
	MASTER_LOG_FILE='master-bin.000001',   
	MASTER_LOG_POS=245,
	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';
start slave;

指令集合

-- 启动主从复制
START SLAVE; 
STOP SLAVE; #停止复制
SHOW SLAVE STATUS; #查看复制状态
Seconds_Behind_Master: 0 #从服务器是否落后于主服务
RESET SLAVE ALL; #重置从服务器的配置
MASTER_SSL=1, #配合 CHANGE MASTER TO 使用,开启ssl加密复制
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';
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr } #删除二进制日志,谨慎操作
SHOW MASTER STATUS #查看二进制日志状态
SHOW BINLOG EVENTS #查看二进制日志
SHOW BINARY LOGS #查看二进制日志
SHOW PROCESSLIST #查看进程,一般配合kill进程使用,
KILL id     #杀掉进程
上一篇
MYSQL之xtrabackup