Learn a little MYSQL dual-machine hot standby--mysql master-slave, main master backup principle and practice

Simple introduction MySQL dual-machine, multi-machine remote hot standby simple principle of actual combat.

The concept of dual-machine hot standby simply say, is to maintain the status of two databases automatically synchronized. Operations on any one database are automatically applied to another database, keeping two of the database data consistent. There are many advantages to doing so. 1. A disaster can be made, one of which is broken can be switched to another.  2. Load balancing can be done to spread the request to any of the platforms to improve website throughput. For off-site hot preparation, especially for disaster preparedness. Don't say much nonsense. We go directly to the subject. We will mainly introduce two parts of the content:

One, how MySQL backup works

Second, backup combat

Let's start.

I'm using MySQL 5.5.34,

One, how MySQL backup works

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

Of course, this replication and duplication are automatically implemented by MySQL, we only need to configure.

We'll cover the details of the principle in more detail, which has a picture:

Has two servers that demonstrate the process of synchronizing data from a primary server (master) to a Slave server (slave).

This is an example of a master-slave copy. The master-master replicates with each other just to do the above example again. So let's take this example to introduce the principle.

For a MySQL server, there are typically two threads that are responsible for replication and replication. After you turn on replication.

1. As master server master, each of its changes will be recorded in the binary log binarylog. (It is the responsibility of the server to read the log and then execute it again on its own.) ）

2. As from the server slave, will use the master account to log on to master, read Master's Binarylog, write to their own trunk log relaylog, and then their own SQL thread will be responsible for reading this trunk log, and execute it again. The changes on the master server are synchronized to the slave server.

On MySQL You can view the current server's master, from the state. is actually the Binary (as the primary server role) status and location of the current server. and its relaylog (as a slave server) to the replication progress.

For example, we view the primary state on the primary server:

Mysql> Show Master status\g*************************** 1. Row ***************************            file:mysql-bin.000014        position:107 in    Set (0.00 sec)

Explain the meaning of these lines a little bit:

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

We can find this file in the MySQL data directory:

2. The second line, 107. Represents the current file offset, which is the record location written to the mysql-bin.000014 file.

These two points constitute the state of the primary server. These two values are required when configuring the slave server. Tells the server from where to read the data from the primary server. (After the server logs in, locate the log file and start copying after that offset.) ）

3. The third row, and line fourth, represent the database that needs to be logged and the database that needs to be ignored.  Only the database that needs to be logged is changed to be written to the mysql-bin.000014 log file. These two parameters are described again later.

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

 Mysql> show Slave status\g *************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_h ost:198.**.***.*** master_user:r******* master_port:3306 Connect_re Try:60 master_log_file:mysql-bin.000014 read_master_log_pos:107 Relay_log_file: mysqld-relay-bin.000013 relay_log_pos:253 relay_master_log_file:mysql-bin.000014 Sl Ave_io_running:yes Slave_sql_running:yes Replicate_Do_DB:Replicate_Ignore_DB:mysql , Information_schema,amh,performance_schema Replicate_Do_Table:Replicate_Ignore_Table:Replicate_W                  ild_do_table:replicate_wild_ignore_table:last_errno:0 Last_error: skip_counter:0 exec_master_log_pos:107 Relay_log_space: 556 Until_condition:none until_log_file:until_log_pos:0 Maste R_ssl_allowed:no

We're still going to focus on explaining the red circle part on the way:

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

2. Master_user refers to the user that is used to replicate on the primary server. This account is used by the server to log in to the Master service. For replication.

3. Master_log_file is the log file name on the primary server that was mentioned earlier.

4. Read_master_log_pos is the log location of the primary server mentioned earlier, and the server selects the copied files and locations based on these two conditions.

5. Slave_io_running: Refers to the thread working state that is responsible for reading the primary server from the server. From the server use this dedicated thread subroutines to the master server and copy the logs back.

6. Slave_sql_running: Refers to a thread that executes SQL exclusively. It is responsible for executing the copied relaylog into its own database. Both of these parameters must be yes to indicate that replication is working correctly.

The other parameters are described later.

Second, MySQL dual machine hot standby combat

After understanding the above principles, we come to the actual combat. There are two key points to synchronizing the database state, requiring the same initial state, and then configuring synchronization to be meaningful. Of course you can not first state, this is your freedom. We'll configure it again from the beginning.

We start with a server, configure its database to synchronize to B. This is the master-copy from. After that, you can back up each other.

1, the first step,

Create a user dedicated to the backup on a:

Grant Replication Slave on * * to ' repl_user ' @ ' 192.***.***.*** ' identified by ' hj34$%&mnkb ';

Change the IP address above to the IP address of machine B. Only b logon is allowed. Safety.

User name is: repl_user

The password is: hj34$********nkb

This will be used on the B top.

2. Turn on the binarylog of the master server.

Many servers are turned on by default, so let's check here:

Open/ETC/MY.CNF

Let me explain the configuration in the red box:

Three lines ahead, you may already have.

Binlog-do-db is used to indicate which database changes are only recorded in the binary log. You can write a note about the Hello database. But I commented it out. Just show it. You can write more than one line, indicating that you are interested in multiple databases.

BINLOG-IGNORE-DB indicates which databases need to be ignored. I have ignored the other 4 databases here.

The latter two are used to back up each other in a dual-master (multi-master loop).  Because each database server can insert data in the same table, if the table has an autogrow primary key, a primary key conflict occurs on multiple servers.  The solution to this problem is to make each database's self-increment primary key discontinuous.   Say yes, I assume that 10 servers may be required for backup in the future, so auto-increment-increment is set to 10. The auto-increment-offset=1 represents the serial number of this server. Starting from 1, no more than auto-increment-increment.

After doing this, the first ID I inserted on this server is 1, and the second row ID is 11, not 2.

(Similarly, the first ID inserted on the second server is 2, the second line is 12, which is explained later) so there is no primary key conflict. We'll show you the effect of this ID later.

3. Get the primary server status, and synchronize the initial state.

Suppose I now have these databases on a.

If you are new to the installation, then you do not need to synchronize the initial state, skip this step, directly to the back to view the primary server status.

Here we assume that 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 initial state, you need to export all these databases:

Then look at the binary log location of a server:

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

The master server is finished and can be unlocked:

4. Set up a database to replicate from server B

Open the/etc/my.cnf file from server B:

Explain what's above.

Server-id must ensure that each server is different. This may be related to cyclic synchronization. Prevent entering the dead loop.

REPLICATE-DO-DB can specify the database that needs to be copied, I've dropped it here. Demo.

REPLICATE-IGNORE-DB copy of the database that needs to be excluded, I used this. All databases are replicated except for a few databases in the system.

Relay_log the name of the trunk log. As mentioned earlier, the replication thread needs to copy the remote changes to this trunk log before executing.

Log-slave-updates means that these changes need to be counted into their own binarylog after the relay log is executed.  When your B server needs to be a master server for another server, it needs to be opened. is the dual-master backup to each other, or multi-master loop backup. We need it here, so open it.

Save and restart MySQL.

5. Import the initial state and start syncing.

Import the hello.sql that you just exported from a server into the Hello database of B, if B does not now have a Hello database, create one before importing:

To create a database:

Create DATABASE Hello default charset UTF8;

Upload the hello.sql to B, and then import:

If you have just exported multiple databases, you need to upload them all to import.

Turn on synchronization and execute 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. I said it earlier.

Restart MySQL, and then check that the slave thread is turned on:

Note that the red box in the figure, two are yes, indicates the success of the Open.

Slave_io_running:yes

Slave_sql_running:yes

If one of them is no, it means that it is unsuccessful. You need to check the MySQL error log. I ran into this problem the first time I did it. Sometimes the password is wrong, sometimes 3306 of the firewall is not open. The IP address is wrong, and so on. will lead to failure.

We look at the error log: MySQL error log is generally in:

The file name should be the name of your machine, and here I am called Host1.err you change to your own.

Here the master-from copy has been opened. Let's experiment first.

We're going to add data to the A database:

I inserted 3 data consecutively in the test table of A's Hello database, and looked at their self-growth ID, respectively, 1,11,21. Know why this is. Already said before, do not know to go back to see.

Let's go see if the B database has these three data:

Open the database for B:

The discovery is already here. The effect here is not intuitive.

Do not modify the data in B at this time.  We then configure replication from B to a. If you only need a master-slave copy, it ends here. Can not look at the back. All changes in a can be automatically synchronized to B, but modifications to B cannot be synchronized to a. Because it is one-way. If you need two-way synchronization, you need to do another copy from B to a.

Basically the same as above: let's simply introduce:

1. Create a user in B;

2. Open/etc/my.cnf and turn on the binarylog of B:

Note the newly added section in the Red box.

3. We do not need to export the initial state of B, because it has just come from a guide. Directly remember its master log status:

Remember these two values, and so on a will be used.

b server is set up.

4. Log on to the a server. To turn on the trunk:

Note the section of the box center is added, not explained.

5. Start the synchronization:

The IP address above is the IP address of B, because a puts B as master. Don't explain it.

Then restart the MySQL service.

Then check to see if the slave status is normal:

There are two no in the figure.

Slave_io_running:no

Slave_sql_running:no

Note that slave did not succeed, that is, the synchronization from B to a was unsuccessful. Let's go to the MySQL error log, where we said before:

To find the machine name. err file, open the look:

Look at the error message in the picture. said the trunk log file could not be found.

This is because we changed the relay file name when we configured the a relay, but MySQL was not synchronized. The solution is simple.

Stop the MySQL service first. Find these three files and delete them. Be sure to stop the MySQL service first. Otherwise it will not be successful. You need to restart the machine. or manually kill Mysqld.

OK, after starting MySQL. We're here to check the slave status:

Notice the two big yes in the picture. Ha ha.

Slave_io_running:yes

Slave_sql_running:yes

Proving that replication from B to A has also been successful.

At this point we go to the B server to insert a few data to try:

I inserted two data in B.  Take a look at their ID. Not explained.

Then we, log in to A to see, a database has changed.

You can see that it has been automatically synced to a.

At this point, AB twin-master mutual hot preparation is finished.

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

Understanding this principle, multi-machine loop mutual preparation is simple. It's not going to start here anymore.

Spent a day writing this blog, we want to top AH.

You are welcome to visit my Independent blog: http://blog.byneil.com a lot of exchanges.

Reference:

1. mysql-keepalived-realize 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 detailed http://blog.csdn.net/hguisu/article/details/7325124

5. mysql Master-master-based dual-machine hot standby configuration
Byneil
Byneil.com

from Blog by Neil, post learn a little MySQL dual-machine hot backup--mysql master-slave, main master backup principle and practice

Original from Blog by Neil, post learn a little MySQL dual-machine hot backup--mysql master-slave, Master Master backup principle and practice reprint please indicate the source. The Site reserves all rights

Learn a little MYSQL dual-machine hot standby--mysql master-slave, main master backup principle and practice