MYSQL-5.6.26 Primary master replication

The environment is as follows:

Centos6.5_64

MySQL5.6.26

master1:192.168.1.112

master2:192.168.1.114

MySQL Installation

Here is ignored, mainly is a careful. Sometimes a careless, you will find that the installation failed.

Configure Master1

1. Modify the My.cnf file to add the following:

[Mysqld]server-id =1 #数据库IDlog-bin=myslq-bin #启用二进制日志 binlog-do-db=tudou1 #需要同步的数据库, here synchronize Tudou1 and TUDOU2 two database BINL Og-do-db=tudou2binlog-ignore-db=mysql #忽略同步的数据库log-bin=/var/log/mysqlbin/bin_log #设置生成的log文件名, if not var/log/ Mysqlbin This directory, you need to create and execute chown-r mysql.mysql/var/log/mysqlbinlog-slave-updates #把从库的写操作记录到binlog中expire_logs_days = 365 #日志文件过期天数, the default is 0, which means that auto-increment-increment=2 #设定为主服务器的数量 is not expired, preventing auto_increment fields from repeating auto-increment-offset=1 #自增 The initial value of the long field, in multiple master environments, does not appear as self-growing ID duplicates

2. Add a copy of the account backup

[Email protected] ~]# mysql-uroot-p123456mysql>grant replication Slave on * * to [e-mail protected] '% ' identified by ' 123456 '; mysql>flush privileges;

3. Add MySQL port 3306 to the firewall and restart the firewall to take effect

-A input-m state--state new-m tcp-p TCP--dport 3306-j ACCEPT

4. Test on Master2 If the backup user can connect to the database on the Master1

[Email protected] ~]# mysql-ubackup-h 192.168.1.112-p123456

Configure Master2

1. Modify the My.cnf file to add the following:

[Mysqld]server-id =2 #数据库IDlog-bin=myslq-bin #启用二进制日志 binlog-do-db=tudou1 #需要同步的数据库, here synchronize Tudou1 and TUDOU2 two database BINL Og-do-db=tudou2binlog-ignore-db=mysql #忽略同步的数据库log-bin=/var/log/mysqlbin/bin_log #设置生成的log文件名, if not var/log/ Mysqlbin This directory, you need to create and execute chown-r mysql.mysql/var/log/mysqlbinlog-slave-updates #把从库的写操作记录到binlog中expire_logs_days = 365 #日志文件过期天数, the default is 0, which means that auto-increment-increment=2 #设定为主服务器的数量 is not expired, preventing auto_increment fields from repeating auto-increment-offset=2 #自增 The initial value of the long field, in multiple master environments, does not appear as self-growing ID duplicates

2. Add a copy of the account backup

[Email protected] ~]# mysql-uroot-p123456mysql>grant replication Slave on * * to [e-mail protected] '% ' identified by ' 123456 '; mysql>flush privileges;

3. Add MySQL port 3306 to the firewall and restart the firewall to take effect

-A input-m state--state new-m tcp-p TCP--dport 3306-j ACCEPT

4. Test on Master1 If the backup user can connect to the database on the Master2

[Email protected] ~]# mysql-ubackup-h 192.168.1.114-p123456

Configuring Master1-master2 Synchronization

Restarting the MySQL service for Master1 and Master2

[[Email protected] ~]# service MySQL restart

View Master Status

Master1

Mysql> Show Master status;+----------------+----------+----------------+------------------+------------------- +| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+----------------+----------+----------------+------------------+-------------------+|      bin_log.000001 | 120 | TUDOU1,TUDOU2 |                   MySQL | |+----------------+----------+----------------+------------------+-------------------+1 row in Set (0.00 sec)

Master2

Mysql> Show Master status;+----------------+----------+----------------+------------------+------------------- +| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+----------------+----------+----------------+------------------+-------------------+|      bin_log.000001 | 120 | TUDOU1,TUDOU2 |                   MySQL | |+----------------+----------+----------------+------------------+-------------------+1 row in Set (0.00 sec)

Set Master1 sync from Master2

Mysql>change master to master_host= ' 192.168.1.114 ', master_port=3306,master_user= ' backup ', Master_password= ' 123456 ', master_log_file= ' bin_log.000001 ', master_log_pos=120;mysql>start slave;mysql >show slave status\g;*************************** 1. row ************************** *               slave_io_state:  Waiting for master to send event                   Master_Host: 192.168.1.114                   master_user:  backup                   Master_Port: 3306                 connect_rEtry: 60              master_log_ File: bin_log.000001          read_master_log_pos:  120               relay_log_ file: master-relay-bin.000002                 relay_log_pos: 281        relay_master_ log_file: bin_log.000001              Slave_io_running: yes            slave_sql_ Running: yes

If the following two items appear, the configuration is successful!

Slave_io_running:yes
Slave_sql_running:yes

Set Master2 sync from Master1

Mysql>change master to master_host= ' 192.168.1.112 ', master_port=3306,master_user= ' backup ', Master_password= ' 123456 ', master_log_file= ' bin_log.000001 ', master_log_pos=120;mysql>start slave;mysql > show slave status\g;*************************** 1. row ********************                slave_io_ state: waiting for master to send event                   Master_Host: 192.168.1.112                   master_ user: backup                   Master_Port: 3306                 conNect_retry: 60              master_ Log_file: bin_log.000001          read_master_log_pos:  120               relay_log_ file: master2-relay-bin.000002                 relay_log_pos: 281        relay_master_ log_file: bin_log.000001              Slave_io_running: yes            slave_sql_ Running: yes

If the following two items appear, the configuration is successful!

Slave_io_running:yes
Slave_sql_running:yes

To test the primary master synchronization:

Enter the Master1 MySQL database

mysql>  create database tudou1; query ok, 1 row affected  (0.02 sec) mysql> use tudou1;database  Changedmysql> create table test (Id int auto_increment,name varchar (10), Primary key (ID)); query ok, 0 rows affected  (0.01 sec) mysql> insert into test ( Name)  values (' a '); query ok, 1 row affected  (0.01 sec) mysql> insert into test ( Name)  values (' B '); query ok, 1 row affected  (0.00 sec) mysql> insert into test ( Name)  values (' C '); query ok, 1 row affected  (0.01 sec) mysql> select * from  test;+----+------+| id | name |+----+------+|  1 | a     | |   3 | b    | |   5 | c    |+----+------+3 rows in set  (0.00 sec) 

Enter Master2 to see if there is a tudou1 this database and test table.

mysql> use tudou1; reading table information for completion of table and column  Namesyou can turn off this feature to get a quicker startup  with -adatabase changedmysql> show tables;+------------------+| tables_in_ tudou1 |+------------------+| test              |+------------------+1 row in set  (0.00 sec) mysql> select *  from test;+----+------+| id | name |+----+------+|  1 | a     | |   3 | b    | |   5 | c    |+----+------+3 rows in set  (0.00  SEC) Mysql>insert into test (name)  values (' d '); 

In the Master1 database, you will find that the data you just inserted into the master2 is also inserted into the database.

Mysql> SELECT * FROM test;+----+------+| ID |  Name |+----+------+| 1 |  A | | 3 |  B | | 5 |  C | | 6 |  D | +----+------+

This article is a reference to the network of some reference materials, I practice the results of the operation. If not, please point out that we are making progress together. Thank you!

MYSQL-5.6.26 Primary master replication