如何设置MySQL主 - 主复制

本文将来自多个来源的信息合并到我用于设置MySQL主/主复制的格式。 Linux和操作系统的美丽...

本文将来自多个来源的信息整合到我用于设置MySQL主/主复制的格式。 Linux和开源的优点在于有很多不同的方法来做到这一点。 请查看我的参考资料,并使用它们来满足您可能需要的任何需求。 如果您有任何问题或遇到任何问题,请随时给我留言。

假设

本文假设您已经将MySQL安装到每个服务器上。 如果不是,您可以通过MySQL网站https://www.mysql.org/downloads轻松实现。 本文尚未在MariaDB上进行测试,但如果您更喜欢使用MariaDB,则应该工作。

将SELINUX更改为允许(如果已安装)

服务器A

[root@mysqla ~]# vi /etc/selinux/config

  
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted
	

服务器B

[root@mysqlb ~]# vi /etc/selinux/config

  
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of these two values:
#     targeted - Targeted processes are protected,
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted
	

停止并禁用每个服务器上的firewalld

服务器A

[root@mysqla ~]# systemctl stop firewalld
[root@mysqla ~]# systemctl disable firewalld

运行以下命令确保没有防火墙规则

[root@mysqla ~]# iptables -L

结果应该是:

Chain INPUT (policy ACCEPT)
target     prot opt source               destination

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

服务器B

[root@mysqlb ~]# systemctl stop firewalld
[root@mysqlb ~]# systemctl disable firewalld

运行以下命令确保没有防火墙规则。

[root@mysqlb ~]# iptables -L

结果应该是:

Chain INPUT (policy ACCEPT)
target     prot opt source               destination

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

在两台服务器上编辑/etc/my.cnf

将以下信息添加到[mysqld]部分的底部

服务器A

[root@mysqla ~]# vi /etc/my.cnf

	
    server-id=1
    log-bin="mysql-bin"
    binlog-do-db=name_of_database
    replicate-do-db=name_of_database
    relay-log="mysql-relay-log"
    auto-increment-offset = 1
    

服务器B

[root@mysqlb ~]# vi /etc/my.cnf

    
    server-id=2
    log-bin="mysql-bin"
    binlog-do-db=name_of_database
    replicate-do-db=name_of_database
    relay-log="mysql-relay-log"
    auto-increment-offset = 2
    

确保将name_of_database替换为要复制的数据库的名称

重新启动并启用每个服务器上的MySQL守护程序

服务器A

[root@mysqla ~]# systemctl restart mysqld

[root@mysqla ~]# systemctl enable mysqld

服务器B

[root@mysqlb ~]# systemctl restart mysqld

[root@mysqlb ~]# systemctl enable mysqld

在每个服务器上创建复制器用户

[root@mysqla ~]# mysql -u root -p

mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'change_me';
mysql> GRANT REPLICATION SLAVE ON foo.* TO 'replicator'@'%'

[root@mysqlb ~]# mysql -u root -p

mysql> CREATE USER 'replicator'@'%' IDENTIFIED BY 'change_me';
mysql> GRANT REPLICATION SLAVE ON foo.* TO 'replicator'@'%'

获取在其他服务器上使用的日志文件信息

服务器A

[root@mysqla ~]# mysql -u root -p

mysql> SHOW MASTER STATUS;

+------------------+----------+------------------+------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000001 | 154      | name_of_database |                  |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)

请注意此命令中的“文件”和“位置”

服务器B

[root@mysqlb ~]# mysql -u root -p

mysql> STOP SLAVE;

mysql> CHANGE MASTER TO MASTER_HOST = 'Server A IP Address or HOSTNAME',MASTER_USER = 'replicator', MASTER_PASSWORD = 'change_me', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154;
mysql> START SLAVE;

在服务器B上重复相同的步骤

服务器B

[root@mysqlb ~]# mysql -u root -p mysql> SHOW MASTER STATUS;

+------------------+----------+------------------+------------------+
| File             | Position | Binlog_Do_DB     | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000001 | 154      | name_of_database |                  |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)

请注意此命令中的“文件”和“位置”

服务器A

[root@mysqla ~]# mysql -u root -p

mysql> STOP SLAVE; CHANGE MASTER TO MASTER_HOST = 'Server B IP Address or HOSTNAME', MASTER_USER = 'replicator', MASTER_PASSWORD = 'passw0rd', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 154;
mysql> START SLAVE;

重新启动两台服务器

服务器A

[root@mysqla ~]# systemctl reboot

服务器B

[root@mysqlb ~]# systemctl reboot

在任一服务器上创建数据库

[root@mysqla ~]# mysql -u root -p

mysql> CREATE DATABASE foo;

在其他服务器上检查数据库是否存在

[root@mysqlb ~]# mysql -u root -p

mysql> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| foo                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

来源