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

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:

 
 

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

① Create a data table as a MySQL user account:

 
 

  
  
/usr/local/webserver/MySQL/bin/MySQL_install_db 
  
  
--basedir=/usr/local/webserver/MySQL 
  
  
--datadir=/usr/local/webserver/MySQL/data 
  
  
--user=MySQL 
 
 

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

 
 

  
  
/bin/sh /usr/local/webserver/MySQL/bin/MySQLd_safe 
  
  
--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

 
 

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

2. Start the master database to take effect

 
 

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

3. log on to the master database

 
 

  
  
#/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.

 
 

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

5. Check whether the creation is successful.

 
 

  
  
select user,host from MySQL.user; 
 
 

6. Lock the master database table

 
 

  
  
MySQL> flush tables with read lock;  
 
 

7. Display master database information

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

 
 

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

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

 
 

  
  
# cd /usr/local/webserver/MySQL/  
  
  
# 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

 
 

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

2. Unlock the master database table

 
 

  
  
MySQL> unlock tables;  
 
 

3. Modify the slave database my. cnf

 
 

  
  
# vi my.cnf   
  
  
# slave  
  
  
server-id=2 
  
  
master-host=192.168.4.191  
  
  
master-user=admin 
  
  
master-password=12345678 
 
 

4. Verify the connection to the master database

 
 

  
  
# /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.

 
 

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

6. Start the slave Database Service

 
 

  
  
MySQL> slave start; 
 
 

7. Test

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

 
 

  
  
MySQL> CREATE TABLE `yuhongchun` (  
  
  
`id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,  
  
  
`username` VARCHAR( 20 ) NOT NULL ,  
  
  
`password` CHAR( 32 ) NOT NULL ,  
  
  
`time` DATETIME NOT NULL ,  
  
  
`number` FLOAT( 10 ) NOT NULL ,  
  
  
`content` TEXT NOT NULL ,  
  
  
PRIMARY KEY ( `id` )   
  
  
) 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.

 
 

  
  
MySQL> stop slave io_thread;  
  
  
Query OK, 0 rows affected (0.01 sec)  
  
  
 
  
  
MySQL> show processlist;  
  
  
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+  
  
  
| Id | User        | Host      | db   | Command | Time | State                                                                 | Info             |  
  
  
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+  
  
  
|  5 | system user |           | NULL | Connect | -626 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |  
  
  
|  6 | root        | localhost | iea  | Query   |    0 | NULL                                                                  | show processlist |  
  
  
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+  
  
  
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.

 
 

  
  
MySQL>stop slave;    
  
  
Query OK,0 affected (0.00 sec)    
  
  
MySQL>reset master;    
  
  
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!