Last updated on January 11th, 2023 at 12:59 pm
In a typical MySQL Master Slave environment replication data stored in the master database will get automatically replicated to slave. This tutorial will help you configure a MySQL Master Slave replication with ease. Let us take 2 MySQL servers as an example. I have also added some extra settings around ignoring a table while replicating, option to just replicate a single database etc.,
Before we begin, I am talking about both existing and new MySQL Master – Slave configuration. Please make sure to follow the steps accordingly.
Table of Contents
- Step 1 (Performed in Master server 172.31.69.173)
- STEP 2 (Performed in Slave server 172.31.79.222)
- STEP 3 (Test database creation in Master )
- Troubleshooting
Database Server Details
Server A (Master)
IP Address= 172.31.69.173
MySQL Server version= 5.7.37
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
Replica User: repl_user_connect
Server B (SLAVE)
IP Address=172.31.79.222
MySQL Server version= 5.7.37
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
Step 1 (Performed in Master server 172.31.69.173)
Before we start the actual changes within MySQL Master server, we have to first update the Master MySQL configuration setting.
Change Master Configuration (Existing Or New)
First step before doing anything is to update your MySQL configuration file, this is how my Master configuration looks like
Take a backup of configuration file before making any changes
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index =/var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
Restart MySQL service
$ sudo systemctl restart mysqld
If you are trying to create replica for an existing database click here, it is the same steps like a new setup the only difference is we need to dump data from Master and import it to Slave. For this we have to do one extra step of locking the database before dumping the existing data. All details explained in the below section.
Now that you have restarted the MySQL service we can start configuration by creating the user named repl_user_connect with password slavepwd. After that step we are granting replication permission for the user in Master from the slave server.
mysql> CREATE USER 'repl_user_connect'@'172.31.79.222' IDENTIFIED BY 'slavepwd';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user_connect'@'172.31.79.222';
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 845 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Make a note of the File and Position, in my case it is mysql-bin.000008 and 845
Note the user we created in Master [email protected] in which 172.31.79.222 is the IP address of Slave
Create Slave User for an existing database (Not New Setup)
Since you are creating slave database for an existing master run this (in Master)
mysql> CREATE USER 'repl_user_connect'@'172.31.79.222' IDENTIFIED BY 'slavepwd';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user_connect'@'172.31.79.222';
Query OK, 0 rows affected (0.00 sec)
mysql > FLUSH TABLES WITH READ LOCK;
The above FLUSH command basically closes all open tables and locks all tables for all databases with a global read lock. Once the Flush Tables command is run generate mysqldump of your database.
You can either exit MySQL prompt or use the same to run the command below (Recommended to initiate a new session in SSH by keeping the MySQL prompt session still live) .
Below example is just creating a dump of single database. It is up to your requirement on whether to dump all the databases or just one.
mysqldump -u<YOUR_USER> -p <DATABASE_NAME> >data-dump.sql
Login to MySQL again (or use the existing session as mentioned above) and run the command (Very Important to unlock tables)
mysql> UNLOCK TABLES;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 845 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Make a note of the File and Position.
STEP 2 (Performed in Slave server 172.31.79.222)
Similar to Step 1 before we do any changes Slave configuration needs to be updated.
Change Slave Configuration (Existing Or New)
You have to change the Slave configuration similar to Master.
Make sure to take a backup of configuration file before any changes
server-id = 2
read_only = 1
If you would like to just replicate a single database and ignore a table within that database add these
replicate-do-db=mydb
replicate_ignore_table=mydb.statistics_useronline
The above line basically ask Slave to just replicate database named mydb and ignore table within mydb, ie mydb.statistics_useronline
If you have multiple tables to be ignored add each database.tablename in new lines within the configuration file.
Restart MySQL service in Slave
$ sudo systemctl restart mysqld
Existing database (Not New Setup) from Master Follow This
Import the database dump you exported from Master . Below is just an example and it depends on how you imported the data
mysql DATABASE_NAME < data-dump.sql
Now that we have imported the database in slave you can follow the below commands to stop slave and adding master.
New Setup from Master Follow This (ignore the mysqldump command above)
First stop the slave threads. After that we are asking the slave server to connect to master using the username and password we created in Step 1. Also make sure to update the MASTER_LOG_FILE and MASTER_LOG_POS with the number you got from Step 1
Note: From MySQL 8.0.22, STOP SLAVE is deprecated and the alias STOP REPLICA should be used instead.
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='172.31.69.173', MASTER_USER='repl_user_connect' , MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=845;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show warnings \G;
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
As you might have noticed we saw a warning. This may be ignored as long as these servers are private and you are sure there is no other security issues.
Tip: If you would like to set up encrypted connection between master and slave then check the documentation.
Also you might have noticed I listed the database in Slave server and there was no database other than the defaults.
STEP 3 (Test database creation in Master )
The final step here is to create a database named test_repl_mistonline_in in master (for new setup) so that we can test whether the replication is working as expected in slave.
mysql> create database test_repl_mistonline_in;
Query OK, 1 row affected (0.00 sec)
Now in Slave, Run the below command and confirm that the database got created.
mysql> show databases;
+-------------------------+
| Database |
+-------------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_repl_mistonline_in |
+-------------------------+
5 rows in set (0.00 sec)
mysql>
Existing database replication, you may have to try to create a table within the master database and watch the replication happen on the slave.
Troubleshooting
If the replication is not working, Run the slave status command in 172.31.79.222, you should see something similar to the one below for a successful connection (truncated for better visibility)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.31.69.173
Master_User: repl_user_connect
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 845
Relay_Log_File: 172-31-79-222-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
If there is an error you should normally see these attributes in the slave status command.
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
If error still exist, try these
- Check and disable any firewall running on both Master / Slave
- Make sure MySQL is listening on port 3306 and allow communication through that port (if you need firewall enabled)
- Make sure the mysql process is binding to the ip address of the respective servers and not the localhost/127.0.0.1 (Check config file of MySQL)
If all the above conditions are checked but still you see an error then restart MySQL process on both Master and Slave and recheck the status.
Final approach, If required redo the setup starting from Step 1.
admin