MySQL master-slave replication, SSL-based master-slave replication, master-master replication, and semi-synchronous Replication

Source: Internet
Author: User

MySQL replication is divided into master-slave replication and master-master replication. What is master-slave Replication refers to a master server with one or more servers as the slave servers. The slave server does not write any data. The data source is the master server. The purpose of replication is to synchronize data. If data is also written to the slave server, the master server will not replicate the data from the slave server in the master-slave architecture, which may cause data not to be synchronized, therefore, data cannot be written to the slave server. MySQL replication is based on binary logs. Because binary logs record all SQL statements that can change or potentially change database data, after these SQL statements are copied from the server and executed again, the data will be the same as the master server. Therefore, the MySQL version of the slave server must be higher than the master server or be the same as the master server. It is best to use the same master-slave version, so that the SQL statements are different for different versions, and cause errors. What is master-master replication? Master-master replication means that two or more MySQL servers are master servers, and everyone replicates to each other. The advantage is that each server, both can write data, while the slave server in the master and slave databases cannot write data. The principle of master-slave replication is similar to that of master-slave replication, but data is replicated and synchronized to each other. Whether it is master-slave replication or master-master replication, the replication process is asynchronous. Data Query can be accelerated, but data writing is not improved. Master slave or master replication can also achieve data redundancy. When the slave server is DOWN, the master server can work normally without any impact. When the master server is DOWN, you only need to temporarily switch from the master server to the master server. It is also convenient for data backup. During backup, you can stop the slave server and then back up the data on the slave server. After the backup is complete, you can enable it again. When you implement start slave for master-SLAVE replication, create an I/O thread from the server to connect to the master server and send binary logs to the master server. The master server creates a Binlog Dump thread to send the binary log content to the slave server. Read the content sent by the Binlog Dump thread of the master server from the server I/O thread and copy the data to a local file in the data directory of the slave server, that is, relay logs. The first thread is an SQL thread. The server uses this thread to read relay logs and execute updates contained in the logs. The show processlist statement can be used to query information about replication on the master server and slave server. The slave server creates two additional State files, and, in the data directory. 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. After flush tables with read lock from the server, data cannot be synchronized for the moment. The copied statements are executed only after being unlocked. The slave server does not need to close the binary log or the account relay log, because the server will automatically open or close your account. If binary logs are not disabled, the SQL statements copied from the master server are not recorded in binary logs during execution. Only the SQL statements executed on the local machine are used, to be recorded in binary logs. Relay logs are stored in the data directory by default, with the file name being host name. relay-00000N. If you want to save it to another location, you can specify it in the configuration file. 1. Install MySQL database (Omitted) 2. Configure Master/Slave server operations

  1. # Vim/etc/my. cnf
  2. Server-id=1// It must not be the same as the server. Use the default value here.
  3. Log-bin=Mysql-Bin // account opening specifies the binary log and storage location of MySQL. By default, the account is opened and saved in the data directory.
  4. Sync-binlog=1// Synchronize data to the disk immediately. We recommend that you open an account.
  6. Mysql>Grant replication client, replication slave on *. * TO repl @ '1. 1.1.2 'identified BY '123 ';
  7. Mysql>Flush privileges;
  8. Replication client allows the use of show status on the REPLICATION host (Master) and Slave (Slave)
  9. Replication slave allows replication slave servers to connect to the master server
Slave server operations
  1. # Vi/etc/my. cnf
  2. #Log-bin=Mysql-Bin // Add the # sign to disable or disable it
  3. Server-id=11// ID, which must not be the same as the master server
  4. Relay-log= Relay-bin // defines the location and file name of the relay log, or you do not need to define
  5. Relay-log-index= Relay-bin-index // relay log index file name and storage location
  8. Mysql>Start slave;
  9. Mysql>Show slave status;
The following two values are displayed: YES, indicating that the slave server is working normally.
  1. Slave_IO_Running: Yes 
  2. Slave_SQL_Running: Yes 
  1. mysql> show processlist \G 
  2. *************************** 1. row *************************** 
  3.      Id: 6 
  4.    User: system user 
  5.    Host:  
  6.      db: NULL 
  7. Command: Connect 
  8.    Time: 1159 
  9.   State: Waiting for master to send event 
  10.    Info: NULL 
  11. *************************** 2. row *************************** 
  12.      Id: 7 
  13.    User: system user 
  14.    Host:  
  15.      db: NULL 
  16. Command: Connect 
  17.    Time: -153412 
  18.   State: Slave has read all relay log; waiting for the slave I/O thread to update it 
  19.    Info: NULL 
We can see that there are two threads, one is waiting for the master server to send data, called I/O, and the other is reading and executing the relay log, then wait for the I/O thread on the slave to update the log, called the SQL thread, and then create a database on the master server. Check the database on the slave server, the description is OK. If the slave server does not close the binary log, the master server creates a database. After the slave server completes synchronization, you can see the SQL statement for creating the database in the relay log of the slave server, but it cannot be seen in binary logs. The slave server's binary logs only record the SQL statements executed directly on the slave server, instead of the SQL statements copied from the master server and then executed. Directly create a database on the server. You can see that the binary log records of the server. However, the master server does not synchronize data from the slave server. This is master-slave replication. You can also write the following configurations TO the slave server, without having to change the master to. There are also some other definitions.
  1. // Configure the slave server/etc/my. cnf file and add the following content:
  2. Server-id=2# Slave server ID. Do not use the same ID as the master ID
  3. Master-host=1. 1.1.3 # specify the master server IP Address
  4. Master-user=Repl# Specify the user name that can be synchronized on the master server
  5. Master-password=123456# Password
  6. Master-port=3306# Ports used for synchronization
  7. Master-connect-retry=60# Resumable connection time
  8. Replicate-ignore-db=Mysql# Blocking mysql Database Synchronization
  9. Replicate-do-db=Test1# Name of the synchronized Database
Common troubleshooting commands and their explanations:
  1. Flush master; // clear binary logs
  2. Flush slave; // clear binary logs
The preceding command clears the binary logs of the MASTER server and slave server when an error occurs during MASTER-slave replication, especially when the fields are repeated. Then, use the change master to command TO point, then, the account will be opened from the server and no error will be reported. Note: The preceding two commands clear binary logs. in the production environment, make sure that the logs have been backed up and no data is written.
  1. Show master status; // view the binary files currently used by the master server and the location of the binary files
Note: If you do not want to clear binary logs, but it is not easy to troubleshoot errors, you can specify the locations of binary files and binary files used by the master server when specifying the master server, indicates that the binary file is copied from the current location. Do not copy the previous content. Before that, you can use the mysqldump command to export data and then import the data to the slave server. To achieve data consistency, and then use the command to specify the binary file and location, to ensure that the master and slave data are consistent. Above, the master-slave replication is complete.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.