使用两台centos7的虚拟机
192.168.8.133
192.168.8.134
首先在两台上面分别搭建MySQL5.7.30的单节点数据库,详细步骤请看:centos7 MySQL5.7.30安装步骤及问题处理
实验只是为了验证主从复制的可行性,所以只需要在配置文件中增加以下两句即可
133的f
[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/f.d
134的f
[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/f.d
保存退出并分别重启两个MySQL服务
service mysql restart
我们首先设置133为主,134为从
在133上
mysql> grant replication slave on *.* to rep1@192.168.8.134 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 'rep1@192.168.8.133: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;
正所谓百因必有果,因果循环报应不爽。操作时的粗心大意,是导致错误发生的根本原因。只有反复练习,才能在错误中积累经验,不断进步。