Interpreting MySQL master-slave configuration and its principle analysis (Master-slave)

Under Windows configuration, the following will be configured under Linux to test, you need to configure the MySQL database synchronization of Friends can refer to.

1. On the primary database server, add a user with permission to access the main library from the server:
GRANT REPLICATION SLAVE on * * to ' test ' @ ' percent ' identified by ' test ';
(% = Allow all IPs, can be set specified from server IP)
After adding a user:
Mysql-h127.0.0.1-utest-ptest can be used on the slave server; To test if you have permission to access the primary database

2. In the main database configuration file plus:
#master Config
Server-id = 1
Log-bin = Mysql-bin

3. In the From server database configuration file:
Server-id = 2
Master-host = 10.0.0.199
Master-user = Test
Master-password = Test
REPLICATE-DO-DB = Test
Master-port = 3306
Log-bin = Mysql-bin

If everything goes well with your configuration,
You enter a command from the server: show slave status\g
Success situation:

Slave_io_running:yes
Slave_sql_running:yes

Enter show master status on the primary server

Well, congratulations, master-slave database configuration OK, you can insert data in the main database to test, whether from the library to synchronize the wood has ...
Note: Check your firewall !!!
In fact, the configuration process is very easy, not too excited, we take a little time to understand its principal and subordinate principle.
The following content should have some effect on your understanding.

the basic process of MySQL replication is as follows :(each part learns from Google, thank you)
1. Slave the above IO line thread attached the Master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;

2. When Master receives a request from an IO thread from Slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to the Slave side. In addition to the information contained in the log, the return information includes the name of the Binary log file on the Master side of the returned information and its location in the Binarylog;
3. After the Slave IO thread receives the information, the received log content is written to the end of the Relaylog file (mysql-relay-lin.xxxxxx) on the Slave side, and the file name and location of the Bin-log read to the master side are recorded to Master-info file, so that the next read can be clear when the high-speed master "I need to start from the location of some bin-log log content, please send me"

4. Slave's SQL thread detects that the contents of the Relay log have been added to the log file immediately after it becomes the Master
The executable query statements at the end of the actual execution and execute these queries on their own. So, it's actually on the Master side and Slave
Side executes the same Query, so the data on both ends is exactly the same.

Second, the advantages of setting up MySQL master-slave configuration:
1, to solve the Web application system, database performance bottleneck, the use of database cluster to implement the query load; a system of database query operation is much more than the update operation, through a number of query servers to share the database query to different query server to improve query efficiency.
2, the MySQL database supports the master-slave replication function of the database, using the main database for data insertion, deletion and update operations, and from the database is specifically used for data query operations, so that the update operation and query operations can be divided into different databases, thereby improving the query efficiency.

The second master-slave principle is as follows:
1.
The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

MySQL replication tracks all changes to the database (updates, deletions, and so on) based on the primary server in the binary log. Therefore, to replicate, you must enable binary logging on the primary server.

Each slave server receives a saved update from the primary server that the primary server has logged to the binary log so that the server can perform the same updates to its copy of the data.

After the server is set up to replicate data from the primary server, it connects to the master server and waits for the update process. If the primary server fails, or if the connection to the primary server is lost from the server, keep trying to connect from the server periodically until it is able to resume the frame listen update. The retry interval is controlled by the--master-connect-retry option. The default is 60 seconds.

Each copy time is tracked from the server. The primary server does not know how many from the server or what has been updated at some point in time.

2. documents related to master-slave synchronization process

By default, the trunk log uses the host_name-relay-bin.nnnnnn form of the file name, where host_name is the hostname from the server, and nnnnnn is the serial number. Create continuous relay log files with sequential serial numbers, starting with 000001. The relay log currently in use in the index file is tracked from the server. The default trunk log index file name is Host_name-relay-bin.index. By default, these files are created in the data directory from the server. You can override the default file name with the--relay-log and--relay-log-index server options

The trunk log is the same format as the binary log and can be read with Mysqlbinlog. When the SQL thread finishes executing all the events in the trunk log and is no longer needed, it is automatically deleted immediately. There is no mechanism to delete the trunk log directly because the SQL thread can be responsible for completion. However, FLUSH logs can cycle through the log, which is affected when the SQL thread deletes the log.

The subordinate replication server creates two additional small files in the data directory. These state files are defaulted to primary master.info and Relay-log.info. They contain the information displayed by the output of the show SLAVE status statement (see section 13.6.2, "SQL statements for controlling from the server"). The state file is saved on the hard disk and is not lost when the server is shut down. The next time you start from the server, read the files to determine how many binary logs it has read from the primary server and how much it handles its own trunk logs.

The Master.info file is updated by the I/O thread. The correspondence between the lines in the file and the columns shown in Show SLAVE status is:

Copy CodeThe code is as follows:
Line Description
1 line numbers in the file
2 Master_log_file
3 Read_master_log_pos
4 Master_host
5 Master_user
6 password (not shown by show SLAVE status)
7 Master_port
8 Connect_retry
9 master_ssl_allowed
Ten Master_ssl_ca_file
Master_ssl_ca_path
Master_ssl_cert
Master_ssl_cipher
Master_ssl_key

The Relay-log.info file is updated by the SQL thread. The correspondence between the lines in the file and the columns shown in Show SLAVE status is:

Copy CodeThe code is as follows:
Line Description
1 relay_log_file
2 Relay_log_pos
3 Relay_master_log_file
4 Exec_master_log_pos

Four: The relationship between the master-slave synchronization process and the MySQL statement
The Master.info file is updated by the I/O thread. The correspondence between the lines in the file and the columns shown in Show SLAVE status is:

Copy CodeThe code is as follows:
Line Description
1 line numbers in the file
2 Master_log_file
3 Read_master_log_pos
4 Master_host
5 Master_user
6 password (not shown by show SLAVE status)
7 Master_port
8 Connect_retry
9 master_ssl_allowed
Ten Master_ssl_ca_file
Master_ssl_ca_path
Master_ssl_cert
Master_ssl_cipher
Master_ssl_key

The Relay-log.info file is updated by the SQL thread. The correspondence between the lines in the file and the columns shown in Show SLAVE status is:

Copy CodeThe code is as follows:
Line Description
1 relay_log_file
2 Relay_log_pos
3 Relay_master_log_file
4 Exec_master_log_pos

When backing up data from the server, you should also back up these two small files as well as the trunk log files. They are used to resume replication after recovering data from the server. If you lose the trunk log but still have the Relay-log.info file, you can determine the extent to which the SQL thread has executed the binary log in the primary server by examining the file. You can then use the Master_log_file and Master_log_pos options to perform change Master to tell the server to re-read the binary log from that point. Of course, the binary logs are required to remain on the primary server.
If the LOAD DATA infile statement is being copied from the server, you should also back up any sql_load-* files within that directory from the server for that purpose. These files are required from the server to continue copying any interrupted load DATA infile operations. Use the--SLAVE-LOAD-TMPDIR option to specify the location of the directory. If not specified, the default value is the value of the Tmpdir variable.
V: Description of the master-slave synchronization starting point
The contents of the Master.info override some of the options specified in the command line or in MY.CNF.
If the Master.info file does not exist when booting from the server, the option takes the value specified in the options file or on the command line. Occurs when the server is first started as a slave server, or the reset slave is already running and then shut down and restarted from the server.
If the Master.info file exists while booting from the server, the server ignores those options. Use the value found in the Master.info file.
If you restart from the server using a different value that corresponds to the startup option in the Master.info file, the different values of the startup option do not take effect because the server continues to use the Master.info file. To use different values for startup options, you must remove the Master.info file and restart from the server, or (preferably) use the change MASTER to statement to reset the value when running from the server.
VI: How to ensure that all the statements in the trunk log have been processed by all slave servers
On each slave server, issue the Stop SLAVE io_thread statement, and then examine the output of the show Processlist statement until you see the have read all relay log. When all of these are done from the server, they can be reconfigured to a new setting. The stop slave and reset master statements are issued on the slave server S1 that is promoted to the primary server.
Seven: If you are sure you can jump over the next statement of the autonomous server, you can execute the following statement

Copy CodeThe code is as follows:
mysql> SET GLOBAL sql_slave_skip_counter = n;
Mysql> START SLAVE;

If the next statement to the autonomic server does not use Auto_increment or last_insert_id (), the n value should be 1. Otherwise, the value should be 2. Statements using Auto_increment or last_insert_id () use the value 2 because they take two events from the primary server's binary log.
Seven: two important options:
1): • --logs-slave-updates
This is configured in the My.cnf file.
Typically, updates received from the server from the primary server are not credited to its binary log. This option tells the server to log updates from its SQL thread into its own binary logs from the server. For this option to take effect, you must also start from the server with the--logs-bin option to enable binary logging. If you want to apply a chained replication server, you should use--logs-slave-updates. For example, you might want to set this up:
C, A-B
That is, a is the primary server from Server B and B is the primary server from server C. To be able to work, B must be both the primary server and the slave server. You must start A and B with--logs-bin to enable the binary log, and start B with the--logs-slave-updates option.
2): • --slave-skip-errors=[err_code1,err_code2,... | all]
This is the option at MySQL startup
Typically, replication stops when an error occurs, giving you a chance to manually resolve inconsistencies in the data. This option tells the SQL thread from the server to continue copying when the statement returns the error listed in any option value.
Do not use this option if you do not fully understand why the error occurred. If there are no bugs in the replication settings and in the client program, and MySQL does not have bugs on its own, there should be no stop replication errors. Abusing this option causes the synchronization to not be saved from the server and the primary server, and you cannot find the reason.
For the error code, you should use the error message from the server error log to provide the number and output of the show SLAVE status. The server error codes are listed in Appendix B: Error codes and messages.
You can also (but should not) use the deprecated all value to ignore all error messages regardless of the error that occurred. Needless to do, we cannot guarantee the integrity of the data if this value is used. In this case, do not complain (or write a bug report) if the data from the server is not close to the primary server. You've been warned.
For example:

Copy CodeThe code is as follows:
--slave-skip-errors=1062,1053
--slave-skip-errors=all

8:2 useful questions and answers:
1) Q: If the primary server is running and you do not want to stop the primary server, how do I configure a slave server?
A: There are several ways. If you have made a primary server backup at some point and recorded the binary log name and offset of the corresponding snapshot (through the output of the show MASTER Status command), take the following steps:

Copy CodeThe code is as follows:
1. Ensure that a unique server ID number is assigned from the server.
2. In the Execute the following statement from the server, fill in the appropriate values for each option:
Mysql> Change MASTER to
Master_host= ' Master_host_name ',
Master_user= ' Master_user_name ',
Master_password= ' Master_pass ',
Master_log_file= ' Recorded_log_file_name ',
Master_log_pos=recorded_log_position;

3. Execute the start slave statement from the server.
If you do not have a backup home server, here is a quick program to create a backup. All steps should be performed on the master server host.

The following is a reference fragment:
1. Issue the statement:
Mysql> FLUSH TABLES with READ LOCK;
2. When still locked, execute the command (or its variant):
Shell> Tar Zcf/tmp/backup.tar.gz/var/lib/mysql
3. Issue the statement and make sure to record the output that is used later:
Mysql>show MASTER STATUS;
4. Release the Lock:
Mysql> UNLOCK TABLES;
An alternative approach is to dump the primary server's SQL instead of the binary copy in the previous step. To do this, you can use mysqldump--master-data on the primary server, and later mount the SQL dump to your slave server. However, this is slower than binary replication.
Regardless of which of these two methods you use, when you have a snapshot and record the log name and offset, then follow the instructions. You can use the same snapshot to build multiple slave servers. Once you have a snapshot of the primary server, you can wait to create one from the server as long as the primary server's binary log is complete. Two time to wait the actual limit is the amount of free hard disk space to save the binary log on the primary server and the time it takes to synchronize from the server.
You can also use the load DATA from MASTER. This is a handy statement that transmits a snapshot to the slave server and adjusts the log name and offset immediately. In the future, the LOAD DATA from master will be the recommended method for creating a Slave server. However, it is important to note that it only works on MyISAM tables and can hold read locks for long periods of time. It is not carried out as efficiently as we would like it to be. If you have a large table, after you execute the flush TABLES with READ lock statement, the preferred method is still to make a binary snapshot on the primary server.
2) Q: Do I need to always connect to the master server from the server?
A: No, not required. From the server can be down or disconnected for several hours or even days, after reconnecting to get updated information. For example, you can set up a master server/slave server relationship on a dial-up link, where only occasionally a short period of time is connected. This means that at any given time, from the server is not guaranteed to synchronize with the primary server unless you perform certain special methods. In the future, we will use the option to block the master server until there is a synchronization from the server.

When backing up data from the server, you should also back up these two small files as well as the trunk log files. They are used to resume replication after recovering data from the server. If you lose the trunk log but still have the Relay-log.info file, you can determine the extent to which the SQL thread has executed the binary log in the primary server by examining the file. You can then use the Master_log_file and Master_log_pos options to perform change Master to tell the server to re-read the binary log from that point. Of course, the binary logs are required to remain on the primary server.
If the LOAD DATA infile statement is being copied from the server, you should also back up any sql_load-* files within that directory from the server for that purpose. These files are required from the server to continue copying any interrupted load DATA infile operations. Use the--SLAVE-LOAD-TMPDIR option to specify the location of the directory. If not specified, the default value is the value of the TMPDIR variable

MySQL Replication is an asynchronous copy process, copied from one MySQL instace (we call Master) to another MySQL instance (we call it Slave). The entire replication process between Master and Slave is done primarily by three threads, of which two threads (SQL thread and IO thread) are on the Slave side, and another thread (IO thread) on the master side.

To implement MySQL Replication, you must first turn on the Binarylog (mysql-bin.xxxxxx) function on the Master side, otherwise it will not be possible. Because the entire replication process is actually a variety of operations that are logged in the execution log that slave obtains the log from the master side and then executes it in its own full sequence. Open the Binary Log of MySQL by using the "-log-bin" parameter option during the start of MySQL Server, or by adding "Log-bin" to the MYSQLD parameter group in the MY.CNF configuration file (the Parameters section after [mysqld] is identified) The parameter item.

Interpreting MySQL master-slave configuration and its principle analysis (Master-slave)