Shulou(Shulou.com)06/01 Report--

Let's take a look at MySQL database 5.6.16. Master-slave replication how to build and configure, I believe you will benefit a lot after reading, the text in the essence is not much, hope MySQL database 5.6.16. Master-slave copy how to build and configure this short article is what you want.

MySQL database 5.6.16. Master-slave copy 1. Environment:

Based on the virtual machine, so directly clone the same virtual machine operation

From the library:

[root@slave ~] # ifconfig eth0 | awk-F'[:] + 'NR==2 {print $4}'; ifconfig eth2 | awk-F'[:] + 'NR==2 {print $4}'

192.168.56.42

172.16.1.42

/ etc/init.d/iptables stop

Or:

Vim / etc/sysconfig/iptable

-An INPUT-m state-- state NEW-m tcp-p tcp-- dport3306-j ACCEPT

Main library: to record bin_log

[root@master ~] # cat-n / etc/my.cnf | sed-n '41p'

41 log_bin = / home/mysql/data/binlog/mysql-bin

Make sure that the server_id value is different from that of the main library.

[root@slave ~] # cat-n / etc/my.cnf | sed-n '11p'

11 server_id = 101

Note: do not change the host name of the CVM at will, otherwise an error will be reported when the MySQL service is restarted. You must kill the current MySQL to drop the process, and then start the MySQL service by restart.

The replication of mysql 5.6introduces the concept of uuid. The server_uuid in each replication structure has to be guaranteed to be different, but the server_uuid is the same after viewing the direct copy data folder, show variables like'% server_uuid%'.

At the same time, it is necessary to ensure that the values in the auto.cnf of the master-slave database are different, and that the firewalls of the two master-slave machines must be turned off.

Modify the file / data/mysql/data/auto.cnf on the two CVMs and then restart the database service to ok.

Responsible for reporting an error: 1593

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

And error: 2003 because the firewall is not turned off. Caused by incorrect password entry during Change master.

Last_IO_Error: error connecting to master 'rep@172.16.1.41:3306'-retry-time: 60 retries: 1

Tip: you must ensure that the time of the two CVMs is the same, update the time at the right time and write scheduled tasks

Crontab-l

# time sync###by wujianwei at 2016-5-19

* / 5 * / usr/sbin/ntpdatentp1.aliyun.com & > / dev/null

Building an one-master and multi-slave database Times error:

Operating from the library:

Mysql > start slave

ERROR 1201 (HY000): Could not initialize masterinfo structure; more error messages can be found in the MySQL error log

Mysql > show slave status\ G

Last_Errno:1872

Last_Error: Slave failed to initialize relay log info structure from therepository

Add a slave to a host and report when it starts.

[ERROR] Slave SQL: Slave failed toinitialize relay log info structure from the repository, Error_code: 1872

Reason: check my.cnf, originally did not specify relay_log,mysql default generated relay_log name is occupied by another mysql slave on this server.

The reason is that no parameters are added from the my.cnf configuration file of the library

Relay_log=/data/mysql/data/relay-log/mysql-relay-bin

1.stop slave

2. Reset slave

Mysql > reset slave

3.mysql > change master to

4.start slave

two。 Master / Slave environment configuration step: 1.master master library operation:

/ etc/init.d/iptables stop

Or:

Vim / etc/sysconfig/iptable

-An INPUT-m state-- state NEW-m tcp-p tcp-- dport3306-j ACCEPT

Echo "log_bin = / home/mysql/data/binlog/mysql-bin" / etc/my.cnf

Sed-I 's#server_id = 100#server_id = 1#g'/etc/my.cnf

Mkdir / home/mysql/data/binlog/-p

Chown mysql.mysql / home/mysql/data/binlog

Mysql-uroot-p123456-e "grant replicationslave on *. * to rep@'172.16.1.%' identified by '111111; flush privileges"

Mysqldump-uroot-p123456-B-A-F-R-x--master-data=1-- events | gzip > / backup/rep_$ (date +% F) .sql.gz

Mysqldump-uroot-p123456-B-A-F-R-x--master-data=1-- events | gzip > / backup/rep_$ (date +% F) .sql.gz

Mysql-uroot-p123456-e "show master status;"

Scp-rp-P22 / backup/rep_2016-08-18.sql.gz root@172.16.1.42:/backup/

2 Slave operation: mysql-5.6s1

Vim / etc/sysconfig/iptables

-An INPUT-m state-- state NEW-m tcp- p tcp--dport 3306-j ACCEPT

Sed-I 's#server_id = 100#server_id = 2ogg' / etc/my.cnf

Gzip-d / backup/rep_2016-08-18.sql.gz

Mysql-uroot-p123456 CREATE table `t1` (`id` bigint (12) NOTNULL auto_increment, `name` varchar (12) NOTNULL, PRIMARY KEY (`id`))

Mysql > INSERT INTO T1 (name) values ('oldboy')

Slave:

Mysql-uroot-p123456-e "showdatabases" | grep wjw05

Mysql-uroot-p123456-e "showdatabases" | grep wjw05

Mysql-uroot-p123456-e "desc wjw01.t1"

Mysql-uroot-p123456-e "select * fromwjw01.t1"

After reading the MySQL database 5.6.16. After copying how to build and configure this article, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.