mysql双主架构搭建
mysql双主架构搭建
一、分配主机IP
1、第一台主数据库:192.168.154.93
2、第二台主数据库:192.168.154.94
二、修改主数据库配置文件
# cat /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql,information_schema,performance_schema
auto_increment_offset=1
auto_increment_increment=2
# systemctl restart mysqld
三、在主数据库中创建主从复制帐号（授权给从数据库服务器）
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%' IDENTIFIED BY 'root';
mysql> FLUSH PRIVILEGES;
四、查询主数据库状态 （记录下返回结果的File列和Position列的值）
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 418
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
五、修改第二台数据库配置文件
第二台主数据库:192.168.10.70
1.配置主机:192.168.10.70
（2）修改文件
# vi /etc/my.cnf
[mysqld]
server_id=2
log-bin=mysql-bin
replicate-ignore-db=mysql,information_schema,performance_schema
binlog-ignore-db=mysql,information_schema,performance_schema
auto_increment_offset=2
auto_increment_increment=2
log-slave-updates
# systemctl restart mysqld
（3）创建用户并授权
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%' IDENTIFIED BY 'root';
mysql> FLUSH PRIVILEGES;
3.查看第一台主数据库的状态
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 418
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
4.在第二台主数据库上同步第一台
mysql> STOP SLAVE;
mysql> change master to master_host='192.168.154.93',
master_user='slave',
master_password='root',
master_log_file='mysql-bin.000002',
master_log_pos=418,
master_connect_retry=30;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.在第一台主数据库上同步第二台
先查看第二台主数据库状态
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 428
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
1 row in set (0.00 sec)
然后到第一台主数据库上操作如下：
mysql> STOP SLAVE;
mysql> change master to master_host='192.168.154.94',
master_user='slave',
master_password='root',
master_log_file='mysql-bin.000001',
master_log_pos=428,
master_connect_retry=30;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
至此搭建完成。
报错1：[ERROR] Slave I/O: 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. Error_code: 1593
解决方案：
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak
[root@localhost ~]# systemctl start mysqld.service
