Master-slave synchronization Summary of MySQL databases in the production environment

Source: Internet
Author: User
Tags mysql backup

MySQL master-slave synchronization is a mature architecture with the following advantages: ① the query can be performed on the slave server (that is, the READ function we often call) to reduce the pressure on the master server; ② back up data from the master server to avoid affecting the services of the master server during the backup process; ③ when the master server encounters problems, you can switch to the slave server. So I often use this solution in project deployment and implementation. In view of the rigor of MySQL in the production environment, I recommend using the MySQL source code compilation method of brother Zhang banquet.

Version 4 is updated as follows:

I. added the MySQL5.1.38 compilation and installation process. The installation process still adopted the MySQL Installation Method in the early stage of Zhang banquet, and abandoned the script control method;

2. The method for retrieving the master database from the database is still obtained using tar. The reason for not using MySQLdump is that the actual configuration shows that this method has a high chance of failure, therefore, we have been using tar to take the complete snapshot of the primary database. Some netizens do not understand it too well. Here we will focus on it;

Iii. Although the configuration of MySQL master-slave replication is relatively simple, it is also a handle blade. Because if the mistaken deletion of the image occurs on the master database, the slave database will also happen; therefore, standalone backup of the master table must be performed. FTP backup is also recommended;

4. binlog must be enabled, and the compliance with the production environment is strict. You are welcome to discuss this post and find out the bugs and errors to avoid misleading new users; I will keep an eye on and update this post (fuqin cooking)

Database directory and others

My. cnf configuration file/usr/local/webserver/MySQL/my. cnf

MySQL database location/usr/local/webserver/MySQL/data/

Primary Database: 192.168.4.191
Slave Database: 192.168.4.192
Operating System: RHEL5.4 64-bit
Server Type: HP 580G5, dual-core XeonE5520, 32 GB memory, 6 300GSAS into RAID 10
The compilation process of MySQL5.1.38 source code is as follows:

 
 
  1. /Usr/sbin/groupadd MySQL
  2. /Usr/sbin/useradd-g MySQL
  3. Unzip MySQL-5.1.38.zip
  4. Cd MySQL-5.1.38/
  5. ./Configure -- prefix =/usr/local/webserver/MySQL/-- enable-validator
  6. -- With-extra-charsets = complex -- enable-thread-safe-client
  7. -- With-big-tables -- with-readline -- with-ssl -- with-embedded-server
  8. -- Enable-local-infile -- with-plugins = innobase
  9. Make & make install
  10. Chmod + w/usr/local/webserver/MySQL
  11. Chown-R MySQL: MySQL/usr/local/webserver/MySQL
  12. Cp support-files/my-medium.cnf/usr/local/webserver/MySQL/my. cnf
  13. # This depends on the specific environment. huge. cnf can be used for high-pressure conversion.
  14. Cd ../

① Create a data table as a MySQL user account:

 
 
  1. /usr/local/webserver/MySQL/bin/MySQL_install_db 
  2. --basedir=/usr/local/webserver/MySQL 
  3. --datadir=/usr/local/webserver/MySQL/data 
  4. --user=MySQL 

② Start the end of MySQL and run it in the background)

 
 
  1. /bin/sh /usr/local/webserver/MySQL/bin/MySQLd_safe 
  2. --defaults-file=/usr/local/webserver/MySQL/my.cnf & 

1. Set the master database

1. Modify the master database my. cnf, mainly to set a different id and logbin

 
 
  1. #vim /usr/local/webserver/MySQL/my.cnf  
  2. server-id = 1 
  3. log-bin= binlog 
  4. binlog-do-db=iea 

2. Start the master database to take effect

 
 
  1. #/bin/sh /usr/local/webserver/MySQL/bin/MySQLd_safe 
  2. --defaults-file=/usr/local/webserver/MySQL/my.cnf & 

3. log on to the master database

 
 
  1. #/usr/local/webserver/bin/MySQL -u root -p 

4. Grant the slave database permission account to allow users to read logs from the master database.

 
 
  1. MySQL> grant all privileges on *.* to 'admin'@'%' 
  2. identified by '12345678';  

5. Check whether the creation is successful.

 
 
  1. select user,host from MySQL.user; 

6. Lock the master database table

 
 
  1. MySQL> flush tables with read lock;  

7. Display master database information

Record File and Position, which will be used in slave database settings

 
 
  1. MySQL> show master status;  
  2. +------------------+----------+--------------+------------------+  
  3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
  4. +------------------+----------+--------------+------------------+  
  5. | MySQL-bin.000003 1635708   |              |                  |   
  6. +------------------+----------+--------------+------------------+  
  7. 1 row in set (0.00 sec) 

8. log on to 124 on another terminal and package the master database for data migration.

 
 
  1. # cd /usr/local/webserver/MySQL/  
  2. # tar zcvf data124.tar.gz ./data 

Ii. Set slave Database

1. Transfer and obtain the master database package and unpackage

Log on to the slave Database

 
 
  1. #cd /usr/local/webserver/MySQL/  
  2. #tar zxvf data124.tar.gz  

2. Unlock the master database table

 
 
  1. MySQL> unlock tables;  

3. Modify the slave database my. cnf

 
 
  1. # vi my.cnf   
  2. # slave  
  3. server-id=2 
  4. master-host=192.168.4.191  
  5. master-user=admin 
  6. master-password=12345678 

4. Verify the connection to the master database

 
 
  1. # /usr/local/webserver/MySQL/bin/MySQL -h 192.168.4.191 -u admin -p  

5. Set synchronization on the slave Database

Set MASTER_LOG_FILE to the MASTER database, and MASTER_LOG_POS to the Position of the MASTER database.

 
 
  1. MySQL> slave stop;  
  2. MySQL> change master to master_host='192.168.4.191',master_user='admin', master_password='12345678',  
  3. master_log_file='binlog.000003', master_log_pos=1635708;  
  4. MySQL> slave start; 

6. Start the slave Database Service

 
 
  1. MySQL> slave start; 

7. Test

Create a table named yuhongchun on the iea table in the master database

 
 
  1. MySQL> CREATE TABLE `yuhongchun` (  
  2. `id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,  
  3. `username` VARCHAR( 20 ) NOT NULL ,  
  4. `password` CHAR( 32 ) NOT NULL ,  
  5. `time` DATETIME NOT NULL ,  
  6. `number` FLOAT( 10 ) NOT NULL ,  
  7. `content` TEXT NOT NULL ,  
  8. PRIMARY KEY ( `id` )   
  9. ) ENGINE = MYISAM ; 

The results are immediately displayed in the table, and the master-slave synchronization is successful. For further verification, enter show slave status \ G on the slave database; MySQL> show slave status \ G;

Slave_IO_Running: Yes (the network is normal); Slave_ SQL _Running: Yes (the table structure is normal), which further verifies the correctness of the above process, as shown below (the two parameters must be YES)

On the primary MySQL server, enter MySQL> show full processlist. Observe the status. The correct results should also be shown in:

If an error occurs in the primary MySQL instance, how does one switch from the primary MySQL instance?

1. Ensure that all updates in the relay log have been executed in all slave databases, stop slave io_thread in the slave server, and check with show processlist to check whether the status is Has read all relay log, indicates that the update is complete.

 
 
  1. MySQL> stop slave io_thread;  
  2. Query OK, 0 rows affected (0.01 sec)  
  3.  
  4. MySQL> show processlist;  
  5. +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+  
  6. | Id | User        | Host      | db   | Command | Time | State                                                                 | Info             |  
  7. +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+  
  8. |  5 | system user |           | NULL | Connect | -626 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |  
  9. |  6 | root        | localhost | iea  | Query   |    0 | NULL                                                                  | show processlist |  
  10. +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+  
  11. 2 rows in set (0.00 sec) 

2. Execute the stop slave and reset master commands on the slave server and reset them to the master database.

 
 
  1. MySQL>stop slave;    
  2. Query OK,0 affected (0.00 sec)    
  3. MySQL>reset master;    
  4. Query OK,0 affected (0.00 sec)  

3. Delete the master.info and relay-log.info files in the new master server database directory, otherwise it will start according to the slave server next restart.

MySQLPrecautions before and after the master-slave architecture is put into production:

1. Before configuration, the hostnames of the master and slave must be different to avoid configuration problems. In addition, we strongly recommend that you set the ntpdate time for the two servers. Otherwise, you will have a future) time is troublesome.

2. Because the MySQL database uses the Intranet, the iptables of the two machines can be disabled. An error occurs because iptables is not disabled during the configuration process, this directly causes the admin to be unable to connect to the master database on the slave database;

3. binlog function of primary MySQL must be enabled. Our online server encountered a change order error due to incorrect PHP program. Thanks to the recovery of binlog; however, to enable this function, you must pay attention to the binlog size. Once a Nagios alarm is triggered, the binlog is almost 1 TB;

4. If the following error occurs during synchronization on the slave server:
The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the-replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)

The server-id in my. cnf is the same from the server. Solution: Modify the server-id in my. cnf and restart the database service.

5. In line with prevention measures, if you are the master of MySQL backup, please use shell scripts to back up FTP at the same time as soon as possible, that is, to instantly FTP local backup data to the storage server, facts have proved that backup is a life-saving straw.

Check the backup files of MySQL at ordinary times, especially the actual size and time. If conditions are met, we recommend that you use the backup database files to perform Restoration experiments on other machines, compare with the real running database to see if there is any difference.

6. The master-slave switchover of databases is a dangerous operation, especially for the e-commerce business, which is easy to lose data. This operation is not recommended unless necessary!

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.