Mysql5.5 master-slave synchronous replication configuration, mysql5.5 master-slave Synchronization

Mysql5.5 master-slave synchronous replication configuration, mysql5.5 master-slave Synchronization

In the previous article "mud: learning the principles of mysql database master-slave synchronous replication", we introduced the basic principles of mysql master-slave replication. In this article, we will test the master-slave synchronous replication function of mysql5.5.

Note that MySQL versions earlier than MySQL and later versions have different parameters in master-slave synchronization. MySQL and later versions do not support parameters such as master-connect-retry. If you add such parameters to the my. cnf file, mysql will report an error at the next restart.

Note: The master database and slave database slave are both centos6.5 64bit, as shown below:

Cat/etc/system-release

Both master and slave databases use mysql database 5.5.39.

Mysql-V

The IP addresses of the master database and slave database slave are as follows:

Master IP: 192.168.1.213 slave IP: 192.168.1.214

We take the ilanni database of the master database as the experimental object, as follows:

According to the article "rotten mud: Learning mysql database master-slave synchronous replication principle", we can see that the master-slave synchronous replication process of mysql is as follows:

1. After the master database runs the SQL statement, the related SQL statements are recorded in the binlog file.

2. Connect to the master database from the database slave, and obtain the binlog file from the master database master, which is stored in the local relay-log file, then, read the pos node at the last synchronization from the master.info file and execute the SQL statement.

The above information shows that the following steps are required for the mysql master-slave synchronous replication experiment:

1. Configure the options for Synchronous replication of the master database master

2. Users who create synchronous replication on the master database and grant permissions

3. master database lock table

4. Record the binlog and pos node of the master database

5. Export the ilanni Database

6. options required for configuring synchronous replication on the slave end

7. Create a database ilanni on the slave database slave and import the backup data.

8. Unlock the master database table

9. Set synchronization between slave database slave and master database

10. enable synchronization on slave database slave

11. view the slave relay-log and master.info

12. Test master-slave Synchronization

1. Configure the options for Synchronous replication of the master database master

For mysql master-slave configuration, you only need to modify the my. cnf file. As follows:

Cat/etc/my. cnf | grep-v ^ # | grep-v ^ $

Log-bin = mysql-bin

Max_binlog_size = 500 M

Server-id = 1

Binlog-do-db = ilanni

Binlog-ignore-db = mysql

Here, log-bin = mysql-bin indicates that mysql binary logs are enabled. This option must be enabled, otherwise the mysql Master/Slave will not take effect.

Max_binlog_size = m indicates the maximum size of each binlog file. When the size of this file is m, a new log file is automatically generated. Note: A record is not written in two log files, so sometimes the log file size exceeds this limit.

Server-id = 1 indicates the mysql server ID, which must be unique in the Master/Slave database. The default value is 1. The ID can be customized but must be a number.

Binlog-do-db = ilanni indicates the name of the database to be synchronized. If multiple databases exist, write another row in this format.

Binlog-ignore-db = mysql indicates the name of the database that does not need to be synchronized. If multiple databases exist, write another row in this format.

NOTE: If binlog-do-db and binlog-ignore-db are not added, the entire mysql database is synchronized by default.

2. Users who create synchronous replication on the master database and grant permissions

Log on to the master, create the database user ilanni, and grant the replication slave permission. As follows:

Grant replication slave on *. * to 'ilanni' @ '%' identified by '20140901 ';

Select user, repl_slave_priv from user where user = 'ilanni ';

We can see that the current database user ilanni is granted the replication slave permission. In the user table, the repl_slave_priv field is Y.

Note: replication slave permission: only users with this permission can view the permissions for reading binary logs from the server slave and the master server.

After authorization, we need to test on slave whether ilanni users can connect to the master. As follows:

Ifconfig eth0 | grep "inet addr" | awk '{print $2}' | cut-d:-f2

Mysql-h 192.168.1.213-uilanni-p123456

We can see that the slave server slave can connect to the master server normally.

Iii. master database lock table

First, lock the tables of the master database to prevent data from being written, resulting in inconsistency between the master and slave databases. Use the following command to lock the table:

Flush tables with read lock;

In this way, the master database can only be read, but cannot be written to Data. As follows:

We can see that the master database cannot write data any more.

Note: Do not exit the terminal of the lock table. Otherwise, the lock will become invalid.

4. Record the binlog file name and pos location node of the master database master

Why do we need to record the binlog file name and pos location node of the master database master at this time?

Because when we migrate or import the database of the master database to the slave database slave, We will synchronize the slave database slave from the pos location node of the binlog file with the master database.

View the binlog file name and pos node of the master database master, as follows:

Show master status;

Show master status \ G;

/Usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/data/mysql-bin.000003

Through the above two, we can see that the current main database binlog is mysql-bin.000003, pos position node is 1616.

5. Export the ilanni Database

There are three methods to synchronize data for the first time between the slave database slave and the master database.

The first method is to use tar to directly package the master database after the master database locks the table, and then use scp or rsync to get the package file to the slave database slave. This situation is generally applicable when the website or business is initializing, or when the database is larger than GB.

The second method is to use the mysqldump command to export the database directly after the master database locks the table, and then restore the database from the slave database. This method is common, so we generally use this method.

Note that the above two methods are required after the master database master locks the table.

In the third way, we don't need to do anything else. You only need to enable synchronous replication. However, the premise is that mysql's binlog must be complete, and that the binlog must also exist when the data is created. In addition, you must start from the initial binlog. Therefore, this method is generally not used.

Next we use the second method to export the ilanni database, as shown below:

Mysqldump-uroot-p123456 ilanni> ilanni. SQL

Ll-h | grep ilanni. SQL

Now we can copy the backup file ilanni. SQL OF THE ilanni database to the slave database slave through the scp command, as shown below:

Scp ilanni. root@192.168.1.214:/root

6. Configure the options required for Synchronous replication from the slave database slave

From the slave database, you only need to modify the server-id value to be unique in the my. cnf file. As follows:

Cat/etc/my. cnf | grep-v ^ # | grep-v ^ $

7. Create a database ilanni on the slave database slave and import the backup data

In step 5, we have copied the ilanni backup file to the slave database through the scp command. Now we need to import the backup data on the slave database slave. As follows:

Create database ilanni;

Mysql-uroot-p123456 ilanni </root/ilanni. SQL

We can see that the ilanni database has been fully restored from the slave database.

8. Unlock the master database master table

After the backup is created and imported from the ilanni database of the slave database, we can unlock the master database master table and run the following command:

Unlock tables;

9. Set synchronization between slave database slave and master database

In step 7, we have recovered the data of the ilanni database. Let's start to set synchronization between the slave database slave and the master database. Run the following command:

Change master to master_host = '192. 168.1.213 ', master_user = 'ilance', master_password = '000000', master_log_file = 'mysql-bin.000003', master_log_pos = 192, master_connect_retry = 123456;

Master_host indicates the IP address of the master database.

Master_user indicates the user that can be synchronized on the master database master.

Maser_password indicates the password for synchronizing users

Master_log_file indicates which binlog file to start Synchronization

Master_log_pos indicates that synchronization starts from the position of the pos node of the binlog file.

Master_connect_retry indicates the synchronization cycle between the slave database slave and the master database master. The default value is 60 s.

Note: master_log_file and master_log_pos are the binlog file names and pos location nodes recorded in Step 4.

10. enable synchronization on slave database slave

After the preceding configuration is complete, enable master-slave synchronization. To enable master-slave synchronization, We need to enable it on the slave database. It is very easy to enable master-slave synchronization. You only need one command, as shown below:

Start slave;

The master-slave synchronous replication of mysql is enabled. Run the following command to check whether the synchronization is normal. As follows:

Show slave status \ G;

Check whether the synchronization is mainly to check the Slave_IO_Running and Slave_ SQL _Running options. If synchronization is normal, the two options must be YES at the same time.

If Slave_IO_Running is NO, the network between the slave database and the master database may be disconnected.

If Slave_ SQL _Running is NO, it is likely that the data in the slave database is inconsistent with that in the master database.

We can see that the current Slave_IO_Running and Slave_ SQL _Running are both YES. It indicates that the master-slave synchronization is normal now.

In addition, we can also see the binlog file names and pos location nodes at the beginning of synchronization between the slave and master databases.

11. view the slave relay-log and master.info

Now let's check the slave database relay-log and master.info. First, let's look at the relay-log information, as shown below:

Mysqlbinlog ilanni-relay-bin.000002 | more

In this figure, we can see in the relay-log that the binlog File Name of the master database and the pos location node at the time of synchronization replication are synchronized from the database slave.

Now let's check master.info, as shown below:

Cat master.info | more

The file stores the information related to the master Database Synchronization from the slave database slave: IP address, user, password, binlog file name, pos location node, and synchronization cycle.

12. Test master-slave Synchronization

Now let's test the master-slave synchronization of mysql. Insert and delete a data entry to the ilanni database on the master database. As follows:

Insert into ilannitable values (6 );

Delete from ilannitable where id = 2;

Select id from ilannitable;

At this time, the ilanni database of the master database has only five pieces of data: 1, 3, 4, 5, and 6.

Now we log on to the slave database slave to check the ilanni database. As follows:

We can see that the ilanni database in the slave database is consistent with the ilanni database in the master database. It indicates that the Master/Slave node has been synchronized and copied successfully.

Now let's check the master.info file, as shown below:

Mysqlbinlog/usr/local/mysql/data/mysql-bin.000003

Cat master.info | more

Through, the pos location node of the binlog file of the master database is compared with master.info. We can find that the master.info file records the binlog file name and pos position node of mysql during synchronous replication.

So far, the experiment on mysql master-slave synchronous replication has come to an end.

Welcome to my personal website