2 年前写过一篇《MySql 主从复制配置》,好久没接触 MySql 相关的东西了,最近刚好有个配置两台数据库做互备的需求,做一个双主互备主要为了一个是做一个数据备份,其次两台机子都可以读写操作,也可以 mysql 做负载均衡吧。配置过程相对还是蛮简单的,差不多就是主从配置再逆操作一下的感觉。

假设两台 MySql 的 IP 分别为:

假设两台 MySql 的 IP 分别为: MySql-Master 192.168.0.1

假设两台 MySql 的 IP 分别为: MySql-Master 192.168.0.1 MySql-Slave 192.168.0.2

1.配置 MySql-Master

~# vi /etc/mysql/my.cn
server-id               = 238 #唯一id,一般用ip的最后一段
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_ignore_db        = mysql #设置不用同步的表
binlog_ignore_db        = information_schema
binlog_ignore_db        = performance_schema
auto-increment-increment = 2 #因为我们有两台机子,这里插入数据的自增长设为2
auto-increment-offset = 1 #这个库中每插入一条自增长的偏移量为1

2.重启一下 mysql

~# service mysql restart

3.设置一个复制 master 的 mysql 用户给 slave

~# mysql -uroot -p'welcome'
mysql>  CREATE USER rep1@'192.168.0.2' IDENTIFIED BY 'welcome';
mysql> GRANT REPLICATION SLAVE ON *.* TO rep1@'192.168.0.1';

4.查看一下 master 的状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |       435 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.配置 MySql-Slave

~# vim /etc/mysql/my.cnf
server-id               = 129
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
replicate-ignore-db     = mysql #不用复制的表
replicate-ignore-db     = information_schema
replicate-ignore-db     = performance_schema
log-slave-updates       = ON
relay_log               = mysqld-relay-bin

6.重启一下 mysql

~# service mysql restart

7.设置一下 master,并查看一下 slave 状态,确认一下是否 OK

mysql> change master to
    > master_host='192.168.0.1',
    > master_user='rep1',
    > master_password='welcome',
    > master_log_file='mysql-bin.000002',
    > master_log_pos=329;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

8.创建一个 mysql 用户给 master 来复制 slave

mysql>  CREATE USER rep2@'192.168.1.1' IDENTIFIED BY 'welcome';
mysql> GRANT REPLICATION SLAVE ON *.* TO rep2@'rep2@'192.168.1.1';

9.配置 salve 的自增长和偏移

~# vim /etc/mysql/my.cnf

read-only               = 0
binlog-ignore-db        = mysql
binlog-ignore-db        = information_schema
binlog-ignore-db        = performance_schema
auto-increment-increment = 2
auto-increment-offset = 2

~# service mysql restart

10.设置 master 复制 slave,其实就是把 slave 当真 master 的主来复制

~# vim /etc/mysql/my.cnf
replicate-ignore-db     = mysql
replicate-ignore-db     = information_schema
replicate-ignore-db     = information_schema
relay_log               = mysqld-relay-bin
log-slave-updates       = ON

mysql> change master to
    > master_host='192.168.0.2',
    > master_user='rep2',
    > master_password='welcome',
    > master_log_file='mysql-bin.000008',
    > master_log_pos=433;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

~# service mysql restart

OK,可以测试一下导入数据库,添删改查一下试试。

转载请注明: 转载自Yuansir-web 菜鸟 | LAMP 学习笔记

本文链接地址: MySql 双主互备复制配置

知识共享许可协议 本作品采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可