MySQL线上与本地实时同步
2016-11-15
运行环境：
线上：RDS（内网）+ECS
本地：xampp
由于本地只能访问ECS，需要在ECS上架设mysql代理：
参考这篇笔记：http://git.malu.me/MySQL-Proxy/
mysql线上与本地实现主从同步
ECS配置（作为主服务器master）:
修改配置文件:
#vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #[必须]启用二进制日志
server-id=1 #[必须]服务器唯一ID，默认是1
重启mysql：
/etc/init.d/mysql restart
在主服务器上建立帐户mysync并授权给slave主机:
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）：
修改配置文件:
X:\Xampp\mysql\bin\my.ini #xampp默认配置文件
[mysqld]
log-bin=mysql-bin #[不是必须]启用二进制日志
server-id=2 #[必须]服务器唯一ID，默认是1，不要与master一样
配置Slave：
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）:
[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）：
[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执行：
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执行：
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主从自动切换脚本:
#!/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