Interpretation of mysql Master-Slave configuration and Its Principle Analysis (Master-Slave)

1. Add a user with the permission to access the master database on the master database server:
Grant replication slave on *. * TO 'test' @ '%' identified by 'test ';
(% Indicates that all IP addresses are allowed. You can specify the slave server IP address)
After adding a user:
You can use mysql-h127.0.0.1-utest-ptest; on the slave server to test whether you have the permission to access the master database.


2. Add:
# Master config
Server-id = 1
Log-bin = mysql-bin

3. In the slave 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 all your configurations are successful
On the slave server, enter the command: show slave status \ G.
Normal condition: Slave_IO_Running: yes
Slave_ SQL _Running: yes




Enter show master status on the master server



Congratulations! The Master/Slave database configuration is OK. you can insert data into the master database for testing. Are there any data synchronized from the slave database...
Note: Check the firewall.!!!
In fact, the configuration process is very easy and simple. Don't be too excited. Let's take some time to understand its master-slave principle.
The following content is helpful to your understanding.

I. the basic process of MySQL replication is as follows: (learn from Google in each part. Thank you)
1. The IO thread on the Slave connects to the Master, and requests the log content after the specified location of the specified log file (or from the beginning of the log;

2. After the Master receives a request from the Slave IO thread, it reads the log information at the specified location of the specified Log Based on the Request Information and returns it to the Slave IO thread. Besides the information contained in the Log, the returned information also includes the name of the Binary Log file on the Master end and the position of the Binary Log file in the BinaryLog;
3. after the Slave IO thread receives the information, it writes the received log content to the end of the RelayLog file (mysql-relay-lin.xxxxxx) at the Slave end in sequence, and record the file name and location of the bin-log on the Master end to the master-info file, so that the next read can clearly show the High-Speed Master "I need to start from the location of a bin-log, please send it to me"

4. After the Slave SQL thread detects that the Relay Log has added a new content, it will immediately parse the content in the Log file to become
Execute the executable Query statements when the end is actually executing, and execute these queries on its own. In this way, it is actually on the Master side and Slave
The data at both ends of the Query is identical.

Ii. Advantages of configuring mysql Master/Slave:
1. Solve the performance bottleneck of the web application system and database, and use the database cluster method to achieve query load. In a system, the database query operation is much more than the update operation, multiple query servers are used to distribute database queries to different query servers, thus improving query efficiency.
2. the Mysql database supports the master-slave replication function of the database. The master database is used for data insertion, deletion, and update operations, while the slave database is used for data query operations, in this way, the update and query operations can be shared to different databases, thus improving the query efficiency.

The master-slave principle is as follows:
1.
The master server writes updates to the binary log file and maintains an index of the file to track log loops. These logs can record updates sent to the slave server. When a slave server connects to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of new updates.

MySQL replication tracks all database changes (updates, deletions, and so on) in binary logs based on the master server ). Therefore, binary logs must be enabled on the master server for replication.

Each slave server receives updates recorded by the master server to the storage of its binary logs from the master server so that the slave server can perform the same update on its data copy.

After the slave server is set to copy the data of the master server, it connects to the master server and waits for the update process. If the master server fails or the slave server loses its connection with the master server, the slave server keeps trying to connect regularly until it can continue frame listening updates. The -- master-connect-retry option controls the retry Interval. The default value is 60 seconds.

The replication time of each slave server. The master server does not know how many slave servers or what has been updated at a certain moment.

2. Files related to the master-slave synchronization process

By default, relay logs use host_name-relay-bin.nnnnnn-style file names where host_name is the slave server host name and nnnnnnnn is the sequential column number. Create a continuous relay log file with a continuous serial number, starting from 000001. Track the currently used relay logs in the index file from the server. The default relay log index file name is the host_name-relay-bin.index. By default, these files are created in the data directory of the server. You can use the -- relay-log and -- relay-log-index Server options to overwrite the default file name.

Relay logs are in the same format as binary logs and can be read using mysqlbinlog. After the SQL thread executes all the events in the relay log and no longer needs them, it will be automatically deleted immediately. There is no direct mechanism to delete relay logs because the SQL thread can complete the process. However, flush logs can relay LOGS cyclically, which may be affected when the SQL thread deletes LOGS.

The slave replication server creates two other small files in the data directory. These status files are named master.info and relay-log.info by default. They contain the information displayed by the output of the show slave status Statement (for the description of this statement, see section 13.6.2 "SQL statements used to control SLAVE servers "). Status files are stored on the hard disk and will not be lost when the slave server is closed. When starting the next time from the server, read these files to determine how many binary logs it has read from the master server and how much it will process its own relay logs.

The master.info file is updated by the I/O thread. The correspondence between the lines in the file and the columns displayed by show slave status is as follows:
Copy codeThe Code is as follows:
Line description
1. the row number in the file
2 Master_Log_File
3 Read_Master_Log_Pos
4 Master_Host
5 Master_User
6. Password (not displayed by show slave status)
7 Master_Port
8 Connect_Retry
9 Master_SSL_Allowed
10 Master_SSL_CA_File
11 Master_SSL_CA_Path
12 Master_SSL_Cert
13 Master_SSL_Cipher
14 Master_SSL_Key

Update the relay-log.info file by the SQL thread. The correspondence between the lines in the file and the columns displayed by show slave status is as follows:
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

Iv. Relationship between files related to the master-slave synchronization process and MySQL statements
The master.info file is updated by the I/O thread. The correspondence between the lines in the file and the columns displayed by show slave status is as follows:
Copy codeThe Code is as follows:
Line description
1. the row number in the file
2 Master_Log_File
3 Read_Master_Log_Pos
4 Master_Host
5 Master_User
6. Password (not displayed by show slave status)
7 Master_Port
8 Connect_Retry
9 Master_SSL_Allowed
10 Master_SSL_CA_File
11 Master_SSL_CA_Path
12 Master_SSL_Cert
13 Master_SSL_Cipher
14 Master_SSL_Key

Update the relay-log.info file by the SQL thread. The correspondence between the lines in the file and the columns displayed by show slave status is as follows:
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 the two small files and relay log files. They are used to continue copying data from the server. If a relay log is lost but there is still a relay-log.info file, you can check the file to determine the extent to which the SQL thread has executed binary logs in the master server. You can then use the Master_Log_File and Master_LOG_POS options TO execute the change master to command the slave server TO re-read binary logs from this point. Of course, binary logs must still be stored on the master server.
If the slave server is copying the load data infile statement, you should also back up any SQL _LOAD-* file used by the slave server for this purpose in the directory. The slave server needs these files to continue copying any interrupted load data infile operations. Use the -- slave-load-tmpdir option to specify the directory location. If not specified, the default value is the value of the tmpdir variable.
V. Description of the start point of master-slave Synchronization
The content of master.info overwrites some of the options specified in the command line or in my. cnf.
If the master.info file does not exist when the slave server is started, the option uses the value specified in the option file or command line. When the server is started as a SLAVE server for the first time, or the SLAVE server has been shut down and restarted after the reset slave is run.
If the master.info file exists when the slave server is started, the server ignores those options. Use the value found in the master.info file.
If you use different values of the startup options corresponding to the master.info file to restart the slave server, the different values of the startup options will not take effect because the server continues to use the master.info file. TO use different values of the startup option, you must delete the master.info file and restart the slave server, or (preferably) use the change master to statement TO reset the value when running the slave server.
6. How to ensure that all slave servers have processed all the statements in the relay log
On each SLAVE server, issue the stop slave IO_THREAD statement and check the output of the show processlist statement until you see the Has read all relay log. When all slave servers perform these operations, they can be reconfigured as a new setting. Issue the stop slave and reset master statements on the SLAVE server S1 promoted to the MASTER server.
7. If you are sure you can jump to 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 on the autonomous server does not use AUTO_INCREMENT or LAST_INSERT_ID (), the value of n should be 1. Otherwise, the value should be 2. The reason why the AUTO_INCREMENT or LAST_INSERT_ID () Statements use value 2 is that they take two events from the binary log of the master server.
Seven: two important options:
1): · -- logs-slave-updates
This is configured in the my. cnf file.
Generally, the updates received by the slave server from the master server are not recorded in its binary log. This option tells the slave server to log the updates executed by its SQL thread to the slave server's own binary log. To make this option take effect, you must also use the -- logs-bin option to start the slave server to enable binary logs. To apply the chain replication server, use -- logs-slave-updates. For example, you may want to set it as follows:
A-> B-> C
That is to say, A is the master server of slave server B and B is the master server of slave server C. To work, B must be both the master server and slave server. You must use -- logs-bin to start A and B to enable binary logs, and use the -- logs-slave-updates option to start B.
2): · -- slave-skip-errors = [err_code1, err_code2,... | all]
This is the option at mysql startup.
Generally, when an error occurs, the replication stops. This gives you a chance to manually solve the inconsistency problem in the data. This option tells the SQL thread of the slave server to continue copying when the statement returns an error in any option value.
If you cannot fully understand why an error occurs, do not use this option. If there are no bugs in the replication settings and client programs, and MySQL itself does not have any bugs, there should be no error of stopping replication. Misuse of this option will make the slave server and the master server unable to save and synchronize, and you cannot find the reason.
For the error code, you should use the number provided by the error message from the server error log and the output of show slave status. The server error code is listed in Appendix B: Error code and message.
You can (but should not) Ignore all error messages with the unrecommended all value, regardless of the error. If this value is used, data integrity cannot be guaranteed. In this case, do not complain (or write a bug report) if the data on the slave server is not similar to that on the master server ). You have been warned.
For example:
Copy codeThe Code is as follows:
-- Slave-skip-errors = Route 2, 1053
-- Slave-skip-errors = all

8. Two useful questions and answers:
1) Q: If the master server is running and you do not want to stop it, how can I configure an slave server?
A: There are multiple methods. If you have backed up the MASTER server at a certain time point and recorded the binary log name and offset of the corresponding snapshot (output using the show master status Command), follow these steps:
Copy codeThe Code is as follows:
1. Make sure that a unique server ID is assigned to the slave server.
2. Execute the following statement on the slave server and fill in the appropriate values for each option:
Mysql> CHANGE MASTER
-> 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 on the SLAVE server.
If you have not backed up the master server, here is a fast program for creating backups. All steps should be performed on the master server host.

The following is a reference clip:
1. Issue the statement:
Mysql> flush tables with read lock;
2. Execute this command (or its variant) when the lock is still applied ):
Shell> tar zcf/tmp/backup.tar.gz/var/lib/mysql
3. Issue the statement and ensure that the output used later is recorded:
Mysql> show master status;
4. Release the lock:
Mysql> unlock tables;
An optional method is to dump the master server's SQL statement instead of the binary copy in the previous step. To do this, you can use mysqldump -- master-data on the master server and load the SQL to your slave server. However, this is slower than binary replication.
No matter which one of the 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 create multiple slave servers. Once you have a snapshot of the master server, you can wait to create an slave server, as long as the binary log of the master server is complete. The actual limits on the two waiting times refer to the available hard disk space for storing binary logs on the master server and the time used for synchronization from the slave server.
You can also use load data from master. This is a convenient statement that transfers a snapshot to the slave server and immediately adjusts the log name and offset. In the future, load data from master will be a recommended method for creating slave servers. However, it only works on MyISAM tables and may hold read locks for a long time. It is not executed as efficiently as we hope. If you have a large table and run the flush tables with read lock statement, the preferred method is to create a binary snapshot on the master server.
2) Q: Do I need to always connect to the master server?
A: No, no. The slave server can be down or disconnected for several hours or even several days. After the slave server is reconnected, the new information is obtained. For example, you can set the relationship between the master server and slave server on the dial-up link, which only occasionally makes connections within a short period of time. This means that at any given time, the slave server cannot guarantee synchronization with the master server unless you execute some special methods. In the future, we will use the option to block the master server until there is a slave server synchronization.

When backing up data from the server, you should also back up the two small files and relay log files. They are used to continue copying data from the server. If a relay log is lost but there is still a relay-log.info file, you can check the file to determine the extent to which the SQL thread has executed binary logs in the master server. You can then use the Master_Log_File and Master_LOG_POS options TO execute the change master to command the slave server TO re-read binary logs from this point. Of course, binary logs must still be stored on the master server.
If the slave server is copying the load data infile statement, you should also back up any SQL _LOAD-* file used by the slave server for this purpose in the directory. The slave server needs these files to continue copying any interrupted load data infile operations. Use the -- slave-load-tmpdir option to specify the directory location. If not specified, the default value is the value of the tmpdir variable.

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

To implement Replication for MySQL, you must first enable the BinaryLog (mysql-bin.xxxxxx) function on the Master side, otherwise it cannot be implemented. The whole replication process is actually because Slave obtains the log from the Master end and then executes the operations recorded in the log in full order on itself. To enable the Binary Log of MySQL, you can use the "-log-bin" parameter option during MySQL Server startup, or. the "log-bin" parameter item is added to the mysqld parameter group (the parameter section marked by [mysqld]) in the cnf configuration file.