CentOS环境下MySQL 5.7主从复制搭建

2019-10-21

加入收藏

软件

数据库: MySQL 5.7
系统：centos 7.6

主从复制架构

MySQL主从复制

主从复制配置

我创建了两台MySQL服务器分别是Master（IP：172.16.20.199）、Slave（IP:172.16.20.26）；
修改Master的MySQL配置:

 [root@localhost ~]# vim /etc/my.cnf
 #将server-id修改为1 默认为1
 #开启blog日志，在server-id上面新增
 # binlog 配置
server-id=1 #注意主从server-id不能一样
log-slave-updates=true #一般用于联机复制中，
log-bin=master-bin #指定binlog日志名
[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

修改Master的ID号和开启blog日志

修改Slave的MySQL配置:

 [root@localhost ~]# vim /etc/my.cnf
 #将server-id修改为2（默认为1）
 #开启blog日志，在server-id上面新增
 # binlog 配置
server-id=2 #server-id修改为2
log-bin=mysql-bin #slave的二进制日志
relay-log=relay-log-bin #中继日志文件名
relay-log-index=slave-relay-bin.index #中继日志的索引文件名
[root@localhost ~]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service

修改Slave的ID号和开启blog日志

配置主数据库

登录主数据库

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.27 Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>

授权从数据库登录主数据库

mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.16.20.226' IDENTIFIED BY '111111';
mysql> FLUSH PRIVILEGES;

查询master的状态

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

修改从数据库配置

登录从数据库

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.27-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql>

修改master信息

CHANGE MASTER TO
MASTER_HOST='172.16.20.199',
MASTER_USER='root',
MASTER_PASSWORD='111111',
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=154;
#配置主节点
mysql> CHANGE MASTER TO
 MASTER_HOST='172.16.20.199',
 MASTER_USER='root',
 MASTER_PASSWORD='111111',
 MASTER_LOG_FILE='master-bin.000001',
 MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status;
+----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| | 172.16.20.199 | root | 3306 | 60 | master-bin.000001 | 154 | relay-log-bin.000001 | 4 | master-bin.000001 | No | Yes | | | | | | | 0 | | 0 | 154 | 154 | None | | 0 | No | | | | | | NULL | No | 1593 | Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. | 0 | | | 1 | | /usr/local/mysql/data/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | 191021 04:59:03 | | | | | | 0 | | | |
+----------------+---------------+-------------+-------------+---------------+-------------------+---------------------+----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------+-----------------------------+------------------+-------------+-----------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set (0.00 sec)
mysql>

找到字段Slave_IO_Running和Slave_SQL_Running，如果Slave_IO_Running=找到字段Slave_IO_Running和Slave_SQL_Running，如果Slave_IO_Running=No

Slave_SQL_Running=Yes

当前Slave_IO_Running：值为No，向下查看错误信息：

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

修改auto.cnf中的UUID值，随意更改一个值即可。

[root@localhost ~]# vim /usr/local/mysql/data/auto.cnf
server-uuid=修改一下
[root@localhost ~]# systemctl restart mysqld
 再次登录MySQL查看从节点信息
Slave_SQL_Running 和Slave_IO_Running值是否为Yes状态，表示配置成功

验证从是否同步数据

在主服务器上新建数据库test

mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed

创建test表，并插入数据

mysql> mysql> CREATE TABLE test
 -> (
 -> id int,
 -> name varchar(255)
 -> )
 -> ;
Query OK, 0 rows affected (0.06 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)

在slave上查看是否更新数据

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.03 sec)
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql>

恭喜主从复制配置成功！！！