双主mysql搭建

相同版本的mysql分别部署在两台服务器上(当然一台也可以配置一下my.cnf中的端口号就后好了).

两台服务器的ip为192.168.100.85和192.168.100.86
修改85上的my.cnf配置文件

[root@node13 bin]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
max_allowed_packet= 64M
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names = 1
# 以下为新增
# 设置3306端口
port = 3306
server_id = 1
 #打开二进制功能,MASTER主服务器必须打开此项
log-bin= mysql-bin
binlog_format = mixed

read-only=0
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#奇数自增
auto-increment-offset=1
auto-increment-increment=2

#主主复制的从库设置（新增）
#replicate-do-db=test
#忽略不同步主从的数据库
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on

修改86上的my.cnf配置文件

[mysqld]
basedir=/usr/local/mysql/
datadir=/usr/local/mysql/data/
max_allowed_packet= 64M
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names = 1
character_set_server = utf8

# 设置3306端口
port = 3306
server_id = 2
log-bin= mysql-bin
binlog_format = mixed

read-only=0
#replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
relay_log=mysql-relay-bin
log-slave-updates=on

#主主复制的主库设置（新增）
#binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#偶数自增
auto-increment-offset=2
auto-increment-increment=2

分别登陆85和86的mysql,都执行以下操作:

[root@node13 bin]# cd /usr/local/mysql/bin/
[root@node13 bin]# ./mysql -uroot -p
Enter password: 
输入你的mysql密码
mysql> GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'mysync';

85上执行如下操作:其中master_log_file,master_log_pos这两个参数的值是在86上执行如下命令得到的,其中File对应master_log_file,Position 对应master_log_pos.

mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000001 |     154  |              | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+

mysql> stop slave;
mysql> flush logs;
mysql> change master to
    -> master_host='192.168.100.86',
    -> master_user='mysync',
    -> master_password='mysync',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos= 154, master_connect_retry=30;

mysql> start slave;

86上执行如下操作:并且 master_log_file,master_log_pos这个两个参数是在85数据库中执行show master status;得到的.

mysql> stop slave;
mysql> flush logs;
mysql> change master to
    -> master_host='192.168.100.85',
    -> master_user='mysync',
    -> master_password='mysync',
    -> master_port=3306,
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos= 444, master_connect_retry=30;
mysql> start slave;

如何验证双主是否搭建成功
两台服务器上分别执行,如果都为Yes表示搭建成功.当然也可以去创建库表看是否数据会有同步来进行验证.

mysql> show slave status\G;
*************************** 1. row ***************************
..前面省略..
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
..后面省略..

最后编辑于：2020.10.16 17:16:58