If you use MySQL native replication, the slave node should be in a version higher than the master MySQL database. If you set up an Oracle GoldenGate replication slave, there is no version restriction.
0 Comments
You can replicate data from AWS MySQL RDS databases to MySQL databases on EC2 or on-premises in real-time. The configuration is to create an external MySQL database on EC2 or on-premises (slave) replicating data from the MySQL databases on RDS (master). This blog explains how you can perform the setup. In summary, you need the following steps:
1. Pre-Requisite
The MySQL slave instance on EC2 or on-premises must be the same version or later version as the MySQL RDS instance. In general, you need MySQL slave instance to be MySQL 5.6.13 or later version. Based on this requirement, you need to perform an upgrade of your current MySQL database.
Please refer to this post for more information:
2. (MySQL EC2 Salve): Start the MySQL slave database on EC2 β
First, you can start the slave database on EC2 shown as follows: $ sudo service mysqld start Starting mysqld: [ OK ]
3. (MySQL RDS Master): Enable the Replication
βTo enable the replication, you need to turn on the Binlog option and create a replication user to connect to the MySQL master database to perform the replication. First, you need know if the Binlog is enabled, you can login to MySQL database and run the following SQL commands: [ec2-user@ip-172-30-0-198 ~]$ mysql -h aws-mysql-master.cserhpfzdaax.us-east-1.rds.amazonaws.com -P 3306 -u jinyu -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 94 Server version: 5.6.27-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.04 sec) mysql> show master status; +----------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------------+----------+--------------+------------------+-------------------+ | mysql-bin-changelog.000332 | 521 | | | | +----------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
For replication, we will create a new ggdemo database shown as follows:
mysql>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | innodb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> create database ggdemo; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ggdemo | | innodb | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec)
Second, you need to create a replication user shown as follows:
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.07 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; Query OK, 0 rows affected (0.00 sec)
You can get the list of MySQL users and find the privileges granted to the repl user as follow:
mysql> select user,host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | oggadm | % | | repl | % | | mysql.sys | localhost | | rdsadmin | localhost | +-----------+-----------+ 4 rows in set (0.00 sec) mysql> show grants for 'repl'@'%'; +----------------------------------------------+ | Grants for repl@% | +----------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' | +----------------------------------------------+ 1 row in set (0.01 sec)
You then check the binary log file shown as follows:
mysqlbinlog -h ggpm-mysql5717.cserhpfzdaax.us-east-1.rds.amazonaws.com -u repl -pslavepass --read-from-remote-server -t mysql-bin-changelog.000332
The output is shown as follows:
[ec2-user@ip-172-30-3-204 ~]$ mysqlbinlog -h ggpm-mysql5717.cserhpfzdaax.us-east-1.rds.amazonaws.com:3306 -u repl -pslavepass --read-from-remote-server -t mysql-bin-changelog.000332 Warning: Using a password on the command line interface can be insecure. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; ERROR: Failed on connect: Unknown MySQL server host 'ggpm-mysql5717.cserhpfzdaax.us-east-1.rds.amazonaws.com:3306' (0) DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [ec2-user@ip-172-30-3-204 ~]$ mysqlbinlog -h ggpm-mysql5717.cserhpfzdaax.us-east-1.rds.amazonaws.com -u repl -pslavepass --read-from-remote-server -t mysql-bin-changelog.000332 Warning: Using a password on the command line interface can be insecure. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #700101 0:00:00 server id 2131247061 end_log_pos 0 Rotate to mysql-bin-changelog.000332 pos: 4 # at 4 #170714 18:15:00 server id 2131247061 end_log_pos 123 CRC32 0xab3fc55d Start: binlog v 4, server v 5.7.16-log created 170714 18:15:00 BINLOG ' JAppWQ/VPwh/dwAAAHsAAAAAAAQANS43LjE2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AV3FP6s= '/*!*/; # at 123 #170714 18:15:00 server id 2131247061 end_log_pos 154 CRC32 0x23ec481a Previous-GTIDs # [empty] # at 154 #170714 18:16:40 server id 2131247061 end_log_pos 219 CRC32 0xe3285383 GTID [commit=yes] SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #170714 18:16:40 server id 2131247061 end_log_pos 300 CRC32 0xdbc41c3e Query thread_id=19 exec_time=0 error_code=0 SET TIMESTAMP=1500056200/*!*/; SET @@session.pseudo_thread_id=19/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1075838976/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 300 #170714 18:16:40 server id 2131247061 end_log_pos 490 CRC32 0x4f58ebdd Query thread_id=19 exec_time=0 error_code=0 use `mysql`/*!*/; SET TIMESTAMP=1500056200/*!*/; INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1500056200715) ON DUPLICATE KEY UPDATE value = 1500056200715 /*!*/; # at 490 #170714 18:16:40 server id 2131247061 end_log_pos 521 CRC32 0x50ca3236 Xid = 64989 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; # at 521 #170714 18:20:00 server id 2131247061 end_log_pos 578 CRC32 0xa7d0bc9b Rotate to mysql-bin-changelog.000333 pos: 4 # at 4 #700101 0:00:00 server id 2131247061 end_log_pos 0 CRC32 0xd4c20f7d Rotate to mysql-bin-changelog.000333 pos: 4 # at 4 #170714 18:20:00 server id 2131247061 end_log_pos 123 CRC32 0xf80f8f51 Start: binlog v 4, server v 5.7.16-log created 170714 18:20:00 BINLOG ' UAtpWQ/VPwh/dwAAAHsAAAAAAAQANS43LjE2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AVGPD/g= '/*!*/; # at 123 #170714 18:20:00 server id 2131247061 end_log_pos 154 CRC32 0xe58ed322 Previous-GTIDs # [empty] DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
4. (MySQL EC2 Slave): Configure the MySQL Slave Instance
In this step, you need to configure the server-id and specify the replication database ggdemo. After the change, you need to restart the database instance. sudo vi /etc/my.cnf [mysqld] server-id=2 replicate-do-db=ggdemo character-set-server=utf8 log_slave_updates=1 ... $ sudo service mysqld stop Stopping mysqld: [ OK ] $ sudo service mysqld start Starting mysqld: [ OK ]
Then, you can start the replication shown as follows:
mysql> change master to master_host='ggpm-mysql5717.cserhpfzdaax.us-east-1.rds.amazonaws.com', master_password='slavepass', master_user='repl', master_log_file='mysql-bin-changelog.000332', master_log_pos=521; Query OK, 0 rows affected, 2 warnings (0.03 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)
Let's check the replication status.
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: ggpm-mysql5717.cserhpfzdaax.us-east-1.rds.amazonaws.com Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-changelog.000339 Read_Master_Log_Pos: 154 Relay_Log_File: mysqld-relay-bin.000004 Relay_Log_Pos: 353 Relay_Master_Log_File: mysql-bin-changelog.000339 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: ggdemo 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: 154 Relay_Log_Space: 584 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: 2131247061 Master_UUID: eba8c3d5-67da-11e7-983b-0af46ed782c8 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 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 1 row in set (0.00 sec)
5. Testing the Replication
Now, let's test the replication by creating a table in the master database and check the replication.
Master
[ec2-user@ip-172-30-0-198 ~]$ mysql -h aws-mysql-master.cserhpfzdaax.us-east-1.rds.amazonaws.com -P 3306 -u jinyu -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 901 Server version: 5.6.27-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ggdemo | | innodb | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use ggdemo Database changed mysql> create table tt_customer (id int, name varchar(100)); Query OK, 0 rows affected (0.20 sec) mysql> show tables; +------------------+ | Tables_in_ggdemo | +------------------+ | tt_customer | +------------------+ 1 row in set (0.00 sec) mysql> insert into tt_customer values (1,'jinyu'); Query OK, 1 row affected (0.03 sec)
Slave
[ec2-user@ip-172-30-0-198 ~]$ mysql -h localhost -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.6.29 MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ggdemo | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) mysql> use ggdemo; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_ggdemo | +------------------+ | tt_customer | +------------------+ 1 row in set (0.00 sec) mysql> select * from tt_customer; +------+-------+ | id | name | +------+-------+ | 1 | jinyu | +------+-------+ 1 row in set (0.00 sec) mysql> show create table tt_customer\G; *************************** 1. row *************************** Table: tt_customer Create Table: CREATE TABLE `tt_customer` ( `id` int(11) DEFAULT NULL, `name` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified
The replication setup is completed.
Reference
Created: 3/19/2016, Last Updated: 1/8/2018
|
GoldenGate for MySQLOracle GoldenGate for MySQL replicates DML operations for MySQL databases. Here, I discuss Oracle GoldenGate for MySQL, MySQL database and MySQL replication. Top Posts
Resources
Buzzwords
Heterogeneity Security High Performance Real-Time DML Globalization Archives
February 2018
Categories
All
|