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

The environment is as follows:

CentOS6.5_64

MySQL5.6.26

Master1:192.168.1.112

Master2:192.168.1.114

Mysql installation

Overlooked here, mainly is a careful. Sometimes if you are not careful, you will find that the installation has failed.

Configure master1

1. Modify the my.cnf file by adding the following:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

[mysql

Server-id = 1 # Database ID

Log-bin=myslq-bin # enable binary logging

Binlog-do-db=tudou1 # databases that need to be synchronized. Here, synchronize tudou1 and tudou2 databases

Binlog-do-db=tudou2

Binlog-ignore-db=mysql # ignore synchronized databases

Log-bin=/var/log/mysqlbin/bin_log # sets the generated log file name

If you do not have the directory var/log/mysqlbin, you need to create it and execute chown-R mysql.mysql / var/log/mysqlbin

Log-slave-updates # records write operations from the library to binlog

Expire_logs_days=365 # days of expiration of log files. Default is 0, which means no expiration.

Auto-increment-increment=2 # sets the number of primary servers to prevent duplicate auto_increment fields

The initial value of auto-increment-offset=1 # self-growing field will not increase in the case of multiple master.

Long ID repeat

two。 Add a replicated account backup

one

two

three

[root@localhost] # mysql-uroot-p123456

Mysql > grant replication slave on. To backup@'%' identified by '123456'

Mysql > flush privileges

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

one

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

4. Test whether users with backup can connect to the database on master1 on master2

one

[root@master] # mysql-ubackup-h 192.168.1.112-p123456

Configure master2

1. Modify the my.cnf file by adding the following:

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

[mysqld]

Server-id = 2 # Database ID

Log-bin=myslq-bin # enable binary logging

Binlog-do-db=tudou1 # databases that need to be synchronized. Here, synchronize tudou1 and tudou2 databases

Binlog-do-db=tudou2

Binlog-ignore-db=mysql # ignore synchronized databases

Log-bin=/var/log/mysqlbin/bin_log # sets the name of the generated log file. If there is no var/log/mysqlbin directory, you need to create it and execute chown-R mysql.mysql / var/log/mysqlbin.

Log-slave-updates # records write operations from the library to binlog

Expire_logs_days=365 # days of expiration of log files. Default is 0, which means no expiration.

Auto-increment-increment=2 # sets the number of primary servers to prevent duplicate auto_increment fields

The initial value of auto-increment-offset=2 # self-growing field will not increase in the case of multiple master.

Long ID repeat

two。 Add a replicated account backup

one

two

three

[root@localhost] # mysql-uroot-p123456

Mysql > grant replication slave on. To backup@'%' identified by '123456'

Mysql > flush privileges

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

one

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

4. Test whether users with backup can connect to the database on master2 on master1

one

[root@master] # mysql-ubackup-h 192.168.1.114-p123456

Configure master1-master2 synchronization

Restart mysql services for master1 and master2

one

[root@localhost ~] # service mysql restart

View master status

Master1

one

two

three

four

five

six

seven

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

one

two

three

four

five

six

seven

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 to synchronize from master2

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.114',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin_log.00

0001massively recorded, logged POS120

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 to synchronize from master1

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.112',MASTER_PORT=3306,MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='bin_log.00

0001massively recorded, logged POS120

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

Test master master synchronization:

Access to master1 mysql database

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

twenty-four

twenty-five

twenty-six

Mysql > create database tudou1

Query OK, 1 row affected (0.02 sec)

Mysql > use tudou1

Database changed

Mysql > 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.01sec)

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.01sec)

Mysql > select * from test

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 3 | b | |

| | 5 | c |

+-+ +

3 rows in set (0.00 sec)

Go to master2 to see if there is a tudou1 database and test table.

one

two

three

four

five

six

seven

eight

nine

ten

eleven

twelve

thirteen

fourteen

fifteen

sixteen

seventeen

eighteen

nineteen

twenty

twenty-one

twenty-two

twenty-three

Mysql > use tudou1

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > 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 master1's database, you will find that the data you just inserted in master2 is also inserted into the database.

one

two

three

four

five

six

seven

eight

nine

Mysql > select from test

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 3 | b | |

| | 5 | c |

| | 6 | d | |

+-+ +

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.