MySQL Master-slave configuration

Working principle diagram:

The principle of Master-slave replication:

is divided into synchronous replication and asynchronous replication, and most of the actual replication schemas are asynchronous. The basic process for replication is as follows:

1). Slave The above IO process connects to master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;

2). After master receives a request from the IO process from slave, the IO process that is responsible for the replication reads the log information after the specified location of the log according to the requested information and returns the IO process to the slave. In addition to the information contained in the log, the returned information includes the name of the Bin-log file returned to the master side and the location of the Bin-log;

3). After the slave IO process receives the information, the received log content is added to the end of the Relay-log file on the slave side, and the file name and location of the Bin-log read to the master side are recorded in the Master-info file. In order to be able to tell Master clearly at the next reading, "I need to start from somewhere in the Bin-log log content, please send me";

4). Slave's SQL process detects new additions to the 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.

Summary below:

Only one master can be set from each.
After the master executes the SQL, log the binary log file (Bin-log).
From the connection master, and get Binlog from the master, save in local relay-log, and execute SQL from the location you last remembered, and stop synchronizing once an error is encountered.

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 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. (It is recommended not to modify the data from the above)

First, MySQL master-slave replication

1, the master-slave installation of MySQL, version consistent

We're pretending to be mysql-5.5.30.tar.gz here to omit ...

2. Modify the Master,slave server

Master 192.168.176.253 Service Configuration

Vim/usr/local/etv/my.cnf

[Mysqld]

server-id=253 #设置服务器唯一的id, default is 1, we set IP last paragraph, slave set 254

Log-bin=mysql-bin # Enable binary logging

Binlog-ignore-db = Mysql,information_schema #忽略写入binlog的库

Slave 192.168.176.254 Server configuration:

Vim/usr/local/etv/my.cnf

[Mysqld]

server-id=254  #这个id与主不能一样

replicate-do-db = abc #只同步abc库

Slave-skip-errors = All #忽略因复制出现的所有错误

3. Restart the slave server MySQL

/etc/init.d/mysqldrestart

4. Establish an account on the primary server and authorize slave

mysql> mysql -u root -p123.com

mysql> GRANT REPLICATION SLAVE ON *.* to ‘sync’@‘192.168.1.2’ identified by ‘1234.com’; #replication:复制 建立一套可以同步的用户sync 可以在192.168.1.2登录

mysql> flush privileges;#刷新权限（）

mysql> flush tables with read lock; #将表暂时锁定，防止此时还查数据；最后记得打开

5. View the primary database status

mysql> show master status;

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

| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000002 | 263 |  |   |

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

6. Configuration from the database

mysql> change master to

-> master_host=‘192.168.176.253‘,

-> master_user=‘sync‘,

-> master_password=‘1234.com‘,

-> master_log_file=‘mysql-bin.000002‘,

-> master_log_pos=263;

#Log和pos是master上随机获取的。这段也可以写到my.cnf里面。

7. Start the slave synchronization process and view the status

1

mysql> start slave;

The value of slave_io_running and slave_sql_running must be yes to indicate that the status is normal.

8. Verify Master-Slave synchronization

CREATE DATABASE ABC in main MySQL, and then view from MySQL has been synchronized successfully!

mysql> create database abc;

mysql> show databases;

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

| Database   |

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

| information_schema |

| abc    |

| mysql    |

| performance_schema |

| test|

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

Start error on Slave:

"Last_io_error:got fatal Error 1236 from master if reading data from binary log: ' Could not ' find first log file name in Binary Log index file ' "

Resolution: The cause of the error is mainly slave set the binary file name of master or POS value does not correspond!

Flush logs first, empty the log, check the status of the primary database show Master state, and look at the log file name and position value;

Again in slave, execute: Change MASTER to master_log_file= ' binary log name ', master_log_pos= value;

Finally start the synchronization process: start slave;

MySQL Master-slave configuration