The internal replication function of MySQL is implemented between two or more servers by setting the master-slave relationship between them. One of them serves as the master server and the other as the slave server. This section describes how to configure two servers, one as the master server and the other as the slave server. And describe the switchover between them.
The internal replication function of MySQL is implemented between two or more servers by setting the master-slave relationship between them. One of them serves as the master server and the other as the slave server. This section describes how to configure two servers, one as the master server and the other as the slave server. And describe the switchover between them.
The internal replication function of MySQL is implemented between two or more servers by setting the master-slave relationship between them. One of them serves as the master server and the other as the slave server. This section describes how to configure two servers, one as the master server and the other as the slave server. And describes the process of switching between them. This section describes how to configure MySQL 3.23.25 and how to test it. It is recommended that MySQL developers use the latest version and the master-slave servers use the same version. At the same time, MySQL 3.23 is still a beta version, and this version may not be backward compatible.
Configure the master server
We will specify two servers. A (IP address 192.168.0.1) serves as the master server (host for short ). B (IP address 192.168.0.2) is used as the slave server ).
1. Create an account for backup
The replication function of MySQL is implemented by connecting slave machine (B) to host (A), and then reading the binary Update log of the host, then merge the changes into your own database. The slave needs a user account to connect to the host. Therefore, create an account on the host and grant only the FILE Permission to the account. The following operations are performed:
Mysql> grant file on *. * TO replicate@192.168.0.2 identified by 'Password ';
To connect the slave to the host, run 'flush privileges' on the host ':
Mysql> 'flush PRIVILEGES;
But don't worry, because we will stop the server in the following steps.
2. Modify the option File
Now we need a snapshot of the host database and configure the host to generate binary update logs. First, edit the 'my. cnf 'file to allow binary updates. Therefore, add a line 'Log-bin' somewhere below [mysqld '. When the next server is started, the host generates a binary Update log (named: <主机名> -Bin. <增量序号#> ).
[Mysqld]
Log-bin
To make the binary Update log valid, close the MySQL service program, then store all the database directories on the host to another directory, and restart mysqld.
3. Get a snapshot of the server database
Make sure all databases are obtained. Otherwise, if a table exists on the host but does not exist on the slave machine during replication, the table will exit due to an error. Now you have obtained a data snapshot and a binary log since the snapshot was created, which records any changes to the database.
Pay attention to the MySQL data file (*. MYD ,*. MYI and *. frm) does not depend on the file system, but the ISAM table data files depend on the system. However, MyISAM tables are used for MySQL and later versions. So you can only transfer files, such as from Solaris to Linux. Only when you are in a heterogeneous server environment and only copying data table files cannot be shared, you will have to use the mysqldump utility or other custom scripts to get data snapshots, however, this possibility is very small.
Configure slave server
1. migrate the database directory of the host
Stop the MySQL service program on the machine and move the database directory copied from the host to the data directory on the machine. Make sure to change the directory owner and group to the MySQL user value and change the file mode to 660 (only readable and writable to the owner and group ), the bibliography is 770 (readable, writable, and executable only for the owner and group ).
Start the MySQL service program on the slave machine and confirm that MySQL works properly. Run several select queries (do not update or insert queries) to check whether the data snapshot obtained in step 1 is successful. Then, after the test is successful, turn off the MySQL service program.
2. Modify the option File
Configure the host to be accessed on the slave machine to receive host changes. Therefore, you need to edit the 'my. cnf 'file on the server and add the following lines in the [mysqld] section:
Master-host = 192.168.0.1
Master-user = replicate
Master-password = password
After starting the slave server service program, the slave server service program will be viewed in 'my. the host specified in the cnf file, check whether there are changes, and merge these changes into your own database. The slave server maintains host update records, which are received from the host's 'master. info' file. The SQL command 'show SLAVE-status' is used to check the thread STATUS. If an error occurs in processing binary logs on the slave machine, it will lead to the exit of the slave machine thread and generate a message in the *. err log file. Then the error can be corrected, and the SQL statement 'slave start' can be used to restart the SLAVE thread. The thread will continue processing from where the binary log processing of the host is interrupted.
So far, the data changes on the host should have been copied to the slave machine. to test it, you can insert or update a record on the host, and select this record on the slave machine.
Now we have this master-slave relationship from server A to server B. This allows us to redirect all queries to server B when server A may become A machine, however, when server A recovers, we cannot recover the changes to server. To solve this problem, we create A master-slave relationship from machine B to machine.
Create a master-slave relationship
1. Slave Configuration
First, my. in the cnf file, add 'Log-bin' to the [mysqld] section, restart mysqld, and create a user account on which the copy function can be executed. Use:
Grant file on *. * TO replicate@192.168.0.1 identified by 'Password ';
Run the 'flush privileges' command on machine B to load the new authorization table after the copied user is added.
2. Host Configuration
Return to machine A and add the following lines to its 'my. cnf:
Master-host = 192.168.0.2
Master-user = replicate
Master-password = password
After restarting the service program of host A, we now have A master-slave relationship between host A and host B. No matter which server updates a record or inserts a record, it will be copied to another server. Note: I am not sure how fast a slave machine merges binary logs. Therefore, using this method for load balancing of insert or update statements may not be a good solution.
Summary
First, make sure you have obtained a complete data snapshot. If you forget to copy a table or database, the offline program will stop. The time when a snapshot is generated is critical. Make sure that the binary log function is invalid before copying data files. If the binary log function is allowed before the snapshot is obtained, the slave thread may stop because the primary key repeats when trying to import important records. It is best to follow the solution discussed in the second part: Disable-copy-enable the binary log function to restart.
You may want to configure replication processing in the first way, and pay attention to the slave at the right time to ensure synchronization between the slave and the host.