MySQL high-availability replication principle and master-slave instance test analysis

First, MySQL replication introduction

The MySQL replication feature allows you to copy data from the master data to multiple slave servers. By default, with asynchronous transfer, data replication can take place in a variety of different network environments. Master-slave replication technology has been widely used in enterprise production, it avoids the single point of failure of the database, when one server down, other servers can provide stable and reliable data services.

1 MySQL Replication principle

The MySQL replication feature distributes data across multiple systems by replicating data from one of MySQL's servers (master) to other servers (slaves). One server acts as the primary server during replication, while one or more other servers act as slave servers. The master server writes the update to the binary log file, receives the updated log content from the server, executes the log content in the local machine again, thus realizes the master-slave data replication. The primary server also maintains an index of the file to track the log loop when it writes to the log, and when one connects to the primary server from the server, it notifies the master and slave server of the last successful update read in the log, receives any updates from the server at that point in time, and then blocks and waits for the primary server to notify the new update.

2 MySQL Replication benefits

High performance: By assigning requests to multiple different slave servers to improve performance and speed, in this environment, all writes must be performed on the primary server, and read operations can be evenly distributed across multiple slave servers.

Data security: Data through the master-slave replication, the primary server to replicate the data of one or more copies, that is, to achieve a secure data backup

Data sharing: For large enterprises, branches in each region also need to share the data resources of the Headquarters, only need to use the master-slave replication to achieve data sharing, and can only be replicated when needed.

3 MySQL Copy format

Statement-based replication: An SQL statement executed on the primary server that executes the same statement from the server. MySQL defaults to statement-based replication with high efficiency.

Row-based replication: Copy the changed content past, rather than executing the command from the server again.

Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

4 MySQL Replication steps

As a whole, there are 3 steps to replication:

(1) Master changes the record to binary log (binary log event, binary logs events);

(2) Slave copies the binary log events of master to its trunk logs (relay log);

(3) Slave redo the event in the trunk log and change the data to reflect its own.

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M01/8D/6A/wKioL1ib4BPx8u6UAABUrjWR9Ok685.gif "title=" 0_ 1330439010p7li.gif "alt=" Wkiol1ib4bpx8u6uaaburjwr9ok685.gif "/>

5 master-Slave copy form

One Master one from

Primary master replication

One master multiple reads from the---extended system, because reads are read from the library;

Multi-master One support from---5.7

Replication---at the same level

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M00/8D/6D/wKioL1icI-qDtQ7hAAEgc_LqBSc851.png "title=" 5389100.png "alt=" Wkiol1ici-qdtq7haaegc_lqbsc851.png "/>

6 semi-synchronous replication

5.5 version integrated into MySQL, in the form of plug-ins exist, need to be installed separately

Ensure that Binlog is transferred to at least one slave library after the transaction commits

There is no guarantee that the Binlog of this transaction is done from the library

Performance is reduced and response time is longer

Network exceptions or downtime from the library, card Master Main library, until timeout or recovery from library

Semi-synchronous schematic diagram:

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M02/8D/6D/wKioL1icIcKgnku3AAGQYSjSMrs836.png "title=" 152. PNG "alt=" Wkiol1icickgnku3aagqysjsmrs836.png "/>

Second, MySQL master-slave Replication instance parsing

First, understand the main points of the master-slave replication deployment of databases

>> Open the binary log on the primary server and set a unique server ID number, set to restart after MySQL is in effect.

>> set a unique server ID number on all the servers, and the restart takes effect.

>> create an account on the primary server that lets you read log files from the server, or use a consolidated account.

>> before copying data, you need to record the location tag of the binary log on the primary server.

The following is a detailed analysis of the entire master-slave copy process

1 Primary server Settings data replication environment

Create a database and data table on a database that has been installed or used to show how to synchronize data with existing data

[Email protected] ~]# mysql-u root-p

mysql> CREATE DATABASE hr;

mysql> use HR;

Mysql> CREATE TABLE Employees (

-employee_id INT not NULL auto_increment,

, name char (a) is not NULL,

E_Mail varchar (50),

PRIMARY KEY (employee_id));

Mysql> INSERT into employees values

(1, ' TOM ', ' [email protected] '),

(2, ' Jerry ', ' [email protected] ');

Mysql> exit

To open the binary log on the primary server and set the server number, the server unique number must be an integer between 1 and 232-1, set according to your actual situation. These settings require that you close the MySQL database and edit the MY.CNF or My.ini file, and add the appropriate configuration options in the [Mysqld] Settings section.

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

[Mysqld]

Log-bin=jacob-bin #启用二进制日志 and set the binary log file prefix

Server-id=25 #设置服务器编号

[Email protected] ~]# service mysqld restart

[[Email protected] ~]# service iptables stop

2 Setting up the data replication environment from the server

Set on a database machine that has been installed or used, the server ID must be set to a unique number in the cluster. For the server, the binary log function does not need to be opened, of course, according to business needs can also be opened, such as primary master replication in the main from the need to open each other.

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

[Mysqld]

Server-id=24

[Email protected] ~]# service mysqld restart

[[Email protected] ~]# service iptables stop

3 Create a data copy account

At least one account must be available on the primary server for use from a server connection. This account must have replication slave permissions, you can create different accounts and passwords for different slave servers, or you can use a unified account and password. Use the Create USER statement for creating users and using the GRANT statement to assign rights to the account.

[Email protected] ~]# mysql-u root-p

mysql> CREATE USER ' slave ' @ '% ' identified by ' 123456 ';

Mysql> GRANT REPLICATION SLAVE on * * to ' SLAVE ' @ '% ';

Mysql> exit

4 Read primary server binary log information

Before you perform master-slave data replication, first look at the basic information of the primary server's binary log files, and the primary server binary file name and the current logging location are required for the settings from the server.

[Email protected] ~]# mysql-u root-p

Mysql> FLUSH TABLES with READ LOCK;

mysql> SHOW MASTER STATUS;

+--------------------------------+------------+---------------------+-------------------------+-------

| file| Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+--------------------------------+------------+---------------------+-------------------------+-------

| jacob-log.000001|                                1226 |                                | |

+--------------------------------+------------+---------------------+--------------------------+------

Mysql> UNLOCK TABLES;

5 Back up the existing database and import the slave server

If a data resource already exists in the database system prior to using data replication, the data is backed up using the Mysqldump tool, and the tool is used on the primary server to restore data from the server after the data is backed up. When the data reaches the master-slave, you can use the data replication feature to automate this synchronization.

[Email protected] ~]# mysqldump--all-databases--lock-all-tables >/tmp/dbdump.sql

[Email protected] ~]# Scp/tmp/dbdump.sql 192.168.1.8:/tmp/

[Email protected] ~]# Mysql-u root-p < Dbdump.sql

6 Configuring data replication from the server connection master server

The key operation of data replication is to configure the server to connect to the primary server for data replication and to configure all the necessary information to establish a network connection from the server. Use the Change master to statement to complete the work, master_host specify the host name or IP address of the primary server, master_user the account name created on the primary server with the copy permission, aster_password the account's password, MASTER_LOG_FILE Specifies the primary server binary log file name, Master_log_pos the location of the current record in the primary server binary log. Start SLAVE turns on the slave server function for the master connection, show SLAVE status View from the server state.

[Email protected] ~]# mysql-u root-p

Mysql> Change MASTER to

Master_host= ' 192.168.1.7 ',

Master_user= ' slave ',

-master_password= ' 123456 ',

Master_log_file= ' jacob-log.000001 ',

master_log_pos=1226;

Mysql> START SLAVE;

mysql> SHOW SLAVE status\g;

7 Data Synchronization Test verification

Create a database and tables on the primary database, and then go from the server to see if they are synchronized.

[Email protected] ~]# mysql-u root-p

mysql> CREATE DATABASE test2;

mysql> use test2;

Mysql> CREATE TABLE T_table (

->name Char (20),

-Age int,

-Note varchar (50));

Mysql> INSERT into t_table values

(' Linda ', ' Beijing '),

(' Jerry ', ' Shanghai '),;

Mysql> exit

[Email protected] ~]# mysql-u root-p

Mysql> select * from Test2.t_table;

Mysql> exit

Three, the problem of master-slave replication and the solution

Problems with MySQL master-slave replication:

Data may be lost after a main library outage

There is only one SQL Thread from the library, the main library write pressure is high, replication is likely to delay

Workaround:

Semi-synchronous Replication---resolve data loss issues

Parallel replication----Solve problems with replication delays from libraries

Replication error Handling:

Common: 1062 (primary key conflict), 1032 (record does not exist)

Workaround:

Manual processing

Skip replication Error settings: Set global sql_slave_skip_counter=1

This article is from the "Practical Linux knowledge and Skills sharing" blog, please be sure to keep this source http://superleedo.blog.51cto.com/12164670/1896421

MySQL high-availability replication principle and master-slave instance test analysis