MySQL (Percona Server) 5.6 Master-slave replication

    MySQL (Percona Server) 5.6.15    Main Library: 192.168.2.21    from library: 192.168.2.22

For example, we synchronize the database as: Test. If you need to synchronize multiple databases, there is a description below.

MySQL Master-slave replication principle

1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events，可以通过show binlog events进行查看）；2. slave将master的binary log events拷贝到它的中继日志(relay log)；3. slave重做中继日志中的事件，将改变反映它自己的数据。

Main Library (192.168.2.21):

Main Library Open 3306 port

#/sbin/iptables-i input-s 192.168.2.0/24-p TCP--dport 3306-j accept# service iptables Save

To view open ports:

# service Iptables status or # IPTABLES-L-n

Authorized Sync Account

Mysql> GRANT REPLICATION SLAVE on * * to [e-mail protected] ' 192.168.2.22 ' identified by ' 123 ';mysql> flush privileges ;

The authorization format is: GRANT REPLICATION SLAVE ON *.* TO [email protected]‘ipaddress‘ IDENTIFIED BY ‘password‘ .

You can now test whether you can access it from the library on 192.168.2.22:

#/usr/local/mysql/bin/mysql-h192.168.2.21-urep-p123

Configuring the Main library MY.CNF

# vi/etc/my.cnf[mysqld]log-bin=mysql-bin  #开启 mysql binary log server-id=1  #主库服务器 ID, note that the server ID cannot be duplicated binlog-do-db=test  #需要做主从备份的数据库名, if you copy multiple databases, repeat this option to expire-logs-days=7  #只保留 7-day binary log in case the disk is full binlog_format=mixed  # The format of the Binlog is MIXED

Binlog in three formats, STATEMENT ROW MIXED see MySQL Binlog ROW and STATEMENT selection.

Can be show variables like ‘binlog_format‘; viewed by.

Save, restart MySQL:

# service MySQL Restart

Copy the Master library to synchronize the data to the slave library

Go to MySQL terminal /usr/local/mysql/bin/mysql -uroot -p :

Set Primary Couchen read-only status

mysql> use test;mysql> FLUSH TABLES with READ LOCK;

Reset Binlog Log

Mysql> RESET MASTER;

View Run status

mysql> SHOW MASTER status\g;******************* 1. Row *******************             file:mysql-bin.000001         position:120     binlog_do_db:test binlog_ignore_db: Executed_gtid_set:1 row in Set (0.00 sec)

Here you need File to record and Position two values, which are required from the library CHANGE MASTER TO commands below.

Next 切换到从库服务器 , export the data in the main library that needs to be synchronized and import it into the slave library:

#/usr/local/mysql/bin/mysqldump-h192.168.2.21-urep-p Test > dumptest.db#/usr/local/mysql/bin/mysql-uroot-p Test < dumptest.db

再回到主库服务器To unlock the read-only state of the main library database

Mysql> UNLOCK TABLES;

From library (192.168.2.22):

Configure MY.CNF from Library

# vi/etc/my.cnf[mysqld]server-id=2  #从库服务器 ID, note that the server ID cannot be repeated replicate-do-db=test  #需要做复制的数据库名, if multiple databases are replicated, Repeat to set this option  to slave-skip-errors=1032,1062,126,1114,1146,1048,1396 #自动跳过的错误代码 to prevent replication errors from being interrupted

If you have tables in your database that do not require synchronization, you can add replicate-ignore-table=table_name,... configuration items that automatically skip these tables.

Save, restart MySQL:

# service MySQL Restart

Specify the main library

Go to MySQL Terminal:

mysql> change MASTER to  master_host= ' 192.168.2.21 ',  master_user= ' rep ',  master_password= ' 123 ',  Master_log_file= ' mysql-bin.000001 ',  master_log_pos=120;

To start a connection from a library

mysql> start slave;

View from library status

mysql> SHOW SLAVE STATUS\G; ...... Slave_IO_Running: Yes Slave_SQL_Running: Yes ......

Two Yes successful.

Test

You can now insert/update data in the main library, add tables, and so on, to see if the corresponding data can be synchronized from the library.

Problem

SHOW SLAVE STATUS\G;there was an error when you last used the view slave run state:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

The server UUID was added from MySQL 5.6, because I stole a lazy, from the library is directly in the VirtualBox in Clone's main library, so the /var/lib/mysql/auto.cnf value in the file server-uuid is the same. Change a value arbitrarily, but the format of the value cannot be changed, and it is not duplicated with other server-uuid in the group.

After the change, restart MySQL:

# service mysql restart

Use SHOW SLAVE STATUS\G; View status again as normal.

Two small commands are included:

mysql> SHOW SLAVE HOSTS; #需在 master 中执行，查看 slave 列表，包括相应的 server-uuid 值mysql> show variables like ‘server_uuid‘; #查看本机的 server-uuid 值

Good luck!

MySQL (Percona Server) 5.6 Master-slave replication