Enterprise-MySQL master-slave backup

First, MySQL master-slave backup principle

First, 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 the data in two databases consistent. There are several benefits 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.

Second, MySQL master-slave backup working principle

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.

Ii. means of realization

MySQL master-slave synchronization is in MySQL master-slave replication (Master-Slave Replication) is implemented based on theMasteron thebinlogso that it is open;Slavethrough aI/Othread fromMasterRead onbinlog, and then transfer toSlavein the trunk log, and then use theSQLThe thread reads the relay log and applies it to its own database, thus realizing the master-slave data synchronization function.

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

For a MySQL server, there are typically two threads that are responsible for replication and replication. When the switch is turned on (start slave)

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, to read the master Binarylog, and then 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.

Third, the process of replication

The first part of the process is the master record binary log. Master records these changes in two logs before each transaction update data is complete. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed.        After the event is written to the binary log, master notifies the storage engine to commit the transaction. The next step is to slave copy the binary log of master to its own trunk logs. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event.        The I/O thread writes these events to the relay log. The SQL slave thread (SQL slave thread) handles the last step of the process. The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master.         As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal. In addition, there is a worker thread in master: As with other MySQL connections, slave opening a connection in master will also cause master to start a thread. The replication process has a very important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.

Experiment

Experimental environment

Server2 Master

Server3 slave

Master Download Package

Mysql-5.7.17-1.el6.x86_64.rpm-bundle.tar

Installing the Database

[Email protected]~]# yum install-y mysql-community-client-5.7.17-1.el6.x86_64.rpm mysql-community-common-5.7.17-1.el6.x86_64.rpm mysql-community-libs-5.7.17-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm mysql-community-server-5.7.17-1.el6.x86_64.rpm

[email protected] ~]# Yum install-y *

Modify the MySQL configuration file

[Email protected] ~]# VIM/ETC/MY.CNF

Server-id = n

Assign a unique ID number to the server

Log-bin [= filename]

All SQL commands that modify the data (that is, the INSERT, update, and delete commands) are logged in binary format (binary change log, binary update logs). The file name for this log is FILENAME.N or the default HOSTNAME.N, where N is a 6-digit integer (the log file is sequentially numbered).

Open service

Modifying the slave configuration file

Server-id = n

Assign a unique ID number to the server

[Email protected] ~]# VIM/ETC/MY.CNF

Open service

View Password

Security Configuration Wizard

The following method modifies the slave password

mysql> alter user [email protected] identified by ' lh=redhat123 ';

Authorize on Master

mysql> grant replication Slave on * * to [e-mail protected] ' 192.168.122.13 ' identified by ' [email protected] '; user grant Right

Mysql> flush Privileges; Refresh

After master authorization, slave can log in remotely

Master side view

Make the slave connect with master, thus synchronizing:
mysql> Change Master to master_host= ' 192.168.122.12 ', master_user= ' Cara ', master_password= ' lh=redhat123 ', master_ Log_file= ' mysql-bin.000003 ', master_log_pos=1706;

Slave End Mysql-p Login

View

[Email protected] mysql]# pwd
/var/lib/mysql
[email protected] mysql]# cat Master.info

[email protected] mysql]# cat Server3-relay-bin.index

Mysql> show Slave status\g; View slave status

mysql> start slave; Open slave

Create a library Westos, create a table USERTB

Inserting data into a table

Change Password

Delete data from a table

[[email protected] mysql]# Mysqlbinlog mysql-bin.000003 to see what Master did

You can also view the data on master on slave

deep understanding of Replication-Global transaction identifiers (GTID) 1) What is Gtid

GTID (global Transaction ID) is the number of a committed transaction and is a globally unique number. Gtid is actually made up of Uuid+tid. Where UUID is the unique identifier of a MySQL instance, stored in the auto.cnf file in the MySQL data directory. The TID represents the number of transactions that have been committed on the instance and is monotonically incremented as the transaction commits. Here is a concrete form of a gtid: 3e11fa47-71ca-11e1-9e33-c80aa9429562:23.

2) The role of Gtid

According to Gtid, you can tell which instance the transaction was originally committed on.

Gtid's presence facilitates replication's failover.

3) Advantages of Gtid than traditional replication

Simpler implementation of failover, without the need to find log_file and Log_pos before.

More simple to build master-slave replication.

More secure than traditional replication.

Gtid are not empty in succession, so when data conflicts occur in the master and slave libraries, they can be skipped by adding empty objects.

4) How the Gtid works:

When master updates the data, it generates Gtid before the transaction, which is recorded in the Binlog log together.

The slave end of the I/O thread writes the changed binlog to the local relay log.

The SQL thread obtains the Gtid from the relay log and then compares the binlog on the slave side for a record.

If there is a record stating that the Gtid transaction has been executed, slave will ignore it.

If there is no record, slave executes the Gtid transaction from the relay log and logs it to binlog.

In the parsing process will determine whether there is a primary key, if not a two-level index, if not the full scan.

Turn off slave first.

Modifying a configuration file/etc/my.cof

Master

Slave

Restart Service

Record a database, view

Master

Slave

Change Master to master_host= ' 192.168.122.12 ', master_user= ' Cara ', master_password= ' lh=redhat123 ', master_auto_ Position=1;

--------------------------------------------------------------------Master End---------------------------------------------- ----------------------

--------------------------------------------------------------------slave End----------------------------------------------- ---------------------

Enterprise-MySQL master-slave backup