阿里云RDS和本地mysql做主从同步

一、从RDS备份要同步的数据库到本地服务器
建议将表结构和数据分开备份。

[root@localhost ~]# mysqldump -hrm-wz9a985njgong7yh5.mysql.rds.aliyuncs.com -ujiangyicheng -p --opt -d article > /root/article_opt.sql
[root@localhost ~]# mysqldump -hrm-wz9a985njgong7yh5.mysql.rds.aliyuncs.com -ujiangyicheng -p -t article > /root/article_data.sql

二、在本地安装mysql，修改相关配置，导入数据
mysql安装过程在此省略。
2.1、启动mysql，修改用户密码，授予用户权限

[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# grep "password" /var/log/mysqld.log
mysql> alter user root@'localhost' identified by "AAAbbb123!!!";
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' identified by "AAAbbb123!!!";
mysql> source /root/article_opt.sql
mysql> source /root/article_data.sql

2.2、修改mysql配置/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

server_id = 175
replicate-do-db = article #设置要同步的库
gtid_mode=on #打开gtid同步
enforce_gtid_consistency= 1
log-slave-updates=1
binlog_format = row #binlog日志格式
max_binlog_size = 500M #一个日志文件的大小
expire_logs_days = 10 #保留10天的日志

2.3、重启MySQL

[root@localhost ~]# systemctl start mysqld

三、配置主从同步
3.1、在本地MySQL执行主从同步

mysql> change master to \
    -> master_host='rm-wz9a985njgong7yh5.mysql.rds.aliyuncs.com', \
    -> master_user='jiangyicheng',master_port=3306,master_password='jiangyicheng', \
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

3.2、手工修改 GTID_PURGED 值
3.2.1、查看备份文件中的GTID_PURGED值

[root@localhost ~]# more /root/article_data.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: rm-wz9a985njgong7yh5.mysql.rds.aliyuncs.com    Database: article
-- ------------------------------------------------------
-- Server version       5.7.20-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup 
--

SET @@GLOBAL.GTID_PURGED='d0b134a8-afe3-11e8-9107-6c92bf464e82:1-6772909,
dec575e8-afe3-11e8-9007-7cd30ac4f854:1-4487736';

3.2.2、重置GTID_PURGED值，

mysql> reset slave;
mysql> SET @@GLOBAL.GTID_PURGED='d0b134a8-afe3-11e8-9107-6c92bf464e82:1-6772909,
dec575e8-afe3-11e8-9007-7cd30ac4f854:1-4487736';

3.3、启动并查看slave状态

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

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: rm-wz9a775njgong7yh5.mysql.rds.aliyuncs.com
                  Master_User: java_service
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001559
          Read_Master_Log_Pos: 30963571
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 10232286
        Relay_Master_Log_File: mysql-bin.001559
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: article
          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: 30963571
              Relay_Log_Space: 24775562
              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: 2891616265
                  Master_UUID: dec575e8-afe3-11e8-9007-7cd30ac4f854
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: dec575e8-afe3-11e8-9007-7cd30ac4f854:4487665-4501058
            Executed_Gtid_Set: d0b134a8-afe3-11e8-9107-6c92bf464e82:1-6772909,
dec575e8-afe3-11e8-9007-7cd30ac4f854:1-4501058
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

完成。