Jarod's Blog

0%

MySQL线上与本地实时同步

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

运行环境：

线上：RDS（内网）+ECS

本地：xampp

由于本地只能访问ECS，需要在ECS上架设mysql代理：

参考这篇笔记：http://git.malu.me/MySQL-Proxy/

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

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

修改配置文件:

1
2
#vim /etc/my.cnf

重启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
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

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

修改配置文件:

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

配置Slave：

1
2
3
4
5
6
7
8
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的状态

接下来往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
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为隧道入口

本地xampp执行：

1
2
3
4
5
6
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主从自动切换脚本:

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