MySQL server image configuration _ MySQL

The image configuration of the MySQL server allows you to copy data from one place to another, and synchronize data in two or more places.

The MySQL server also supports images to greatly improve data security and stability. The following describes the image mechanism in MySQL data:

In the image relationship, a MySQL server assumes the master server role, and one or more servers assume the slave server role. The data in the slave server is exactly the same as that in the master server.

Before creating an image, the master server and slave server must be fully synchronized. After synchronization, the operations performed on the master server will be performed on the slave server, and the operations on the master server will not directly act on the slave server.

The media responsible for transmitting various modification actions on the master and slave servers is the binary change log on the master server, which records all the operation actions on the master server. Therefore, the master server must activate the binary log function.

The slave server must have sufficient permissions to receive binary log files from the master server. The image coordination information records the progress of the slave server, including the binary change log file name being read from the slave server and its current read/write location in the file.

Each master server can have multiple slave servers, but each slave server can have only one master server. However, the MySQL server allows a slave server to act as the master server of another slave server, thus creating an image server chain.

The image mechanism is still a new thing in MySQL, which was first implemented in version 3.23.15. The image capabilities of different versions vary. generally, we recommend that you use the latest version whenever possible. The following lists the compatibility rules for the image mechanism of MySQL servers of different versions:

The slave server of 3.23.x cannot communicate with the master server of 4.x.

The slave server of Version 4.0.0 can only communicate with the master server of Version 4.0.0.

The slave server of version 4.0.1 or later can communicate with both the master server of version 3.23.x and the master server of version 4.x. In the latter case, the version number of the master server must be equal to or greater than that of the slave server.

Generally, the following principles are recommended:

Try to make the master server and slave server use the same version of the system.

After selecting a system, try to use the latest version of the system.

To create a master-slave backup storage, follow these steps:

Determine the image ID of the master/slave server. the IDs of the master and slave servers cannot be the same. When starting the master/slave server, use the -- server_id startup option to give its ID.

The slave server must have an account with sufficient permissions on the master server. the slave server will use this account to connect to the master server and request the master server to send the binary change log to it. Run the following command to create the account:

Mysql> grant replication slave on *. * TO 'slave _ user' @ 'slave _ host' identified by 'slave _ pass ';
# The REPLICATION permission is only available in versions later than MySQL4.0.2. for earlier versions, use the FILE permission.
Copy the database files on the master server to the slave server to complete the initial synchronization. You can also attach a backup file. MySQL 4.0.0 and later versions can also run the load data from master statement on the MASTER server to create the slave server. But there are constraints:

All data tables must be MyISAM tables.

The account used to connect to the slave server to issue this command must have the SUPER permission.

The account used by the slave server to connect to the master server must have the RELOAD and SUPER permissions. Note: This is an account on the MASTER server, and the account used to issue the load data from master statement is an account on the slave server.

When executing the load data from master statement, you need to apply for a Global read operation lock, which blocks all write operations on the MASTER server during statement execution.

No matter which method is used for data synchronization, make sure that the modification operation cannot be performed on the master server during the time when the backup is created and the binary log function is reconfigured to the master server.

Disable the server.

Modify the configuration of the master server-tell it its image ID and activate its binary log function. Add the following content to the option file to be read by the master server:

[Mysqld]
Server-id = master_server_id
Log-bin = binlog_name
Restart the master server. from now on, it will record the customer's modifications to the database to the binary change log. If the binary log function has been activated before the restart, back up the binary change log before the restart, and then run a reset master statement to clear the existing binary change log.

Disable the slave server.

Configure the slave server so that it can know its own image ID, where to find the master server, and how to connect to the master server. The configuration is as follows:

[Mysqld]
Server-id = slave_server_id
Master-host = master_host
Master-user = slave_user # account created on the master server for the slave server
Msater-password = slave_pass # password of the account created for the slave server on the master server
Master-connet-retry = 30 # set the connection retry interval. the default value is 60 seconds.
Master-retry-count = 100000 # set the number of retries. the default value is 86400.
Note: The last two options are set when the network connection is unreliable.
Restart the slave server. The slave server uses two information sources to determine its own progress position in the image work: one is the master.info file in the data directory, and the other is the configuration information given by the startup option. When the slave server is started for the first time, the master.info file does not exist. the slave server will connect to the master server based on the various master-xxx option values in the option file. Once the connection is successful, the slave server creates a master.info file to save various connection parameters and the operating status of its own image. If you restart the slave server later, the slave server will read the file first, instead of the option file. If you modify the content of the option file, you need to delete the master.info file and restart the slave server if you want this option to take effect.

The above steps are the operation process for mirroring all databases. if we want to keep mysql permission data on the master server, and exclude it from the image mechanism, you can add the -- binlog-ignore-db = mysql option to [mysqld] in the option File. in this way, operations on the mysql database are not recorded in the binary change log. To exclude other databases, you only need to add several more options.

Use the following commands to monitor and manage master/slave servers:

Slave stop and slave start are used to hook up and restore images from the server. for example, this statement can be used to temporarily STOP Image activity on the SLAVE server during backup.

Show slave status: view the image coordination information on the SLAVE server. This information can be used to determine which binary change logs are no longer used.

Purge master, which processes invalid binary change logs on the MASTER server. Delete binary change logs that are no longer used by slave servers.

Change master, and modify the image parameters on the slave server. For example, if you are reading the binary change log on the master server and writing the relay log file.

In versions later than MySQL4.0.2, the slave server in the image mechanism is composed of two internal threads:

A "I/O thread" is responsible for communicating with the master server, requesting the master server to send a binary change log, and writing the received data modification command into a relay log file; you can use slave stop IO_THREAD or slave start IO_THREAD to suspend or resume this thread.

The other is called "SQL thread", which reads and executes data modification commands from relay logs. Similarly, you can use slave stop SQL _THREAD or slave start SQL _THREAD to suspend or resume this thread.

The default file for relay log files is hostname-relay-bin.nnn and hostname-relay-bin.index. You can modify the -- relay-log and -- relay-log-index options of the slave server. There is also a relay-log.info relay information file in the slave server that can be used to modify the file name with the -- relay-log-info-file startup option.