阿里云RDS(基础版)及阿里云ECS自建Mysql5.7主从

参考文章:
新主从数据库搭建–>>http://blog.csdn.net/abcdocker/article/details/71249809
RDS高级版主从ECS搭建–>>http://blog.csdn.net/dongsong1117/article/details/51800072

• 主从基础环境配置
• 数据迁移关系及FAQ
• 总结

---

基础环境搭建

RDS为阿里云数据库,不提供linux控制台,只能通过从库远程mysql登入;ECS服务器部署自建mysql作为自读服务,很多文章都讲到RDS->ECS主从,但大部分都是高级版RDS,本文涵盖高级RDS及基础RDS区别

1.1RDS账户配置

1.2ECS自建数据库配置

假设大家已经在ECS上已经装好mysql….如果自动安装后不知道配置文件在哪,可以whereis mysql查询相关位置,

root@iZwz98pmxwulw5x6ekf1r2Z:~# whereis mysql
mysql: /usr/bin/mysql /usr/lib/mysql /etc/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz
root@iZwz98pmxwulw5x6ekf1r2Z:~# cd /etc/mysql
root@iZwz98pmxwulw5x6ekf1r2Z:/etc/mysql# ls
conf.d  debian.cnf  debian-start  my.cnf  my.cnf.fallback  mysql.cnf  mysql.conf.d
root@iZwz98pmxwulw5x6ekf1r2Z:/etc/mysql# cd mysql.conf.d
root@iZwz98pmxwulw5x6ekf1r2Z:/etc/mysql/mysql.conf.d# ls
mysqld.cnf  mysqld_safe_syslog.cnf
root@iZwz98pmxwulw5x6ekf1r2Z:/etc/mysql/mysql.conf.d# vi mysqld.cnf

MYSQL5.7后提供俩个配置文件,分别在conf.d和mysql.conf.d;其中conf.d/mysql.cnf为自己配置,mysql.conf.d/mysqld.conf为服务配置,没区别,方便区分;其实就是my.cnf引用单独俩个配置文件

系统版本
root@iZwz98pmxwulw5x6ekf1r2Z:~# uname -a
Linux iZwz98pmxwulw5x6ekf1r2Z 4.4.0-62-generic #83-Ubuntu SMP Wed Jan 18 14:10:15 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux

mysql版本
root@iZwz98pmxwulw5x6ekf1r2Z:~# mysql -V
mysql  Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using  EditLine wrapper

MySQL配置文件
root@iZwz98pmxwulw5x6ekf1r2Z:/etc/mysql/mysql.conf.d# vi mysqld.cnf

server_id=211
read_only   = 1
port = 3307
replicate-wild-ignore-table     = mysql.%
replicate-wild-ignore-table     = test.%
binlog_format   = ROW
log-bin                  = mysql-bin
log-bin-index            = mysql-bin.index
relay-log                = relay-log
relay_log_index          = relay-log.index

其中:port不能和主库一样,replicate-wild-ignore-table和replicate-wild-ignore-table是作为过滤数据库同步的,可多个,以前是设置replicate_do_db或replicate_ignore_db但存在更新问题,参见:http://blog.csdn.net/tlpower/article/details/7891870

当在主库存在的库而从库不存在的库同步时,会出现sql错误,这时候可以排除或者从库手动导入主库数据库;

其中replicate-wild-ignore-table代表需要排除的表,写法[数据库名.%]
不建议使用,具体原因见 不建议使用从库过滤DB同步关键字的原因
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:

1.2.1GUID/binlog同步方式

参考：MySQL GTID是在传统的mysql主从复制的基础之上演化而来的产物，即通过UUID加上事务ID的方式来确保每一个事物的唯一性。这样的操作方式使得我们不再需要关心所谓的log_file和log_Pos，只是简单的告诉从库，从哪个服务器上去找主库就OK了。简化了主从的搭建以及failover的过程，同时比传统的复制更加安全可靠。由于GTID是连续没有空洞的，因此主从库出现数据冲突时，可以通过注入空事物的方式进行跳过。本文主要讲述GTID主从架构的错误处理方式

具体原理:[MySQL主从复制原理、半同步操作步骤及原(https://www.abcdocker.com/abcdocker/42)

binlog方式命令过程

-- 从RDS主库导出数据
**linux命令行下:**/usr/bin/mysqldump -uroot -p1 -h[RDS内网地址] --lock-tables --events --triggers --routines --flush-logs --master-data=2 --default-character-set=utf8mb4 --databases stockgang > /data/db.sql

--databases stockgang为需要导出数据库
--导入主库数据到从库
**linux命令行下:**mysql -uroot -p1  stockgang < /data/db.sql

--查看主库postion位置,2种方式
1.**linux命令行下:**head /data/db.sql -n80 | grep "MASTER_LOG_POS"
2.登录主库mysql,后sql执行show master status;

--建立主从关系
**登录从库后sql命令行下:**change master to MASTER_HOST='RDS内网地址',master_user='slave',master_password='1',MASTER_LOG_FILE='mysql-bin.000325',MASTER_LOG_POS=194;
后面俩个参数为上面那条命令查询的日志文件,和position位置;

--启动从服务
从库mysql命令行下:start slave;


可能运行此命令会出现ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
解决:登录从库mysql后:
1.change master to master_auto_position=0;
2.开启从库同步,start slave;



**FAQ:**
--------

 1. `ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.`
 2. 解决方式:从库sql下输入:reset master;重置主配置;



查看同步状态:
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: private-gubang.mysql.rds.aliyuncs.com
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000325
          Read_Master_Log_Pos: 35631
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 35757
        Relay_Master_Log_File: mysql-bin.000325
             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: mysql.%,test.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 35631
              Relay_Log_Space: 35998
              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: 404247635
                  Master_UUID: d339fa36-8c85-11e7-ae07-00163e04f361
             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: d339fa36-8c85-11e7-ae07-00163e04f361:9874-9924
            Executed_Gtid_Set: d339fa36-8c85-11e7-ae07-00163e04f361:1-9924
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysqldump具体参数说明

其中Slave_IO_Running: Yes,Slave_SQL_Running: Yes需要俩个状态都是YES,LAST_ERROR,Slave_SQL_Running_State需要正常无错误信息

3 总结

总得来说,搭建比较简单,但是对原理性还了解的不够,才导致碰到问题只会百度,还需要系统性的了解才行,在导入过程中一直在本机查看数据是乱码,而服务器不管怎么改从库的编码为utf8都无用,重启后依旧是初始值,客户端乱码,后台看了N多文章N多次导入,最后问题点在于我的navicat连接编码问题….大家不要犯错..

附录>>>>>>>
有时候需要手动跳过一些事件错误

1：停止SLAVE 服务

mysql> STOP SLAVE;

2：设置跳过的EVENT个数 (当前需要跳过错误数)

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

3：启动SLAVE服务

mysql> START SLAVE;