mysql互为主从(双主)配置
环境:
ubuntu18.04.2
mysql5.7.21
---------------master1服务器操作记录---------------
在myf⽂件的[mysqld]配置区域添加下⾯内容:
[root@master1 ~]# vim /etc/myf
server-id = 1
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
[root@master1 ~]# cd /usr/local/mysql/support-files
[root@master1 ~]#./mysql.server restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
数据同步授权(iptables防⽕墙开启3306端⼝)这样I/O线程就可以以这个⽤户的⾝份连接到主服务器,并且读取它的⼆进制⽇志。mysql>grant all privileges on *.* to root@'%' identified by "123456";
#grant replication slave,replication client on *.* to root@'192.168.85.%' identified by "123456";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
最好将库锁住,仅仅允许读,以保证数据⼀致性;待主主同步环境部署后再解锁;
锁住后,就不能往表⾥写数据,但是重启mysql服务后就会⾃动解锁!
mysql> flush tables with read lock; //注意该参数设置后,如果⾃⼰同步对⽅数据,同步前⼀定要记得先解锁!
Query OK, 0 rows affected (0.00 sec)
查看下log bin⽇志和pos值位置
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000004 | 430 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
---------------master2服务器操作记录---------------
在myf⽂件的[mysqld]配置区域添加下⾯内容:
[root@master2 ~]# vim /etc/myf
server-id = 2
log-bin = mysql-bin
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment = 2
auto-increment-offset = 2
slave-skip-errors = all
[root@master1 ~]# cd /usr/local/mysql/support-files
[root@master1 ~]#./mysql.server restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
mysql> grant replication slave,replication client on *.* to root@'192.168.85.%' identified by "123465";
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 | 430 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
---------------master1服务器做同步操作---------------
mysql> unlock tables; //先解锁,将对⽅数据同步到⾃⼰的数据库中
mysql> slave stop;
mysql> change master to master_host='192.168.85.141',master_user='root',master_password='12346',master_log_file='mysql-bin.000003',master_log_pos=430;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看同步状态,如下出现两个“Yes”,表明同步成功!
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 182.148.15.237
Master_User: wang
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 430
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 279
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.........................
Seconds_Behind_Master: 0
.........................
这样,master1就和master2实现了主从同步,即master1同步master2的数据。
---------------master2服务器做同步操作---------------
mysql> unlock tables; //先解锁,将对⽅数据同步到⾃⼰的数据库中
mysql> slave stop;
mysql> change master to master_host='192.168.85.140',master_user='root',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=430;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 182.148.15.238
Master_User: wang
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 430
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 279
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
........................
Seconds_Behind_Master: 0
........................
这样,master2就和master1实现了主从同步,即master2也同步master1的数据。
以上表明双⽅已经实现了mysql主主同步。
当运⾏⼀段时间后,要是发现同步有问题,⽐如只能单向同步,双向同步失效。可以重新执⾏下上⾯的change master同步操作,只不过这样同步后,只能同步在此之后的更新数据。下⾯开始进⾏数据验证:
-----------------主主同步效果验证---------------------
1)在master1数据库上写⼊新数据
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> create database huanqiu;
Query OK, 1 row affected (0.01 sec)
mysql> use huanqiu;
Database changed
mysql> create table if not exists haha (
-> id int(10) PRIMARY KEY AUTO_INCREMENT,
-> name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into haha values(1,"王⼠博");
Query OK, 1 row affected (0.00 sec)
mysql> insert into haha values(2,"郭慧慧");
Query OK, 1 row affected (0.00 sec)
mysql> select * from haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 王⼠博 |
| 2 | 郭慧慧 |
+----+-----------+
2 rows in set (0.00 sec)
然后在master2数据库上查看,发现数据已经同步过来了!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| huanqiu |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use huanqiu;
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_huanqiu |
+-------------------+
| haha |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 王⼠博 |
| 2 | 郭慧慧 |
+----+-----------+
2 rows in set (0.00 sec)
2)在master2数据库上写⼊新数据
mysql> create database hehe;
Query OK, 1 row affected (0.00 sec)
mysql> insert into huanqiu.haha values(3,"周正"),(4,"李敏"); Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
然后在master1数据库上查看,发现数据也已经同步过来了! mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hehe |
| huanqiu |
| mysql |
| performance_schema |
| test |
+--------------------+
ubuntu网络配置6 rows in set (0.00 sec)
mysql> select * from huanqiu.haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 王⼠博 |
| 2 | 郭慧慧 |
| 3 | 周正 |
| 4 | 李敏 |
+----+-----------+
4 rows in set (0.00 sec)
⾄此,Mysql主主同步环境已经实现。
---恢复内容结束---
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论