一、单实例初始化：主从数据库各自初始化安装
2、主库关键参数
server-id=330601
log-bin=/data/mysql/binlog/mysql-bin
binlog-format=ROW
max_binlog_size=128M
2、从库关键参数
server-id=330661
log-bin=/data/mysql/binlog/mysql-bin
binlog-format=ROW
max_binlog_size=128M
二、配置主从复制关系
1、主从库各自reset master
mysql>reset master;
2、主库建复制用户
create user 'rep1'@'%' identified with mysql_native_password by 'test$1234';
grant replication slave on *.* to 'rep1'@'%';
grant replication slave on *.* to rep1@'%' identified by 'test$1234';
3、从库执行复制配置
----从库中change master to 语句指向主库
change master to master host='192.168.106.131',master_user='rep1',master_password='test$1234',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=0;
三、启动复制线程
1、从库中启动复制流程
start slave
2、从库查看状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.106.131
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: DB3-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 13117
Last_IO_Error: 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.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330601
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 221229 18:27:28
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
Last_IO_Error: 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.
-------错误定位分析
--主库
mysql> select @@server_id;show variables like '%server_uuid%';
+-------------+
| @@server_id |
+-------------+
| 330601 |
+-------------+
1 row in set (0.00 sec)
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 4b6fb5b6-1265-11ed-b91b-000c29a77d1d |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
--从库
mysql> select @@server_id;show variables like '%server_uuid%';
+-------------+
| @@server_id |
+-------------+
| 330661 |
+-------------+
1 row in set (0.00 sec)
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 4b6fb5b6-1265-11ed-b91b-000c29a77d1d |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
=========================================================
UUID一样：原因使用了VMware克隆了两台虚拟机作
解决
解决方法就是找到主机和从机的auto.cnf文件修改uuid值或删除auto.cnf这个文件。
=========================================================
----从库重启
[root@DB3 ~]# more /data/mysql/data/auto.cnf
[auto]
server-uuid=4b6fb5b6-1265-11ed-b91b-000c29a77d1d
[root@DB3 ~]# mv /data/mysql/data/auto.cnf /home
[root@DB3 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
[root@DB3 ~]# mysqld_multi stop 1
[root@DB3 ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
[root@DB3 ~]# mysqld_multi start 1
[root@DB3 ~]# ls -l /data/mysql/data/auto.cnf
-rw-r----- 1 mysql mysql 56 Dec 29 19:35 /data/mysql/data/auto.cnf
[root@DB3 ~]# more /data/mysql/data/auto.cnf
[auto]
server-uuid=f759a28a-876c-11ed-8181-000c29b9004b
[root@DB3 ~]# mysql -u root -p -S /tmp/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.16 MySQL Community Server - GPL
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> select @@server_id;show variables like '%server_uuid%';
+-------------+
| @@server_id |
+-------------+
| 330661 |
+-------------+
1 row in set (0.00 sec)
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | f759a28a-876c-11ed-8181-000c29b9004b |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
------从库状态正常
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.106.131
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 658
Relay_Log_File: DB3-relay-bin.000003
Relay_Log_Pos: 872
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 658
Relay_Log_Space: 1078
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 330601
Master_UUID: 4b6fb5b6-1265-11ed-b91b-000c29a77d1d
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
四、验证同步
1、主库插入数据
mysql> create database test_rep1;
Query OK, 1 row affected (0.00 sec)
mysql> use test_rep1;
Database changed
mysql> create table test_table(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test_table values (1),(2),(3),(4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select @@server_id;select * from test_table;
+-------------+
| @@server_id |
+-------------+
| 330601 |
+-------------+
1 row in set (0.00 sec)
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
mysql>
mysql> show processlist;
+-----+-----------------+-----------------------+-----------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------------------+-----------+-------------+------+---------------------------------------------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 23 | Waiting for next activation | NULL |
| 9 | root | localhost | test_rep1 | Query | 0 | starting | show processlist |
| 110 | rep1 | 192.168.106.132:44590 | NULL | Binlog Dump | 572 | Master has sent all binlog to slave; waiting for more updates | NULL |
+-----+-----------------+-----------------------+-----------+-------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
2、从库查询验证
mysql> use test_rep1;
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> select @@server_id;select * from test_table;
+-------------+
| @@server_id |
+-------------+
| 330661 |
+-------------+
1 row in set (0.00 sec)
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
mysql> show processlist;
+----+-----------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+
| 4 | system user | | NULL | Connect | 560 | Waiting for master to send event | NULL |
| 5 | system user | | NULL | Query | 385 | Slave has read all relay log; waiting for more updates | NULL |
| 6 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 11 | root | localhost | test_rep1 | Query | 0 | starting | show processlist |
+----+-----------------+-----------+-----------+---------+------+--------------------------------------------------------+------------------+
4 rows in set (0.00 sec)
mysql> select * from mysql.slave_master_info\G;
*************************** 1. row ***************************
Number_of_lines: 28
Master_log_name: mysql-bin.000001
Master_log_pos: 155
Host: 192.168.106.131
User_name: rep1
User_password: dsg$1234
Port: 3306
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: 4b6fb5b6-1265-11ed-b91b-000c29a77d1d
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 0
Channel_name:
Tls_version:
Public_key_path:
Get_public_key: 0
Network_namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select * from mysql.slave_relay_log_info\G;
*************************** 1. row ***************************
Number_of_lines: 7
Relay_log_name: ./DB3-relay-bin.000003
Relay_log_pos: 1584
Master_log_name: mysql-bin.000001
Master_log_pos: 1370
Sql_delay: 0
Number_of_workers: 0
Id: 1
Channel_name:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select * from mysql.slave_worker_info\G;
Empty set (0.00 sec)
ERROR:
No query specified
