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

Preface

The need for real-time database backup is very common. MySQL itself provides Replication mechanism. The official introduction is as follows:

MySQL Replication can synchronize data from one master database to one or more slave databases. And this synchronization process works asynchronously by default, and there is no need to maintain a real-time connection to the master-slave database (that is, the connection is allowed to be interrupted). It also allows custom configuration of databases and tables that need to be synchronized.

The advantages and application scenarios of MySQL Replication are as follows:

1. Achieve load balancing and read-write separation through MySQL Replication (only the master database is updated and only read from the slave database) to improve database performance.

2. Realize the real-time backup of data through MySQL Replication to ensure the security of data.

3. Realize the offline analysis of the data through MySQL Replication (the master database generates data, and the analysis and calculation of the slave database does not affect the performance of the master database).

4. Data distribution.

For the complete official documentation of MySQL Replication, please refer to: https://dev.mysql.com/doc/refman/5.7/en/replication.html

working principle

1111

1. All database change events in Master are written to the Binary Log file.

2. When the "SLAVE START" command is executed in Slave, open the Slave I Thread O Thread and connect the Master

3. Master detects the connection of Slave I Thread O Thread, and opens Log Jump Thread to respond.

4. Master Binary Log is transferred to Slave Relay Log via Master Log Jump Thread and Slave I _ Thread.

5. Slave SQL Thread restores Relay Log to data and completes synchronization

Note: you can use the "SHOW PROCESSLIST" command to view the operation of the corresponding threads in Master and Slave.

Configure Master

Enable Binary Log and set ServerID,ServerID must be unique. Values range from 1 to 232-1

[mysqld] # enable Binary Loglog-bin=mysql-bin# Settings Global IDserver-id=1# to specify databases to be synchronized (because database names may contain commas, multiple databases must be repeatedly configured and cannot be separated by commas) binlog-do-db=database_name# specifies databases that prohibit synchronization binlog-ignore-db=database_name# specifies Binary Log format binlog_format=MIXED

Create a synchronization account

Because each Slave needs to connect to the primary database with an account password, an account must be provided on the primary database. It is recommended that you use a separate account and only authorize data synchronization permissions.

CREATE USER 'repl'@'%.example.com' IDENTIFIED BY' password';GRANT REPLICATION SLAVE ON *. * TO 'repl'@'%.example.com'

Get Binary Log information

You need to input some information of Binary Log when starting Slave O Thread, so you need to obtain the information of Binary Log:

SHOW MASTER STATUS

Use the "SHOW MASTER STATUS" command to get Binary Log information and record the File and Position field values.

Ensure that the data of Master and Slave are consistent before synchronization

Before Slave starts the Master O Thread, it is necessary to ensure that the data of the Master is consistent with that of the Slave, so lock the Master (prevent data changes), manually synchronize and ensure that the data is consistent before unlocking.

FLUSH TABLES WITH READ LOCK

Manual data synchronization related operations.

UNLOCK TABLES

Configure Slave

Set ServerID, but you do not need to enable BinLog:

[mysqld] # set global IDserver-id=2# specify synchronized database replicate-do-db=database_name# specify database replicate_ignore_db=database_name that forbids synchronization

To set the Master information, execute the following command:

Mysql > CHANGE MASTER TO-> MASTER_HOST='master_host_name',-> MASTER_PORT='master_host_port',-> MASTER_USER='replication_user_name',-> MASTER_PASSWORD='replication_password',-> MASTER_LOG_FILE='recorded_log_file_name',-> MASTER_LOG_POS=recorded_log_position

Start the iCandra O Thread

START SLAVE

View synchronization status:

SHOW SLAVE STATUS

Binlog_format parameter of Master

Binlog_format is used to configure the format of Binary Log. It supports the following three types:

Row

Record according to the changes of data rows, the mode has nothing to do with SQL statements, stored procedures, functions, triggers, etc., it only cares about whether the data of each row has changed, if the change is recorded, so Row mode is the most accurate. However, its disadvantage is that in some cases, a large amount of content can be generated, resulting in inefficiency, such as when the table structure changes.

Statement

Recording by the SQL statement obviously solves the deficiency of the Row pattern, but the problem is that the accuracy is not high enough, because the SQL statement can be very complex and prone to unexpected situations.

Mixed

Row is mixed with Statement, and MySQL automatically decides when to use Row and when to use Statement, which is also the default mode.

Considerations for replicate-do-db

When using replicate-do-db and replicate-ignore-db configuration items in Slave, it is important to note that SQL statements across databases will not be synchronized, such as:

Replicate-do-db=ause bmistress update a.some_table set some_field = 'some value'

The solution is to use replicate_wild_do_table and replicate_wild_ignore_table, such as:

Replicate_wild_do_table=database_name.%replicate_wild_ignore_table=database_name.%

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.