Learn a little about mysql dual-host hot backup-quickly understand mysql master-slave, master-master backup original _ MySQL

Learn a little about mysql dual-host hot backup-quickly understand the principle and practice of mysql master/slave backup

Thank you for learning Git in the previous article-quick start with git in 20 minutes. Here we will share the dry goods and briefly introduce the simple practices of mysql dual-host and multi-host remote hot standby.

The concept of dual-machine hot standby is simply to maintain automatic synchronization of the two databases. Operations on any database are automatically applied to another database, and data consistency is always maintained between the two databases. This method has many benefits. 1. you can perform disaster recovery. if one of them is broken, you can switch to another. 2. server load balancer can be used to distribute requests to any server to improve the website throughput. Remote Hot standby is especially suitable for disaster recovery. No more nonsense. We enter the topic directly. We will mainly introduce the following two parts:

I. How mysql backup works

II. backup practices

Let's start.

I am using mysql 5.5.34,

I. How mysql backup works

Simply put, the SQL statements executed on one server are repeated on other servers, so that as long as the first State of the two databases is the same, they can be synchronized all the time.

Of course, this replication and repetition are automatically implemented by mysql. you only need to configure them.

The following figure shows the detailed principle:

There are two servers that demonstrate the process of synchronizing data from a master to a slave server (slave.

This is an example of master-slave replication. Master-master replication only re-executes the preceding example. So we will introduce the principle in this example.

For a mysql server, there are generally two threads responsible for copying and being copied. When replication is enabled.

1. as the Master server, each change will be recorded in the binary log Binarylog. (The slave server reads the log and executes it again on its own .)

2. as the Slave server Slave, it will log on to the master using the account on the master, read the Binarylog of the master, and write the Relaylog to its own relay log, then, your SQL thread reads the relay log and executes it again. The changes on the master server are synchronized to the slave server.

On mysql, you can view the master and slave status of the current server. It is actually the status and location of the current server's Binary (as the master server role. And the replication progress of its RelayLog (as the slave server.

For example, we can view the master status on the master server:

mysql> show master status/G*************************** 1. row ***************************            File: mysql-bin.000014        Position: 107    Binlog_Do_DB: Binlog_Ignore_DB: mysql,information_schema,performance_schema,amh1 row in set (0.00 sec)

Explain the meanings of these rows a little:

1. the first line indicates that the binarylog file name being logged is: mysql-bin.000014.

You can find this file in the mysql data directory:

2. the second line, 107. indicates the current file offset, that is, the location of the record written to the mysql-bin.000014 file.

These two points constitute the status of the master server. These two values are required when configuring slave servers. Tells the slave server where to read data from the master server. (After logging on from the server, find the log file and copy it from the offset .)

3. rows 3 and 4 indicate the databases to be recorded and the databases to be ignored. Only the database that needs to be logged will its changes be written into the mysql-bin.000014 log file. These two parameters will be introduced again later.

We can also view the replication status of the slave server on the slave server.

   1:  mysql> show slave status/G

   2:  *************************** 1. row ***************************

   3:                 Slave_IO_State: Waiting for master to send event

   4:                    Master_Host: 198.**.***.***

   5:                    Master_User: r*******

   6:                    Master_Port: 3306

   7:                  Connect_Retry: 60

   8:                Master_Log_File: mysql-bin.000014

   9:            Read_Master_Log_Pos: 107

  10:                 Relay_Log_File: mysqld-relay-bin.000013

  11:                  Relay_Log_Pos: 253

  12:          Relay_Master_Log_File: mysql-bin.000014

  13:               Slave_IO_Running: Yes

  14:              Slave_SQL_Running: Yes

  15:                Replicate_Do_DB: 

  16:            Replicate_Ignore_DB: mysql,information_schema,amh,performance_schema

  17:             Replicate_Do_Table: 

  18:         Replicate_Ignore_Table: 

  19:        Replicate_Wild_Do_Table: 

  20:    Replicate_Wild_Ignore_Table: 

  21:                     Last_Errno: 0

  22:                     Last_Error: 

  23:                   Skip_Counter: 0

  24:            Exec_Master_Log_Pos: 107

  25:                Relay_Log_Space: 556

  26:                Until_Condition: None

  27:                 Until_Log_File: 

  28:                  Until_Log_Pos: 0

  29:             Master_SSL_Allowed: No

Let's focus on the red circles on the way:

1. Master_host refers to the address of the master server.

2. Master_user refers to the user on the master server for replication. The slave server uses this account to log on to the master service. .

3. Master_log_file is the log file name on the master server.

4. Read_Master_log_pos is the log location of the master server mentioned above. the slave server selects the copied file and location based on these two conditions.

5. Slave_IO_Running: refers to reading the working state of the main server thread from the server. The slave server uses this dedicated thread to link to the master server and copy the logs back.

6. Slave_ SQL _Running: refers to the thread dedicated to SQL execution. It is responsible for executing the copied Relaylog into its own database. Both parameters must be Yes to indicate that the replication works properly.

Other parameters will be introduced later.

II. mysql dual-machine hot standby practice

After learning about the above principles, let's take a look. There are two key points here. to synchronize the database status, you need to have the same initial state, and then configure synchronization to make sense. Of course, you can leave it alone. this is your freedom. We will configure it from the beginning.

Start with server A and configure its database to be synchronized to server B. This is master-slave replication. After that, you can back up each other.

1. Step 1,

Create A user dedicated for backup on:

grant replication slave on *.* to 'repl_user'@'192.***.***.***' identified by 'hj34$%&mnkb';

Replace the IP address with the IP address of Machine B. Only B is allowed to log on. Security.

Username: repl_user

Password: hj34 $ ********* nkb

This will be used on B.

2. enable binarylog of the master server.

Many servers are enabled by default. let's check them here:

Open/etc/my. cnf

Let me explain the configuration in the red box:

You may already have the first three rows.

Binlog-do-db is used to indicate which database changes are recorded in binary logs. Follow the hello database. But I commented it out. This is just a demonstration. You can write multiple rows to focus on multiple databases.

Binlog-ignore-db indicates which databases need to be ignored. I have ignored four other databases here.

The latter two are used for mutual backup in dual-Master (multi-master cycle. Because each database server may insert data into the same table, if the table has an automatically increasing primary key, a primary key conflict will occur on multiple servers. The solution to this problem is to make the auto-incrementing primary keys of each database discontinuous. Yes, I assume that 10 servers may be required for backup in the future, so auto-increment is set to 10. while auto-increment-offset = 1 indicates the serial number of the server. Starting from 1, it cannot exceed auto-increment.

After doing so, the first id I inserted on this server is 1, and the id of the second row is 11, instead of 2.

(Similarly, the first id inserted on the second server is 2, and the second row is 12, which will be introduced later) so that there will be no primary key conflict. We will demonstrate the effect of this id later.

3. obtain the status of the master server and the initial state of synchronization.

Suppose I have these databases on.

If you are newly installed, skip this step without synchronizing the initial state, and check the status of the master server directly later.

Here we assume there is a hello database as the initial state.

Lock the hello database first:

FLUSH TABLES WITH READ LOCK;

Then export the data:

I only need to export the hello database here. if you have multiple databases as the initial state, you need to export all these databases:

View the binary log location of server:

Remember the file name and location, which will be used on the slave server.

The master server has been completed and can be unlocked:

4. set the database to be copied from server B

Open the/etc/my. cnf file of slave server B:

Explain the above content.

The server-id must be different for each server. This may be related to cyclic synchronization. Prevents endless loops.

Replicate-do-db can specify the database to be copied. I have noted it here. Demo.

The database to be excluded during replicate-ignore-db replication. I used this. Apart from several databases in the system, all databases are copied.

The relay_log relay log name. As mentioned above, the replication thread needs to copy the remote changes to the relay log before execution.

Log-slave-updates indicates whether these changes need to be included in their own binarylog after the relay log is executed. When your server B needs to act as the master server of another server, it needs to be enabled. That is, dual-master mutual backup or multi-master cyclic backup. We need it here, so open it.

Save and restart mysql.

5. import the initial state and start synchronization.

Import the hello. SQL exported from server A to B's hello Database. if B does not have A hello database, create one first and then import it:

Create a database:

mysql> create database hello default charset utf8;

Upload hello. SQL to B and import it:

If you have exported multiple databases, you need to upload them all one by one.

Enable synchronization and run the following command on server B:

 CHANGE MASTER TO        MASTER_HOST='192.***.***.***',        MASTER_USER='repl_user',        MASTER_PASSWORD='hj3****',        MASTER_LOG_FILE='mysql-bin.000004',        MASTER_LOG_POS=7145;

I will not explain the above parameters. As mentioned above.

Restart mysql and check whether the slave thread is enabled:

Note that the red boxes in the figure are both "Yes", indicating that the feature is enabled successfully.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

If one of them is "No", it means it is unsuccessful. You need to view the mysql error log. I encountered this problem when I did it for the first time. Sometimes the password is entered incorrectly, and sometimes the 3306 of the firewall is not enabled. The IP address is incorrect. Will cause failure.

We can see the error log: the mysql error log is generally in:

The file name should be your machine name. here we call it host1.err and you replace it with your own name.

Here, master-slave replication is enabled. Let's first experiment.

We add data in database:

I have inserted three data records consecutively in the test table of A's hello database. Note that their auto-growth IDs are and 21 respectively. do you know why. I have already said this before. I don't know how to go back.

Let's see if Database B has these three data items:

Open Database B:

This is already found. The effect is not intuitive.

Do not modify data in B. We then configure replication from B to. If you only need master-slave replication, it will end here. You can skip it later. All the changes in A can be automatically synchronized to B, but the changes to B cannot be synchronized to. Because it is unidirectional. If bidirectional synchronization is required, A copy from B to A is required.

Basically the same as above: Let's briefly introduce:

1. create a user in B;

2. open/etc/my. cnf and enable binarylog of B:

Note the newly added part in the red box.

3. we do not need to export the initial state of B because it was just imported from. Remember its master log status directly:

Remember these two values, which will be used on.

Server B is complete.

4. log on to server. Enable relay:

Note that the content added in the box center is not explained.

5. start synchronization:

The above IP address is the IP address of B, because A regards B as the master. No.

Then restart the mysql service.

Then check whether the slave status is normal:

There are two No in the figure.

Slave_IO_Running: No

Slave_ SQL _Running: No

It indicates that slave is not successful, that is, synchronization from B to A is not successful. Let's check the mysql error log, as mentioned earlier:

Find the machine name. err file and open it:

See the error information in the figure. The relay log file cannot be found.

This is because we changed the relay file name when configuring the relay file of A, but mysql was not synchronized. The solution is simple.

Stop the mysql service first. Find the three files and delete them. You must stop the mysql service first. Otherwise, it still fails. You need to restart the machine. Or manually kill mysqld.

Okay. After mysql is started. Let's check the slave status:

Note the two big Yes values in the figure. Haha.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

It proves that the replication from B to A is successful.

In this case, try to insert several data entries in server B:

I inserted two pieces of data in B. Check their id. Not explained.

Then, log on to database A and check whether Database A has changed.

We can see that it has been automatically synchronized to.

So far, the introduction of active/standby is complete.

The principle is actually very simple, isn't it.

After understanding this principle, it is easy to implement multi-machine cyclic mutual backup. It will not be expanded here.

It took a day to write this blog.

Welcome to my independent blog: http://blog.byneil.com more exchange.

Refer:

1. mysql-keepalived-implements dual-master hot standby read/write separation

Http://gitsea.com/2013/06/16/mysql-keepalived-%E5% AE %9E%E7%8E%B0%E5%8F%8C%E4%B8%BB%E7%83%AD%E5%A4%87%E8%AF%BB%E5%86%99%E5%88%86%E7%A6%BB/

2. MySQL data synchronization [dual master hot standby] http://www.cnblogs.com/zhongweiv/archive/2013/02/01/mysql_replication_circular.html

3. Mysql dual-machine hot standby implementation

Http://yunnick.iteye.com/blog/1845301

4. High Performance Mysql master-slave architecture replication principle and configuration details http://blog.csdn.net/hguisu/article/details/7325124

5. mysql master-based hot standby configuration

BitsCN.com