Tutorial
How To Set Up Replication in MySQL
A previous version of this tutorial was written by Etel Sverdlov.
Introduction
When working with databases, it can be useful to have multiple copies of your data. This provides redundancy in case one of the database servers fails and can improve a database’s availability, scalability, and overall performance. The practice of synchronizing data across multiple separate databases is called replication.
MySQL is a relational database management system, and is the most popular open-source relational database in the world today. It comes installed with a number of built-in replication features, allowing you to maintain multiple copies of your data.
This tutorial outlines how to configure a MySQL instance on one server as a source database and then configure a MySQL instance on another server to function as its replica. It also includes an overview of how MySQL handles replication.
Note: Historically, this type of database replication has been referred to as “master-slave” replication. In a blog post published in July of 2020, the MySQL team acknowledged the negative origin of this terminology and announced their efforts to update the database program and its documentation to use more inclusive language.
However, this is an ongoing process. Although MySQL’s documentation and much of the commands in version 8 of the program have been updated to instead refer to the servers in a replication topology as the source and its replicas, there are places where the negative terminology still appears. This guide will default to the more inclusive source-replica terminology wherever possible, but there are a few instances where the older terms unavoidably come up.
Prerequisites
To complete this guide, you will need:
- Two servers running Ubuntu 20.04. Both should have a non-root administrative user with
sudoprivileges and a firewall configured with UFW. Follow our initial server setup guide for Ubuntu 20.04 to set up both servers. - MySQL installed on each server. This guide assumes that you’re using the latest version of MySQL available from the default Ubuntu repositories which, as of this writing, is version 8.0.25. To install this on both servers, follow our guide on How To Install MySQL on Ubuntu 20.04.
Be aware that the procedure outlined in this guide involves designating the MySQL installation on one server as the source database, and then configuring the MySQL installation on the other server to be the source’s replica. To keep things clear, any commands that must be run on the source database’s server will have a blue background, like this:
Likewise, any commands that must be run on the replica MySQL instance’s server will have a red background:
Lastly, this tutorial includes optional instructions on how to migrate data in an existing database from the source to the replica. This process involves creating a snapshot of the source’s database and copying the resulting file to the replica. To do this, we recommend that you set up SSH keys on the source server server and then make sure that the source’s public key has been copied to the replica.
Understanding Replication in MySQL
In MySQL, replication involves the source database writing down every change made to the data held within one or more databases in a special file known as the binary log. Once the replica instance has been initialized, it creates two threaded processes. The first, called the IO thread, connects to the source MySQL instance and reads the binary log events line by line, and then copies them over to a local file on the replica’s server called the relay log. The second thread, called the SQL thread, reads events from the relay log and then applies them to the replica instance as fast as possible.
Recent versions of MySQL support two methods for replicating data. The difference between these replication methods has to do with how replicas track which database events from the source they’ve already processed.
MySQL refers to its traditional replication method as binary log file position-based replication. When you turn a MySQL instance into a replica using this method, you must provide it with a set of binary log coordinates. These consist of the name of the binary log file on the source which the replica must read and a specific position within that file which represents the first database event the replica should copy to its own MySQL instance.
These coordinates are important since replicas receive a copy of their source’s entire binary log and, without the right coordinates, they will begin replicating every database event recorded within it. This can lead to problems if you only want to replicate data after a certain point in time or only want to replicate a subset of the source’s data.
Binary log file position-based replication is viable for many use cases, but this method can become clunky in more complex setups. This led to the development of MySQL’s newer native replication method, which is sometimes referred to as transaction-based replication. This method involves creating a global transaction identifier (GTID) for each transaction — or, an isolated piece of work performed by a database — that the source MySQL instance executes.
The mechanics of transaction-based replication are similar to binary log file-based replication: whenever a database transaction occurs on the source, MySQL assigns and records a GTID for the transaction in the binary log file along with the transaction itself. The GTID and the transaction are then transmitted to the source’s replicas for them to process.
MySQL’s transaction-based replication has a number of benefits over its traditional replication method. For example, because both a source and its replicas preserve GTIDs, if either the source or a replica encounter a transaction with a GTID that they have processed before they will skip that transaction. This helps to ensure consistency between the source and its replicas. Additionally, with transaction-based replication replicas don’t need to know the binary log coordinates of the next database event to process. This means that starting new replicas or changing the order of replicas in a replication chain is far less complicated.
Keep in mind that this is only a general explanation of how MySQL handles replication; MySQL provides many options which you can tweak to optimize your own replication setup. This guide outlines how to set up binary log file position-based replication. If you’re interested in configuring a different type of replication environment, though, we encourage you to check out MySQL’s official documentation .
Step 1 — Adjusting Your Source Server’s Firewall
Assuming you followed the prerequisite Initial Server Setup Guide, you will have configured a firewall on both your servers with UFW. This will help to keep both your servers secure, but the source’s firewall will block any connection attempts from your replica MySQL instance.
To change this, you’ll need to include a UFW rule that allows connections from your replica through the source’s firewall. You can do this by running a command like the following on your source server.
This particular command allows any connections that originate from the replica server’s IP address — represented by replica_server_ip — to MySQL’s default port number, 3306:
Be sure to replace replica_server_ip with your replica server’s actual IP address. If the rule was added successfully you’ll see the following output:
OutputRule added
Following that, you won’t need to make any changes to the replica’s firewall rules, since the replica server won’t receive any incoming connections and the outgoing connections to the source MySQL server aren’t blocked by UFW. You can move on to updating the source MySQL instance’s configuration to enable replication.
Step 2 — Configuring the Source Database
In order for your source MySQL database to begin replicating data, you need to make a few changes to its configuration.
On Ubuntu 20.04, the default MySQL server configuration file is named mysqld.cnf and can be found in the /etc/mysql/mysql.conf.d/ directory. Open this file on the source server with your preferred text editor. Here, we’ll use nano:
Within the file, find the bind-address directive. It will look like this by default:
. . .
bind-address = 127.0.0.1
. . .
127.0.0.1 is an IPv4 loopback address that represents localhost, and setting this as the value for the bind-address directive instructs MySQL to only listen for connections on the localhost address. In other words, this MySQL instance will only be able to accept connections that originate from the server where it’s installed.
Remember that you’re turning your other MySQL instance into a replica of this one, so the replica must be able to read whatever new data gets written to the source installation. To allow this, you must configure your source MySQL instance to listen for connections on an IP address which the replica will be able to reach, such as the source server’s public IP address.
Replace 127.0.0.1 with the source server’s IP address. After doing so, the bind-address directive will look like this, with your own server’s IP address in place of source_server_ip:
. . .
bind-address = source_server_ip
. . .
Next, find the server-id directive, which defines an identifier that MySQL uses internally to distinguish servers in a replication setup. Every server in a replication environment, including the source and all its replicas, must have their own unique server-id value. This directive will be commented out by default and will look like this:
. . .
# server-id = 1
. . .
Uncomment this line by removing the pound sign (#). You can choose any number as this directive’s value, but remember that the number must be unique and cannot match any other server-id in your replication group. To keep things simple the following example leaves this value as the default, 1:
. . .
server-id = 1
. . .
Below the server-id line, find the log_bin directive. This defines the base name and location of MySQL’s binary log file.
When commented out, as this directive is by default, binary logging is disabled. Your replica server must read the source’s binary log file so it knows when and how to replicate the source’s data, so uncomment this line to enable binary logging on the source. After doing so, it will look like this:
. . .
log_bin = /var/log/mysql/mysql-bin.log
. . .
Lastly, scroll down to the bottom of the file to find the commented-out binlog_do_db directive:
. . .
# binlog_do_db = include_database_name
Remove the pound sign to uncomment this line and replace include_database_name with the name of the database you want to replicate. This example shows the binlog_do_db directive pointing to a database named db, but if you have an existing database on your source that you want to replicate, use its name in place of db:
. . .
binlog_do_db = db
Note: If you want to replicate more than one database, you can add another binlog_do_db directive for every database you want to add. This tutorial will continue on with replicating only a single database, but if you wanted to replicate more it might look like this:
. . .
binlog_do_db = db
binlog_do_db = db_1
binlog_do_db = db_2
Alternatively, you can specify which databases MySQL should not replicate by adding a binlog_ignore_db directive for each one:
. . .
binlog_ignore_db = db_to_ignore
After making these changes, save and close the file. If you used nano to edit the file, do so by pressing CTRL + X, Y, and then ENTER.
Then restart the MySQL service by running the following command:
With that, this MySQL instance is ready to function as the source database which your other MySQL server will replicate. Before you can configure your replica, though, there are still a few more steps you need to perform on the source to ensure that your replication topology will function correctly. The first of these is to create a dedicated MySQL user which will perform any actions related to the replication process.
Step 3 — Creating a Replication User
Each replica in a MySQL replication environment connects to the source database with a username and password. Replicas can connect using any MySQL user profile that exists on the source database and has the appropriate privileges, but this tutorial will outline how to create a dedicated user for this purpose.
Start by opening up the MySQL shell:
Note: If you configured a dedicated MySQL user that authenticates using a password, you can connect to your MySQL with a command like this instead:
Replace sammy with the name of your dedicated user, and enter this user’s password when prompted.
Be aware that some operations throughout this guide, including a few that must be performed on the replica server, require advanced privileges. Because of this, it may be more convenient to connect as an administrative user, as you can with the previous sudo mysql command. If you want to use a less privileged MySQL user throughout this guide, though, they should at least be granted the CREATE USER, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, and REPLICATION_SLAVE_ADMIN privileges.
From the prompt, create a new MySQL user. The following example will create a user named replica_user, but you can name yours whatever you’d like. Be sure to change replica_server_ip to your replica server’s public IP address and to change password to a strong password of your choosing:
Note that this command specifies that replica_user will use the mysql_native_password authentication plugin. It’s possible to instead use MySQL’s default authentication mechanism, caching_sha2_password, but this would require setting up an encrypted connection between the source and the replica. This kind of setup would be optimal for production environments, but configuring encrypted connections is beyond the scope of this tutorial. The MySQL documentation includes instructions on how to configure a replication environment that uses encrypted connections if you’d like to set this up.
After creating the new user, grant them the appropriate privileges. At minimum, a MySQL replication user must have the REPLICATION SLAVE permissions:
Following this, it’s good practice to run the FLUSH PRIVILEGES command. This will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:
With that, you’ve finished setting up a replication user on your source MySQL instance. However, do not exit the MySQL shell. Keep it open for now, as you’ll use it in the next step to obtain some important information about the source database’s binary log file.
Step 4 — Retrieving Binary Log Coordinates from the Source
Recall from the Understanding Replication in MySQL section that MySQL implements replication by copying database events from the source’s binary log file line by line and implementing each event on the replica. When using MySQL’s binary log file position-based replication, you must provide the replica with a set of coordinates that detail the name of the source’s binary log file and a specific position within that file. The replica then uses these coordinates to determine the point in the log file from which it should begin copying database events and track which events it has already processed.
This step outlines how to obtain the source instance’s current binary log coordinates in order to set your replicas to begin replicating data from the latest point in the log file. To make sure that no users change any data while you retrieve the coordinates, which could lead to problems, you’ll need to lock the database to prevent any clients from reading or writing data as you obtain the coordinates. You will unlock everything shortly, but this procedure will cause your database to go through some amount of downtime.
You should still have your source server’s MySQL shell open from the end of the previous step. From the prompt, run the following command which will close all the open tables in every database on your source instance and lock them:
Then run the following operation which will return the current status information for the source’s binary log files:
You will see a table similar to this example in your output:
Output+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 899 | db | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
This is the position from which the replica will start copying database events. Record the File name and the Position value, as you will need these later when you initiate replication.
What you do immediately after obtaining this information depends on whether your source database has any existing data you want to migrate over to your replicas. Jump to whichever of the two following subsections makes the most sense for your situation.
If Your Source Doesn’t Have Any Existing Data to Migrate
If your source MySQL instance is a new installation or doesn’t have any existing data you want to migrate to your replicas, you can at this point unlock the tables:
If you haven’t done so already, you could create the database you’ve chosen to replicate while you still have the MySQL shell open. In keeping with the example given in Step 2, the following operation will create a database named db:
OutputQuery OK, 1 row affected (0.01 sec)
After that, close the MySQL shell:
Following that, you can move on to the next step.
If Your Source Has Existing Data to Migrate
If you have data on your source MySQL instance that you want to migrate to your replicas, you can do so by creating a snapshot of the database with the mysqldump utility. However, your database should still be currently locked. If you make any new changes in the same window, the database will automatically unlock. Likewise, the tables will automatically unlock if you exit the client.
Unlocking the tables could lead to problems since it would mean that clients could again change the data in the database. This could potentially lead to a mismatch between your data snapshot and the binary log coordinates you just retrieved.
For this reason, you must open a new terminal window or tab on your local machine so you can create the database snapshot without unlocking MySQL.
From the new terminal window or tab, open up another SSH session to the server hosting your source MySQL instance:
Then, from the new tab or window, export your database using mysqldump. The following example creates a dump file named db.sql from a database named db, but make sure you include the name of your own database instead. Also, be sure to run this command in the bash shell, not the MySQL shell:
Following that you can close this terminal window or tab and return to your first one, which should still have the MySQL shell open. From the MySQL prompt, unlock the databases to make them writable again:
Then you can exit the MySQL shell:
You can now send your snapshot file to your replica server. Assuming you’ve configured SSH keys on your source server and have added the source’s public key to your replica’s authorized_keys file, you can do this securely with an scp command like this:
Be sure to replace sammy with the name of the administrative Ubuntu user profile you created on your replica server, and to replace replica_server_ip with the replica server’s IP address. Also, note that this command places the snapshot in the replica server’s /tmp/ directory.
After sending the snapshot to the replica server, SSH into it:
Then open up the MySQL shell:
From the prompt, create the new database that you will be replicating from the source:
You don’t need to create any tables or load this database with any sample data. That will all be taken care of when you import the database using the snapshot you just created. Instead, exit the MySQL shell:
Then import the database snapshot:
Your replica now has all the existing data from the source database. You can complete the final step of this guide to configure your replica server to begin replicating new changes made on the source database.
Step 5 — Configuring the Replica Database
All that’s left to do is to change the replica’s configuration similar to how you changed the source’s. Open up the MySQL configuration file, mysqld.cnf, this time on your replica server:
As mentioned previously, each MySQL instance in a replication setup must have a unique server-id value. Find the replica’s server-id directive, uncomment it, and change its value to any positive integer, as long as it’s different from that of the source:
server-id = 2
Following that, update the log_bin and binlog_do_db values so that they align with the values you set in the source machine’s configuration file:
. . .
log_bin = /var/log/mysql/mysql-bin.log
. . .
binlog_do_db = db
. . .
Lastly, add a relay-log directive defining the location of the replica’s relay log file. Include the following line at the end of the configuration file:
. . .
relay-log = /var/log/mysql/mysql-relay-bin.log
After making these changes, save and close the file. Then restart MySQL on the replica to implement the new configuration:
After restarting the mysql service, you’re finally ready to start replicating data from your source database.
Step 6 — Starting and Testing Replication
At this point, both of your MySQL instances are fully configured to allow replication. To start replicating data from your source, open up the the MySQL shell on your replica server:
From the prompt, run the following operation, which configures several MySQL replication settings at the same time. After running this command, once you enable replication on this instance it will try to connect to the IP address following SOURCE_HOST using the username and password following SOURCE_USER and SOURCE_PASSWORD, respectively. It will also look for a binary log file with the name following SOURCE_LOG_FILE and begin reading it from the position after SOURCE_LOG_POS.
Be sure to replace source_server_ip with your source server’s IP address. Likewise, replica_user and password should align with the replication user you created in Step 2; and mysql-bin.000001 and 899 should reflect the binary log coordinates you obtained in Step 3.
You may want to type this command out in a text editor before running it on your replica server so that you can more easily replace all the relevant information:
Following that, activate the replica server:
If you entered all the details correctly, this instance will begin replicating any changes made to the db database on the source.
You can see details about the replica’s current state by running the following operation. The \G modifier in this command rearranges the text to make it more readable:
This command returns a lot of information which can be helpful when troubleshooting:
Output*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: 138.197.3.190
Source_User: replica_user
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 1273
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 729
Relay_Source_Log_File: mysql-bin.000001
. . .
Note: If your replica has an issue in connecting or replication stops unexpectedly, it may be that an event in the source’s binary log file is preventing replication. In such cases, you could run the SET GLOBAL SQL_SLAVE_SKIP_COUNTER command to skip a certain number of events following the binary log file position you defined in the previous command. This example only skips the first event:
Following that, you’d need to start the replica again:
Also, if you ever need to stop replication, note that you can do so by running the following operation on the replica instance:
Your replica is now replicating data from the source. Any changes you make to the source database will be reflected on the replica MySQL instance. You can test this by creating a sample table on your source database and checking whether it gets replicated successfully.
Begin by opening up the MySQL shell on your source machine:
Select the database you chose to replicate:
Then create a table within that database. The following SQL operation creates a table named example_table with one column named example_column:
OutputQuery OK, 0 rows affected (0.03 sec)
If you’d like, you can also add some sample data to this table:
OutputQuery OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
After creating a table and optionally adding some sample data to it, go back to your replica server’s MySQL shell and select the replicated database:
Then run the SHOW TABLES statement to list all the tables within the selected database:
If replication is working correctly, you’ll see the table you just added to the source listed in this command’s output:
Output+---------------+
| Tables_in_db |
+---------------+
| example_table |
+---------------+
1 row in set (0.00 sec)
Also, if you added some sample data to the table on the source, you can check whether that data was also replicated with a query like the following:
In SQL, an asterisk (*) is shorthand “all columns.” So this query essentially tells MySQL to return every column from example_table. If replication is working as expected, this operation will return that data in its output:
Output+------------------------+
| example_column |
+------------------------+
| This is the first row |
| This is the second row |
| This is the third row |
+------------------------+
3 rows in set (0.00 sec)
If either of these operations fail to return the example table or data that you added to the source, it may be that you have an error somewhere in your replication configuration. In such cases, you could run the SHOW REPLICA STATUS\G operation to try finding the cause of the issue. Additionally, you can consult MySQL’s documentation on troubleshooting replication for suggestions on how to resolve replication problems.
Conclusion
By completing this tutorial, you will have set up a MySQL replication environment that uses MySQL’s binary log file position-based replication method with one source and one replica. Bear in mind, though, that the procedure outlined in this guide represents only one way of configuring replication in MySQL. MySQL provides a number of different replication options which you can use to produce a replication environment optimized for your needs. There are also a number of third-party tools, such as Galera Cluster, that you can use to expand upon MySQL’s built-in replication features.
If you have any further questions about the specific capabilities of replication in MySQL, we encourage you to check out MySQL’s official documentation on the subject. If you’d like to learn more about MySQL generally, you could also check out our entire library of MySQL-related content.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
About the author(s)
This textbox defaults to using Markdown to format your answer.
You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.
A few suggestions I would like to make in regards to this article:
I think it should be mentioned that MySQL has the capabilities to bind to multiple IP addresses and that to take advantage of this feature, an additional “bind-address” line is required.
In the following section (regarding server-ids) you mentioned that this line has to be “uncommented” but I’m not sure all users will know that means that they have to remove the “#” preceding it if there is one (which there will be in the installed version of my.cnf.
In the section regarding the databases that they want to replicate, I believe it should be mentioned that leaving this line commented will result in all databases being replicated. In some, though admittedly rather rare situations, this is the desired behavior. This, too, will allow new databases to be replicated automatically without any additionally editing of the configuration files.
Some of the wording in the explanations is a bit awkward, specifically: “This is the position from which the slave database will start replicating. Record these numbers, they will come in useful later.” This might be more grammatically correct if stated as “This is the file name and position that the master server is using for logging purposes. Make a note of these, as you will need to enter them later”.
After the mysqldump step, there should be an additional step explaining how to send the newly created .sql file to the remote host. SCP would be the easiest way, in my opinion, to accomplish this.
Jason thanks for expanding on this!
Well, i just followed the steps as mentioned and everything went fine. But the changes in Master DB did not reflect in slave DB In my case, Master DB & Slave DB resides on two different droplets. Where could i have gone wrong ?
Nice post, works great. It would be nice if you could extend this article to include how one would add additional slave servers to the setup, since most people following this would want to scale someday.
This is a good link to add yet another slave and from my experience it works great. http://www.redips.net/mysql/add-new-slave/
As a backup I would not recommended this approach. If data is corrupted, lost or deleted in the master that will be synced to the slave. Restoring the slave to the master does not get you your data back.
What I miss is the explanation how to use the slave for failover. If somebody could add that that would be helpful (at least for me)
Ruter
MySQL 5.6 supports delayed replication such that a slave server deliberately lags behind the master by at least a specified amount of time.
@Ruter
Good point. What do you suggest?
Thanks, Mike
Great article !! Just one comment from my experience : Use short short passwords to the replica user :) password like bKpGpJIQEm1KHhbEuf6zueTBvfW84mI6XYCcxaS2 WON’T work !! half of it works well, took me some hours checking everything before find it.
Good starter, but I wonder: why are you running bin-logs on the slave?
How often slave updated from master? Is it configurable?
@Sahaya: I believe it’s instant by default. It is configurable: http://alexalexander.blogspot.com/2013/03/mysql-slave-delay-how-to.html
This works really well but I have had a problem where the server-id config variable set in the [mysqld] section of the my.cnf was not being picked up by mysql on restart. No matter what I set it to on the slave it would always be the default which is 0.
When I ran “start-slave” I would get an error message…
“The server is not configured as slave; fix in config file or with CHANGE MASTER TO”
I ended up running the following command to give it a server-id of 2 before I could run start slave.
set global server_id=2
thanks for the excellent article. however, i could not get it to work, until i changed this on the slave: binlog_do_db = dbnae
change to replicate_do_db = dbname
Thanks for the great article. What happens when a slave db is modified? Does it update the master which then in-turn updates any other slave dbs?
@matan: The write will fail as it is not supposed to accept writes. You might want to check out master-master replication: <a href=“https://www.digitalocean.com/community/articles/how-to-set-up-mysql-master-master-replication”>https://www.digitalocean.com/community/articles/how-to-set-up-mysql-master-master-replication</a>
Works GREAT, thanks for the tutorial!
Great tutorial, I am going to add a 1GB slave server where I will do my backups.
Will the slave server slow down my prod/master server?
@KiwoT: It shouldn’t noticeably affect the master’s performance.
where do you set the tables from which it will copy from the master and also where do you set where you want to copy that data TO on the slave?
so i have database called web1.0 which has a table called contacts. Then i have a different database on another server called web2.0.
i want to replicate everything contacts on web1.0 to web2.0
how to do this pls?
@m.kamran237: As far as I know you can’t do that. It has to be exactly the same, e.g. web1.0 gets replicated to web2.0. I’m not sure if you can choose which tables you want to be replicated or if it just replicates all of the database.
If I’m going to have multiple slaves and web servers query the master, would it make more sense to do this:
And then in iptables:
Adding a line for each IP I want to allow to connect to the master?
Sometimes he stops updating.
Restart the severs or restart the process doesn’t always work.
What to do?
Hi all! master-slave (Server version: 5.6.11-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)) (test master- slave from Staging ok) document :https://www.digitalocean.com/community/articles/how-to-set-up-master-slave-replication-in-mysql But i config PRODUCTION FAILED :( ########################## bind-address = localIP log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = dbpro ###################
when i restart mysql (master) error: Starting MySQL…The server quit without updating PID file ([FAILED]mysql/data/server_pro.com.pid).
FIX: -ps aux | grep mysql --> kill -9 … or -mv /etc/my.cnf /etc/my.cnf.backup or -mv /var/lib/mysql/ib_logfile* /root/
but it FAILED start
log-err
2014-05-12 12:38:37 8577 [Note] Giving 0 client threads a chance to die gracefully 2014-05-12 12:38:37 8577 [Note] Event Scheduler: Purging the queue. 0 events 2014-05-12 12:38:37 8577 [Note] Shutting down slave threads 2014-05-12 12:38:37 8577 [Note] Forcefully disconnecting 0 remaining clients 2014-05-12 12:38:37 8577 [Note] Binlog end 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘partition’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘ARCHIVE’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘BLACKHOLE’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_SYS_DATAFILES’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_SYS_TABLESPACES’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN_COLS’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_SYS_FOREIGN’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_SYS_FIELDS’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_SYS_COLUMNS’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_SYS_INDEXES’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_SYS_TABLESTATS’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_SYS_TABLES’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_FT_INDEX_TABLE’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_FT_INDEX_CACHE’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_FT_CONFIG’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_FT_BEING_DELETED’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_FT_DELETED’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_FT_DEFAULT_STOPWORD’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_METRICS’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_BUFFER_POOL_STATS’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE_LRU’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_BUFFER_PAGE’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX_RESET’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_CMP_PER_INDEX’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_CMPMEM_RESET’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_CMPMEM’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_CMP_RESET’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_CMP’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_LOCK_WAITS’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_LOCKS’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘INNODB_TRX’ 2014-05-12 12:38:37 8577 [Note] Shutting down plugin ‘InnoDB’ 2014-05-12 12:38:37 8577 [Note] InnoDB: FTS optimize thread exiting. 2014-05-12 12:38:37 8577 [Note] InnoDB: Starting shutdown… 2014-05-12 12:38:39 8577 [Note] InnoDB: Shutdown completed; log sequence number 4943347270 2014-05-12 12:38:39 8577 [Note] Shutting down plugin ‘PERFORMANCE_SCHEMA’ 2014-05-12 12:38:39 8577 [Note] Shutting down plugin ‘CSV’ 2014-05-12 12:38:39 8577 [Note] Shutting down plugin ‘MEMORY’ 2014-05-12 12:38:39 8577 [Note] Shutting down plugin ‘MRG_MYISAM’ 2014-05-12 12:38:39 8577 [Note] Shutting down plugin ‘MyISAM’ 2014-05-12 12:38:39 8577 [Note] Shutting down plugin ‘sha256_password’ 2014-05-12 12:38:39 8577 [Note] Shutting down plugin ‘mysql_old_password’ 2014-05-12 12:38:39 8577 [Note] Shutting down plugin ‘mysql_native_password’ 2014-05-12 12:38:39 8577 [Note] Shutting down plugin ‘binlog’ 2014-05-12 12:38:39 8577 [Note] /usr/sbin/mysqld: Shutdown complete
140512 12:38:39 mysqld_safe mysqld from pid file /backup/mysql/data/server_pro.com.pid ended 140512 12:38:40 mysqld_safe Starting mysqld daemon with databases from /backup/mysql/data 2014-05-12 12:38:40 0 [Warning] option ‘log_queries_not_using_indexes’: boolean value ‘/backup/mysql/log/query-not-used-index.log’ wasn’t recognized. Set to OFF. 2014-05-12 12:38:40 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). ^G/usr/sbin/mysqld: File ‘/var/log/mysql/mysql-bin.index’ not found (Errcode: 13 - Permission denied) 2014-05-12 12:38:40 4031 [ERROR] Aborting
2014-05-12 12:38:40 4031 [Note] Binlog end 2014-05-12 12:38:40 4031 [Note] /usr/sbin/mysqld: Shutdown complete
140512 12:38:40 mysqld_safe mysqld from pid file /backup/mysql/data/server_pro.com.pid ended 140512 12:38:55 mysqld_safe Starting mysqld daemon with databases from /backup/mysql/data 2014-05-12 12:38:56 0 [Warning] option ‘log_queries_not_using_indexes’: boolean value ‘/backup/mysql/log/query-not-used-index.log’ wasn’t recognized. Set to OFF. 2014-05-12 12:38:56 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2014-05-12 12:38:56 4757 [Note] Plugin ‘FEDERATED’ is disabled. 2014-05-12 12:38:56 7fd0079786f0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be rem oved in future releases, together with the option innodb_use_sys_malloc and with the InnoDB’s internal memory allocator. 2014-05-12 12:38:56 4757 [Note] InnoDB: The InnoDB memory heap is disabled 2014-05-12 12:38:56 4757 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2014-05-12 12:38:56 4757 [Note] InnoDB: Compressed tables use zlib 1.2.3 2014-05-12 12:38:56 4757 [Note] InnoDB: Using Linux native AIO 2014-05-12 12:38:56 4757 [Note] InnoDB: Using CPU crc32 instructions 2014-05-12 12:38:56 4757 [Note] InnoDB: Initializing buffer pool, size = 2.9G 2014-05-12 12:38:56 4757 [Note] InnoDB: Completed initialization of buffer pool 2014-05-12 12:38:56 4757 [Note] InnoDB: Highest supported file format is Barracuda. 2014-05-12 12:38:56 4757 [Note] InnoDB: 128 rollback segment(s) are active. 2014-05-12 12:38:56 4757 [Note] InnoDB: Waiting for purge to start 2014-05-12 12:38:56 4757 [Note] InnoDB: 5.6.11 started; log sequence number 4943347270 2014-05-12 12:38:56 4757 [Note] RSA private key file not found: /backup/mysql/data//private_key.pem. Some authentication plugins will not work. 2014-05-12 12:38:56 4757 [Note] RSA public key file not found: /backup/mysql/data//public_key.pem. Some authentication plugins will not work. 2014-05-12 12:38:56 4757 [Note] Server hostname (bind-address): ‘*’; port: 3306 2014-05-12 12:38:56 4757 [Note] IPv6 is available. 2014-05-12 12:38:56 4757 [Note] - ‘::’ resolves to ‘::’; 2014-05-12 12:38:56 4757 [Note] Server socket created on IP: ‘::’. 2014-05-12 12:38:56 4757 [Note] Event Scheduler: Loaded 0 events 2014-05-12 12:38:56 4757 [Note] /usr/sbin/mysqld: ready for connections. Version: ‘5.6.11-enterprise-commercial-advanced’ socket: ‘/backup/mysql/mysql.sock’ port: 3306 MySQL Enterprise Server - Advanced Edition (Commercial) 2014-05-12 12:38:57 4757 [Warning] IP address ‘x.x.x.13’ could not be resolved: Name or service not known
Hi,
I have set up master master replication as mentioned in your tutorial. Anytime if I restart mysql service, replication stops working because log file and position are getting changed. I have to run CHANGE MASTER TO MASTER_HOST command again to work replication properly. Is there anyway to permanently add log file and position anywhere?
Thank you, Umaimath
Master-slave replication is meant for scalability purpose, not backup. If you want to do a backup, write a small shell script to do a nightly mysqldump and archive it.
Regarding binlog_do_db this article might be interesting. Be careful.
why-mysqls-binlog-do-db-option-is-dangerou
One question on replication… if i change slave database, it will affect to master table?
This comment has been deleted
thanks for this tutorial and was able to make a master-slave setup of mysql… one question though, what if the slave server restarts, will the settings will stay intact and will still contact the master server? chkconfig mysqld on already set on the slave server, thanks!
Thanks Very Much for your clear steps ,
Finally I replicate my DB :) and I can use the other database for Dev & Reporting purpose.
I have note about backup step : you cant take backup if the database read lock , so for this step you need to lock database then run command : show master status ; then take requested information (File name & Position ) then unlock database and from second tab start taking backup by mysqldump !
also kindly your feedback about performance , how the DB replication affects server performance (both Slave & Master) ?
Thanks
I’m surprised the tutorial doesn’t just use the --master-data=2 switch. This will save you a lot of effort with not having to manually flush all tables with read lock in one session, or have to remember any binlog numbers. Those of you that have innodb only tables can even use the --single-transaction switch to not have all tables locked, yet still take a consistent dump.
master-data info http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_master-data
single-transaction http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_single-transaction
Unfortunately, the D.O. commenting system strips out the underscores in the links, so the links wont take you to the correct section, just the correct page.
Nice manual.
BTW you have an error in the last chunk of SQL code:
mysql> SLAVE START; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SLAVE START’ at line 1
Please change the SLAVE START; to START SLAVE;
Great article! I configured replication in no time and it actually worked - confirmed. Any suggestions for the simple replication monitoring solution? I need to be sure that the slave is never too far behind master (“Seconds behind master”).
Great post!
My two cents on it: after you follow all the steps, your replication may not work at first, so check your slave status (“show slave status”), and read the error messages.
If you followed the steps closely, and performed the process in Linux Ubuntu and MySQL 5.6, you might get this message: “The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.”
You need to remove the “auto.cnf” from the datadir in the slave machine. The datadir might differ from every linux distro. To find that out in mysql, run: “SELECT @@DATADIR”.
All the best!
Why did you use fictitious IP Addresses?!
While running CHANGE MASTER TO MASTER_HOST command on slave , I am getting this error,
“ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.”
When executing “CHANGE MASTER”: -> \g ERROR 29 (HY000): File ‘/var/log/mysql/relay-log.index’ not found (Errcode: 2 “No such file or directory”)
Make sure to create the dir and change owner/group to mysql.
mkdir /var/log/mysql/ && chown mysql:mysql /var/log/mysql
I noticed nothing was mentioned about security. What are best practices for replicating between NYC and SFO? I’m assuming MySQL DB replication is done in clear text.
Is it possible to replicate over SSL by generating a certificate and creating a replication user that requires it? Then modify the my.cnf on both sides with the cert file location.
Awesome. Thanks for this nice article.
Nice article, but I get “ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository” when I try “start slave;” on the slave box. I have followed the instructions to the letter (I believe ). Any ideas? Mysql 5.6, ubuntu 14.
Google found a solution: first type “reset slave;”, then put in the CHANGE MASTER line again, then “start slave;”
thanks for this.
The very last line - SLAVE START; should be START SLAVE;
what happens when my DB expands and the droplet runs out of storage space?
Isn’t there a security threat to replicate a database over IP like this? Can I protect myself from that in some way? And do I need to enable droplet-to-droplet private networking for this to work? And can I have my master on a New York droplet and 2 slaves in Singapore + London?
I’m getting this error in my master when running STATUS\G
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Can anyone help?
I’ve checked for an auto.cnf file as @dbconsultoria mentioned but I that wasn’t in my mysql directory. My servers are running mariadb not MySql… could this have any affect on why it’s not working?
That was a greate tutorial, and everything went fine :) I just had a little problem with the my.cnf that is now /etc/mysql/mysql.conf.d/mysql.conf on ubuntu for some reason. –> Replication works great :-)
But what if i lost my dump-file and want to create a new slave after several weeks of running the replication? How is this process going then?
And: how should the clients handle these databases? Should every client connect to the master (that would be pretty nonsense i think :) ) or should the clients connect to one of these databases?
If a clients is connected to one of the slaves, does the master receive it’s changes too? Or only from master to slave? If so, what would be the best way to connect clients with database replication?
thank you, it is very useful, the step is very clean, and clarity.
hi Ive followed every step in this tutorial but when I make changes in my Master I dont see it reflected in the slave, How can I tell that the replication works?
Hi, thank you for the tutorial. Just as a clarifier, where it says “relay-log”, should it say “relay_log”? Cheers!
Hi, I try this tuts for my master-slave database configuration and it working fine as master slave. I have 3 databases on my server1 like db1, db2 and db3 and I use db1 as master and db1 as slave on another server2 and it works properly. My problem is that I have my restapi running on server1 which uses other two databases db2 and db3 but after setting up my db1 as master api unable to connect or uses my other two data bases and also I’m not able to connect to my server1 databases from my workbench client. How to solve this problem . It is solve by changing bind-address to 127.0.0.1 in my.cnf file but in tuts It is said to use server ip address in bind-address.
This article doesn’t actually say how to copy the exported sql file to the slave server to be imported.
My architecture is a 2 server LAMP with the db being on a dedicated server, and I am looking to replicate the db’s on the master to a new slave.
A few questions - first my Master db edits were in /etc/mysql/mysql.conf.d/mysqld.cnf not /etc/mysql/my.cnf/ I will configure the bind on the private IP.
Before I request an outage and continue my journey - should I be setting up any additional settings relating to innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file (is this mysqld.cnf)? All dbs are innodb
The /etc/mysql/my.cnf file is fairly empty save for a few comments and directory identifiers…
i have been following this tutorial step by step to configure master to master replication on two separate machines running xampp on my local area network, but it’s not working. can you please show me how to step by step configure master to master replication in xampp? my email is: mdwana1@gmail.com
It’d be great to see an example using the newer GTID method, which is apparently recommended, but poorly documented.
I am not able to restart slave instance after adding the line
I am using Docker.
Great Article
can use --master-data flag while doing mysqldump without lock the db in the read mode.
If the MySQL server is for several cpanel servers how do we ensure the cpanel replication is in sync with the database server?
Say we replicate every 30 mins, the data on the server could be up to 29 minutes older?
Hi, if I want to config two or more than slave, how should I do? Thank you.
I report today that still is valid this article: my environment are two virtual machines with Debian 9.8 and MariaDB and all is right, it works very well. Maybe some details for update, starting with the title itself (works with MariaDB too).
Thanks a lot for the info!
DigitalOcean Secure phpMyAdmin on Ubuntu 18.04
MySQL Replicate Data In Real-Time | Access Data Wherever, Whenever
https://github.com/innyzl/MysqlReplica/blob/master/MySQL_Replicate
Be careful of mySQL duplicate UUIDs had to change this for mine to work
Yes, please read:
Storing UUID Values in MySQL Tables by Guilhem Bichot (2015)
Thanks for your attention, have a nice day!
MISTAKE IN THIS LINE :
XXX —> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START;
CORRECT LINE IS:
-------> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;
Very good article! Congrats! It is work fine.
Thank you.
As @jason commented in his #5 comment, please don’t forget to copy the file produced by the mysqldump command in the slave server.
I believe you have a typo in the last statement:
should be
Hi Can I use local MySQL as a slave? I need to copy remote data to local Maybe?
Step 1: Edit the configuration files & start the MySQL Servers. The first step in setting up replication involves editing the “my. … Step 2: Create Replication User. Create an account on the master server that the slave server can use to connect. … Step 3: Initialize Replication. … Step 4: Basic Checks. …
Two things to note in this article:
(log_bin) When commented out, as this directive is by default, binary logging is disabled.Binary logging is enabled by default even when log_bin is commented and you don’t need log_bin directive for replication.To make sure that no users change any data while you retrieve the coordinates, which could lead to problems, you’ll need to lock the database to prevent any clients from reading or writing data as you obtain the coordinates.When tables are locked clients can read, but not write, so there is no prevention from reading.In step 4 “If Your Source Doesn’t Have Any Existing Data to Migrate”. Before following to step 5, in this step if you will create “db” database at source, you need to create “db” database at replica too.
In step 4 “If Your Source Doesn’t Have Any Existing Data to Migrate”. Before following to step 5, in this step if you will create “db” database at source, you need to create “db” database at replica too.
great article