軟件
- 數(shù)據(jù)庫: MySQL 5.7
- 系統(tǒng)：centos 7.6
主從復(fù)制架構(gòu)
MySQL主從復(fù)制
主從復(fù)制配置
- 我創(chuàng)建了兩臺MySQL服務(wù)器分別是Master（IP：172.16.20.199）、Slave（IP:172.16.20.26）；
- 修改Master的MySQL配置:
[root@localhost ~]# vim /etc/my.cnf #將server-id修改為1 默認(rèn)為1 #開啟blog日志，在server-id上面新增 # binlog 配置 server-id=1 #注意主從server-id不能一樣 log-slave-updates=true #一般用于聯(lián)機(jī)復(fù)制中， 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（默認(rèn)為1） #開啟blog日志，在server-id上面新增 # binlog 配置 server-id=2 #server-id修改為2 log-bin=mysql-bin #slave的二進(jìn)制日志 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日志
配置主數(shù)據(jù)庫
- 登錄主數(shù)據(jù)庫
[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>
- 授權(quán)從數(shù)據(jù)庫登錄主數(shù)據(jù)庫
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'172.16.20.226' IDENTIFIED BY '111111'; mysql> FLUSH PRIVILEGES;
查詢master的狀態(tài)
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>
修改從數(shù)據(jù)庫配置
- 登錄從數(shù)據(jù)庫
[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; #配置主節(jié)點 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
當(dāng)前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查看從節(jié)點信息 Slave_SQL_Running 和Slave_IO_Running值是否為Yes狀態(tài)，表示配置成功
驗證從是否同步數(shù)據(jù)
- 在主服務(wù)器上新建數(shù)據(jù)庫test
mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> use test; Database changed
- 創(chuàng)建test表，并插入數(shù)據(jù)
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上查看是否更新數(shù)據(jù)
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>
恭喜主從復(fù)制配置成功！！！
