Setup MySQL Master-Slave Replication on Ubuntu with Existing Data
MySQL Master-Slave Replication is a procedure that is replicating or in other words copying the databases from one (Master) server to other (Slave) servers in the network. It can be useful for having multiple read replicas (that I plan to use in the future as my server grows) or have it as a place to do backups.
As I have my Master server in place, it’s a production server with data on it, it’s a Virtual Server running Ubuntu 16.04 LTS and MySQL 5.7 with sufficient RAM memory and vCPU cores running on SSD drives. The server is starting to be used all day long and I can’t find a suitable time when I can do my backups, so from now on, I can do my backup jobs on my Slave server anytime I want.
In my current setup I have my Master MySQL server mysql01 and my Slave MySQL server mysql02 which is in another datacenter
mysql01 IP: 10.90.21.16 mysql02 IP: 10.91.21.21
Master server setup
You will need to plan for downtime because we will need to take a consistent snapshot of the databases for which we will need to put the server into a read-only mode.
1. Update the configuration file
Edit: /etc/mysql/mysql.conf.d/mysqld.cnf and under [mysqld] uncomment or add the following lines
[mysqld] bind-address = 10.90.21.16 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log
This will make the database server listen on private IP, set server-id which should be unique among all Master and Slave nodes and enable binary logging. Some of this you should already have but let’s check it once again.
2. Restart for services to be effective
systemctl restart mysql
3. Create Replication User
root@mysql01:~# mysql -u root -p Enter password: mysql> CREATE USER 'repl'@'10.91.21.21' IDENTIFIED BY 'Sl@v3p@$$'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.91.21.21'; Query OK, 0 rows affected (0.00 sec)
This will create a dedicated repl user for the Slave server IP address 10.91.21.21 and we will give privileges for REPLICATION SLAVE. Remember to change 10.91.21.21 with your Slave server IP address.
4. Lock the Master Server Databases
mysql> FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (0.22 sec)
This will lock ALL tables with READ-ONLY flag and during the lock we need to create a consistent snapshot of the database so we can bootstrap the Slave server later on. Remember this will lock all INSERT and UPDATE queries.
Lock will be released when you exit the MySQL CLI or issue UNLOCK TABLES. Keep the lock until you finish the mysqldump.
5. Master Replication log position
mysql> SHOW MASTER STATUS; +------------------+-----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+-----------+--------------+------------------+-------------------+ | mysql-bin.003577 | 141378624 | | | | +------------------+-----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
Write the File and the Position values, you will need them later when you initialize the Slave replication.
6. DUMP the Master server databases
mysqldump -uroot -p --all-databases --single-transaction --triggers --routines > dump.sql
This will dump all the databases into a single dump.sql file. You can check my MySQL Backup and Restore post regarding the backup and restore commands.
7. Unlock the databases on the Master server
mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
WAIT until your backup from step 6 has finished before issuing this command. After this, your Master server will be fully operational.
8. Transfer the data to the Slave server
root@mysql01:~# $ scp dump.sql 10.91.21.21:/tmp
This will transfer the data from the Master server to the Slave server and we can jump and configure the Slave server.
You can even have a break at this point.
Slave Server Setup
As with the Master server we need to make few adjustments with the Slave server, restore the databases and start the Replication process.
1. Update the configuration file
Edit: /etc/mysql/mysql.conf.d/mysqld.cnf and under [mysqld] uncomment or add the following lines
[mysqld] bind-address = 10.91.21.21 server-id = 2 log_bin = /var/log/mysql/mysql-bin.log
Like the Master server above we need to make the server listen on it’s private IP, set server-id to a unique value (in our case 2) and enable binary logging.
2. Restart for services to be effective
systemctl restart mysql
3. Import the dump file
root@mysql02:~# mysql -u root -p < /tmp/dump.sql
4. Setup the slave to communicate with the Master
root@mysql02:~# mysql -u root -p mysql> STOP SLAVE; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CHANGE MASTER TO -> MASTER_HOST='10.90.21.16', -> MASTER_USER='repl', -> MASTER_PASSWORD='Sl@v3p@$$', -> MASTER_LOG_FILE='mysql-bin.003577', -> MASTER_LOG_POS=141378624; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec)
Change the Master Host, Password, Log file and Log Position with YOUR VALUES.
Check if replication is working
To see if your configuration is working you need to connect on your Slave server and issue SHOW SLAVE STATUS\G
mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.90.21.16 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.003577 Read_Master_Log_Pos: 229958895 Relay_Log_File: mysql02-relay-bin.000002 Relay_Log_Pos: 11083560 Relay_Master_Log_File: mysql-bin.003577 Slave_IO_Running: Yes <-- Imporant Slave_SQL_Running: Yes <-- Imporant Replicate_Do_DB: 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: 152461864 Relay_Log_Space: 88580800 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: 1575 <-- Imporant 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: 1 Master_UUID: ee9774f5-8552-11e8-b6cb-00505684dbd9 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: System lock 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)
From all this data there are few parameters you need to check, Slave_IO_Running and Slave_SQL_Running should be Yes, and since I had a break before setting up the Slave I have Seconds_Behind_Master: 1575 Wait for this parameter to be 0 which indicates that the Slave is not behind the Master with the data.
MySQL Replication Chapter: https://dev.mysql.com/doc/refman/5.7/en/replication.html
I followed the steps in the article to set up MySQL Master-Slave Replication on Ubuntu with our existing data, but I seem to have run into an issue. I’m encountering an error at [Master_Password], and I’m not sure how to proceed from here.
you need to have the same password for the replication user. whatever you choose in master step3, you need to set in slave step 4