How to replicate MySQL in a few easy steps!

I am Masoud Tavakkoli and I want to show you how to replicate MySQL in a few steps! the purpose of this content is to show you only required commands and information may you need during process.

Let’s start! for beginnings we need 2 MySQL server (or instance if your using Docker). for local testing I use docker for this tutorial there is no deference.

MySQL Replication

The MySQL replication process allows you to maintain multiple copies of MySQL data. All data in the master is synced to Slave servers in an automated process and if you have a disaster, you can easily promote Slave to a Master for commit operations. The main role of replication is to spread read and write workloads across multiple servers for easy scalability.

You can install MySQL on your servers or use this docker-compose.yml file to start.

Step 1: Start MySQL serves.

Here we use docker but it’s on you how to run your mysql server. If your not using docker skip this step.

Step 2: Edit Master MySQL config file

This file is located here most of the time it may be different in OS version./etc/mysql/mysql.conf.d/mysqld.cnf

Notice: Don’t forget to change `binlog_do_db` to your own database name.

Step 3: Start slave MySQL. Like step 1 you can use docker or using your way to use MySQL.

Step 4: Update Slave MySQL config file

Notice: Don’t forget to change `binlog_do_db` to your own database name.

Step 5: create replica user in master.

Login to mysql and create a user and grant it to replicate.mysql> CREATE USER myuser@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.08 sec)

then grant replicationmysql> grant replication slave on *.* to myuser@'%';
Query OK, 0 rows affected (0.09 sec)mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

exit the mysql and restart mysql usingsudo systemctl restart mysql

or using dockerdocker-compose restart db

Step 6: Backup Master. If your database is fresh database skip this step.

first lock the database tables to stop write in database using this

FLUSH TABLES WITH READ LOCK;

then exit the MySQL and dump database with this commandmysqldump -u root -p newdatabase > newdatabase.sql

again login to mysql and run thismysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | newdatabase | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Remember File and Position fields values we need them later.

at last unlock tables with

SET GLOBAL read_only = 0; 
UNLOCK TABLES;

Step 6: Copy data to slave MySQL server

if your database is in other server you can use scp tool to copy file like this> scp newdatabase.sql root@your_server_ip:/root

in slave server import newdatabase.sql file to slave database using:> mysql -u root -p newdatabase < newdatabase.sql

Step 7: Configure slave to read the master logs.

now login to slave MySQL and run thismysql> CHANGE MASTER TO MASTER_HOST='your_master_server_ip',
-> MASTER_USER='myuser',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=155;

REMEMBER: Replace MASTER_LOG_FILE and MASTER_LOG_POS values with that values you get in the end of STEP 5;

and finaly start slave with thismysql> start slave;
Query OK, 0 rows affected (0.06 sec)

to check your replication is work use this commandmysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.2
Master_User: myuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-1.000001
Read_Master_Log_Pos: 356955
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 43995
Relay_Master_Log_File: mysql-bin-1.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 356955
Relay_Log_Space: 44204
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: 0
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: 34943b62-a683-11eb-9e3a-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

Congratulations! you did it.
If you have any question about this I ‘ll happy to see your messages in Telegram or twitter.

telegram id: pro_masoud