本文将来自多个来源的信息整合到我用于设置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)