The principle and configuration method of Mysql master-Slave Copying (more detailed) _mysql

First, the principle of reproduction

MySQL replication tracks all changes to the database (update, delete, and so on) based on the primary server in the binary log. 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.

One way to copy the data from the primary server to the server is to use the load data from master statement. Note that the load DATA from master currently works only on the primary server using the MyISAM storage engine for all tables. Also, the statement will get a global read lock.

MySQL uses 3 threads to perform the replication function, where 1 are on the primary server and the other two on the server. When the start slave is issued, an I/O thread is created from the server to connect to the primary server and have it send a statement that is recorded in the binary log.
The primary server creates a thread that sends the contents of the binary log to the server. The thread can identify the Binlog dump thread in the output from show Processlist on the primary server.
Reads the contents of the primary server Binlog dump thread from the server I/O thread and copies the data to a local file from the server data directory, that is, the relay log.
The 3rd thread is the SQL thread that is created from the server to read the relay log and perform the updates contained in the log.
There are multiple primary servers from the server to create a thread for each current connection from the server, each with its own I/O and SQL thread.

Second, the status of the replication thread

1. Copy the state of the main thread

Copy Code code as follows:

Sending Binlog event to Slave
Binary logs consist of various events, and an event usually adds some additional information to an update. The thread has read an event from the binary log and is sending it to the server.
Finished reading one binlog; Switching to next Binlog
The thread has read the binary log file and is opening the next log file to be sent to the server.
has sent all binlog to slave; Waiting for Binlog to is updated
The thread has read all the major updates from the binary log and has been sent to the server. The thread is now idle, waiting for a new event to appear in the binary log caused by a new update on the primary server.
Waiting to finalize termination
A very simple state that occurs when a thread stops.

2. Copying from I/O thread state

Copy Code code as follows:

Connecting to Master
The line one thread attempted to connect to the primary server.

Checking Master Version
A status that appears temporarily immediately after the connection to the primary server is established.

Registering slave on Master
A status that appears temporarily immediately after the connection to the primary server is established.

Requesting Binlog Dump
A status that appears temporarily immediately after the connection to the primary server is established. The thread sends a request to the primary server for the contents of the binary log starting from the requested binary log file name and location.

Waiting to reconnect after a failed Binlog dump request
If the binary log dump request fails (because there is no connection), the thread goes to sleep and then periodically attempts to reconnect. You can use the –master-connect-retry option to specify the interval between retries.

Reconnecting after a failed Binlog dump request
The thread is attempting to reconnect to the primary server.

Waiting for master to send event
The thread is already connected to the primary server and is waiting for the binary log event to arrive. If the primary server is idle, it lasts a long time. If you wait for slave_read_timeout seconds to continue, a timeout occurs. At this point, the thread considers the connection to be interrupted and attempts to reconnect.

Queueing master event to the relay log
The thread has read an event and is copying it to the relay log for processing by the SQL thread.

Waiting to reconnect after a failed master event read
An error occurred while reading (due to no connection). The thread will sleep master-connect-retry seconds before attempting to reconnect.

Reconnecting after a failed master event read
The thread is attempting to reconnect to the primary server. When the connection is reset, the status becomes waiting for master to send event.

Waiting for the slave SQL thread to free enough relay log spaces
A non-0 Relay_log_space_limit value is being used, and the relay log has grown to its combined size over this value. The I/O thread is waiting until the SQL thread processes the contents of the relay log and deletes some of the relay log files to free enough space.

Waiting for slave mutexes on exit
A very simple state that occurs when a thread stops.

3. Copy from SQL Thread state

Copy Code code as follows:

Reading event from the relay log
The thread has read an event from the relay log and can handle the event.

has read all relay log; Waiting for the slave I/O thread to update it
The thread has processed all the events in the relay log file and is now waiting for the I/O thread to write the new event to the relay log.

Waiting for slave mutexes on exit
A very simple state that occurs when a thread stops.

Iii. replication delivery and status files

From the server to the relay log to receive the log from the main server upload back. And relies on the status file to record which logs have been received from the primary server and which logs have been recovered.

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. You can overwrite the default relay log and index file name with the –relay-log and –relay-log-index server options. The function of the index file name is to record that the relay log is currently being used.

A new relay log is created under the following conditions:
1. Create a new relay log each time I/O thread starts.
2. When the log is refreshed, for example, by flush logs or mysqladmin flush-logs.
3. When the current relay log file becomes too large. How to determine the meaning of "too big":
Max_relay_log_size, if max_relay_log_size > 0
Max_binlog_size, if max_relay_log_size = 0
The status file name defaults to Master.info and Relay-log.info. Where the IO thread updates the Master.info file, and the SQL thread updates the Relay-log.info file.
The rows in the file correspond to the columns shown in Show SLAVE status:
Master.info file:

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

Relay-log.info file:

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. Therefore, it is best to recommend that the automatic removal of the relay log feature is turned off, and the hand-written shell angle would have prevented space-filled problems.

Iv. Replication Configuration steps

1. Create a user dedicated to replication (recommended) To use the account from the server to log on to the primary server:

Copy Code code as follows:

GRANT REPLICATION SLAVE on *.* to ' rep ' @ '% ' identified by ' logzgh ';

If you plan to use the load TABLE from master or the load DATA from master statement from a secondary server host, you need to grant additional permissions to the account:

Grant account super and reload global permissions.
Grant SELECT permissions for all tables that you want to load. Any table on the primary server that the account cannot select is ignored by load DATA from master.

2. Move the database file to the server

Situation one: If only use the MyISAM table

Copy Code code as follows:

Mysql> FLUSH TABLES with READ LOCK;

(Refresh all tables and block other writes, and do not exit the client to keep the read lock valid.) If you exit, the read lock will be released. )
The simpler way is to pack the data directories and compress them.

Copy Code code as follows:

$ tar-cvf/home/mysql/snapshot.tar./data (on Master)
$ tar-xvf/home/mysql/snapshot.tar (on slave)

The MySQL database may not need to be synchronized because the permissions table on the slave is not the same as master. At this point, you need to remove the compressed package when you unpack it.
Also, do not include any log files in the compressed package, and state files Master.info, Relay-log.info.

Copy Code code as follows:

Mysql> show MASTER STATUS;
+ —————— + ———-+ ———— –+ —————— +
| File | Position | binlog_do_db | binlog_ignore_db |
+ —————— + ———-+ ———— –+ —————— +
| mysql-bin.000058 | 45036137 | | |
+ —————— + ———-+ ———— –+ —————— +

Mysql> UNLOCK TABLES;

Situation Two: If Use the InnoDB table

Method One: Use the InnoDB hot Backup tool. It does not need to request any locks on master to achieve snapshot consistency, and the log file name and offset location have been recorded in the snapshot to be used later on slave.

Method Two: Record the current log file and offset position before master closes:

Copy Code code as follows:

Mysql> FLUSH TABLES with READ LOCK;
Mysql> show MASTER STATUS;

Note the log file and offset position in the display results as soon as possible. Then, shut down master when the lock is unlocked, ensuring that snapshots and records on master are consistent.

Close master server, $ mysqladmin-u root shutdown
Copy InnoDB data files, log files, and table structure definition files (. frm files).

Case three: can be used for both MyISAM and InnoDB tables
Do a SQL dump on master without having to back up the binary log as described above. Run the Mysqldump–master-data command, and then dump the resulting file to the slave.
However, this is slower than copying binary logs.

3. Modify the My.cnf file
my.cnf files on Master: (reboot takes effect)

Copy Code code as follows:

[Mysqld]
Log_bin
Server_id=1 (value is a positive integer between 1 and 2^32-1)

my.cnf files on slave:

Copy Code code as follows:

[Mysqld]
server_id=2 (ID must be different from Master's ID.) If you have more than one slave, each slave must have a unique ID. )

Configuring extended options for Slave

Copy Code code as follows:

Master_host=db-master.mycompany.com
master_port=3306
Master_user=rep
Master_password=freitag
MASTER_CONNECT_RETRY=60 (if Master is down or the slave connection is disconnected, slave periodically tries to connect to master, and the retry interval is controlled by this option, with a default value of 60 seconds.) )
Report_host=db-slave.mycompany.com
slave_net_timeout=3600 (Slave The default is 3,600 seconds later, if you haven't received data from master, it will be treated as a network disconnect.) )

The server considers the Master.info priority to be higher than the my.cnf of the configuration file.
The first time you start slave, Master.info does not exist, it reads the option values from the MY.CNF, and then saves them in Master.info.
The next time you restart Slave, it reads only the contents of the Master.info and does not read the option values in MY.CNF.
To use a different option value, you can either remove the Master.info and restart the slave, or use the change MASTER to statement (recommended) To reset the option value.

4. Start from server thread

Copy Code code as follows:

Mysqld_safe–user=mysql–skip-slave-start & (Start MySQL server, but do not start slave)
Setting parameters such as Master_log_file
mysql> change MASTER to master_host= ' qa-sandbox-1′,
Master_user= ' rep ',
Master_password= ' Logzgh ',
Master_log_file= ' mysql-bin.000007′,
master_log_pos=471632;

Mysql> START SLAVE;

After these programs are executed, you should connect to the primary server from the server and supplement any updates that have occurred since the snapshot.
If you forget to set the Server-id value for the primary server, you cannot connect to the primary server from the server.

Note: To ensure the maximum possible tolerance and consistency of transactional InnoDB replication settings,
Innodb_flush_log_at_trx_commit=1 and sync-binlog=1 should be used in the my.cnf file of the master server.

Copy Code code as follows:

Mysql> Show variables; (Check for read-only, which makes it impossible for slave to update data except slave threads or users with super privileges) to ensure that slave does not accept updates from other clients. )
Mysql> show Processlist; (Check if slave-start)

Start slave at the same time that you start MySQL:

Copy Code code as follows:

Mysqld_safe–user=mysql–read-only & (Start the MySQL server while starting the slave I/O thread)

Mysql> show SLAVE STATUSG;

5. Switch slave to master, on the Slave:

Copy Code code as follows:

Mysql> STOP SLAVE;
Mysql> RESET MASTER;

Five. Replication Startup options

–read_only
This option allows updates from the server to be allowed only from server threads or users with super privileges. You can ensure that updates from the customer are not accepted from the server.

–replicate_do_db=db_name
Tells the copy of the statement from the server that only makes the default database (selected by use) as db_name. To specify multiple databases, use this option multiple times, one for each database. Note that statements that cross databases are not replicated

–replicate_do_table=db_name.tbl_name
Tells you to copy only the specified table from the server thread. To specify more than one table, you should use this option multiple times, one for each table. In contrast to –replicate-do-db, updates are allowed across databases.

–replicate_ignore_db=db_name
Tells the statement from the server not to copy the default database (selected by use) as db_name. to ignore multiple databases, use this option multiple times, one for each database.

–replicate-ignore-table=db_name.tbl_name
Tells the server thread not to copy any statements that update the specified table (even if the statement might update other tables). to ignore multiple tables, you should use this option multiple times, one for each table.

–replicate_wild_do_table=db_name.tbl_name
Tells a statement that matches the specified database and table name schema from the server thread limit replication Update table. The pattern can contain the '% ' and ' _ ' wildcard characters, which have the same meaning as the like pattern matching operator. To specify more than one table, you should use this option multiple times, one for each table. This option can be updated across databases.

–replicate_wild_ignore_table=db_name.tbl_name
Tells the statement from the server thread not to copy the wildcard pattern given by the table. to ignore multiple tables, you should use this option multiple times, one for each table. This option can be updated across databases.

–replicate_rewrite_db=from_name->to_name
Tells from the server if the default database (selected by use) is from_name on the primary server, translate to To_name. Affects only statements that contain tables

–report_host=slave_name
The host name or IP address reported to the primary server from the server registration process. This value appears in the output of show SLAVE hosts on the primary server. If you do not want to register from the server itself on the primary server, this value is not set.

–report_port=slave_port
Connect the TCP/IP port number from the server and report it to the primary server during the server registration process.

–skip_slave_start
Tells from the server when the server starts without booting from the server thread. Use the start slave statement to start the thread at a later time.

–slave_skip_errors=[err_code1,err_code2,... | all]
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.
For example:
–slave-skip-errors=1062,1053
–slave-skip-errors=all

Six, do not stop the method of configuring replication

Method One:

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:

1. Ensure that a unique server ID number is assigned from the server.
2. Copy the backup file to the server.
3. Execute the following statement from the server and fill in the appropriate values for each option:

Copy Code code as follows:

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;

4. 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.

1. Issue the statement:

Copy Code code as follows:

Mysql> FLUSH TABLES with READ LOCK;

2. Execute the command (or its variant) when the lock is still added:

Copy Code code as follows:

Shell> Tar Zcf/tmp/backup.tar.gz/var/lib/mysql

and copy it to the server.
3. Issue the statement and ensure that the subsequent output is recorded:

Copy Code code as follows:

Mysql>show MASTER STATUS;

4. Release Lock:

Copy Code code as follows:

Mysql> UNLOCK TABLES;

Method Two:

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.

Vii. Other

1. You cannot copy from the server using the new binary log format from the old binary log format.

2. When upgrading from the server, you should shut down from the server, upgrade to the appropriate 5.1.x version, and then reboot from the server and start copying again. The version 5.1 from the server can read the old relay logs written before the upgrade and execute the statements contained in the log. The relay log created from the server after the upgrade is 5.1 format.

3. The same global character set and collation rules (–default-character-set, –default-collation) must always be used on the primary server and from the server. Otherwise, a replication key value error is encountered from the server because the key value that is considered unique in the character set of the primary server may not be unique from the server's character set.

4.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.

5.Q: How do I know the latest comparisons from the server to the home server? In other words, how do I know the date of the last query that was copied from the server?
A: You can view the results of the Seconds_behind_master column of the show SLAVE status statement.

6. Q: How do I force the primary server to block updates until it is synchronized from the server?
A: Use the following steps:
1. On the primary server, execute these statements:

Copy Code code as follows:

Mysql> FLUSH TABLES with READ LOCK;
Mysql> show MASTER STATUS;

Record the log name and offset of the output of the show statement. These are the copy coordinates.

2. On the server, issue the following statement, where the parameters of the master_pos_wait () function are the values of the copied coordinates obtained in the previous step:
mysql> SELECT master_pos_wait (' Log_name ', log_offset);
The SELECT statement blocks until the specified log file and offset are reached from the server. At this point, synchronize from the server to the primary server, and the statement returns.

3. On the primary server, issue the following statement to allow the primary server to restart processing updates:

Copy Code code as follows:

Mysql> UNLOCK TABLES;

7.Q: How to improve the performance of the system by copying?
A: You should set up a server as the primary server and point all writes to that server. The budget is then configured with as many server and stack space as possible, and the read operation is distributed between the primary server and the server. You can also use the –skip-innodb, –SKIP-BDB, –low-priority-updates, and –delay-key-write=all options to start from the server to improve speed from the server side. In this case, in order to improve speed, the MyISAM table is used from the server instead of the InnoDB and BDB tables.