After doing some research around this topic, I should be attempting this task soon. However, I have two questions going around my head - could do with an experienced professional answering them.

I am using MariaDB 10.3 and the latest version of Centos 7.

Q1. From the MySQL console, when granting user permission rights, you would grant these for the Private IP and not the Public IP?

Q2. Currently, root permissions are disabled for remote sign-in. Is it a good practise to create a slave user instead and don’t touch root user permissions?

Thank you all!


A1. Use Private IPs where practical -- they are somewhat more secure. This assumes, however, that both Master and Slave are on the same Private network or is 'tunneled' through.

A2. Create a separate "user" for the replication stream. GRANT only REPLICATION SLAVE to that user.

Use root (or other SUPER) only for admin purposes. Create specific user(s) for application code. Grant them more limited access, perhaps GRANT ALL PRIVILEGES ON app_db.*

  • Thank you, Rick, it truly means a lot. I’m having a problem with connecting and I think it’s down to the statement ALL PRIVILEGES ON statement that needs executing. I will give it a shot in the morning! – Cam Jan 1 at 0:32
  • A quick question; is it recommended to also backup information_schema, mysql, and performance_schema databases on slave? – Cam Jan 1 at 0:37
  • @Cam - I_S and P_S are not "real" tables and they cannot be reloaded; so 'no' to backing them up. Backing up mysql will mostly provide a backup of the GRANTs. – Rick James Jan 1 at 1:48
  • Thank you @Rick! I am getting a 2003 error - connection timeout. I think it is something related to iptables by the looks of it. – Cam Jan 1 at 21:16
  • @Cam - Search for mysql connection timeout both here and in stackoverflow. – Rick James Jan 3 at 18:17

Your Answer

