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

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, master.info and relay-log.info, 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

 
 

  
  
# Vim/etc/my. cnf
  
  
Server-id=1// It must not be the same as the server. Use the default value here.
  
  
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.
  
  
Sync-binlog=1// Synchronize data to the disk immediately. We recommend that you open an account.
  
  
 
  
  
Mysql>Grant replication client, replication slave on *. * TO repl @ '1. 1.1.2 'identified BY '123 ';
  
  
Mysql>Flush privileges;
  
  
Replication client allows the use of show status on the REPLICATION host (Master) and Slave (Slave)
  
  
Replication slave allows replication slave servers to connect to the master server
 
 

Slave server operations

 
 

  
  
# Vi/etc/my. cnf
  
  
#Log-bin=Mysql-Bin // Add the # sign to disable or disable it
  
  
Server-id=11// ID, which must not be the same as the master server
  
  
Relay-log= Relay-bin // defines the location and file name of the relay log, or you do not need to define
  
  
Relay-log-index= Relay-bin-index // relay log index file name and storage location
  
  
 
  
  
Mysql>CHANGE MASTERMASTER_HOST='1. 1.1.1',MASTER_USER='Repl',MASTER_PASSWORD='123';
  
  
Mysql>Start slave;
  
  
Mysql>Show slave status;
 
 

The following two values are displayed: YES, indicating that the slave server is working normally.

 
 

  
  
Slave_IO_Running: Yes 
  
  
Slave_SQL_Running: Yes 
 
 

 
 

  
  
mysql> show processlist \G 
  
  
*************************** 1. row *************************** 
  
  
     Id: 6 
  
  
   User: system user 
  
  
   Host:  
  
  
     db: NULL 
  
  
Command: Connect 
  
  
   Time: 1159 
  
  
  State: Waiting for master to send event 
  
  
   Info: NULL 
  
  
*************************** 2. row *************************** 
  
  
     Id: 7 
  
  
   User: system user 
  
  
   Host:  
  
  
     db: NULL 
  
  
Command: Connect 
  
  
   Time: -153412 
  
  
  State: Slave has read all relay log; waiting for the slave I/O thread to update it 
  
  
   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.

 
 

  
  
// Configure the slave server/etc/my. cnf file and add the following content:
  
  
Server-id=2# Slave server ID. Do not use the same ID as the master ID
  
  
Master-host=1. 1.1.3 # specify the master server IP Address
  
  
Master-user=Repl# Specify the user name that can be synchronized on the master server
  
  
Master-password=123456# Password
  
  
Master-port=3306# Ports used for synchronization
  
  
Master-connect-retry=60# Resumable connection time
  
  
Replicate-ignore-db=Mysql# Blocking mysql Database Synchronization
  
  
Replicate-do-db=Test1# Name of the synchronized Database
 
 

Common troubleshooting commands and their explanations:

 
 

  
  
Flush master; // clear binary logs
  
  
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.

 
 

  
  
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.