一、首先两台服务器安装好mysql数据库环境
https://www.cnblogs.com/sky-cheng/p/10564604.html
二、在两台mysql上都创建复制账号
mysql> grant replication slave,replication client on *.* to 'repl'@'%' identified by 'Zaq1xsw@'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> use mysql; 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 user,host from user; +---------------+-----------+ | user | host | +---------------+-----------+ | repl | % | | root | % | | mysql.session | localhost | | mysql.sys | localhost | +---------------+-----------+ 4 rows in set (0.00 sec)
三、两台mysql服务器上上分别设置不同的Server_id，同时都开启二进制日志
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 0 |
| server_id_bits | 32 |
+----------------+-------+
如果配置文件没有设置server_id参数，则默认都是0
编辑/etc/my.cnf
添加service_id,它的值可以跟服务器的IP最后一位数字一样，这样就能保证内网中的服务器ID不重复。一台设置
server_id=103
log-bin=master
binlog_format=row
另一台
server_id=69 log-bin=master-1 binlog_format=row
四、重启两台服务器的mysql服务
五、两台服务器分别执行change master操作
首先获取第一台数据库日志文件名称和偏移量：
mysql> show master statusG; *************************** 1. row *************************** File: master-1.000001 Position: 154 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) ERROR: No query specified
在另外一台上执行
mysql> change master -> to -> master_host='172.28.18.69', -> master_port=3306, -> master_user='repl', -> master_password='xxxxxxxxx', -> master_log_file='master-1.000001', -> master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.20 sec)
开启这台上的主从复制
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
查看从库状态
mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.28.18.69 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-1.000001 Read_Master_Log_Pos: 154 Relay_Log_File: node2-relay-bin.000002 Relay_Log_Pos: 319 Relay_Master_Log_File: master-1.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: 154 Relay_Log_Space: 526 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: 69 Master_UUID: c76bbd08-7acd-11e9-badd-14feb5dc2c77 Master_Info_File: /home/mysql-5.7.26/data/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:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes从库同步线程启动成功，同样在另外一台服务器上也执行上述的操作，实际上主主配置就是互为主从配置。配置完后，在两台服务器上都可以查看主库和从库的状态
六、测试数据
在其中一台上插入数据，另外一台立即可以同步数据，表示配置成功。
首先在两台服务器上分别执行查询，看看表里数据是否一致
mysql> select * from test; +------+------+ | id | name | +------+------+ | 1 | aaaa | | 2 | bbbb | | 3 | ccc | +------+------+ 3 rows in set (0.00 sec)
mysql> select * from test; +------+------+ | id | name | +------+------+ | 1 | aaaa | | 2 | bbbb | | 3 | ccc | +------+------+
两台服务器数据一致，在其中一台插入数据
mysql> insert into test values(4,'dddd'); Query OK, 1 row affected (0.03 sec) mysql> select * from test; +------+------+ | id | name | +------+------+ | 1 | aaaa | | 2 | bbbb | | 3 | ccc | | 4 | dddd | +------+------+ 4 rows in set (0.00 sec)
另外一台查询
mysql> select * from test; +------+------+ | id | name | +------+------+ | 1 | aaaa | | 2 | bbbb | | 3 | ccc | | 4 | dddd | +------+------+ 4 rows in set (0.00 sec)
插入的数据已经同步了
再在这台服务器上插入数据
mysql> insert into test values(5,'eeee'); Query OK, 1 row affected (0.02 sec) mysql> select * form test -> ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form test' at line 1 mysql> select * from test; +------+------+ | id | name | +------+------+ | 1 | aaaa | | 2 | bbbb | | 3 | ccc | | 4 | dddd | | 5 | eeee | +------+------+ 5 rows in set (0.00 sec)
在另外一台查询
mysql> select * from test; +------+------+ | id | name | +------+------+ | 1 | aaaa | | 2 | bbbb | | 3 | ccc | | 4 | dddd | | 5 | eeee | +------+------+ 5 rows in set (0.00 sec)
数据同步了，至此mysql主主配置成功。