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

What is the replication function?

The replication function of MySQL is the function of moving the backed-up data to other servers, which can be used through simple settings.

Master → slave (master-> slave) architecture.

A data change occurs on the master server, and the change content is transmitted to the slave server, and the slave server accepts the change content of the master server.

Reflect the changes to the database.

Advantages of replication featur

1. Improve query performance

In the case of high query processing load, load balancing can be achieved and performance can be improved by adding slave servers.

two。 Achieve high availability

When the master server fails, you can upgrade the slave server to the master server

3. Achieve remote replication

Construction of remote disaster recovery system

4. Used as a backup server

Backup on the slave server without affecting the performance of the master server

For example, the master server can be run without downtime, and the database of the slave server will be backed up after it is stopped.

The composition of the replication function

All change points for the primary server are recorded in the binary log. The SQL text related to the update is recorded in the binary log, as well as metadata such as the date and time when the query was executed. Transaction commit is recorded in binary form at the same time (sync_binlog=1).

The mysqlbinlog command can view the contents of the log. Specify the startup option to output the binary log.

-- log-bin [= file_name]

It is recommended that you put the output path and the data file path on a different hard disk. The extension of the log file is recorded as a hyphen

File_name-bin.001, file_name-bin.002, etc. The log number currently in use will be recorded in the index file (file_name.index)

All change points of the master server are recorded in the log, which is executed after the replication function is started from the slave server and the contents of the binary log are transferred to the slave server.

From files, threads on the server

File

Relay log file: a file that records the change point of the primary server.

Binary log file: a file that records change points from the server. (output only if log-slave-updates is valid)

Master.info: a file that records the necessary information to connect to the master server, reads the starting location of the log, and so on. (MySQL 5.6can be saved in the table from now on)

Relay-log.info: a file that records the location of the relay log execution. (MySQL 5.6can be saved in the table from now on)

Thread

Icano thread: saves the binary log received from the primary server as a relay log

SQL thread: reflect the updates in the relay log to the DB

Types of replication

Binary logs are recorded in different ways

STATEMENT: (SQL)

ROW: OK

MIXED: text and line mixing

Uncertain SQL text-- SQL with results that may change during execution

UUID (), UUID_SHORT ()

USER ()

FOUND_ROWS ()

LOAD_FILE ()

SYSDATE ()

GET_LOCK (), RELEASE_LOCK ()

IS_FREE_LOCK (), IS_USED_LOCK ()

MASTER_POS_WAIT ()

SLEEP ()

VERSION ()

Unsorted LIMIT sentences

UDF, indecisive stored procedures / functions

Query INFORMATION_SCHEMA

READ-COMMITTED/READ-UNCOMMITTED

Synchronization methods are different.

Async: transfer change points asynchronously

Semi-synchronous: transfer the change point synchronously and reflect the time to DB asynchronously

Asynchronous (default)

Asynchronous transfer change point

Advantages: compared with the semi-synchronous mode, the update response of the primary server is faster.

Disadvantages: when the master server fails, some of the contents before the failure may not be delivered to the slave server

Suitable for load balancing

(when the data before the failure needs to be protected, it needs to be corresponding with the application.)

Semi-synchronous (MySQL 5.5Additive function)

Change points are transmitted synchronously and data are reflected to DB asynchronously

Advantages: when the master server fails, the updated data before the failure ensures that it is transferred to the slave server.

Disadvantages: poor update response of the primary server compared to asynchronous mode

Suitable for high availability applications (need to protect updated data before failure)

Whether to use GTID

Do not use GTID traditional replication mode

Use the new appended mode of GTID:MySQL 5.6

Transactions can be easily tracked and compared in a replication environment composed of multiple servers

Transactions have a unique identification ID in the global, which can be recorded in binary logs

There are changes in use and in traditional ways.

Automatically recognize the location at the beginning of replication

When you fail over, you can automatically identify the latest slave server

It is easy to form multi-layer replication.

Advantages of GTID:

The location in the log file can be automatically identified without having to specify it manually.

(failover can be achieved without confirming the location of the log in the event of a primary server failure)

Disadvantages of GTID:

Limiting factors

You can only use InnoDB

Cannot use "CREATE TABLE... SELECT"

Cannot use "CREATE TEMPORARY TABLE" and "DROP TEMPORARY TABLE" in a transaction

Sql_slave_skip_counter is not supported

Binary logs need to be output from the server

Cannot use the filtering function of the replication function. Using the filtering function, ID will appear in the GTID that has never been used before.

Making GTID effective requires all servers to stop and then switch to GTID mode.

MySQL 5.7allows each server to launch GTID in turn.

How to set the copy function (do not use GTID)

1. Set the parameters of the replication function

2. Establish a user for replication on the primary server

3. Back up the master server data and restore it to the slave server. You need to record the name and location of the binary log at the time of backup.

4. Execute CHANGE MASTER TO from the server

5. Execute START SLAVE from the server

Master server: start after setting the write down option

Server-id

Log-bin

Datadir *

Slave server: start after setting the write down option

Server-id

Datadir *

Port *

Socket * (Lunix is OS)

Read_only (recommended setting)

Give the user "REPLICATION SLAVE" permission

Example

CREATE USER 'repl'@'localhost' IDENTIFIED BY' repl'

GRANT REPLICATION SLAVE ON *. * TO 'repl'@'localhost'

Restore backup data from the master server to the slave server (without using GTID)

Restore cold backup data

Using mysqldump for data backup and recovery

Backup

$mysqldump-user=root-password=root-master-data=2\

-- socket=/usr/local/mysql/data/mysql.sock\

-hex-blob-default-character-set=utf8-all-databases\

-- single-transaction > mysql_bkup_dump.sql

Log file name and location where backups need to be recorded

Add: options for mysqldump

-- master-data=2

Record the file name and location of the backup as comments in the backup file.

-- hex-blob

Output data of binary types (BINARY, VARBINARY, BLOG) and BIT types in hexadecimal

-- default-character-set

Set the default characters for mysqldump

Usually consistent with the setting of the system variable default-character-set

-- all-databases

Back up all databases

-- lock-all-tables

Back up all the tables after being locked

-- single-transaction

Consistent backup of InnoDB tables using transaction processing supported by InnoDB

Note: use mysqldump to back up

To ensure the integrity of the data, DDL (backup) is not performed in the backup.

ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE

Quote the description of "--single-transaction" in the manual

"While a-- single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.

4. Execute CHANGE MASTER TO from the server

5. Execute START SLAVE from the server (without using GTID)

Execute CHANGE MASTER TO

Execute START SLAVE

Example

CHANGE MASTER TO MASTER_HOST='localhost'

-> MASTER_USER='repl'

-> MASTER_PASSWORD='repl'

-> MASTER_LOG_FILE='bin.000001'

-> MASTER_LOG_POS=1790

START SLAVE

How to set up the copy function (using GTID)

1. Set the parameters of the replication function

2. Establish a replication user on the primary server

3. Restore the data from the master server to the slave server after backup without using the name and location of the log

4. Execute CHANGE MASTER TO from the server

5. Execute START SLAVE from the server

1. Set the parameters of replication function (using GTID)

Primary server: start after setting the following options

Server-id

Log-bin

Datadir *

Gtid-mode=on

Enforce-gtid-consistency=on

Log-slave-updates

Slave server: start after setting the following options

Server-id

Log-bin

Datadir *

Port *

Socket * (using Lunix)

Read_only (recommended setting)

Gtid-mode=on

Enforce-gtid-consistency=on

Log-slave-updates

Give the user "REPLICATION SLAVE" permission

Example

CREATE USER 'repl'@'localhost' IDENTIFIED BY' repl'

GRANT REPLICATION SLAVE ON *. * TO 'repl'@'localhost'

3. Restore the data from the master server to the slave server after backup (using GTID)

Restore data after cold backup

Delete the auto.cnf under Datadir

(the server-uuid of the master-slave server is consistent.)

Using mysqldump for backup and recovery

Backup example

$mysqldump-user=root-password=root-master-data=2\

-- socket=/usr/local/mysql/data/mysql.sock\

-hex-blob-default-character-set=utf8-all-databases\

-single-transaction-- triggers-- routines-- events > mysql_bkup_dump.sql

4. Execute CHANGE MASTER TO from the server

5. Execute START SLAVE from the server (using GTID)

Example

CHANGE MASTER TO MASTER_HOST='localhost'

-> MASTER_USER='repl'

-> MASTER_PASSWORD='repl'

-> MASTER_AUTO_POSITION=1

START SLAVE

Management log

Use SHOW MASTER STATUS to confirm the current log name and location

Use SHOW MASTER LOGS to list all log file names

Log rotation when using the FLUSH [BINARY] LOGS command or when the MySQL server is restarted

Use PURGE MASTER to delete logs at a specific point in time

Delete all logs using RESET MASTER

Commands for managing replication functions (from the server)

START SLAVE [SLAVE_TYPE] starts the slave server

STOP SLAVE [SLAVE_TYPE] stops the slave server

SHOW SLAVE STATUS confirms the status of the slave server

Confirm the location of the binary log delivered by the Ithumb O thread

Confirm the location of the relay log executed by the SQL thread

STOP SLAVE

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1

STAT SLAVE; ignores the next transaction

When an error occurs, it is used to confirm the status later when a specific transaction needs to be ignored.

This method is not applicable in GTID mode.

Other considerations

MySQL replication itself does not have the function

Failover when highly available

MySQL Utilities of MySQL 5.6provides scripts for automatic failover

Control functions of read-write separation and load balancing

= > Connector/J (Java) or mysqlnd_ms (PHP) can be controlled

Do not perform a lot of update processing at a time

Prevent latency from the server

After the master server transaction is committed, the content of the change is transmitted to the slave server. If the transaction execution time is too long, there will be a delay to the slave server.

Monitor replication function

Use the results of SHOW SLAVE STATUS to monitor the following

Are Icano threads and SQL threads active normally?

I am O thread: Slave_IO_Running

SQL thread: Slave_SQL_Running

Is there a delay in replication?

Is there a delay in replication: Seconds_Behind_Master

The execution location of binary log and relay log, etc.

Delivery status of binary logs: Master_Log_File, Read_Master_Log_Pos

Execution status of relay logs: Relay_Master_Log_File, Exec_Master_Log_Pos

Confirming network latency requires SHOW MASTER STATUS on the primary server to confirm. (compare Master_Log_File and Read_Master_Log_Pos of SHOW SLAVE STATUS)

Monitor slow query log

Confirm the reasons for the delay and the long execution time of the query

Disk remaining space of master-slave server

If the disk space from the server is reduced, the relay log cannot be deleted, and the replication function will stop.

Resource usage of master and slave servers

(CPU, memory, I / O volume, network traffic)

Replication functions constitute high availability

Advantages

The standard functions of MySQL do not require the use of shared disks, software, etc., and are cheap.

Load balancing for high availability and query processing

Shortcoming

Failover needs to be realized by other methods, and there are many considerations when using it.

What method is used to switch over in the event of a failure?

When a failover occurs, the connection of the application needs to be switched

(before MySQL 5.5) there is no crashing security mechanism, and when the slave server fails, you need to reinstall the slave server.

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.