MySQL主从配置

一、实验环境

需要关闭selinux，防火墙，并清空Iptables规则。

二、实验原理

• MySQL Replication，可以实现将数据从一台库服务器（ master）复制到一 台或多台数据库服务器 （slave）。默认情况下属于异步复制，无需维持长连接，通过配置，可以复制所有的库或者几个甚至中一些表，这个功能是 MySQL 内建的，本身自带的。

复制过程简单的说就是 master 将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志行数据操作。

DML：SQL操作语句， update, insertinsert，delete
Relay log （中继日志）

• Replication的作用

1. Fail Over
   故障切换
2. Backup Server
   备份服务，无法对 SQL 语句执行产生的故障恢复，有限备份
3. High Performance
   高性能，可以多台 slave, 实现读写分离

• Replication 工作原理
  整体上来说，复制有 3 个步骤：

1. master 将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）
2. slave 将 master 的 binary log events 拷贝到它的中继日志(relay log)
3. slave 重做中继日志中的事件，修改 salve 上的数据。
   

• MySQL 主从复制：

1. 第一步：master 记录二进制日志。在每个事务更新数据完成之前，master 在二进制日志记录这些改变。MySQL 将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master 通知存储引擎提交事务。
2. 第二步：slave 将 master 的 binary log 拷贝到它自己的中继日志。首先，slave 开始一个工作线程——I/O 线程。I/O 线程在 master 上打开一个普通的连接，然后开始 binlog dump process。Binlog dump process 从 master 的二进制日志中读取事件，如果已经执行完 master 产生的所有文件，它会睡眠并等待 master 产生新的事件。I/O 线程将这些事件写入中继日志。
3. 第三步：SQL slave thread（SQL 从线程）处理该过程的最后一步。SQL 线程从中继日志读取事件，并重新执行其中的事件而更新 slave 的数据，使其与 master 中的数据一致。

• Replication 常见方案

1. One master and Muti salve 一主多备
   一般用来做读写分离的，master 写，其他 slave 读，这种架构最大问题 I/O 压力集中 在 Master 上<多台同步影响 IO>
   

2. M-S-S
   使用一台 slave 作为中继，分担 Master 的压力，slave 中继需要开启 bin-log,并配置 log-slave-updates
   Slave 中继可使用 Black-hole 存储引擎，不会把数据存储到磁盘，只记录二进制日志。
   

3. M-M 双主互备 (互为主从)
   很多人误以为这样可以做到 MySQL 负载均衡，实际没什么好处，每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性。
   

4. M-M-M
   监控三台机器互相作对方的 master
   天生的缺陷：复制延迟，slave 上同步要慢于 master,如果大并发的情况那延迟更严重
   

三、搭建主从

1.安装MySQL

主从2台服务器都要安装，下面过程以主服务器安装为例，与从服务器安装过程相同

#首先解压mysql5.7安装包
[root@master ~]# tar xvf mysql-5.7.tar.gz
#使用yum本地安装mysql
[root@master ~]# yum install -y mysql*.rpm
#启动mysql
[root@master ~]# systemctl start mysqld
#关闭密码强度审计插件 
[root@master ~]# echo "validate-password=OFF" >> /etc/my.cnf
#重启mysql
[root@master ~]# systemctl restart mysqld
#筛选日志文件，查找mysql初始root密码
[root@master ~]# grep password /var/log/mysqld.log | head -1 
2021-07-14T11:50:39.368593Z 1 [Note] A temporary password is generated for root@localhost: /j_ieG?jn8!!
#登录mysql
[root@master ~]# mysql -u root -p'/j_ieG?jn8!!'
#修改root密码
mysql> set password for root@localhost=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
#测试修改的密码
[root@master ~]# mysql -u root -p'123456'

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

至此MySQL安装完成，下面开始进行主从的搭建

2.MySQL主服务器配置

[root@master ~]# mysql -u root -p'123456'

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test; #创建测试主从同步的数据库
Query OK, 1 row affected (0.00 sec)

mysql> use test; #切换使用新建的数据库
Database changed
mysql> create table test1(id int,name varchar(20)); #创建测试表test1
Query OK, 0 rows affected (0.01 sec)
mysql> exit

配置MySQL配置文件

[root@master ~]# vim /etc/my.cnf
#启用二进制日志 
log-bin=mysql-bin-master 
#本机数据库 ID 标示，主从配置中ID要唯一
server-id=1 
#可以被从服务器复制的库, 二进制需要同步的数据库名
binlog-do-db=test 
#不可以被从服务器复制的库
binlog-ignore-db=mysql 
#保存并重启MySQL服务(如果重启卡死现象，kill 掉再启动) 
[root@master ~]# systemctl restart mysqld

授权主从同步slave用户权限

[root@master ~]# mysql -u root -p'123456'
mysql> grant replication slave on *.* to slave@192.168.31.62 identified by "123456"; #ip地址为从库的IP
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show master status; #查看状态信息
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000003 |      450 | test         | mysql            |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show binlog events\G
*************************** 1. row ***************************
   Log_name: mysql-bin-master.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 123
       Info: Server ver: 5.7.25-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin-master.000001
        Pos: 123
 Event_type: Previous_gtids
  Server_id: 1
End_log_pos: 154
       Info: 
......

导出测试主从的数据库，要保证主从数据在同步之前保持他一致

[root@master ~]# mysqldump -uroot p'123456' -B test > test.sql
#scp传输到从服务器
[root@master ~]# scp test.sql root@192.168.31.62:/root 
root@192.168.31.62's password: 
test.sql                                                     100% 1929     2.8MB/s   00:00 

3.MySQL从服务器配置

#测试slave用户是否可以正常连接
[root@slave ~]# mysql -uslave -p123456 -h 192.168.31.61

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

mysql> exit
#只有复制的权限, 是看不到其他库的。导入测试数据库使两台数据库数据一致。
[root@slave ~]# mysql -uroot -p123456 < test.sql

修改从服务器配置文件

[root@slave ~]# vim /etc/my.cnf
#从服务器没必要开启bin-log日志，
server-id=2 #从服务器ID号，不要和主ID相同，如果设置多个从服务器，每个从服务器必须有一个唯一的server-id值，必须与主服务器的以及其它从服务器的不相同。可以认为 server-id值类似于IP地址：这些ID值能唯一识别复制服务器群集中的每个服务器实例。
#重启从服务器MySQL服务
[root@slave ~]# systemctl restart mysqld
#从服务器设置主服务器配置
[root@slave ~]# mysql -uroot -p123456

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to master_host='192.168.31.61',master_user='slave',master_password='123456'; #IP地址为主服务器地址。
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave; #启动slave
mysql> show slave status\G； #查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.61
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000003
          Read_Master_Log_Pos: 450
               Relay_Log_File: node1-relay-bin.000004
               Relay_Log_Pos: 677
        Relay_Master_Log_File: mysql-bin-master.000003
              Slave_IO_Running: Yes
              Slave_SQL_Running: Yes

可以看到出现了两个为 YES，则主从配置成功了

4.查看主服务器主从同步状态

[root@master ~]# mysql -u root -p'123456'

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 8
   User: slave
   Host: node1:56922
     db: NULL
Command: Binlog Dump
   Time: 143
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

4.测试主从同步

在主服务器模拟程序插入数据

[root@master ~]# mysql -u root -p'123456'
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into test1 values(1,'man')
    -> ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | man  |
+------+------+
1 row in set (0.00 sec)

切换到从库进行查看

[root@slave~]# mysql -u root -p'123456'
mysql> select * from test.test1;
+------+------+
| id   | name |
+------+------+
|    1 | man  |
+------+------+
1 row in set (0.00 sec)

mysql> 

至此，主从同步基础搭建完成。

四、主从同步排错

1.主从不同步
登录主库看一下bin -log 的位置，然后再进行同步。

mysql> show master status;
+-------------------------+----------+--------------+------------------+-------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-master.000003 |      713 | test         | mysql            |                   |
+-------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

登录从库执行 MySQL 命令：

mysql> stop slave； #停止slave服务
mysql> change master to master_log_file='mysql-bin-master.000003',master_log_pos=713; #根据上面主服务器的show master status的结果，进行从服务器的二进制数据库记录回归，达到同步的效果。
Query OK, 0 rows affected (0.00 sec)

mysql> start slave ; #启动 I/O 线程和 SQL 线程看一下从服务器的同步情况
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.61
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000003
          Read_Master_Log_Pos: 713
               Relay_Log_File: node1-relay-bin.000002
                Relay_Log_Pos: 327
        Relay_Master_Log_File: mysql-bin-master.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

如果出现Slave_IO_Running: Yes，Slave_SQL_Running: Yes那代表已经在同步了。

排错总结：

IO_Running排错思路： 二进制日志没有开启 IPTABLES 没有放开端口 对应的主机 IP 地址写错了 用户名或密码写错了

SQL_Running排错思路：
主从服务器数据库结构不统一。出错后如果数据少，可以手动解决创建插入，再更新 slave 状态。 注：如果主上误删除了。那么从上也就误删除了。 #因此主上要定期做 mysqldump 备份。