Edit the my. Share this: Email Print Tweet. Leave a Reply Cancel reply Your email address will not be published. Remember Me Forgot Password? Sorry, your blog cannot share posts by email. Link Text. Open link in a new tab. No search term specified. 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. In a blog post published in July of , 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. 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. Lastly, this tutorial includes optional instructions on how to migrate data in an existing database from the source to the replica.
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 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.
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. Binary log file position-based replication is viable for many use cases, but this method can become clunky in more complex setups. 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.
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. 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. You can do this by running a command like the following on your source server. In order for your source MySQL database to begin replicating data, you need to make a few changes to its configuration. On Ubuntu Open this file on the source server with your preferred text editor.
Within the file, find the bind-address directive. It will look like this by default:. Replace 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:. Uncomment this line by removing the pound sign. To keep things simple the following example leaves this value as the default, 1 :.
When commented out, as this directive is by default, binary logging is disabled. After doing so, it will look like this:. This tutorial will continue on with replicating only a single database, but if you wanted to replicate more it might look like this:.
After making these changes, save and close the file. 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. For example, the binary log file would look like mysql-bin.
Record these values. Note: If the master has been running previously without binary logging enabled, the log file name and position values will be empty. Once the data dump has been completed, you can then import this data into the slave before starting the replication process. This dumps all databases to a file named dbdump. Dump without locking tables will not result in downtime.
Once a slave is replicating, you can find in its data directory one file named master. Do not remove or edit these files unless you know exactly what you are doing and fully understand the implications. The slave will use the values specified in the statement to update the status files automatically. A good configuration should look like this. Unfortunately this entire process is a bit convoluted, as I ran into some random issues along the way.
Usually a simple Google search of your problem will help, however, you should be maintaining backups of all servers and databases just in case anything goes wrong. Ryan Murphy.
0コメント