MySQL (Percona Server) 5.6 Master-slave replication

Source: Internet
Author: User
Tags uuid iptables percona percona server

    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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.