MySQL5.7.30主从复制集群搭建及互为主从集群搭建（附问题处理方案）
使用两台centos7的虚拟机
192.168.8.133
192.168.8.134
首先在两台上面分别搭建MySQL5.7.30的单节点数据库，详细步骤请看：centos7 MySQL5.7.30安装步骤及问题处理
实验只是为了验证主从复制的可行性，所以只需要在配置文件中增加以下两句即可
133的my.cnf
[mysqld]
port=3306
socket=/usr/local/mysql/mysql.sock
#skip-grant-tables
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
server-id=1
log_bin=mysql-bin
[mysqld_safe]
pid-file=/usr/local/mysql/mysql.pid
log-error=/usr/local/mysql/error.log
!includedir /etc/my.cnf.d
134的my.cnf
[mysqld]
port=3306
socket=/usr/local/mysql/mysql.sock
#skip-grant-tables
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
server-id=2
log_bin=mysql-bin
[mysqld_safe]
pid-file=/usr/local/mysql/mysql.pid
log-error=/usr/local/mysql/error.log
!includedir /etc/my.cnf.d
保存退出并分别重启两个MySQL服务
service mysql restart
我们首先设置133为主，134为从
在133上
mysql> grant replication slave on *.* to [email protected] identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.03 sec)
这里要注意一点：新建的数据库，默认的Position值为154，为了实验方便，如果不是的，我们就重置master。生产环境千万不能如此哦。
在134上
首先使用
show slave status;
确保没有已经做过的主从，如果有，我们可以重置
reset slave;
change master to master_host='192.168.8.133',master_user='rep1',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
这句中的红色部分，要和主库中的配资完全一致，如果输错了，可以改正之后再次输入这个命令
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.133
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
......
然后测试：
在133上创建一个名为test的数据库，在134上可以看到，即为主从复制成功
133上
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
134上：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
接下来我们设置134为主，133为从。
在134上
mysql> grant replication slave on *.* to rep2@192.168.8.133 identified by '123456';
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.000001 | 593 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
这里要注意：两个用于复制的账号不能一样哦
在133上
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status;
Empty set (0.00 sec)
mysql> change master to master_host='192.168.8.134',master_user='rep2',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.134
Master_User: rep2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
。。。。。
此时，在134上创建数据库test1，在133上要能看到
134：
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
+--------------------+
6 rows in set (0.01 sec)
133：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
| test1 |
+--------------------+
6 rows in set (0.00 sec)
到此，互为主从的集群就搭建好了。
下面是搭建过程中的一些问题及处理方法：
1， Slave_IO_Running: No
Slave_SQL_Running: No
这个问题，是因为从节点的slave服务没有启动，使用start slave; 即可解决
2，Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 1
问题表现为：Replicate_Do_DB为空，且报错误1045。意思为：当前节点没有连接主节点133数据库的权限。那就只能看133是否给了正确的权限了。一般的错误原因在133上创建账号的时候IP等信息写错了，或者slave节点上的change语句写错了，网上翻一翻，肯定能看到错误之处。
3， Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
错误1236，这里提示为master_log_file文件不存在。这个文件在从节点的change语句中定义的
mysql> change master to master_host='192.168.8.134',master_user='rep2',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
这个文件的名称，要和主节点的file字段信息一致
4，以上错误改完之后，在主库133上创建test库，在从库134上看不到
这个问题无法截图，但肯定会存在。究其原因，是因为我们反复的修改了配置信息，导致主库133的Position信息发生了变化，且134这边授权问题也进行了改变，主从信息无法匹配导致的。
这时候，我们要重置master和slave，然后重新授权即可。
重置master:
reset master;
grant replication slave on *.* to rep2@192.168.8.133 identified by '123456';
重置slave:
stop slave;
reset slave;
change master to master_host='192.168.8.134',master_user='rep2',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
正所谓百因必有果，因果循环报应不爽。操作时的粗心大意，是导致错误发生的根本原因。只有反复练习，才能在错误中积累经验，不断进步。