MySQL Master-slave replication

Master-slave replication facilitates robustness of database architecture improve access speed easy maintenance Management

Master-Slave Mutual backup m----S one main asynchronous synchronous

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/80/43/wKiom1c8K-Cij7jyAABUrjWR9Ok242.gif "style=" float: none; "title=" 1.gif "alt=" Wkiom1c8k-cij7jyaaburjwr9ok242.gif "/>

The first part of the process is the master record binary log. Master records these changes in two logs before each transaction update data is complete. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

The next step is to slave copy the binary log of master to its own trunk logs. First, slave starts a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the relay log.

The SQL slave thread (SQL slave thread) handles the last step of the process. The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.

In addition, there is a worker thread in master: As with other MySQL connections, slave opening a connection in master will also cause master to start a thread. The replication process has a very important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.

Mysql-uroot-p123456-s/data/3306/mysql.sock

Mysql> Show variables like ' log_bin% ';

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

| variable_name | Value |

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

| Log_bin | On |

Server-id = Not the same

mysql> grant replication Slave on * * to ' rep ' @ ' 192.168.10.% ' identified by ' 123456 ';

mysql> flush Privileges;

Mysql> Flush table with read lock; Read lock

Mysql> Show Master Status/logs;

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

| Log_name | File_size |

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

|       mysql-bin.000005 | 338 |

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

Mysqldump-uroot-p123456-s/data/3306/mysql.sock-a-B--events --master-data=2 >/opt/rep.sql

mysql> unlock tables; Unlock

# Mysql-uroot-p123456-s/data/3307/mysql.sock </opt/rep.sql

Mysql-uroot-p123456-s/data/3307/mysql.sock

Change MASTER to

Master_host= ' 192.168.10.10 ',

master_port=3306,

Master_user= ' rep ',

Master_password= ' 123456 ',

Master_log_file= ' mysql-bin.000005 ',

master_log_pos=417;

/data/3307/data/master.info//Generated files

mysql> start slave;

Mysql> show Slave status\g;

Slave_io_running:yes

Slave_sql_running:yes

Relay-bin.index

/data/3307/relay-bin.000001

/data/3307/relay-bin.000002

Relay-log.info/Relay Log

427

mysql-bin.000005

591

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s1.51cto.com/wyfs02/m02/80/43/wkiom1c8k_aq3ezyaafj_mtnfna526.png "title=" 2.png "alt=" wKiom1c8K_aQ3eZYAAFJ_ Mtnfna526.png "/>

--master-data=1

This article is from the "what-all" blog, please be sure to keep this source http://hequan.blog.51cto.com/5701886/1774806

MySQL Master-slave replication