linux mysql5.7主主同步

服务器A：0.0.0.1

服务器B： 0.0.0.2

 

1. 首先在两个服务器上分别插入可以访问对方服务器的账号

在服务器A中设置：

create user 'myuser'@'0.0.0.2' identified by 'myuser';

grant all on *.* to 'myuser'@'0.0.0.2';

grant replication slave on *.* to 'myuser'@'0.0.0.2';

服务器B则添加一模一样的账号：

create user 'myuser'@'0.0.0.1' identified by 'myuser';

grant all on *.* to 'myuser'@'0.0.0.1';
grant replication slave on *.* to 'myuser'@'10.0.0.1';

2. 然后要在my.cnf中修改设置

就是这一步！！我累个去的网上的都是旧版本的不顶用了~！

服务器A作为主服务器，注意server-id=1

在服务器A中的/etc/my.cnf中添加如下设置：

[client]
port=3306
default-character-set=utf8mb4
[mysql]
port=3306
default-character-set=utf8mb4
[mysqld]
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true

server-id = 1#id的是唯一的
auto_increment_offset=1#id开始值
auto_increment_increment=2#自增量
log-bin=mysql-bin
max_binlog_size=1024M

binlog-ignore-db = mysql#不同步的数据库
binlog-ignore-db = information_schema
binlog-ignore-db = performation_schema
binlog-ignore-db = sys
binlog-do-db=test#同步的数据库

replicate-do-db=test#同步的数据库

服务器B作为主服务器，注意server-id=2

在服务器B中的/etc/my.cnf中添加如下设置：

[client]
port=3306
default-character-set=utf8mb4
[mysql]
port=3306
default-character-set=utf8mb4
[mysqld]
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.err
pid-file=/data/mysql/mysql.pid
#character config
character_set_server=utf8mb4
symbolic-links=0
explicit_defaults_for_timestamp=true

server-id = 2
auto_increment_offset=2
auto_increment_increment=2
log-bin=mysql-bin
max_binlog_size=1024M

binlog-ignore-db = mysql#不同步的数据库
binlog-ignore-db = information_schema
binlog-ignore-db = performation_schema
binlog-ignore-db = sys
binlog-do-db=test#同步的数据库

replicate-do-db=test#同步的数据库

4. 这时候重启服务器A\B的MySQL，应该是成功进入的

登录root 后执行下面的命令

使用命令show master status应该能看到下面结果：

+---------------+----------+--------------+--------------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+---------------+----------+--------------+--------------------------+-------------------+
| my-bin.000001 |      154 |              | information_schema,mysql |                   |
+---------------+----------+--------------+--------------------------+-------------------+
————————————————

5. 分别改变master的设置

A服务器：

change master to master_host='0.0.0.2', master_user='myuser', master_password='myuser', master_log_file='mysql-bin.000001', master_log_pos=154;

B服务器：

change master to master_host='0.0.0.1', master_user='myuser', master_password='myuser', master_log_file='mysql-bin.000001', master_log_pos=154;

这里讲解一下，master_log_file就是你用show master status命令看到的file一行的内容，而master_log_pos则是position的内容，不要设置错了

 

6. 然后在A、B服务器上开启slave（在登录状态下执行）

start slave;

7. 查看slave状态

show slave status\G

我截取一部分展示

  Slave_IO_State:
                  Master_Host: 0.0.0.1
                  Master_User: pccw
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: my-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: apisec-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: my-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mobile
          Replicate_Ignore_DB: mysql,information_schema
中间有颜色的那俩货是YES，就成功了