Shulou(Shulou.com)06/01 Report--

I. Principle of mysql master-slave backup

First, the concept of dual hot standby is simply to keep the state of the two databases automatically synchronized. Operations on any database are automatically applied to the other database, always keeping the data in the two databases consistent. There are several advantages to doing so:

1. Disaster preparedness can be done, and one of them can be switched to the other when it breaks down.

2. You can do Load Balancer, you can distribute requests to any one of them, improve website throughput. For remote hot recovery, it is especially suitable for disaster recovery.

Second, mysql master-slave backup working principle

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

II. Implementation

MYSQL master-slave synchronization is implemented on the basis of MySQL master-slave replication, by setting binlog on Master to make it open;Slave reads binlog from Master through an I/O thread, then transfers it to Slave relay log, and then uses SQL thread to read relay log and apply it to its own database, thus realizing master-slave data synchronization function.

There are two servers, demonstrating the process of synchronizing data from a master to a slave.

For a mysql server, there are generally two threads responsible for copying and being copied. When the copy switch is turned on (start slave)

1. As the master server, it records every change it makes to the binary log Binarylog. (The slave server is responsible for reading the log and executing it again on itself.)

2. As a slave server, log in to master with the account on master, read master's Binarylog, and then write it to its relay log, and then its sql thread will be responsible for reading this relay log and executing it again. At this point the changes on the master are synchronized to the slave.

On mysql, you can view the master and slave status of the current server. This is the Binary status and location of the current server (as master). and its RelayLog (as a slave) replication progress.

III. The process of replication

The first part of this process is the master record binary log. Before each transaction updates the data, the master logs these changes in two logs. MySQL writes transactions serially to the binary log, even if the statements in the transaction are interleaved. After writing the event to the binary log is complete, the master notifies the storage engine to commit the transaction. The next step is for slave to copy master's binary log to its own relay log. First, slave starts a worker thread--I/O thread. The I/O thread opens a normal connection on master and starts the binlog dump process. Binlog dump process reads events from master's binary log, and if it has caught up with master, it sleeps and waits for master to generate new events. The I/O thread writes these events to the relay log. SQL slave thread handles the last step of the process. The SQL thread reads events from the relay log and replays the events to update the slave's data to match the data in the master. As long as the thread remains consistent with the I/O thread, relay logs are usually located in the OS cache, so relay logs have minimal overhead. In addition, there is also a worker thread in master: like other MySQL connections, slave opening a connection in master causes master to start a thread. The replication process has one important limitation-replication is serialized on slaves, meaning parallel updates on masters cannot be performed in parallel on slaves.

experimental

experimental environment

server2 master

server3 slave

Master download package

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

install the database

[root@server2~]# 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

[root@server3 ~]# yum install -y *

Modify mysql configuration file

[root@server2 ~]# vim /etc/my.cnf

server-id = n

Assign a unique ID number to the server

log-bin [= filename]

Log all SQL commands that modify data (i.e. INSERT, UPDATE, and Delete commands) in binary format (binary update log). The file name for this log is filename.n or the default hostname.n, where n is a 6-digit integer (log files are numbered sequentially).

opening service

Modify the slave profile

server-id = n

Assign a unique ID number to the server

[root@server3 ~]# vim /etc/my.cnf

opening service

view password

security configuration Wizard

Change the slave password as follows

mysql> alter user root@localhost identified by 'LH=redhat123';

Authorize on master

mysql> grant replication slave on *.* to cara@'192.168.122.13' identified by 'LH@redhat123' ; user authorization

mysql> flush privileges; refresh

After master authorization, slave can log in remotely.

Master View

Connect slave to master to synchronize:

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 mysql -p login

Views

[root@server3 mysql]# pwd

/var/lib/mysql

[root@server3 mysql]# cat master.info

[root@server3 mysql]# cat server3-relay-bin.index

mysql> show slave status\G; View slave status

mysql> start slave; start slave

Create library westos, create table usertb

Insert data into a table

change Password

Delete data from table

[root@server2 mysql]# mysqlbinlog mysql-bin.000003 See what master does

You can also view data on master on slave.

Learn more about replication-Global Transaction Identifier (GTID) 1) What is a GTID

GTID(Global Transaction ID) is the number for a committed transaction and is a globally unique number. GTID is actually composed of UUID+TID. The UUID is the unique identifier of a MySQL instance, stored in the auto.cnf file in the mysql data directory. TID represents the number of transactions committed on the instance and increases monotonically with transaction commit. The following is an embodiment of GTID: 3E11FA47-71CA-11E1-9E33-C80AA9429562:23.

2) The role of GTID

From the GTID, you can know on which instance the transaction was originally committed

The presence of GTID facilitates Failover of Replication

3) Advantages of GTID over traditional replication

A simpler implementation of failover, instead of looking for log_file and log_Pos as before.

Easier to build master-slave copies.

Safer than traditional copying.

GTID is continuous without holes, so when there is a data conflict between the master and slave libraries, you can skip it by adding empty things.

4) How GTID works:

When the master updates data, it generates GTID before the transaction and records it in the binlog.

The slave i/o thread writes the binlog of the change to the local relay log.

The sql thread gets the GTID from the relay log and compares it to the binlog on the slave side for records.

If there is a record, it means that the transaction for this GTID has been executed, and slave ignores it.

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

In the parsing process will determine whether there is a primary key, if not with secondary index, if not with all scan.

Turn off slave first.

Modify the configuration file/etc/my.cof

master

slave

restart the service

Update database, view

master

slave

change master to master_host='192.168.122.12',master_user='cara',master_password='LH=redhat123',master_auto_position=1;

----------------------

-----------------------

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.