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

The role of MySQL master-slave replication

1) what is MySQL master-slave replication

MySQL master-slave replication means that data can be replicated from one MySQL database server master node to one or more MySQL database slave nodes. MySQL uses asynchronous replication by default, so that the slave node does not have to access the master server all the time to update its own data, the data update can be carried out on the remote connection, and the slave node can replicate all the databases or specific databases in the master database.

2), MySQL master-slave replication function

1. Data hot standby

As a standby database, when the master database server fails, it can switch to the slave database to continue to work to avoid data loss.

II. Separation of reading and writing

Can support MySQL database server to support greater concurrency. Data read and write operations can be assigned to different servers. Such as the operation of the report is particularly important, because part of the report SQL statement is very slow, will lead to table lock, affecting the front desk service. Use master-slave replication, the foreground uses master, responsible for writing, the report uses slave, responsible for reading, then the report SQL will not cause the foreground to lock the table, ensuring the normal operation of the foreground.

III. Architecture expansion

With the increasing volume of business, the access frequency of Iamp O is too high, and the stand-alone may not be able to meet it. At this time, do multi-database storage, such as one master and multi-slave mode, in order to reduce the disk Imax O access and improve the Imax O performance of a single machine. Principle of MySQL master-slave replication

MySQL data replication is based on binary log files (binary log file). Once the binary log is enabled in a MySQL database, as a master node, all operations in the database will be recorded in the binary log in the way of "events". Other databases as slave maintain communication with the main server through an I / O thread, and monitor the changes in the master binary log files. If a change is found in the master binary log file, the change will be copied to its own relay log. Then a SQL thread of slave executes the relevant "events" into its own database to achieve consistency between the slave database and the master database, that is, master-slave replication.

The summary is as follows:

1) master records the operation statements in the binlog log, and then grants slave the permission to connect remotely (users who create authorization on master enable binlog binary log feature; usually for data security reasons, slave also enables binlog feature).

2), slave starts two threads: IO thread and SQL thread. The IO thread is responsible for reading the binlog content of the master to the relay log relay log; the SQL thread is responsible for reading the binlog content from the relay log log and updating it to the slave database, thus ensuring that the slave data is consistent with the master data.

3) Mysql master-slave replication requires at least two Mysql services. Of course, Mysql services can be distributed on different servers, or multiple MySQL services can be started on one server.

4), Mysql master-slave replication is best to ensure that the version of Mysql on master and slave servers is the same. MySQL master-slave form

Common master-slave forms:

One master and one slave

One master, two followers, one master and multiple followers.

Cascade replication

MySQL master-slave replication operation

OS:CentOS7

Master-slave form: one master and one slave

The configuration required to implement MySQL master-slave replication:

Primary server:

Open binary log

Configure a unique server-id

Get the master binary log file name and location

Create a user account for slave and master communication

From the server:

Configure a unique server-id

Read the master binary log using the user account assigned by master

Enable the slave service

Specific operations:

Master server operation

1) modify the configuration file of the main database server my.cnfserver_id = 1 # unique ID The master library slave library cannot repeat log_bin = mysql-bin # enable log binlog _ format=MIXED # log record format max_binlog_size = 512m # single log file maximum expire_logs_day = 3 # log validity period (days) binlog_do_db = test1,test2 # log records those databases binlog_ignore_db = mysql,performance_schema,information_schema # log records ignore those databases

2) create an account for synchronization

Mysql > CREATE USER 'user'@'10.10.10.11' IDENTIFIED BY' passwd'; # create user mysql > GRANT REPLICATION SLAVE ON *. * TO 'user'@'10.10.10.11'; # assign permissions, only 10.10.10.11 can connect mysql > flush privileges from the server

3) check the status of master

The show master status command records the binary file name (mysql-bin.000003) and the position location (77).

Mysql > SHOW MASTER STATUS +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000003 | 77 | test | manual Mysql | +-+

Operate from the server

1) modify the configuration file my.cnf from the database server

Server_id = 2log_bin = mysql-bin # enable log binlog _ format=MIXED # log recording format max_binlog_size = 512m # single log file maximum expire_logs_day = 3 # log validity period (days) replicate_do_db = test1,test2 # slave configuration, specify which library slave to copy the configuration on replicate-ignore-db=mysql,performance_schema,information_schema # slave Specify which libraries to ignore by slave relay_log_recovery = 1 # it is recommended to enable slave libraries for data consistency log_slave_updates = 1 # if slave libraries can also be used as master libraries, it is recommended to enable them

2), set synchronization information

Stop synchronization first

Mysql > stop slave

Set synchronization information

Mysql > CHANGE MASTER TO- > MASTER_HOST='10.10.10.10',- > MASTER_USER='user',- > MASTER_PASSWORD='passwd',- > MASTER_LOG_FILE='mysql-bin.000003',- > MASTER_LOG_POS=77

Master_log_file and master_log_pos are the information recorded by the above main library show master status command.

3) start the slave synchronization process

Mysql > start slave

4), slave status check

Show slave status\ G command to view, mainly view these items:

Slave_IO_Running: YesSlave_SQL_Running: YesMaster_Log_File: mysql-bin.000003Relay_Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 2720Exec_master_log_pos: 2720

Both Slave_IO_Running and Slave_SQL_Running are YES, indicating that the master-slave synchronization is set successfully.

Verification operations, such as inserting a piece of data in a table in the test database of the master database, and checking whether there is any new data in the same data table in the test database of slave, you can verify whether the master-slave replication function is valid, and you can also close slave (mysql > stop slave;), and then modify the master to see if the slave is modified accordingly (after stopping slave, the changes of master will not be synchronized to slave), you can complete the verification of the MySQL master-slave replication function.

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.