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

软件

数据库: MySQL 5.7
系统：centos 7.6

文章插图
【CentOS环境下MySQL 5.7主从复制搭建】
主从复制架构

文章插图
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 restartRedirecting 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修改为2log-bin=mysql-bin #slave的二进制日志relay-log=relay-log-bin #中继日志文件名relay-log-index=slave-relay-bin.index #中继日志的索引文件名[root@localhost ~]# service mysqld restartRedirecting to /bin/systemctl restart mysqld.service

文章插图
修改Slave的ID号和开启blog日志
配置主数据库

登录主数据库

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

修改master信息

CHANGE MASTER TOMASTER_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>