MySQL Master-slave replication

MySQL Master-slave replication principle

Mysql Master-Slave synchronization is actually an asynchronous replication process, in order to achieve replication first need to open the Bin-log log on master, the entire process needs to open 3 threads, respectively, the master open IO thread, slave open IO thread and SQL thread.

a) when executing slave start from the server, the IO thread from the server connects to master via an authorized user and requests master to send the Bin-log log content after the specified file and location .

b) After the master server receives a request from an IO thread from the slave server, the IO thread on the master server sends the contents of the specified Bin-log log according to the slave server and returns the IO line to the slave side Ride. (The returned information is in addition to the Bin-log log content, the new Binlog file name on the master server and the next specified update location in Binlog after this time the log content is returned.) )

c) After the Slave IO thread receives the information, the received log content is added to the end of the Relay-log file on the Slave side, and the Bin-log file name and location of the read to the Master side are recorded to Master.inf o file, so that you can clearly tell the Master"Where I need to start the log content from one of the Bin-log, please send it to me" in the next reading;

d) Slave's SQL thread detects new additions to relay-log and immediately resolves the contents of Relay-log as executable content at the real execution time on the Master side and executes on its own.

MySQL Master-slave replication Architecture installation configuration

Environment preparation: 192.168.1.103 for Master master server, 192.168.33.11 for slave from server.

Install MySQL-related software on both the master and slave servers, with the following commands:

Yum install-y MySQL mysql-devel mysql-server mysql-libs

After the installation is complete, the contents of the Master modification vi/etc/my.cnf are as follows:

[mysqld]datadir=/data/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# disabling symbolic-links is recommended to Prevent assorted security riskssymbolic-links=0 Log-bin=mysql-bin # #need to change Server-id = 1 # #need to Chan Geauto_increment_offset=1auto_increment_increment=2[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run /mysqld/mysqld.pidreplicate-do-db =jfedu

Create the/data/mysql Data directory,

Mkdir–p/data/mysql, Chown–r Mysqld:mysql/data/mysql

Start MySQL, service mysqld restart

Then modify the contents of the Slave Mysql database my.cnf configuration file as follows:

[mysqld]datadir=/data/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# disabling symbolic-links is recommended to Prevent assorted security Riskssymbolic-links=0 Server-id = 2 # #auto_increment_offset =2auto_increment_increment=2[mys Qld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Set permissions on the Master database server and execute the following command:

Grant Replication Slave on * * to ' tongbu ' @ '% ' identified by ' 123456 ';

Execute the following command in the Master database:

Mysql> Show master status;

Then specify the master IP and the synchronized Pos points on the slave server:

Change Master to master_host= ' 192.168.1.103 ', master_user= ' Tongbu ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000006 ', master_log_pos=98;

In slave start slave start and perform show slave status\g view Mysql master and slave Status:

Slave_IO_Running:YesSlave_SQL_Running:Yes #两个状态为 Yes, representing Slave has started two threads, one for IO

Thread, one for the SQL thread.

Then create a database and a table on the Master server with the following command:

Create Dababase mysql_db_test charset=utf;use mysql_db_test;create table t0 (id varchar (), name varchar (20));

You can also test inserting two data on the master server, and slave to see if the insert data is synchronized:

On master, perform such as:

129 on slave, the data inserted in master has been synchronized to slave:

MySQL Master-Slave considerations

The principle of MySQL master-slave synchronization:

1. Turn on the Bin-log log function on master and record the updated, inserted and deleted statements.

2, must open three threads, the Lord to open the IO thread, from the open IO thread and SQL thread.

3, from the upper IO thread to connect master,master through the IO thread to check the request slave come over, request day

Location of Postsion.

4, Master will return these corresponding logs to slave,slave himself to download to the local realy_log inside,

Write a Master-info

The point at which the log records are synchronized.

5. Slave's SQL thread checks to the Realy-log log for updates, and then executes the exec locally.

6, master-slave synchronization is an asynchronous way.

Change Master to master_host= ' 192.168.1.12 ', master_user= ' Tongbu ', master_password= ' 123456 ', master_log_file= ' Mysqlbin.000001 ', master_log_pos=272;

Second, Mysql master-slave synchronization, Master suddenly down the machine, how to recover:

1, slave database must be started, on the slave to authorize the site IP access to the database.

2, modify the Site Server Config directory:

config_global.php config_ucenter.php changed the original master IP to slave IP address.

3, restart HTTPD service, switch success.

Homework:

1, in the master has already created the Discuz database, at the same time there is a forum, new from, How to synchronize data?

Mysqldump >mysql.sql;

Import in, then sync.

3.7.5 MySQL Master-Slave synchronization failure Solution

Method one: After ignoring the error, continue syncing

This method is suitable for the case that the master-slave database data is not very different, or the data can not be completely unified, the data requirements are not

The strict situation

Solve:

Execute on Master:

Flush tables with read lock;

Execute on Slave:

Stop slave;

#表示跳过一步错误, the following numbers are variable

Set global Sql_slave_skip_counter =1;start slave;

Then use

Mysql> show Slave status\g;

View:

Slave_io_running:yes

Slave_sql_running:yes

OK, now the master-slave synchronization state is normal ...

Mode two: Re-master from, fully synchronized

This method is suitable for the case that the master-slave database data is large or the data is completely unified.

The steps to resolve are as follows:

1) Advanced Master Library, lock table, prevent data write

Use the command:

Mysql> flush tables with read lock;

Note: This is locked to a read-only state, and the statement is case insensitive

2) Perform data backup

#把数据备份到 mysql.bak.sql File

[[email protected] MySQL] #mysqldump-uroot-p-hlocalhost > Mysql.sql

Note here: Database backups must be done regularly, either with shell scripts or Python scripts,

Easy to ensure data is foolproof

3) View Master status

Mysql> Show Master status;+-------------------+----------+--------------+-------------------------------+| File | Position | binlog_do_db | binlog_ignore_db |+-------------------+----------+--------------+-------------------------------+| mysqld-bin.000001 | 272 | |+-------------------+----------+--------------+-------------------------------+1 row in Set (0.00 sec)

4) Upload MySQL backup file to slave library for data recovery

#使用 SCP Command

[Email protected]]# SCP mysql.sql [email protected]:/tmp/

5) Stop the state from the library

mysql> stop Slave;

6) Then go to the MySQL command from the library, import the data backup

Mysql> Source/tmp/mysql.sql

7) Set synchronization from the library, note that the synchronization point is in the main library show Master status information | file|

Position two items

Change Master to Master_host = ' 192.168.1.12 ', master_user = ' Tongbu ', master_password= ' 123456 ', master_log_file = ' Mysqld-bin.000001 ', master_log_pos=272;

8) re-open from sync

mysql> start slave;

9) View sync status

Mysql> show Slave status\g;

View:

Slave_io_running:yes

Slave_sql_running:yes

10) Unlock on Master:

Unlock tables;

This article is from the "Night Empty Watch Snow" blog, please be sure to keep this source http://12550795.blog.51cto.com/12540795/1968599

MySQL Master-slave replication