MySQL Master-slave replication (Synchronization principle and build process)

A website, the most important is the data above it, but when the database hangs one day, will affect the normal operation of the entire site, MySQL master-slave replication can be a good solution to this problem, MySQL master-slave replication is, Synchronize data from one primary database server (master) to another standby database server (Slave), and when the database server in use fails, we can point the site data to another database server that has the same data and quickly recover the site data.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6E/49/wKiom1V3_IqQY-ogAAD4-AbEqao785.jpg "title=" U4di (~ add1i5{k742v@ ' 7oi.png "alt=" Wkiom1v3_iqqy-ogaad4-abeqao785.jpg "/>

I. MySQL master-slave replication principle:

1. Turn on the Log-bin log function on master, record the operation information of the database, including update, INSERT, delete and so on.

2. Master-slave replication is implemented with three threads, the IO thread on master, the IO thread on the slave, and the SQL thread.

3. The IO thread connection on slave master,master detects a slave request through its own IO thread and requests log-bin logs.

4. Master logs the Log-bin log back to Slave,slave, which downloads the log to a local realy_log and writes a master-info log record to the POS point of Synchronization.

5. Slave's SQL thread detected an update to the Realy-log log and then performed the new operation information locally.

   Note: The master-slave synchronization is asynchronous synchronous, and the synchronization data will not be updated on slave after the data is updated on master.

   
   

Two. mysql master-slave configuration:

1. Master configuration:
   

   vi/etc/my.cnf modifying fields

   
   

[Mysqld]

Datadir=/var/lib/mysql #数据目录

Socket=/var/lib/mysql/mysql.sock

User=mysql #用户名

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

Log-bin=mysql-bin #开启log-bin function

Server-id = 1 #设置server The-id,master on the Server-id is set to a higher number priority than slave

Auto_increment_offset=1

auto_increment_increment=2

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

Replicate-do-db=all #需要同步的数据库, multiple separated by commas, where all is the database

2.Slave configuration:

VI/ETC/MY.CNF modifying fields

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

Server-id = 2

auto_increment_offset=2

auto_increment_increment=2

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

Replicate-do-db=all

3. Sync Authorization for slave in MySQL in master:

mysql> grant replication Slave on * * to ' Tongbu ' @ ' 192.168.1.103 ' identified by "Liupengfang";

4. Query Master status on master:

Mysql> Show master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|      mysql-bin.000003 |              272 |                  | |

+------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

Record Mysql-bin points and position points.

5. Specify Master on Slave:

mysql> Change Master to master_host= ' 192.168.1.102 ', master_user= ' Tongbu ', master_password= ' Liupengfang ', master_ Log_file= ' mysql-bin.000003 ', master_log_pos=272;

Start slave on 6.Slave:

mysql> start slave;

7. View master-slave sync Status:

Mysql> Show Slave status \g

If the following two field states are yes, the master-slave synchronization is established.

Slave_io_running:yes

Slave_sql_running:yes

Three. Test:

1. Create a database on the master database and view the master status:

Mysql> CREATE database Tomcat Charset=utf8;

Mysql> Show master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|      mysql-bin.000003 |              572 |                  | |

+------------------+----------+--------------+------------------+

The point of Position changed

2. On slave, check to see if there is a database and see the Master status:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Test |

| Tomcat |

+--------------------+

The data on the master has been synced locally on the slave.

This article is from the IT Network blog, so be sure to keep this source http://lpf1015.blog.51cto.com/6627801/1660657

MySQL Master-slave replication (Synchronization principle and build process)