Amazon Relational Database Service (Amazon RDS) 是一种Web 服务，可让用户更轻松地在云中设置、操作和扩展关系数据库。它可以为行业标准关系数据库提供经济高效且可以调节大小的容量，并管理常见
数据库管理任务。
现有的环境如下,计划将MySQL主库billingdb迁移到RDS上,减少运维成本.
计划迁移之后的环境如下
现在开始迁移过程
1. 在当前的RDS slave上记录需要过滤的表(RDS有自己的系统表,当RDS slave变成master后,这些表不需要被同步到EC2上的slave上)
mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.188.102.92 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000861 Read_Master_Log_Pos: 18626649 Relay_Log_File: relaylog.005026 Relay_Log_Pos: 685 Relay_Master_Log_File: mysql-bin.000861 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: mysql.rds_sysinfo,innodb_memcache.cache_policies,mysql.rds_replication_status,innodb_memcache.config_options,mysql.rds_history,mysql.plugin,mysql.rds_monitor
2. 在RDS slave上创建复制及相关账户(创建RDS作为备库的时候,并没有导入系统表,所以不会有主库上的数据库用户)
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'repl'; mysql> flush privileges;
3. 修改RDS slave上二进制日志保留时间(默认情况下,RDS并不会保留二进制日志,这样RDS切为master后就没有二进制日志,也就谈不上将EC2转换为slave连上RDS了)
mysql> call mysql.rds_show_configuration; mysql> call mysql.rds_set_configuration('binlog retention hours', 72);
4. 记录当前master的session连接信息
/usr/local/mysql/bin/mysql -u root -p -e "show processlist"|awk '{print $3}'|grep ":"|awk '{print substr($0,0,index($0,":")-1) }'|sort|uniq -c 1 10.188.100.36 1 10.188.104.196 1 ec2-backoffice-01.mypna.com 20 ec2-billingcommerce-01.mypna.com 20 ec2-billingcommerce-02.mypna.com 10 ec2-billingen-01.mypna.com 10 ec2-billingen-02.mypna.com 11 ec2-billingengines-01.mypna.com 10 ec2-billingentest-01.mypna.com 10 ec2-billingentest-02.mypna.com 25 ec2-billingiden-01.mypna.com 25 ec2-billingiden-02.mypna.com 20 ec2-billingoffer-01.mypna.com 20 ec2-billingoffer-02.mypna.com 20 ec2-billingoffer-03.mypna.com 32 ec2-billingui-01.mypna.com 31 ec2-billingui-02.mypna.com 1 ec2-cliwebreport-01.mypna.com 1 ec2-dbmon-01.mypna.com
5. 准备第4步主机名到IP地址的解析(RDS并不能解析出客户端主机名,迁移到RDS后,检查session连接的时候会方便很多)
|
IP address |
Hostname |
|
10.188.100.36 |
ec2-billingdb-04 |
|
10.188.104.196 |
ec2-csr-tracking-01 |
|
10.188.3.22 |
ec2-backoffice-01.mypna.com |
|
10.188.1.102 |
ec2-billingcommerce-01.mypna.com |
|
10.188.1.105 |
ec2-billingcommerce-02.mypna.com |
|
10.188.1.252 |
ec2-billingen-01.mypna.com |
|
10.188.1.59 |
ec2-billingen-02.mypna.com |
|
10.188.1.189 |
ec2-billingengines-01.mypna.com |
|
10.188.1.246 |
ec2-billingentest-01.mypna.com |
|
10.188.1.232 |
ec2-billingentest-02.mypna.com |
|
10.188.1.15 |
ec2-billingiden-01.mypna.com |
|
10.188.1.16 |
ec2-billingiden-02.mypna.com |
|
10.188.101.54 |
ec2-billingoffer-01.mypna.com |
|
10.188.101.128 |
ec2-billingoffer-02.mypna.com |
|
10.188.101.63 |
ec2-billingoffer-03.mypna.com |
|
10.188.1.235 |
ec2-billingui-01.mypna.com |
|
10.188.1.236 |
ec2-billingui-02.mypna.com |
|
10.188.102.92 |
ec2-cliwebreport-01.mypna.com |
|
10.188.100.65 |
ec2-dbmon-01.mypna.com |
|
10.188.102.92 |
ec2-cliwebreport-01.mypna.com |
6. 将主库master重启到只读状态,并记录当前的master日志位置信息(这样做是为了让所有的slave都达到一致性状态)
/etc/init.d/mysql restart --read-only mysql> show master statusG *************************** 1. row *************************** File: mysql-bin.000865 Position: 120 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
7. 稍等片刻,检查所有EC2 slave,确保所有的EC2的slave已经赶上master的二进制日志位置
mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.188.100.73 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000865 #看该行 Read_Master_Log_Pos: 120 #看该行 Relay_Log_File: ec2-billingdb-04-relay-bin.000047 Relay_Log_Pos: 279 Relay_Master_Log_File: mysql-bin.000865 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: 120 #看该行 Relay_Log_Space: 618 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 #看该行
8. EC2的机器ec2-billingdb-06是RDS的master,所以RDS的二进制日志要看是否追上ec2-billingdb-06
- 在ec2-billingdb-06查看
mysql> show master statusG *************************** 1. row *************************** File: mysql-bin.000861 Position: 18626649 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
- 在RDS上查看
mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.188.102.92 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000861 #这里和上一步骤06机器的二进制日志一致 Read_Master_Log_Pos: 18626649 #这里和上一步骤06的机器的二进制日志一致 Relay_Log_File: relaylog.005026 Relay_Log_Pos: 685 Relay_Master_Log_File: mysql-bin.000861 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: mysql.rds_sysinfo,innodb_memcache.cache_policies,mysql.rds_replication_status,innodb_memcache.config_options,mysql.rds_history,mysql.plugin,mysql.rds_monitor Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 18626649 #这里和上一步骤06机器的二进制日志一致 Relay_Log_Space: 79732 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
8. 将master主库数据库关机
/etc/init.d/mysql stop
9. 记录RDS slave的二进制日志信息
mysql> show master status; *************************** 1. row *************************** File: mysql-bin-changelog.007492 Position: 120 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
10. 清除RDS的slave信息,使其作为独立的主库运行
mysql>CALL mysql.rds_stop_replication; mysql>CALL mysql.rds_reset_external_master; mysql>show slave statusG; mysql>show master statusG; File: mysql-bin-changelog.007492 Position: 1025
11. 将步骤1中需要过滤的表添加到原master的配置文件/etc/my.cnf里
replicate-ignore-table=mysql.rds_sysinfo replicate-ignore-table=innodb_memcache.cache_policies replicate-ignore-table=mysql.rds_replication_status replicate-ignore-table=innodb_memcache.config_options replicate-ignore-table=mysql.rds_history replicate-ignore-table=mysql.plugin replicate-ignore-table=mysql.rds_monitor replicate-ignore-table=mysql.rds_heartbeat2
12. 将原master以只读的方式启动(确保应用不会连接进来写脏数据)
/etc/init.d/mysql start --read-only
13. 将原master作为slave连接到RDS
mysql> change master to master_host='RDS endpoint name', master_user='repl', master_password='repl', master_port=3306, master_log_file='mysql-bin-changelog.xxxxxx', master_log_pos=xxx; mysql> start slave; mysql> show slave statusG;
14, 将应用解析到RDS上,并检查RDS的session信息,确保应用可以正常接入