mysql主从复制搭建

文档

https://dev.mysql.com/doc/refman/8.2/en/replication.html

本文档只是记录一些简单或者常用的复制，遇到问题还是看官网或者查资料吧。

简单主从复制

Ip	内容
192.168.158.140	Mysql master
192.168.158.141	Mysql slave

两台机器都安装mysql。

在master服务器创建用户并授权：

## 创建用户
CREATE USER 'replication_root'@'192.168.158.141'
  IDENTIFIED BY '123456';
## 授权
GRANT REPLICATION SLAVE
  ON *.*
  TO 'replication_root'@'192.168.158.141'
  WITH GRANT OPTION;

主服务器设置

查看是否开启了binlog

SHOW VARIABLES LIKE 'log_bin'

on 为开启, OFF为关闭。

Bin log记录这个mysql实例的所有库的所有表。可以使用 binlog-do-db 和binlog-ignore-db 记录你想复制的库，一般生产环境，一个mysql实例只使用一个库，所以下面使用的是所有库都记录binlog。

bin log的一些配置请查看官网：https://dev.mysql.com/doc/refman/8.2/en/replication-options-binary-log.html

配置主服务器的server_id：默认是1，所以可以不用配置。

临时配置：

SET GLOBAL server_id = 2;

添加或修改/etc/my.cnf

my.cnf配置规则：https://dev.mysql.com/doc/refman/8.2/en/option-files.html

[mysqld]
server-id=2

从服务器设置

添加或修改/etc/my.cnf

[mysqld]
server-id=3

service mysql start

SSL配置

我这个版本的复制时，需要ssl配置。

在从服务器上：

mkdir /usr/local/mysql/data/master_ssl

在主服务器上：

cd /usr/local/mysql/data
# chmod +r client-key.pem 
scp ca.pem client-cert.pem client-key.pem root@192.168.158.141:/usr/local/mysql/data/master_ssl

在从服务器上：

chmod +r client-key.pem 

如果创建的用户只能用ssl登录：

创建：

CREATE USER 'repl'@'192.168.158.141' IDENTIFIED BY '123456' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.158.141';

修改：

ALTER USER 'repl'@'192.168.158.141' REQUIRE SSL;

可以使用登录语句测试：

./mysql -urepl -h192.168.158.140 --ssl-ca=/usr/local/mysql/data/master_ssl/ca.pem --ssl-cert=/usr/local/mysql/data/master_ssl/client-cert.pem --ssl-key=/usr/local/mysql/data/master_ssl/client-key.pem -p

获取主的binlog名称及坐标

1.加全局锁

Flush tables with read lock 

我使用的新库，不用，生产上这肯定不行，需要停服的。

2.查看

8.2以前

SHOW MASTER STATUS

8.2以后

SHOW BINARY LOG STATUS

在msyql命令行时可以在结尾加\G，好看一点。

记录file与Position的值用于下一节的sql。

执行复制语句

在主服务器上解锁：

这一句是跟上面配套的。

UNLOCK TABLES;

在从服务器上执行：

CHANGE REPLICATION SOURCE TO
   SOURCE_HOST='192.168.158.140',
   SOURCE_USER='repl',
   SOURCE_PASSWORD='123456',
   SOURCE_LOG_FILE='binlog.000005',
   SOURCE_LOG_POS=157,
   SOURCE_SSL = 1,
   SOURCE_SSL_CA = '/usr/local/mysql/data/master_ssl/ca.pem',
   SOURCE_SSL_CERT = '/usr/local/mysql/data/master_ssl/client-cert.pem',
   SOURCE_SSL_KEY = '/usr/local/mysql/data/master_ssl/client-key.pem';

CHANGE REPLICATION SOURCE TO 语句的详情说明：https://dev.mysql.com/doc/refman/8.2/en/change-replication-source-to.html

启动从复制

start slave; 
show slave status;
stop slave;

或者

start REPLICA;
show REPLICA status;
stop REPLICA;

测试

在主上创建数据test，可以同步。

在主上创建表test,可以同步。

在主上插入语句，可以同步。

主从复制原理

从服务器启动复制后，会跟主服务器建立连接，主服务器会创建一个线程发送binlog, 从服务器创建一个IO线程，接收binlog,把binlog转换为relay log（中继日志），从服务器创建一个或多个SQL线程(worker),从relay log中读取，执行sql语句。

从库挂了，会怎么样

挂了，最终是监控及告警的任务。

直接启动

正常同步。

注意：

start REPLICA;

这个命令会重新从CHANGE REPLICATION SOURCE TO 设定的位置开始同步，所以配置完同步后，就不要再执行该语句。

从新的位置开始复制

异常情况需要从新的位置开始复制。

跳进复制启动：

bin/mysqld_safe --user=mysql  --skip-replica-start &

获取新的位置：

show REPLICA status;

Source_log_File 及 read_source_log_Pos 记录下来。

006 446

重新建立复制：

## 这个命令执行完后，就看不了同步到哪了。 mysql库下slave_开头的表将被重置。
RESET SLAVE;

使用新文件及位置：

 CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='192.168.158.140',
    SOURCE_USER='repl',
    SOURCE_PASSWORD='123456',
    SOURCE_LOG_FILE='binlog.000006',
    SOURCE_LOG_POS=446,
    SOURCE_SSL = 1,
    SOURCE_SSL_CA = '/usr/local/mysql/data/master_ssl/ca.pem',
    SOURCE_SSL_CERT = '/usr/local/mysql/data/master_ssl/client-cert.pem',
    SOURCE_SSL_KEY = '/usr/local/mysql/data/master_ssl/client-key.pem';
## 查看是否改变
show REPLICA status;
## 启动复制
start REPLICA;
## 查看是否启动成功
show REPLICA status;

已经有数据使用binlog复制

https://dev.mysql.com/doc/refman/8.2/en/replication-howto.html

https://dev.mysql.com/doc/refman/8.2/en/replication-howto-masterstatus.html

https://dev.mysql.com/doc/refman/8.2/en/replication-snapshot-method.html

官网获取binlog文件名及位置说明，如果主上有数据当锁住表后，生成数据快照，其实就是把主数据保存下来，并在备库执行。

先停止上面的服务器的复制。

创造一些数据（比如创建库、创建表、插入数据）。

使用mysqldump创建数据快照

跟上面获取位置一样，需要全局锁，导出后解锁。

mysqldump --all-databases --source-data=2 > dbdump.db

–source-data[=value]

value默认是1，1的时候会有一条CHANGE REPLICATION SOURCE TO 生成，在导入数据时会执行。

2时会生成CHANGE REPLICATION SOURCE TO，但是在注释里。

导入数据

mysql -h source < fulldb.dump

执行上面简单复制的流程，看是否会复制。都会停服，所以要一开始决定，或者做好公告之类的。导出导入数据还是比较耗时的。

添加新的从服务器

https://dev.mysql.com/doc/refman/8.2/en/replication-howto-additionalslaves.html

如果已经有主从架构后，再添加新的从服务器，不需要停止主服务器，可以根据从服务器进行操作。

使用GTID复制

https://dev.mysql.com/doc/refman/8.2/en/replication-gtids.html

可以停服的复制。因为要启用GTID。

查看服务器是否启用GTID

show VARIABLES like "gtid_mode"

注意：没有 GTID 的事务的binlog不能在启用 GTID 的服务器上使用

步骤1、2是已经有了binlog复制的需要步骤。以前没有主从复制直接从步骤3开始。

步骤1 只读，完成复制

SET @@GLOBAL.read_only = ON;

使主从完成复制。相当停服，只能读。

步骤2 停止两台服务器

mysqladmin -uroot -p shutdown

步骤3 启动两台启用 GTID 的服务器

在my.cnf文件中[mysqld]中添加

gtid_mode=ON
enforce-gtid-consistency=ON

启用 gtid_mode

enforce_gtid_consistency启用该变量以确保仅记录对于基于 GTID 的复制安全的语句。

启动时使用：

bin/mysqld_safe --user=mysql  --skip-replica-start &

步骤4 配置副本使用基于 GTID 的自动定位

CHANGE REPLICATION SOURCE TO
   SOURCE_HOST='192.168.158.140',
   SOURCE_USER='repl',
   SOURCE_PASSWORD='123456',
   SOURCE_AUTO_POSITION = 1,
   SOURCE_SSL = 1,
   SOURCE_SSL_CA = '/usr/local/mysql/data/master_ssl/ca.pem',
   SOURCE_SSL_CERT = '/usr/local/mysql/data/master_ssl/client-cert.pem',
   SOURCE_SSL_KEY = '/usr/local/mysql/data/master_ssl/client-key.pem';

步骤5 进行新的备份

启用 GTID，则在启用 GTID 之前所做的现有备份将无法再在这些服务器上使用。此时进行新的备份，这样您就不会没有可用的备份。

FLUSH LOGS

可以备份数据及binlog。

步骤6 启动副本并禁用只读模式。

START REPLICA;
## 两台机器都执行
SET @@GLOBAL.read_only = OFF;

启用GTID的主服务器，不停服，添加从

当主服务器已经开启了GTID后，添加从时，只要按照上面的步骤3至步骤6只操作从服务器，并且保障从服务器的server-id与主服务器不同。

binlog复制与GTID复制的

binlog复制需要binlog的文件名及位置，如果文件名和位置错误，会造成脏数据，比如插入两条除ID外一样的数据，多次修改等问题。

gtid复制会自动定位，不需要运维操作，执行过的gtid不会重复执行，服务器会跳过。

异步复制、半同步复制、全同步复制

mysql默认使用的就是异步复制。

半同步复制：/usr/local/mysql/lib/plugin下的semisync_slave.so

https://dev.mysql.com/doc/refman/8.2/en/replication-semisync.html

全同步复制：使用mysql的group Replication 的多主复制。

https://dev.mysql.com/doc/refman/8.2/en/group-replication.html