MySQL线上与本地实时同步

发表于 更新于 分类于 本文字数： 7.5k 阅读时长 ≈ 7 分钟

运行环境：

线上：RDS（内网）+ECS 本地：xampp 由于本地只能访问ECS，需要在ECS上架设mysql代理： 参考这篇笔记：http://git.malu.me/MySQL-Proxy/

mysql线上与本地实现主从同步

ECS配置（作为主服务器master）:

修改配置文件:

1
2
3
4
5
#vim /etc/my.cnf

   [mysqld]
   log-bin=mysql-bin   #[必须]启用二进制日志
   server-id=1         #[必须]服务器唯一ID，默认是1

重启mysql：

1
/etc/init.d/mysql restart

在主服务器上建立帐户mysync并授权给slave主机:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql -uroot -h localhost

mysql> GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'passwd';

mysql> show master status;    #查看master的状态
+------------------+----------+--------------+------------------+
 File              Position  Binlog_Do_DB  Binlog_Ignore_DB 
+------------------+----------+--------------+------------------+
 mysql-bin.000002       532                                 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值Position变化

mysql> show master logs;    #查看master二进制日志位置

mysql> show variables like 'server_id';   #查看server_id

mysql> SET GLOBAL server_id=2;            #临时设置server_id，下次重启会失效

本地xampp配置（作为从服务器slave）：

修改配置文件:

1
2
3
4
5
X:\Xampp\mysql\bin\my.ini   #xampp默认配置文件

[mysqld]
log-bin=mysql-bin   #[不是必须]启用二进制日志
server-id=2         #[必须]服务器唯一ID，默认是1，不要与master一样

配置Slave：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
mysql> change master to  master_host='mysql.malu.me',master_port=3306,master_user='mysync',master_password='passwd',master_log_file='mysql-bin.000001',master_log_pos=532;

mysql> start slave;          #启动从服务器复制功能

注：master_log_pos即为master主机状态里的Position

mysql> show slave status\G   #检查slave的状态

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql.malu.me
                  Master_User: mysync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 532
               Relay_Log_File: mysql-relay-bin.000008
                Relay_Log_Pos: 695
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 532
              Relay_Log_Space: 868
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID:
             Master_Info_File: X:\Xampp\mysql\data2\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0

接下来往RDS写入数据的时候会自动同步到本地mysql

mysql线上与本地实现主主同步

双主架构需要mysql双方能互相通讯，但是本地mysql在内网，所以必须先让ECS穿透到内网: 内网穿透参考这篇笔记：http://git.malu.me/二级proxy部署/

ECS （作为master-A）:

1
2
3
4
5
6
7
8
9
10
11
[mysqld]
log-bin=mysql-bin                           #开启二进制日志功能
server-id=1                                 #注意server-id的唯一性
binlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库
relay-log=/app/mysql/relaylogs/relay-bin    #指定中继日志路径
log_slave_updates=1                         #开启从库更新操作写入二进制日志功能*
auto_increment_increment=2                  #双主复制中自增长字段的步长*
auto_increment_offset=1                     #双主复制中自增长字段的起始值，主为1 副为2*
sync_binlog = 1                             #可保证事务日志及时写入磁盘文件
binlog-do-db = TestXXX                      #指定binlog日志记录哪个db
slave-skip-errors                           #定义复制过程中从服务器可以自动跳过错误

本地xampp（作为master-B）：

1
2
3
4
5
6
7
8
9
10
11
[mysqld]
log-bin=mysql-bin                           #开启二进制日志功能
server-id=2                                 #注意server-id的唯一性
binlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库
relay-log=/app/mysql/relaylogs/relay-bin    #指定中继日志路径
log_slave_updates=1                         #开启从库更新操作写入二进制日志功能*
auto_increment_increment=2                  #双主复制中自增长字段的步长*
auto_increment_offset=2                     #双主复制中自增长字段的起始值，主为1 副为2*
sync_binlog = 1                             #可保证事务日志及时写入磁盘文件
binlog-do-db = TestXXX                      #指定binlog日志记录哪个db
slave-skip-errors                           #定义复制过程中从服务器可以自动跳过错误

ECS执行：

1
2
3
4
5
6
7
mysql> GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'passwd';
mysql> flush  privileges;
mysql> change master to  master_host='127.0.0.1',master_port=3307,master_user='mysync',master_password='passwd',master_log_file='mysql-bin.000001',master_log_pos=532;

注：3307为隧道入口

mysql> start slave;          #启动master-A复制功能

本地xampp执行：

1
2
3
4
5
6
7
mysql> GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'passwd';
mysql> flush  privileges;
mysql> change master to  master_host='mysql.malu.me',master_port=3306,master_user='mysync',master_password='passwd',master_log_file='mysql-bin.000001',master_log_pos=532;

mysql> start slave;          #启动master-B复制功能

mysql> show slave status\G   #检查slave的状态

接下来写入数据试试吧！ 附录： mysql主从自动切换脚本:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#!/bin/bash
cat << README
#####################################################################################################
#Step 1: point the slave IP                                                                         #
#Step 2: check the master and slave information whether good for change                             #
#Step 3: stop old slave,then get new master binlog name and postation,then execute change master to #
#Step 4: start slave,and show whether change successed.                                             #
#####################################################################################################
README
User=root
PW=123456
read -p "-- Please input the slave IP:" Slave
Master=$(mysql -u${User} -h${Slave} -p${PW} -e "show slave status \G;"awk '/Master_Host/{print $2}')
 if [ -n "${Master}" ]
 then
echo -e "--Master IP:${Master},Slave IP:${Slave}"
M=$(mysql -u${User} -h${Master} -p${PW} -e "show master status;"awk 'NR==2{print $2}')
S=$(mysql -u${User} -h${Slave} -p${PW} -e "show slave status \G;"awk '/Read_Master_Log_Pos/{print $2}')
echo -e "-- master pos:${M};slave pos is:${S}"
 else
echo "-- Slave IP input wrong,please input again ! "
exit 1
 fi
       if [ "${M}" -eq "${S}" ]
       then
            read -p "-- Master-Slave is accordance,input Yes to start changing:" var
            case "$var" in
                [Yy]es)
                    mysql -u${User} -h${Slave} -p${PW} -e "stop slave;reset slave;change master to master_host='';"
                    Pos=$(mysql -u${User} -h${Slave} -p${PW} -e "show master status;"awk 'NR==2{print $2}')
                    File=$(mysql -u${User} -h${Slave} -p${PW} -e "show master status;"awk 'NR==2{print $1}')
                    mysql -u${User} -h${Master} -p${PW} -e "stop slave;
                    change master to master_user='ideal',master_host='${Slave}',master_password='123456',master_log_file='${File}',master_log_pos=${Pos};
                   start slave;"
                   ;;
                *)
                   echo "-- error input .... exit!"
                   ;;
           esac
               echo "-- changing, please wait 3s...."
               sleep 3
               echo "-- change successfull,the new master is:${Slave},new slave is ${Master}"
                SlaveIOStatus=$(mysql -u${User} -h${Master} -p${PW} -e "show slave status \G;"awk '/Slave_IO_Running/{print $2}')
                SlaveSQLStatus=$(mysql -u${User} -h${Master} -p${PW} -e "show slave status \G;"awk '/Slave_SQL_Running/{print $2}')
                printf "The new master is: \n Slave_IO_Running=${SlaveIOStatus}\n"
                printf "Slave_SQL_Running=${SlaveSQLStatus}\n"
       else
          echo "node change failed please change again ! "
          exit 1
       fi