1. In the primary database server, add a user with access to the main library from the server:
GRANT REPLICATION SLAVE on *.* to ' test ' @ '% ' identified by ' test ';
(% to allow all IP, can set specified from server IP)
After adding a user:
Mysql-h127.0.0.1-utest-ptest can be used on the server; To test whether you have access to the primary database
2. In the main repository configuration file plus:
#master Config
Server-id = 1
Log-bin = Mysql-bin
3. In the database configuration file from the server:
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 are entering a command from the server: show slave status\g
Normal Condition: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 primary database to test, whether from the library synchronized wood ...
Note: Please check the firewall !!!
In fact, the configuration process is easy, not too excited, we spend some time to understand the principle of master and slave.
The following content should have some effect on your understanding.
a MySQL copy of the basic process is as follows :(each part of learning from Google, thank you)
1. The IO line above the Slave Chenglian connect to Master and requests the contents of the log after the specified location of the log file (or from the first log);
2. Master receives the request from the IO thread from Slave, and returns the IO thread to the Slave end by reading the log information after the specified log specified location based on the request information from the IO thread responsible for the replication. In addition to the information contained in the log, the return information includes the name of the Binary log file for the returned information at the Master end and the location in the Binarylog;
3. When the Slave IO thread receives the information, it writes the received log contents to the end of the Slave end Relaylog file (mysql-relay-lin.xxxxxx), and records the Bin-log file name and location of the master end read to Master-info file so that the next time you read it, you will be able to clear the high speed master "I need to start from somewhere in the Bin-log, please send me the log."
4. Slave's SQL thread detects the new additions to the Relay log and immediately parses the contents of the log file into the Master
The executable query statements that are executed at the end of the actual execution and execute these queries on their own. So, it's actually in the Master and Slave
The same Query is executed on the end, so the data at both ends are exactly the same.
Second, set the advantages of MySQL master-slave configuration:
1, to solve the Web application system, database performance bottlenecks, using the way of database clustering to achieve query load; In a system, the query operation of the database is much more than the update operation, and the query server of the database is divided into different query servers to improve the query efficiency.
2, MySQL database to support the master-slave copy function of the database, the use of the main database for data insertion, deletion and update operations, and from the database is dedicated to data query operations, so that the update operation and query operations can be shared into different databases, thus improving the efficiency of the query.
The second master principle is as follows:
1.
The master 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 sent to the server. When a primary server is connected from a server, it notifies the primary server of the location of the last successful update that was read from the server in the log. Receive any updates from the server that have occurred since then, and then block and wait for the primary server to notify the new updates.
MySQL replication tracks all changes to the database (update, delete, 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 receive from the server from the primary server has been logged to the saved update of the binary log so that the same update can be performed on its data copy from the server.
After the server is set up to replicate the primary server's data, it connects to the primary server and waits for the update process. If the primary server fails or loses a connection to the primary server from the server, maintain a regular attempt to connect from the server until it can continue the frame-listening update. The retry interval is controlled by the--master-connect-retry option. The default is 60 seconds.
Each track replication time from the server. The primary server does not know how many have been updated from the server or in a moment.
2.
related files for master-slave synchronization process
By default, the relay log uses the file name in host_name-relay-bin.nnnnnn form, where host_name is from the server host name and nnnnnn is the serial number. Use sequential serial numbers to create continuous relay log files, starting at 000001. The relay log that is currently in use in the index file is tracked from the server. The default relay log index file name is Host_name-relay-bin.index. By default, these files are created from the server's data directory. You can overwrite the default filename with the--relay-log and--relay-log-index server options
The relay log is the same format as the binary log and can be read with Mysqlbinlog. Once the SQL thread finishes all the events in the relay log and then automatically deletes it after it is no longer needed. There is no mechanism to delete the relay log directly because the SQL thread can be responsible for completing it. However, FLUSH logs can loop through the log, which is affected when the SQL thread deletes the log.
The secondary replication server creates two additional small files in the data directory. The default names for these state files are the main 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, "To control SQL statements from the server"). The status file is saved on the hard disk and will not be lost when it is shut down from the server. 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 to handle your own relay logs.
The Master.info file is updated by the I/O thread. The rows in the file correspond to the columns shown in Show SLAVE status:
Copy Code code as follows:
Row description
1 The line number 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
One 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 rows in the file correspond to the columns shown in Show SLAVE status:
Copy Code code as follows:
Row 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 rows in the file correspond to the columns shown in Show SLAVE status:
Copy Code code as follows:
Row description
1 The line number 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
One 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 rows in the file correspond to the columns shown in Show SLAVE status:
Copy Code code as follows:
Row 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 both small files and relay log files. They are used to resume replication after recovering data from the server. If you lose the relay log but still have the Relay-log.info file, you can check the file to determine the extent to which the SQL thread has executed the binary log on the primary server. 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 log is required to remain on the primary server.
If you are copying the LOAD DATA infile statement from the server, you should also back up any sql_load-* files in the directory that are used for that purpose from the server. These files are required from the server to continue replicating 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.
Five: The beginning of the master-slave synchronization instructions
The Master.info content overrides 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 option file or the command line. Occurs when the server is first started as a server, or when reset slave has been run and then turned off and restarted from the server.
If the Master.info file exists from the server startup, the server ignores those options. Use the value found in the Master.info file.
If you restart from the server using a different value for the startup option corresponding to the Master.info file, the different values of the boot option will 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 reboot from the server, or (preferably) reset the value using the change MASTER to statement when running from the server.
VI: How to make sure all the statements from the relay log have been processed from the server
On each from the server, issue a stop SLAVE io_thread statement, and then check the output of the show Processlist statement until you see has read all relay log. When all these are done from the server, they can be reconfigured to a new setting. On the server S1 that is promoted as the primary server, the stop slave and reset master statements are issued.
Seven: If you are sure you can jump over the next statement of the autonomic server, you can execute the following statement
Copy Code code 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 in the my.cnf file configuration.
Typically, updates received from the server from the primary server are not recorded in its binary log. This option tells the update from the server that its SQL thread executes to the binary log from the server itself. 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:
A-> B-> C
In other words, A is the primary server from Server B, and B is the primary server from server C. In order to work, B must be both a primary server and a server. You must start A and B with--logs-bin to enable binary logging and start B with the--logs-slave-updates option.
2): · --slave-skip-errors=[err_code1,err_code2,... | all]
This is the option when MySQL starts
Typically, replication stops when an error occurs, which gives you a chance to manually resolve inconsistencies in your data. This option tells you to continue copying from the server SQL thread when the statement returns the errors listed in any of the option values.
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 itself does not have a bug, there should be no stop replication errors. Misuse of this option can cause synchronization from the server to the primary server, and you cannot find the reason.
For error codes, you should use the number provided from the error message in the server error log and the output of show SLAVE status. The server error code is listed in Appendix B: Error codes and messages.
You can (but should not) use the not recommended all value to ignore all error messages, regardless of the errors that occur. Needless to point out, if you use this value, we cannot guarantee the integrity of the data. In this case, do not complain (or write bug reports) if the data from the server is not similar to the primary server. You have been warned.
For example:
Copy Code code 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 does not want to stop the primary server, how to configure a from the server?
A: There are a number of ways. If you have done a primary server backup at a point in time and recorded the binary log name and offset of the corresponding snapshot (through the show MASTER Status Command's output), take the following steps:
Copy Code code as follows:
1. Ensure that a unique server ID number is assigned from the server.
2. Execute the following statement from the server and 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. Executes the start slave statement from the server.
If you don't have a backup home server, here is a quick program to create a backup. All steps should be performed on the primary server host.
The following is a reference fragment:
1. Issue the statement:
Mysql> FLUSH TABLES with READ LOCK;
2. Execute the command (or its variant) when the lock is still added:
Shell> Tar Zcf/tmp/backup.tar.gz/var/lib/mysql
3. Issue the statement and ensure that the subsequent output is recorded:
Mysql>show MASTER STATUS;
4. Release Lock:
Mysql> UNLOCK TABLES;
An alternative approach is to dump the primary server's SQL instead of the binary replication in the previous step. To do this, you can use mysqldump--master-data on the primary server and later load the SQL dump to your from server. However, this is slower than the binary replication speed.
Whether you use one of these two methods, 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 from the server. Once you have a snapshot of the primary server, you can wait to create one from the server, as long as the master server's binary log is complete. Two time to wait the actual limit is the time it takes to save the free hard disk space of the binary log on the primary server and synchronize from the server.
You can also use the load DATA from MASTER. This is a handy statement that transmits a snapshot to the server and adjusts the log name and offset immediately. In the future, the LOAD DATA from master becomes the recommended way to create a server. Note, however, that it works only on MyISAM tables and may hold read locks for long periods of time. It is not implemented as efficiently as we would like it to be. If you have a large table and execute flush tables with READ lock statements, then the preferred method is still to make a binary snapshot on the primary server.
2 Q: Is it necessary to always connect to the primary server from the server?
A: No, you don't. From the server can be down or disconnected for several hours or even days, reconnect and get updated information. For example, you can set up a master server/server relationship on a dial-up link, which is only occasionally connected over a short period of time. This means that at any given time, the server is not guaranteed to sync with the primary server unless you perform certain special methods. In the future, we will use options to block the primary server until there is a sync from the server.
When backing up data from the server, you should also back up both small files and relay log files. They are used to resume replication after recovering data from the server. If you lose the relay log but still have the Relay-log.info file, you can check the file to determine the extent to which the SQL thread has executed the binary log on the primary server. 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 log is required to remain on the primary server.
If you are copying the LOAD DATA infile statement from the server, you should also back up any sql_load-* files in the directory that are used for that purpose from the server. These files are required from the server to continue replicating 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's Replication is an asynchronous replication process, copied from a MySQL instace (called Master) to another MySQL instance (we call it Slave). The implementation of the entire replication process between Master and Slave is done primarily by three threads, where two threads (SQL threads and IO threads) are on the Slave side and another thread (IO thread) is on the master side.
To achieve MySQL's Replication, you must first turn on the Binarylog (mysql-bin.xxxxxx) feature on the Master side, otherwise it will not be implemented. Because the entire replication process is actually the slave from the master to get the log and then in the full order of their own execution log of the various operations recorded. Open MySQL's Binary Log to add "mysqld" by using the "-log-bin" parameter option during the startup of MySQL Server, or in the MY.CNF configuration file mysqld parameter group ([Log-bin] The parameters section of the identity) The parameter item.
