mysql8主从复制

1. 主从复制解释

将主数据库的增删改查等操作记录到二进制日志文件中，从库接收主库日志文件，根据最后一次更新的起始位置，同步复制到从数据库中，使得主从数据库保持一致。

2. 主从复制的作用

• 高可用性：主数据库异常可切换到从数据库
• 负载均衡：实现读写分离
• 备份：进行日常备份

3. Mysql主从复制过程

Binary log：主数据库的二进制日志；Relay log：从服务器的中继日志。

复制过程：
  （1）主数据库在每次事务完成前，将该操作记录到binlog日志文件中；
  （2）从数据库中有一个I/O线程，负责连接主数据库服务，并读取binlog日志变化，如果发现有新的变动，则将变动写入到relay-log，否则进入休眠状态；
  （3）从数据库中的SQL Thread读取中继日志，并串行执行SQL事件，使得从数据库与主数据库始终保持一致。

注意事项：
  （1）涉及时间函数时，会出现数据不一致。原因是，复制过程的两次IO操作和网络、磁盘效率等问题势必导致时间戳不一致；
  （2）涉及系统函数时，会出现不一致。如：@@hostname，获取主机名称，主从数据库服务器名称不一致导致数据不一致；

4. 一主一从配置

• 服务器划分

服务器IP	角色
10.12.69.51	Master1
10.12.69.52	Slave1

4.1、主从数据库安装

参考mysql8安装部署文档

4.2、mysql数据库主从配置

4.2.1、主数据库

# 配置my.cnf
$ vi /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password

# 主从复制-主机配置
# 主服务器唯一ID
server-id=1
# 启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
# 设置需要复制的数据库(可设置多个)
binlog-do-db=test
# 设置logbin格式
binlog_format=STATEMENT
$ systemctl restart mysqld
$ mysql -uroot -p
# 修改root权限，增加远程连接
mysql> use mysql
mysql> update user set host ='%' where user='root';
mysql> alter user 'root'@'%' identified with mysql_native_password by 'new password';
mysql> flush privileges;

##### 4.2.2、从数据库

# 配置my.cnf
$ vi /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql-8.0.20
datadir=/usr/local/mysql-8.0.20/data
character-set-server=utf8
lower-case-table-names=1
default_authentication_plugin=mysql_native_password
# 主从复制-从机配置
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay
$ systemctl restart mysqld
$ mysql -uroot -p
# 修改root权限，增加远程连接
mysql> use mysql
mysql> update user set host ='%' where user='root';
mysql> alter user 'root'@'%' identified with mysql_native_password by 'new password';
mysql> flush privileges;

4.2.3 主从数据库测试是否已经可以远程访问

# 主数据库服务器测试从数据库
mysql -uroot -p -h10.12.69.52 -P3306

# 从数据库服务器测试主数据库
mysql -uroot -p -h10.12.69.51 -P3306

4.2.4、主数据库创建slave并授权

$ mysql -uroot -p
# 创建用户
mysql> create user 'slave'@'%' identified with mysql_native_password by 'password';
# 授权
mysql> grant replication slave on *.* to 'slave'@'%';
# 刷新权限
mysql> flush privileges;

4.2.5、从数据库验证slave用户是否可用

$ mysql -uslave -p -h10.12.69.51 -P3306

4.2.6、主数据库查询服务ID以及master状态

# 登录
$ mysql -uroot -p

# 查询server_id是否可配置文件中一致
mysql> show variables like 'server_id';

# 若不一致，可设置临时ID（重启失效）
mysql> set global server_id = 1;

# 查询Master状态，并记录 File 和 Position 的值
mysql> show master status;

# 注意：执行完此步骤后退出主数据库，防止再次操作导致 File 和 Position 的值发生变化

4.2.7、从数据库设置主数据库（主从复制的关键部分）

# 登录
$ mysql -uroot -p

# 查询server_id是否可配置文件中一致
mysql> show variables like 'server_id';

# 若不一致，可设置临时ID（重启失效）
mysql> set global server_id = 2;

# 设置主数据库参数
mysql> change master to master_host='10.12.69.51',master_port=3306,master_user='slave',master_password='password',master_log_file='mysql-bin.000002',master_log_pos=156;

# 开始同步
mysql> start slave;

# 若出现错误，则停止同步，重置后再次启动
mysql> stop slave;
mysql> reset slave;
mysql> start slave;

# 查询Slave状态
mysql> show slave status\G

# 查看是否配置成功
# 查看参数 Slave_IO_Running 和 Slave_SQL_Running 是否都为yes，则证明配置成功。若为no，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异常值。

4.3、测试

通过工具连接主从数据库或者在服务器连接。
注意：主数据库的配置文件中配置了需要同步的数据库，因此只会同步配置的数据库，不配置则同步全部。

# 在主数据库创建数据库test
mysql> create database test;

# 从数据库查看
mysql> show databases;

# 在主数据库创建表
mysql> use test;
mysql> create table t_user(id int, name varchar(20));

# 插入数据
mysql> insert into t_user values(1, 'C3Stones');

# 在从数据库查看
mysql> use test;
mysql> select * from t_user;

# 其他删改查操作请自行测试