{"id":1018,"date":"2025-06-29T15:03:01","date_gmt":"2025-06-29T15:03:01","guid":{"rendered":"https:\/\/james-batchelor.com\/?p=1018"},"modified":"2025-07-05T11:33:46","modified_gmt":"2025-07-05T11:33:46","slug":"mysql-replication","status":"publish","type":"post","link":"https:\/\/james-batchelor.com\/index.php\/2025\/06\/29\/mysql-replication\/","title":{"rendered":"MySQL Replication"},"content":{"rendered":"\n

Sure, its not the most original of topics, but it is one I’ve relied upon from time to time. For years, this guide from Digital Ocean<\/a> was my go to choice.<\/p>\n\n\n\n

It wasn’t until my most recent visit for an upcoming project that things looked, well, a little different. It was mostly the same, but subtle differences meant it was no longer compatible with how I’d been familiar with setting it up.<\/p>\n\n\n\n

Therefore this quick post is to capture the old method of setting it up for posterity.<\/p>\n\n\n

\n
\"\"<\/a><\/figure><\/div>\n\n\n\n\n\n\n

The below steps are to replicate all MySQL databases on another server.<\/p>\n\n\n\n

Prepare MySQL .conf files<\/h2>\n\n\n\n

To allow MySQL to replicate, the servers must be arranged in a hirarchy and logging enabled so each system can keep track and update any changes made.<\/p>\n\n\n\n

The .conf files can be found in the following locations:<\/p>\n\n\n\n

\/etc\/my.cnf.d\/server.cnf<\/code><\/pre>\n\n\n\n
Or on newer distros:<\/p>\n\n\n\n
\/etc\/mysql\/mariadb.conf.d\/50-server.cnf<\/code><\/pre>\n\n\n\n
Source\/Master Server<\/h3>\n\n\n\n
On the source server (server you want to copy from<\/em>), add the following lines below and within the [mysqld] section:<\/p>\n\n\n\n
bind-address            = {local ip address},{remote ip address}\nserver-id               = 1\nlog_bin                 = \/var\/log\/mariadb\/mysql-bin.log\n\nbinlog-format = mixed\nsync_binlog=1<\/code><\/pre>\n\n\n\n
Replica\/Slave Server<\/h3>\n\n\n\n
On the replica server (server that databases are copied to), add the following lines below and within the [mysqld] section:<\/p>\n\n\n\n
server-id               = 2\nlog_bin                 = \/var\/log\/mysql\/mysql-bin.log\nrelay-log               = \/var\/log\/mysql\/mysql-relay-bin.log\nbinlog-format           = mixed\nread-only               = 1<\/code><\/pre>\n\n\n\n
On both servers, MySQL requires a restart to pick up the config changes:<\/p>\n\n\n\n
systemctl restart mariadb<\/code><\/pre>\n\n\n\n
Create a replication user<\/h2>\n\n\n\n
A replication needs to be authenticated, rather than using the root user its preferred to create a user that is only able to perform the replication.<\/p>\n\n\n\n
On the source server, login to MySQL:<\/p>\n\n\n\n
mysql -u root -p<\/code><\/pre>\n\n\n\n
Create the user, and give it replication duties with the following commands:<\/p>\n\n\n\n
CREATE USER '{replica_user}'@'{replica_ip}' IDENTIFIED BY '{password}';\nGRANT REPLICATION SLAVE ON *.* TO '{replica_user}'@'{replica_ip}';\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n
Copy current databases<\/h2>\n\n\n\n
Before the replication can begin, we’ll need a copy of the existing data captured within a known point of time. This allows the replica to know where to pick up from where the imported databases left off.<\/p>\n\n\n\n
Source Server<\/h3>\n\n\n\n
If not already, log into mysql :<\/p>\n\n\n\n
mysql -u root -p<\/code><\/pre>\n\n\n\n
Lock the databases from being able to be written to:<\/p>\n\n\n\n
FLUSH TABLES WITH READ LOCK;<\/code><\/pre>\n\n\n\n
Display the current state of the logfiles while the databases are frozen in time:<\/p>\n\n\n\n
SHOW MASTER STATUS;<\/code><\/pre>\n\n\n\n
This will produce something similar to this:<\/p>\n\n\n\n
MariaDB [(none)]> SHOW MASTER STATUS;\n+------------------+----------+--------------+------------------+\n| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |\n+------------------+----------+--------------+------------------+\n| mysql-bin.000016 |      342 |              |                  |\n+------------------+----------+--------------+------------------+\n1 row in set (0.000 sec)<\/code><\/pre>\n\n\n\n
Make a note of the File and Position values, we’ll need this as a reference point for the replica. I’d recommend saving it as a file for future reference.<\/p>\n\n\n\n
Now, open up new SSH session to the source server. Its important that the current MySQL session is kept open and in its locked state.<\/p>\n\n\n\n
Take a mysqldump of the entire database:<\/p>\n\n\n\n
mysqldump -u root -p --all-databases > mysql_replication.sql<\/code><\/pre>\n\n\n\n
When this completes, return to the original session to return the databases to normal:<\/p>\n\n\n\n
UNLOCK TABLES;<\/code><\/pre>\n\n\n\n
Transfer SQL file to Replica<\/h2>\n\n\n\n
Move the generated mysqldump file to the replica server, I usually transfer the master status txt file too for future reference:<\/p>\n\n\n\n
scp mysql_replication.sql mysql_master.txt root@{replica_ip}:\/root\/<\/code><\/pre>\n\n\n\n
Setup Replication<\/h2>\n\n\n\n
Import the newly transferred file to the replica’s MySQL:<\/p>\n\n\n\n
mysql -u root -p < mysql_replication.sql<\/code><\/pre>\n\n\n\n
Then log in to MySQL:<\/p>\n\n\n\n
mysql -u root -p<\/code><\/pre>\n\n\n\n
Time to setup the replication, using the replica user setup previously, and the logfile positions captured during the mysqldump. The SHOW MASTER STATUS; output from above is referenced in this command example:<\/p>\n\n\n\n
 CHANGE MASTER TO MASTER_HOST='{source_ip}', MASTER_USER='{replica_user}', MASTER_PASSWORD='{password}', MASTER_LOG_FILE='mysql-bin.000016', MASTER_LOG_POS=342;<\/code><\/pre>\n\n\n\n
Reference points now in place, start the replication process:<\/p>\n\n\n\n
START SLAVE;<\/code><\/pre>\n\n\n\n
You can check it is running with:<\/p>\n\n\n\n
SHOW SLAVE STATUS\\G;<\/code><\/pre>\n\n\n\n
In the output you’re looking for Slave_IO_Running and Slave_SQL_Running to both be Yes:<\/p>\n\n\n
\n
\"\"<\/a><\/figure><\/div>\n\n\n
With both reporting Yes, the replication is running. All changes made to the source server is automatically pushed to the replica.<\/p>\n\n\n\n
Notes<\/h2>\n\n\n\n
Ports<\/h3>\n\n\n\n
The two databases communicate over its MySQL port, default is 3306. Ensure that both servers are able to communicate on this port.<\/p>\n\n\n\n
Authentication<\/h3>\n\n\n\n
Immediately after setting up replication, you’ll be able to log in to the replica server as normal. However, if the service\/server was to be restarted you may not be able to login as normal.<\/p>\n\n\n\n
As this guide replicates everything on the source MySQL, it will also replicate the users and permissions.<\/p>\n\n\n\n
Following a replication start, the “old” credentials are still usable But following a service restart, the credentials of the source are the new normal on the replica, and should be used for any future login.<\/p>\n\n\n\n
This will also apply to any instances of phpMyAdmin.<\/p>\n","protected":false},"excerpt":{"rendered":"
Sure, its not the most original of topics, but it is one I’ve relied upon from time to time. For years, this guide from Digital Ocean was my go to choice. It wasn’t until my most recent visit for an upcoming project that things looked, well, a little different. It was mostly the same, but … Continue reading “MySQL Replication”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[212,5],"tags":[50,414],"class_list":["post-1018","post","type-post","status-publish","format-standard","hentry","category-network","category-servers","tag-mysql","tag-replication"],"_links":{"self":[{"href":"https:\/\/james-batchelor.com\/index.php\/wp-json\/wp\/v2\/posts\/1018","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/james-batchelor.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/james-batchelor.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/james-batchelor.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/james-batchelor.com\/index.php\/wp-json\/wp\/v2\/comments?post=1018"}],"version-history":[{"count":16,"href":"https:\/\/james-batchelor.com\/index.php\/wp-json\/wp\/v2\/posts\/1018\/revisions"}],"predecessor-version":[{"id":1038,"href":"https:\/\/james-batchelor.com\/index.php\/wp-json\/wp\/v2\/posts\/1018\/revisions\/1038"}],"wp:attachment":[{"href":"https:\/\/james-batchelor.com\/index.php\/wp-json\/wp\/v2\/media?parent=1018"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/james-batchelor.com\/index.php\/wp-json\/wp\/v2\/categories?post=1018"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/james-batchelor.com\/index.php\/wp-json\/wp\/v2\/tags?post=1018"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}