ec2mysql_AWSEC2MySQL迁移到RDS案例

Amazon Relational Database Service (Amazon RDS) 是一种Web 服务&＃xff0c;可让用户更轻松地在云中设置、操作和扩展关系数据库。它可以为行业标准关系数据库提供经济高效且可以调节大小的容量&＃xff0c;并管理常见

数据库管理任务。

现有的环境如下,计划将MySQL主库billingdb迁移到RDS上,减少运维成本.

计划迁移之后的环境如下

现在开始迁移过程

1. 在当前的RDS slave上记录需要过滤的表(RDS有自己的系统表,当RDS slave变成master后,这些表不需要被同步到EC2上的slave上)

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:10.188.102.92Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000861Read_Master_Log_Pos:18626649Relay_Log_File: relaylog.005026Relay_Log_Pos:685Relay_Master_Log_File: mysql-bin.000861Slave_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 &＃39;repl&＃39;&＃64;&＃39;%&＃39; identified by &＃39;repl&＃39;;

mysql> flush privileges;

3. 修改RDS slave上二进制日志保留时间(默认情况下,RDS并不会保留二进制日志,这样RDS切为master后就没有二进制日志,也就谈不上将EC2转换为slave连上RDS了)

mysql>call mysql.rds_show_configuration;

mysql> call mysql.rds_set_configuration(&＃39;binlog retention hours&＃39;, 72);

4.  记录当前master的session连接信息

/usr/local/mysql/bin/mysql -u root -p -e "show processlist"|awk &＃39;{print $3}&＃39;|grep ":"|awk &＃39;{print substr($0,0,index($0,":")-1) }&＃39;|sort|uniq -c1 10.188.100.36

1 10.188.104.196

1 ec2-backoffice-01.mypna.com20 ec2-billingcommerce-01.mypna.com20 ec2-billingcommerce-02.mypna.com10 ec2-billingen-01.mypna.com10 ec2-billingen-02.mypna.com11 ec2-billingengines-01.mypna.com10 ec2-billingentest-01.mypna.com10 ec2-billingentest-02.mypna.com25 ec2-billingiden-01.mypna.com25 ec2-billingiden-02.mypna.com20 ec2-billingoffer-01.mypna.com20 ec2-billingoffer-02.mypna.com20 ec2-billingoffer-03.mypna.com32 ec2-billingui-01.mypna.com31 ec2-billingui-02.mypna.com1 ec2-cliwebreport-01.mypna.com1 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 status\G*************************** 1. row ***************************

File: mysql-bin.000865Position:120Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:1 row in set (0.00 sec)

7. 稍等片刻,检查所有EC2 slave,确保所有的EC2的slave已经赶上master的二进制日志位置

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:10.188.100.73Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000865 #看该行Read_Master_Log_Pos:120 #看该行Relay_Log_File: ec2-billingdb-04-relay-bin.000047Relay_Log_Pos:279Relay_Master_Log_File: mysql-bin.000865Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:120 #看该行Relay_Log_Space:618Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_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 status\G*************************** 1. row ***************************

File: mysql-bin.000861Position:18626649Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:1 row in set (0.00 sec)

在RDS上查看

mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event

Master_Host:10.188.102.92Master_User: repl

Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000861 #这里和上一步骤06机器的二进制日志一致Read_Master_Log_Pos:18626649 #这里和上一步骤06的机器的二进制日志一致Relay_Log_File: relaylog.005026Relay_Log_Pos:685Relay_Master_Log_File: mysql-bin.000861Slave_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:0Last_Error:

Skip_Counter:0Exec_Master_Log_Pos:18626649 #这里和上一步骤06机器的二进制日志一致Relay_Log_Space:79732Until_Condition: None

Until_Log_File:

Until_Log_Pos:0Master_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.007492Position:120Binlog_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 status\G;

mysql>show master status\G;File: mysql-bin-changelog.007492Position:1025

11. 将步骤1中需要过滤的表添加到原master的配置文件/etc/my.cnf里

replicate-ignore-table&＃61;mysql.rds_sysinfo

replicate-ignore-table&＃61;innodb_memcache.cache_policies

replicate-ignore-table&＃61;mysql.rds_replication_status

replicate-ignore-table&＃61;innodb_memcache.config_options

replicate-ignore-table&＃61;mysql.rds_history

replicate-ignore-table&＃61;mysql.plugin

replicate-ignore-table&＃61;mysql.rds_monitor

replicate-ignore-table&＃61;mysql.rds_heartbeat2

12. 将原master以只读的方式启动(确保应用不会连接进来写脏数据)

/etc/init.d/mysql start --read-only

13. 将原master作为slave连接到RDS

mysql> change master tomaster_host&＃61;&＃39;RDS endpoint name&＃39;,

master_user&＃61;&＃39;repl&＃39;,

master_password&＃61;&＃39;repl&＃39;,

master_port&＃61;3306,

master_log_file&＃61;&＃39;mysql-bin-changelog.xxxxxx&＃39;,

master_log_pos&＃61;xxx;

mysql>start slave;

mysql> show slave status\G;

14, 将应用解析到RDS上,并检查RDS的session信息,确保应用可以正常接入