MySQL replication overview, installation, failure, skills, tools (1)

Source: Internet
Author: User

Compared with the replication of NoSQL databases such as MongoDB and Redis,MySQL ReplicationIt looks quite complicated! The next step is a detailed introduction. I believe the content below will be helpful for you to master the replication of MySQL databases.

Overview

First, the master server records data changes to the master log, then reads the master log on the master server from the server through the I/O thread, and writes it to the slave server's relay log, then, the SQL thread reads the relay log and replays it on the slave server to achieve MySQL replication. As shown in:

MySQL Replication

The whole process is reflected on the slave server. You can view three sets of log information on the slave server with the following command:

Mysql> show slave status;

Master_Log_File & Read_Master_Log_Pos: information of the next master log.

Relay_Master_Log_File & Exec_Master_Log_Pos: information about the main log of the next execution.

Relay_Log_File & Relay_Log_Pos: information about the next relay log.

Understanding the meaning of the log information is essential for fault resolution, which will be described in detail later.

Install

Create a copy account on the master server:

Mysql> grant replication slave on *.*

TO '<SLAVE_USER>' @ '<SLAVE_HOST>'

Identified by '<SLAVE_PASSWORD> ';

NOTE: For the sake of security and flexibility, do not use a user with SUPER permissions such as root as a copy account.

Then set the default configuration file of the master server:/etc/my. cnf ):

[Mysqld]

Server_id = 100

Log_bin = mysql-bin

Log_bin_index = mysql-bin.index

Sync_binlog = 1

Innodb_flush_log_at_trx_commit = 1

Innodb_support_xa = 1

Note: ensure that the server_id of the master and slave servers is unique to avoid conflicts.

NOTE: If log_bin is not specified, the host name is used as the name by default. Once the host name changes, a problem occurs. Therefore, we recommend that you specify the relay_log of the log_bin slave server to have the same problem ).

Note: sync_binlog, innodb_flush_log_at_trx_commit, and innodb_support_xa are all set for security purposes and are not required for replication.

Next, set the slave server configuration file default:/etc/my. cnf ):

[Mysqld]

Server_id = 200

Log_bin = mysql-bin

Log_bin_index = mysql-bin.index

Relay_log = mysql-relay-bin

Relay_log_index = mysql-relay-bin.index

Read_only = 1

Skip_slave_start = 1

Log_slave_updates = 1

Note: If the user has the SUPER permission, read_only is invalid.

NOTE: With skip_slave_start, replication will not START on the server unless the start slave command is used.

Note: Setting log_slave_updates allows the slave server to record logs, which helps switch the slave to the master when necessary.

The following most important step is to clone the data of the master server:

If the database uses the MyISAM Table type, perform the following operations:

Shell> mysqldump -- all-databases -- master-data = 1> data. SQL

Note: by default, the master-data option enables lock-all-tables and writes the change master to statement.

If the database uses the InnoDB table type, use single-transcation:

Shell> mysqldump -- all-databases -- single-transaction -- master-data = 1> data. SQL

With the data file, transfer it to the slave server and import it:

Shell> mysql <data. SQL

If the data volume is large, mysqldump will be very slow. Copying data files directly saves a lot of time:

You must lock the data before obtaining the relevant log information FILE & POSITION ):

Mysql> flush tables with read lock;

Mysql> show master status;

Next, copy the data file directly if it is of the MyISAM Table type. If it is of the InnoDB table type, stop the MySQL service before copying it. Otherwise, the copy file may not be available. Copy the copied data file directly to the data directory of the slave server.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.