MySQL Master-Slave architecture configuration detailed

Original http://www.codeceo.com/article/mysql-master-slave.html

Regardless of the database, the security of the data is critical, so mastering the secure backup capabilities of the database is an essential skill for developers, especially back-end developers. MySQL database built-in replication capabilities, can help us to do offsite backup, read and write separation, to a large extent to avoid data loss, the database server pressure is too large or the loss caused by downtime.

Using MySQL master-slave architecture for more than a year, think of the time when learning these things, suffering from a complete Chinese literature is less, then English is not very good, encountered many problems. Just recently there has been a period of time not updated blog, whim, decided to translate the MySQL official website of the English document, the official website of the document explained in very detailed, can help more novice understanding and quick start.

The first translation of such a large number of English technical documents, as far as possible to take a step-by-step translation, so as to maintain the original document. There are many places that need to be changed, and I implore you to correct me.

That's a lot of crap, start here ~ ~ ~

Overview

The MySQL replication feature allows data to be copied from one MySQL database server (which we call the main library, i.e. master ) to another or multiple MySQL database servers (we call it from the library, i.e. slave ). By default, the replication process is asynchronous, so you do not have to connect to the primary database server to receive updates from the database server. This also means that updates can be connected over long distances, even in the case of temporary or intermittent dialing services. The MySQL replication feature can replicate all databases, or several databases that need to be replicated, or even database tables that need to be replicated in the database, depending on how you configure them.

Configuring the Configuration Master Library

The primary library that needs to be replicated must have the binary logging feature turned on, and a unique server number () must be created, server-id after which the database has to be restarted.

Because the binary log files are the basis of master-slave replication, the main library must turn on the binary logging feature. If the binary logging feature is log-bin not enabled using the option, master-slave replication will not work.

You can use all the libraries as a group, each from the library need to create a unique server number ( server-id ), in order to use this number in the group to identify the specified from the library, the database server number ( server-id ) must be between 1 to 232−1 Integer. It is up to you to decide which integers to use, and there are no other special rules.

To configure the server number () option for the database, you server-id need to stop the MySQL database and then edit my.cnf or my.ini file. In this section of the configuration file [mysqld] , add log-bin and server-id two options. If they already exist, but are commented out, make a comment and then modify it to suit your needs. For example, using the prefix as the mysql-bin log file name, enable the binary logging feature, configure the server number as 1 , you can refer to the following example:

[Mysqld] log-bin=mysql-binserver-id=1  

After saving the above changes, restart the database.

Attention:

• If there is no configuration server-id option (or set it to the default value of 0), the main library rejects any connection requests from the library.
• In order to be able to use transactions in the InnoDB storage engine, the maximum possible durability and consistency is achieved, my.cnf innodb_flush_log_at_trx_commit=1 and sync_binlog=1 two options are required in the file configuration.
• Do not configure options on the main library skip-networking . If the main library's network is disabled, the library cannot connect to the main library, resulting in replication failure.

Configure from Library

Before configuring the main library, it has been said that each server number must be created from the library, and the database must be restarted after it has been created.

If the server number from the library is not set, or the number set is conflicting with the main library, you will need to first stop from the library and then edit this section from the library's configuration file, [mysqld] specifying a unique server number, as follows:

[Mysqld] server-id=2 

After saving the changes, restart from the library.

If multiple slave libraries are configured, each slave library must have a unique server-id option value (that is, each value from the library server-id must be different from the main library and other values from the library other than itself server-id ).

Attention:
If you do not configure an option from the Library server-id (or configure its value as the default value of 0), the connection to the main library will be denied from the library.

You do not need to turn on binary logging for master-slave replication from the library. If you turn on binary logging from the library, the resulting binary log files can be used as backup data, or when the database crashes, use binary log files to recover data, or you can use this to turn on the binary logging feature from the library as part of a more complex master-slave architecture. For example, you can use this from the library as the other main library from the library.

To create a user from a library

Each slave library uses the database user name and password to connect and log in to the main library, so there must be a MySQL account in the main library that allows access from Cullen to this main library. Any REPLICATION SLAVE mysq L account that is granted permission can be used as a connection operation. You can assign an account to each one from the library, or you can have all the same account used from the library to connect to the main library.

Although you do not need to assign a separate account to the library to perform replication, you should be aware that the user name and password used for the copy operation are stored in plaintext in the main library file or table (RELATED Links: section 17.2.2.2, "Slave Status Logs"). Therefore, you may want to create an isolated, copy-only account that minimizes damage to other accounts.

Use CREATE USER the statement to create a new MySQL account. Use the GRANT statement to give the account the permissions necessary to copy the operation. If the account is created only for the purpose of reproduction, then this account is only required REPLICATION SLAVE . For example, create a new account so that repl it can mydomain.com connect to the main library from any host under the domain name and perform replication, executing the following statement on the main library:

' Repl ' @' slavepass ';' Repl ' @'%.mydomain.com ';  

For more information on user accounts, see section 13.7.1, "account Management statements".

Get the binary log coordinates of the main library

You need to know the current binary log coordinates of the main library in order to perform the copy operation in the next configuration from the library so that it can start from the correct location.

If you have already stored some data on the main library and need to synchronize to the slave library before you start, you must stop executing the statement on the main library, then get the current log coordinates of the main library and dump its data. If the main library is not stopped executing the statement, it will cause the data to be dumped and the state information of the database to be inconsistent, resulting in inconsistencies from the library data or caused errors from the library.

Follow these steps to get the binary log coordinates of the main library:

2. Use the command line to connect to the main library to start a session, empty the cache for all tables, and to FLUSH TABLES WITH READ LOCK lock out read-only operations on all tables by performing a write-blocking on the database:

   LOCK

   For InnoDB the storage engine, the FLUSH TABLES WITH READ LOCK statement also blocks the COMMIT operation.

   Remind:
   Do not exit the session where you just executed the FLUSH TABLES statement. If you exit this session, the lock table operation above will be released.

3. Open a new session, connect to the main library, and use theSHOW MASTER STATUSStatement to confirm the name and location of the current binary log file:

   STATUS;

   Results:

   File	Position	binlog_do_db	binlog_ignore_db
   mysql-bin.000003	73	Test	Manual,mysql

   FileThe column shows the name of the current binary log file, and the Position column shows the location of the file. In the above example, the name of the binary log file is mysql-bin.000003 , the location is 73 . This data represents the coordinates of the update that will be processed from the library to the Autonomous library. This data is recorded and will be used later when the configuration is from the library.

   If the main library is already running without first opening the binary log function, the binary SHOW MASTER STATUS log file name and location will be empty by executing the statement, or by executing the mysqldump–master-data command. In that case, when you configure the binary log name and location from the library, the empty string ( ‘‘ ) and numeric values are used 4 instead.

So far, we've got enough information that we need to use this information to configure the binary logs from the library to be read from the library to the correct location from the repository to start copying.

If you have some data that needs to be synchronized to the slave library before starting the copy from the library, keep the previously opened session from shutting down so that the previously opened database lock is not released. Next, view section 17.1.1.5, "Creating a data Snapshot using mysqldump" or section 17.1.1.6, "Creating a data Snapshot using Ra W Data Files ". They will tell you how to prevent any further changes to be made to synchronize data that already exists in the main library to the slave library.

If you are building a new master-slave replication group, you can exit the first session to release read locks on the database.

Configure replication with the new master, slave library

The simplest and most straightforward way to configure database replication with a new master, slave library.

If you are setting up a new server, but have some databases that have been dumped from other servers, and you want to load into your replication configuration new, you can also use the methods described in this section. By loading the data into the new main library, the data is automatically copied to the slave library.

Follow these steps to configure the replication functionality between the new master and slave libraries:

1. Configure the main library with some of the necessary configuration properties files. Reference: Configure the Main library
2. Start the main library.
3. Set up a user. Reference: Create a user from the library.
4. Gets the status information for the main library. Reference: Gets the binary log coordinates of the main library.
5. To release a read lock on the main library:

   TABLES;

6. Edit the MySQL configuration file from the library. Reference: Configure from Library.
7. Start from the library.
8. Executes CHANGE MASTER TO the statement to set the information for the main library. Reference: Information for configuring the master library from the library.

On each of the libraries, complete the steps above.

Because you are using a new database, there is no data or configuration information to replace, so you do not need to copy or import any additional information.

If you need to configure a new replication environment using data from other database servers, you may need to import these dumped data on the new Main library. The resulting updates to the main library will be automatically synced to the slave library:

Mysql-h Master < Fulldb. Dump

Configuring the replication environment with existing data

When you use your existing data to configure your replication environment, you may want to make a decision about how to get the data from the library to the main library in the best way possible before starting the replication service.
The following basic steps will guide you through the configuration of the replicated environment with the data you already have:

2. In the case where the MySQL main library is running, create a user to connect to the main library when copying from the library. Reference: Create a user from the library.
4. If you have not configured the options on the main library, server-id or have not yet turned on the binary logging feature, you will need to stop the main library and configure them. Reference: Configure the Main library.
   In the process of configuring the main library, if you need to stop (restart) The main library service, you can create a snapshot of the main library by taking advantage of the time that the primary library service stopped. But don't forget, you need to get the status information for the main library before you stop the main library service, update the configuration information, and create the snapshot (refer to: Get the binary log coordinates of the main library). For information on how to create a DB snapshot using raw data, you can refer to this section 17.1.1.6, "Creating a data Snapshot using RAW database Files".
6. If the main library information is properly configured, then you can get its status information (refer to: Get the binary log coordinates of the main library), and then use the MySQL-brought mysqldump tool to create a database snapshot (ref.: section 17.1.1.5, "Creating a Data Snapshot using Mysqldump "), you can also refer to section 17.1.1.6," Creating a data Snapshot Using raw data Files "to directly create a snapshot of the primary library's original file.
8. Updates the configuration information from the library. Reference: Configure from Library.
9. The
10. operation of this step depends on the snapshot of the data that you created on the main library.
    If you are using the   mysqldump   Snapshot created:

     

    • using   --skip-slave-start The   option starts from the library so that master-slave replication is not started.
    • Import the dumped data file as follows:

      mysql < fulldb.< Span class= "hljs-built_in" >dump  

    If you are using the second scenario, the way to directly create a snapshot of the original data file:

    • Extract the data file into the data ( data ) directory from the library:

      tar xvf dbdump.tar     

      You may need to set permissions and owners for these files so that you can obtain them from the library and update them.

    • use   --skip-slave-start   option to start from the library so that master-slave replication is not started.
12. Configures the binary log coordinate information for the main library to be from the library. This step is used to specify which binary log files from the main library need to be copied from the library, and from where to start the file. Of course, you also need to configure the credentials and the main library's IP or domain name to use when connecting to the main library from the library. For more CHANGE MASTER TO information about the required parameters for statements, see: To configure the main library from the library.
14. Start from library:

    SLAVE;

After performing these steps, the library will be connected to the main library, and any update operations of the main library will be sent to the slave library, and the library will perform the updates synchronously, based on the existing snapshot data.

If you forget to set options information for the main library server-id , you will not be able to connect to the main library from the library.

If you forget to set options information from the library server-id , the following error message appears in the error log from the library:

Warning:you should set Server-id to a non-0 value if Master_host
is set; We'll force server ID to 2, but the this MySQL server would
Not act as a slave.

Similarly, if you cannot perform replication from the library for any other reason, you can get the associated error message in the error log file.

From the library by using the information stored in the main library information warehouse, keep track of how much of the main library generated binary logs it has executed. With --master-info-repository options, you can set up warehouse information in a form on a file, or in a table. When the configuration information is available --master-info-repository=FILE , you can find two files in the data () directory from the library data , with the names master.info respectively relay-log.info . When the configuration information is in --master-info-repository=TABLE place, the information is saved in the mysql table in the database master_slave_info . In any case, do not delete or edit the data in the file and table mentioned above unless you know exactly what you are doing and fully understand what it means to do so. Even so, you should prioritize the use of CHANGE MASTER TO statements to modify replication parameters. From the library, you can use the values specified in the statement to automatically update the status information file. For more information please refer to section 17.2.2, "Replication Relay and Status Logs".

Attention:
The contents of the main library information warehouse override some of the options set in the command line or my.cnf file. For more information, refer to section 17.1.4, "Replication and Binary Logging Options and Variables".

A snapshot of the main library that can be used by multiple libraries. If you need to configure some additional from the library, you can use the same main library snapshot, as described earlier in how to configure these additional slave libraries as described in how you configured the library.

To configure the main library information from the library

You must tell some necessary information from the library so that the library can connect to the main library for replication. To do this, replace the corresponding option value in the statement, based on the actual value on your system, by executing the following statement from the library:

To master_host=' Master_host_name ', master_user=' Replication_user_name ', master_password=' Replication_ Password ', master_log_file=' recorded_log_file_name ', master_log_pos=recorded_log_position;   

Attention:
Replication cannot use a Unix socket file ( socket file ). You must make sure that you are able to TCP/IP connect to the main library using protocols.

CHANGE MASTER TOThe statement also has some additional parameters to configure. For example, you can use the other options of this statement to decide whether to use the SSL protocol in order to achieve a secure replication.

For information about the complete options for this statement, as well as the maximum allowable length of the option value, see: Section 13.4.2.1, "Change MASTER to Syntax".

MySQL Master-Slave architecture configuration detailed