MySQL master-slave Architecture and Data replication based on SSL

Source: Internet
Author: User

 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

 
 
  1. In the master-slave architecture of MySQL, there are three key threads:
  2. 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)
  3. 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.
  4. I/O thread: monitors the binary log of the master server, and transmits database operation statements to record these statements into relay logs.
  5. SQL thread: Execute the relay log statement from the server to the database storage.
  6.  
  7. In MySQL 5.5, the master server in the master/slave architecture may write multiple threads to handle high-concurrency business,
  8. 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

 
 
  1. First, establish the master-slave relationship between the MySQL database Master172.16.10.4) and Slave172.16.10.5 ).
  2. Master server-id: 1
  3. Server-id of Slave: 15
  4. Master:
  5. Enable binary log
  6. Set server-id
  7. Create a user with replication permission and require ssl Authentication
  8. Slave:
  9. Enable relay logs
  10. Set server-id
  11. Start the Slave service and specify relevant Master parameters.
  12. Create the certificate and key required for ssh connection for Mater and Slave.
  13. For more information about the certificate, see ---> http://haicang.blog.51cto.com/2590303/897660.
  • Master Server Configuration
 
 
  1. 1: Enable binary log
  2. 2: Modify server-id to 1
  3. 3: create a user with the copy permission mysql and provide it with the redhat Password
 
 
  1. 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

 
 
  1. Enable relay logs
  2. 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:
 
 
  1. Both Master and Slave enable have_ssl.
  2. Open the have-openssl option in the configuration file, and add certificate and password information.
  3. 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.

 
 
  1. CHANGE MASTER TO  
  2. ->MASTER_HOST='172.16.10.4', 
  3. ->MASTER_USER='mysql', 
  4. ->MASTER_PASSWORD='redhat', 
  5. ->MASTER_LOG_FILE='msql-bin.000001', 
  6. ->MASTER_LOG_POS=107 
  7.  
  8. CHANGE MASTER TO 
  9. -> MASTER_SSL=1 
  10. ->MASTER_SSL_CA="/etc/pki/CA/cacert.pem", 
  11. ->MASTER_SSL_CERT="/usr/local/mysql/ssh/Slave.crt", 
  12. ->MASTER_SSL_KEY="/usr/local/mysql/ssh/Slave.key"; 
  13.  
  14. START SLAVE; 
  15. SHOW SLAVE STATUS; 
650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/193QUa4-5.png" border="0" alt="" />
 
 
  1. See the official document for details: http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.h
  2. Tml
  3.    
       
    1. In addition, in the master-slave architecture, we recommend that you configure the following configuration to ensure data consistency of the transaction engine database"
    2. Master Server:
    3. Sync_binlog = 1 ----------> write binary log if the transaction is not submitted
    4. Innodb_flush_logs_at_trx_commit = 1
    5.  
    6. Slave Server:
    7. Skip_slave_start = 1-Disable the following two processes to automatically start with MySQL
    8. Slave_IO_Running: Yes
    9. Slave_ SQL _Running: Yes
    10. Read_only = 1
  • Verify the master-slave replication of the MySQL server
 
 
  1. Master server: Create a student table
650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/193QWX7-6.png "border =" 0 "alt =" "/>
 
 
  1. 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

 
 
  1. 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.

 
 
  1. Solution:
  2. Stop MySQL server first: service mysqld stop
  3. Rm-fr master.info
  4. Rm-fr relay-log.info
  5. Add skip-slave-start to the configuration file/etc/my. cnf.
  6. Service mysqld start
  7. Log on to mysql
  8. Mysql> show master status;
  9. + ------------------ + ---------- + -------------- + ------------------ +
  10. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  11. + ------------------ + ---------- + -------------- + ------------------ +
  12. | Mysql-bin.000011 | 107 |
  13. + ------------------ + ---------- + -------------- + ------------------ +
  14. Execute again:
  15. Change master to command
  16. Query OK, 0 rows affected (0.02 sec)
  17. Mysql> start slave;
  18. Make sure that Slave ready _ IO_Running and Slave ready _ SQL _Running are in the yes status.
 
 
  1. View results

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

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

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

V. Summary

 
 
  1. Check the error log, Goolge, and find the cause of the problem. Many problems can be solved.
  2. 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

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: info-contact@alibabacloud.com 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.