On the replication of MySQL

MySQL replication is a multi-MySQL database master from a synchronous scheme, characterized by asynchrony, widely used in a variety of situations where MySQL has higher performance and higher reliability requirements. Another technique that corresponds to this is the synchronous MySQL Cluster, but because of the complexity, fewer users.

Is the MySQL official gave the scene using replication:
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4D/B3/wKiom1RXfpvC6o6ZAAFTXNi6cDU783.jpg "title=" 123. PNG "alt=" wkiom1rxfpvc6o6zaaftxni6cdu783.jpg "/>


Replication principle

MySQL Replication is an asynchronous replication process that replicates from one MySQL node (called master) to another MySQL node (called the slave). The entire replication process between Master and Slave is done primarily by three threads, of which two threads (SQL thread and I/O thread) are on the Slave side, and another thread (I/O thread) on the master side.

To implement MySQL's Replication, you must first turn on the Binary log on the master side, because the entire replication process is actually Slave the log from the master side and then the full sequence of execution logs on its own.

It seems that the replication principle of MySQL is very simple, summed up:
* Each from only one master can be set.
* After the master executes the SQL, log the binary log file (Bin-log).
* From the connection master, and obtain Binlog from the master, stored in the local relay-log, and from the location of the last remembered to execute SQL, once encountered an error will stop synchronization.

Judging from these replication principles, these inferences can be inferred:
* Master-slave database is not real-time synchronization, even if the network connection is normal, there is an instant, master-slave data inconsistency.
* If the master-slave network is disconnected, from the network after normal, batch synchronization.
* If you modify the data from, then it is very likely to be from the execution of the main Bin-log error and stop synchronization, this is a very dangerous operation. So in general, be very careful to modify the data from above.
* A derivative configuration is a dual master, mutual main from the configuration, as long as the two sides of the modification does not conflict, can work well.
* If you need multi-master, you can use a ring configuration, so that any one of the node's modifications can be synchronized to all nodes.




Master and slave settings
1. mysql master and slave application scenario

The principle is that when data is updated on master, a bin-log,slave will be recorded in real-time and the Bin-log then slave based on Bin-log SQL statements.
MySQL master-slave can be backed up in real time to ensure high data security
Can be applied in a read/write separation scenario to reduce I/O for a single MySQL server
Ha clusters that can implement MySQL services
Can be 1 master multiple from, can also be mutual master and slave (master)


2. Install and configure MySQL
Refer to previous steps to build MySQL service


3. Configure Master-Slave preparation

Create a test library in your Lord:
Set up a database for testing |
#myslq –u root–p
Mysql>create database Ocean;
Mysql>use Ocean;
Mysql>create table test1 (ID int,sname char (8));
Mysql>insert into test1 values (1, ' Zhangsang ');



4. Configuration Master (Master)
Vim./MY.CNF #修改或添加:
Server-id=1
Log-bin=mysql-bin
Two optional parameters (2 Select 1):
Binlog-do-db=ocean #需要同步的库 "Between multiple libraries, separating"
Binlog-ignore-db=ocean #忽略不同步的库
After modifying the configuration file, restart MySQL
Pid= ' PS uax |grep mysql.sock |grep-v grep |awk ' {print $} '; Kill $pid; cd/usr/local/mysql_2/bin/;/mysqld_safe--defaults-file=. /MY.CNF--user=mysql &

Master authorized to Slave
Grant Replication Slave on * * to ' ocean ' @ ' 192.168.9.10 ' identified by ' ocean ';
Flush tables with read lock; #锁住表 to prevent data from being written, remember to unlock when the slave configuration is complete
Flush privileges; #刷新
Show master status; #一定要记住前两列的内容, I'll use it in a minute.
Mysql> Show master status;
+----------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+----------------------+----------+--------------+------------------+
|      mysql-bin-log.000028 | 330 |                  MySQL | |
+----------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)


5. Set from (slave)
Vim my.cnf #修改或增加
Server-id = 2 #这个数值不能和主一样
Optional Parameters:
Replicate-do-db=ocean,ocean2 and Replicate-ignore-db=ocean,ocean2 #意义同主的那两个可选参数
Service mysqld Restart
Copy the master's DB1 library data to from:
Then export the master MySQL library data and import it to slave MySQL
Mysqldump-uroot-p mysql > Ocean.sql
Import data into a newly created database in Salve
Mysql-uroot-p-E "CREATE Database Ocean"; Mysql-uroot-p Ocean < Ocean.sql


Mysql-uroot-p #登陆Slave的mysql
Slave stop;

Change Master to master_host= ' 192.168.9.9 ', master_port=3306, master_user= ' ocean ',
master_password= ' Ocean ', master_log_file= ' mysql-bin-log.000028 ', master_log_pos=330;

Slave start;

Master: mysql-uroot-p-E "unlock tables"
Slave view from the status:mysql> show slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.9.9
Master_user:ocean
master_port:3306
Connect_retry:60
master_log_file:mysql-bin-log.000028
read_master_log_pos:330
relay_log_file:mysqld-relay-bin.000002
relay_log_pos:255
relay_master_log_file:mysql-bin-log.000028
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:ocean
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
.....



6. Test Master and slave
Insert a record in the Ocean database in master:
Mysql>use Ocean;
Mysql>insert into test1 values (2, ' Lisi ');
See if Oceandb has a new record generated in slave.

Recommendation: MySQL master-slave mechanism is relatively fragile, cautious operation. If you restart Master, be sure to stop the slave first, that is, you need to slave up the slave Stop command, and then go to restart the master MySQL service, otherwise it is likely to be interrupted. Of course, after the restart, you also need to slave to open slave start.

This article is from the "Jeffery blog" blog, please be sure to keep this source http://oceanszf.blog.51cto.com/6268931/1571424

On the replication of MySQL