MySQL master-slave Architecture and Data replication based on SSL

 MySQL master-slave Architecture and Data replication based on SSL

I. Mysql Master/Slave architecture topology:
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QUD5-0.png "border =" 0 "alt =" "/>

Ii. Knowledge about MySQL master-slave architecture

 
 

  
  
In the master-slave architecture of MySQL, there are three key threads:
  
  
Dump thread: when there are statements on the master server that change the database, these statements are based on statements and line-based and mixed)
  
  
The change operation statement is recorded in the binary log first. This thread notifies the slave server that there are database operation statements, so that the slave server's I/O thread can be copied.
  
  
I/O thread: monitors the binary log of the master server, and transmits database operation statements to record these statements into relay logs.
  
  
SQL thread: Execute the relay log statement from the server to the database storage.
  
  
 
  
  
In MySQL 5.5, the master server in the master/slave architecture may write multiple threads to handle high-concurrency business,
  
  
The SQL thread on the slave server is written into the database storage by a single thread, which may cause the slave database to lag behind.
 
 

Iii. Process of MySQL master-slave architecture

 
 

  
  
First, establish the master-slave relationship between the MySQL database Master172.16.10.4) and Slave172.16.10.5 ).
  
  
Master server-id: 1
  
  
Server-id of Slave: 15
  
  
Master:
  
  
Enable binary log
  
  
Set server-id
  
  
Create a user with replication permission and require ssl Authentication
  
  
Slave:
  
  
Enable relay logs
  
  
Set server-id
  
  
Start the Slave service and specify relevant Master parameters.
  
  
Create the certificate and key required for ssh connection for Mater and Slave.
  
  
For more information about the certificate, see ---> http://haicang.blog.51cto.com/2590303/897660.
 
 

• Master Server Configuration

 
 

  
  
1: Enable binary log
  
  
2: Modify server-id to 1
  
  
3: create a user with the copy permission mysql and provide it with the redhat Password
 
 

 
 

  
  
mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO  mysql@'172.16.10.5' IDENTIFIED BY 'redhat'  REQUIRE SSL; 
 
 

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QWZ5-1.png "border =" 0 "alt =" "/>

• Slave Server Configuration

 
 

  
  
Enable relay logs
  
  
Modify server-id to 15
 
 
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QV619-2.png "border =" 0 "alt =" "/>

• Configuration of the Master/Slave server based on ssl replication:

 
 

  
  
Both Master and Slave enable have_ssl.
  
  
Open the have-openssl option in the configuration file, and add certificate and password information.
  
  
Master Server Configuration:
 
 
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QWY5-3.png "border =" 0 "alt =" "/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QTQ5-4.png "border =" 0 "alt =" "/> Note: restart the MySQL service to view the enabled status

We recommend that you use the change master to command for slave server configuration.

 
 

  
  
CHANGE MASTER TO  
  
  
->MASTER_HOST='172.16.10.4', 
  
  
->MASTER_USER='mysql', 
  
  
->MASTER_PASSWORD='redhat', 
  
  
->MASTER_LOG_FILE='msql-bin.000001', 
  
  
->MASTER_LOG_POS=107 
  
  
 
  
  
CHANGE MASTER TO 
  
  
-> MASTER_SSL=1 
  
  
->MASTER_SSL_CA="/etc/pki/CA/cacert.pem", 
  
  
->MASTER_SSL_CERT="/usr/local/mysql/ssh/Slave.crt", 
  
  
->MASTER_SSL_KEY="/usr/local/mysql/ssh/Slave.key"; 
  
  
 
  
  
START SLAVE; 
  
  
SHOW SLAVE STATUS; 
 
 
650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/193QUa4-5.png" border="0" alt="" />

 
 

  
  
See the official document for details: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.h
  
  
Tml
  
  
   
   

    
    
In addition, in the master-slave architecture, we recommend that you configure the following configuration to ensure data consistency of the transaction engine database"
    
    
Master Server:
    
    
Sync_binlog = 1 ----------> write binary log if the transaction is not submitted
    
    
Innodb_flush_logs_at_trx_commit = 1
    
    
 
    
    
Slave Server:
    
    
Skip_slave_start = 1-Disable the following two processes to automatically start with MySQL
    
    
Slave_IO_Running: Yes
    
    
Slave_ SQL _Running: Yes
    
    
Read_only = 1
   
   
 
 

• Verify the master-slave replication of the MySQL server

 
 

  
  
Master server: Create a student table
 
 
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QWX7-6.png "border =" 0 "alt =" "/>

 
 

  
  
View slave servers
 
 
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QTT9-7.png "border =" 0 "alt =" "/>

Iv. Problems Encountered during configuration

 
 

  
  
View slave server logs
 
 
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QS910-8.png "border =" 0 "alt =" "/> you can find from the log that it may be caused by the slave server's relay log.

 
 

  
  
Solution:
  
  
Stop MySQL server first: service mysqld stop
  
  
Rm-fr master.info
  
  
Rm-fr relay-log.info
  
  
Add skip-slave-start to the configuration file/etc/my. cnf.
  
  
Service mysqld start
  
  
Log on to mysql
  
  
Mysql> show master status;
  
  
+ ------------------ + ---------- + -------------- + ------------------ +
  
  
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  
  
+ ------------------ + ---------- + -------------- + ------------------ +
  
  
| Mysql-bin.000011 | 107 |
  
  
+ ------------------ + ---------- + -------------- + ------------------ +
  
  
Execute again:
  
  
Change master to command
  
  
Query OK, 0 rows affected (0.02 sec)
  
  
Mysql> start slave;
  
  
Make sure that Slave ready _ IO_Running and Slave ready _ SQL _Running are in the yes status.
 
 

 
 

  
  
View results
 
 

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QWM3-9.png "border =" 0 "alt =" "/>

 
 

  
  
The following error occurs when the slave server is started after the configuration is complete:
 
 
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QV5U-10.png "border =" 0 "alt =" "/>

 
 

  
  
Search slave server logs
 
 
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QV952-11.png "border =" 0 "alt =" "/>

 
 

  
  
It can be seen from the log that an error occurred while the server was searching for the latest binary log of the master server. The following is the information about the slave server.
 
 

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QQ559-12.png "border =" 0 "alt =" "/>

 
 

  
  
View master server
 
 
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QQB2-13.png "border =" 0 "alt =" "/>

 
 

  
  
The comparison shows that the latest binary log pointing from the slave server is inconsistent with the master server, resulting in an error.
  
  
Modify as follows:
 
 
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QSN9-14.png "border =" 0 "alt =" "/>

V. Summary

 
 

  
  
Check the error log, Goolge, and find the cause of the problem. Many problems can be solved.
  
  
I hope this little experience can help you and encourage each other!
 
 

This article from the "Good Hope" blog, please be sure to keep this source http://haicang.blog.51cto.com/2590303/958370