All commands in red must be replaced with actual values used in your environment.
Configuring MySQL replication leaves port 3306 open to the Internet and data between the replication servers is not encrypted. Using SSH tunneling MySQL replication data can be transferred through the SSH connection. This method doesn't need any additional ports to be opened in the firewall. For this article:
Commands to be entered in the master are marked (master) and commands for slave are marked (slave).
It is assumed that you've read this article on MySQL replication.
Create a user and assign a password. This user will be used to create the SSH tunnel: (master)
root@mysql-master:~# useradd -d /home/tunneluser -m tunneluser root@mysql-master:~# passwd tunneluser
The tunneluser must be allowed to connect only from the slave server so it must be entered in the /etc/ssh/sshd_config file as an allowed user. (master)
root@mysql-master:~# nano /etc/ssh/sshd_config
Since allowed users are being defined, users not in this list will be denied access, so add the administrative users who will use SSH to login to this VPS. (master)
AllowUsers root alice bob email@example.com
Restart the SSH server: (master)
root@mysql-master:~# service ssh restart
The steps to generate a SSH key are mentioned in this article but I'll repeat the commands here. (slave)
[root@mysql-slave ~]# ssh-keygen
[root@mysql-slave ~]# ssh-keygen Generating public/private rsa key pair. Enter file in which to save the key (/root/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /root/.ssh/id_rsa. Your public key has been saved in /root/.ssh/id_rsa.pub. The key fingerprint is: 5d:db:9c:50:e8:b2:88:18:de:78:
5f:ed:83:14:47:d7 root@mysql-slave The key's randomart image is: +--[ RSA 2048]----+ | ... | | o.. E| | oo. | | . .o.o= . | | . = .S..*. + | | + + . + . | | . . o o | | . . o | | . | +-----------------+
Do NOT enter a passphrase because this key will be used to automatically establish a SSH tunnel which will not be possible if it contains a passphrase. Copy the public key to the master server. (slave)
[root@mysql-slave ~]# ssh-copy-id firstname.lastname@example.org
[root@mysql-slave ~]# ssh-copy-id email@example.com The authenticity of host '188.8.131.52 (184.108.40.206)' can't be established. RSA key fingerprint is 3f:33:0c:73:bd:da:51:b9:45:2e:
d7:2e:00:47:33:17. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '220.127.116.11' (RSA) to the list of known hosts. firstname.lastname@example.org's password: Now try logging into the machine, with "ssh 'email@example.com'", and check in: ~/.ssh/authorized_keys to make sure we haven't added extra keys that you weren't expecting.
The tunneluser user will be used only for tunneling and not for administration so change the shell to nologin and remove the password. (master)
usermod -s /sbin/nologin tunneluser passwd -d tunneluser
Debian and Ubuntu users replace /sbin/nologin with /usr/sbin/nologin.
Create a SSH tunnel using the following command. (slave)
ssh -L 33061:localhost:3306 firstname.lastname@example.org -f -N
The tunnel has been created, so accessing port 33061 at localhost will connect the slave to the master via SSH. The -f parameter runs this command in the background and the -N parameter means "Do not execute a command" since the tunneluser has a nologin shell.
This section mentions only the changes in the steps outlined in this article on MySQL replication.All things to be changed are mentioned in orange. If the my.cnf file was edited to listen on the public IP address change it back to localhost.
root@mysql-master:~# nano /etc/mysql/my.cnf
The following line:
bind-address = 18.104.22.168
will be changed to the localhost IP address:
bind-address = 127.0.0.1
Change the permissions of slave_user to login only from localhost. (master)
root@mysql-master:~# mysql -u root -p mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY 'password';
The CHANGE MASTER query entered previously pointed to the public IP address of the Master server and didn't specify a port number. The following query will change that. (slave)
root@mysql-slave:~# mysql -u root -p mysql>STOP SLAVE; mysql>CHANGE MASTER TO MASTER_HOST='127.0.0.1',
MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_PORT=33061, MASTER_LOG_FILE='mysql-bin. 000001', MASTER_LOG_POS=107; mysql>START SLAVE;
Connectivity can be tested from the slave using the following command: (slave)
mysql -h 127.0.0.1 -u slave_user -P 33061 -p
Please do not use localhost with the -h parameter as MySQL will login locally with the default port number.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.Sign up now
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Wouldn’t it be preferable to install autossh for MySQL Replication? If the ssh tunnel dies, it won’t restart automatically, but with autossh it will start again. My 2 cents.
I followed the tutorial carefully but at the last stage when trying to test mysql connection to master, on my slave I get
ERROR 1045 (28000): Access denied for user 'slave_user'@'localhost' (using password: YES). I copy pasted the correct password.
On the master it shows:
mysql> show grants for 'slave_user'@'localhost'; +----------------------------------------------------------------------+ | Grants for slave_user@localhost | +----------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' | +----------------------------------------------------------------------+ 1 row in set (0.00 sec)
I believe the ssh connection is setup correctly, if I ssh into the master from the slave it connects and then says
Connection to <ip address> closed.It’s closed because nologin and password removed I assume.
Why can’t I connect to the master through mysql? The ssh tunnel appears to be working from the slave to the master.
error connecting to master ‘email@example.com:3306’ - retry-time: 60 maximum-retries: 86400 message: Access denied for user ‘slave_user’@‘localhost’
I am trying to follow your tutorial but I got the following :
ssh -L 33061:localhost:3306 firstname.lastname@example.org -f -N
bind: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 33061 Could not request local forwarding.
Also, I don’t understand that since you added “email@example.com” in Allowusers, how can one login to user tunneluser on 22.214.171.124 ???