I was struggled to complete migration task of on-premises MySQL database to AWS, as we had tight budget which is unable to afford dedicated VPN connection service between these two environments.
There were 3 following options were given to achieve the mission.
- Setup SSL configuration on on-premises MySQL server.
=> This requires me to change current configuration on running server, so I said "No thanks". - Implement internet VPN connection with the use of OpenVPN.
=> This requires me a lots of effort, test and verification process, I immediately turned down. - Establish SSH tunneling between two environments.
=> This is it!
And the summary is
- Setup ssh and mysqlrouter on both environments.
- Establish SSH tunnel from bastion@AWS to bastion@on-premises.
- bastion@AWS listen replication request on 56446, and the request redirects to its 53306.
- The request received on bastion@AWS:53306 flows to bastion@on-premises:56446 via SSH tunnel.
- bastion@on-premises passes the request to DB1:53306 as it configured at MySQL router.
Actual configurations would be like followings.
mysqlrouter: bastion@AWS
[DEFAULT]
logging_folder = /var/log/mysqlrouter
[logger]
level = debug
filename = mysqlrouter.log
#timestamp_precision = second
[routing:basic_failover]
# To be more transparent, use MySQL Server port 3306
bind_address = 0.0.0.0
bind_port = 56446 # listening Aurora replication slave
routing_strategy = first-available
mode = read-write
destinations = 127.0.0.1:53306 # ssh tunnel
# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval = 60
mysqlrouter: bastion@on-premises
[DEFAULT]
logging_folder = /var/log/mysqlrouter
[logger]
level = debug
filename = mysqlrouter.log
#timestamp_precision = second
[routing:basic_failover]
# To be more transparent, use MySQL Server port 3306
bind_address = 0.0.0.0
bind_port = 56446 # ssh tunnel port
routing_strategy = first-available
mode = read-write
destinations = DB1:53306 # to master database
# If no plugin is configured which starts a service, keepalive
# will make sure MySQL Router will not immediately exit. It is
# safe to remove once Router is configured.
[keepalive]
interval = 60
Establishing SSH tunnel (Run on bastion@AWS)
ssh -L 53306:0.0.0.0:56446 onpremises_user@1.1.1.1 -i /actual_ssh_key_path
Avoid ssh connection time-out
watch -n10 ls -l /tmp
Setup replication user on on-premises MySQL server
mysql> create user 'repl'@'%' identified with mysql_native_password by 'replpassword';
mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl';
Get master file position of on-premises MySQL server
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.009516
Position: 1367403
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 8bxxxx-xxxx-11e5-8ebd-xxxxx69757cf:1-38xxxx5990,
1 row in set (0.00 sec)
Replication setup on AWS database server (Aurora)
mysql> CALL mysql.rds_set_external_master ('IP addr of bastion@AWS', 53306, 'repl', 'replpassword', 'mysql-bin.009516', 1367403, 0);
mysql> CALL mysql.rds_start_replication;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.0.70
Master_User: repl
Master_Port: 56446
Connect_Retry: 60
Master_Log_File: mysql-bin.009516
Read_Master_Log_Pos: 1428514
Relay_Log_File: relaylog.002290
Relay_Log_Pos: 1098342
Relay_Master_Log_File: mysql-bin.009516
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
This worked well for me, even running for 2 weeks without any network failure!
