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

Please read the whole article patiently. The mistakes made in the process are summarized and solved at the end of the article.

Server architecture

Install MySQL

Create a mysql user

Groupadd mysql

Useradd-r-g mysql-s / bin/false mysql

Install MySQL

Yum install-y libaio

Cd / usr/local/src/

Wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

Tar-zxf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

Cp-rf mysql-5.7.17-linux-glibc2.5-x86_64 / data/app/mysql-3306

Cp-rf mysql-5.7.17-linux-glibc2.5-x86_64 / data/app/mysql-3307

Chown-R mysql:mysql / data/app/mysql-3306

Chown-R mysql:mysql / data/app/mysql-3307

/ data/app/mysql-3306/bin/mysqld-initialize-insecure-user=mysql-basedir=/data/app/mysql-3306-datadir=/data/app/mysql-3306/data

/ data/app/mysql-3307/bin/mysqld-initialize-insecure-user=mysql-basedir=/data/app/mysql-3307-datadir=/data/app/mysql-3307/data

Modify my.cnf

Parameters to be modified:

Server-id: make sure each profile is unique

The self-growing ID of the two master must be different

Linux-node2

Master

Cat > / data/app/mysql-3306/my.cnf MASTER_PORT=3306

-> MASTER_USER='repl'

-> MASTER_PASSWORD='mysql'

-> MASTER_LOG_FILE='mysql-bin.000001'

-> MASTER_LOG_POS=613

Query OK, 0 rows affected, 2 warnings (0.04 sec)

Mysql > start slave

Query OK, 0 rows affected (0.02 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.12

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 613

Relay_Log_File: linux-node2-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Linux-node3

Master

Cd / data/app/mysql-3306/

. / bin/mysql-uroot-p-S mysql.sock-P 3306

Mysql > CREATE USER 'repl'@'192.%' IDENTIFIED BY' mysql'

Query OK, 0 rows affected (0.05 sec)

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.%'

Query OK, 0 rows affected (0.00 sec)

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 613 | |

+-+

1 row in set (0.00 sec)

Slave

Cd / data/app/mysql-3307/

. / bin/mysql-uroot-p-S mysql.sock-P 3307

Mysql >

CHANGE MASTER TO MASTER_HOST='192.168.56.13'

MASTER_PORT=3306

MASTER_USER='repl'

MASTER_PASSWORD='mysql'

MASTER_LOG_FILE='mysql-bin.000001'

MASTER_LOG_POS=613

Query OK, 0 rows affected, 2 warnings (0.04 sec)

Mysql > start slave

Query OK, 0 rows affected (0.02 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.13

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 613

Relay_Log_File: linux-node2-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Configure dual hosts

Binlog location of master

Linux-node2 master end

D / data/app/mysql-3306/

. / bin/mysql-uroot-p-S mysql.sock-P 3306

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 613 | |

+-+

1 row in set (0.00 sec)

Linux-node3 master end

Cd / data/app/mysql-3306/

. / bin/mysql-uroot-p-S mysql.sock-P 3306

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 613 | |

+-+

1 row in set (0.00 sec)

Linux-node2 master configuration is synchronized with linux-node3 master

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.56.13'

MASTER_PORT=3306

MASTER_USER='repl'

MASTER_PASSWORD='mysql'

MASTER_LOG_FILE='mysql-bin.000001'

MASTER_LOG_POS=613

Mysql > start slave

Query OK, 0 rows affected (0.03 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.13

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 613

Relay_Log_File: linux-node2-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Linux-node3 master configuration is synchronized with linux-node2 master

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.56.12'

MASTER_PORT=3306

MASTER_USER='repl'

MASTER_PASSWORD='mysql'

MASTER_LOG_FILE='mysql-bin.000001'

MASTER_LOG_POS=613

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql >

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.12

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 613

Relay_Log_File: linux-node3-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Verification

Verify from linux-node2

Create data on linux-node2 master

Mysql > create database test

Query OK, 1 row affected (0.01sec)

Mysql > use test

Database changed

Mysql > create table temp (id int,name varchar (64))

Query OK, 0 rows affected (0.11 sec)

Mysql > insert into temp values (1)

Query OK, 1 row affected (0.28 sec)

Mysql > CREATE TABLE temp2 (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, nname VARCHAR (64))

Query OK, 0 rows affected (0.01 sec)

Mysql > insert into temp2 (nname) values ('bbb')

Query OK, 1 row affected (0.01sec)

Mysql > select * from test.temp

+-+ +

| | id | name |

+-+ +

| | 1 | aaa |

+-+ +

1 row in set (0.01 sec)

View data on linux-node2 slave

Mysql > select * from test.temp

+-+ +

| | id | name |

+-+ +

| | 1 | aaa |

+-+ +

1 row in set (0.00 sec)

View data on linux-node3 master

Mysql > select * from test.temp

+-+ +

| | id | name |

+-+ +

| | 1 | aaa |

+-+ +

1 row in set (0.00 sec)

View data on linux-node3 slave

Mysql > select * from test.temp

+-+ +

| | id | name |

+-+ +

| | 1 | aaa |

+-+ +

1 row in set (0.00 sec)

Verify from linux-node3

Create data on linux-node3 master

Mysql > use test

Mysql > insert into temp2 (nname) values ('ddd')

Query OK, 1 row affected (0.02 sec)

Mysql > insert into temp2 (nname) values ('fff')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from test.temp2

+-+ +

| | id | nname |

+-+ +

| | 1 | bbb |

| | 2 | ddd |

| | 4 | fff |

+-+ +

View data on linux-node3 slave

Mysql > select * from test.temp2

+-+ +

| | id | nname |

+-+ +

| | 1 | bbb |

| | 2 | ddd |

| | 4 | fff |

+-+ +

3 rows in set (0.00 sec)

View data on linux-node2 master

Mysql > select * from test.temp2

+-+ +

| | id | nname |

+-+ +

| | 1 | bbb |

| | 2 | ddd |

| | 4 | fff |

+-+ +

3 rows in set (0.00 sec)

View data on linux-node2 slave

Mysql > select * from test.temp2

+-+ +

| | id | nname |

+-+ +

| | 1 | bbb |

| | 2 | ddd |

| | 4 | fff |

+-+ +

3 rows in set (0.00 sec)

Conclusion

Update data on any master side, and any other side can update data

The two servers are configured with self-increasing intervals and different data conflicts.

Install mycat on linux-node1

Install mycat

Cd / usr/local/src

Wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

Tar-zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

Mv mycat / data/app/mycat-1.6

Ln-s / data/app/mycat-1.6 / data/app/mycat

Modify schema.xml

Balance= "1"

All readHost and stand by writeHost participate in load balancing of select statements

WriteType= "0"

All write operations are sent to the first writeHost of the configuration, and the first one hangs the second writeHost that is still alive. After restarting, the switch shall prevail. The switch is recorded in the configuration file: dnindex.properties.

SwitchType= "1"

The default is 1, which switches automatically

Cd / data/app/mycat

Cp conf/schema.xml conf/schema.xml.bak

Cat > conf/schema.xml show databases

+-+

| | DATABASE |

+-+

| | TESTDB |

+-+

1 row in set (0.00 sec)

Mysql > use TESTDB

Mysql > insert into temp2 (nname) values ('eee')

Query OK, 1 row affected (0.09 sec)

Mysql > insert into temp2 (nname) values ('ggg')

Query OK, 1 row affected (0.01sec)

The linux-node3 slave side checks whether the data is synchronized.

Mysql > select * from test.temp2

+-+ +

| | id | nname |

+-+ +

| | 1 | bbb |

| | 2 | ddd |

| | 4 | fff |

| | 5 | eee |

| | 7 | ggg |

+-+ +

5 rows in set (0.00 sec)

It was found that the data was written to the linux-node2 slave side.

test

Automatic service migration

Turn off the MySQL service for linux-node2 master

Mysql > shutdown

Query OK, 0 rows affected (0.01 sec)

Shell > ss-lntup | grep 3306

The mycat side inserts new data to check whether the data is synchronized

Mysql > insert into temp2 (nname) values ('mmmm')

Query OK, 1 row affected (0.07 sec)

Mysql > insert into temp2 (nname) values ('nnnn')

Query OK, 1 row affected (0.01sec)

The linux-node3 slave side checks whether the data is synchronized.

Mysql > select * from test.temp2

+-+ +

| | id | nname |

+-+ +

| | 1 | bbb |

| | 2 | ddd |

| | 4 | fff |

| | 5 | eee |

| | 7 | ggg |

| | 8 | mmmm |

| | 10 | nnnn |

+-+ +

7 rows in set (0.00 sec)

The linux-node2 slave side checks whether the data is synchronized.

Mysql > select * from test.temp2

+-+ +

| | id | nname |

+-+ +

| | 1 | bbb |

| | 2 | ddd |

| | 4 | fff |

| | 5 | eee |

| | 7 | ggg |

+-+ +

5 rows in set (0.00 sec)

It is found that the data cannot be synchronized because the master end of linux-node2 has been hung up.

Is the data access normal?

Log in to the mycat server and execute the following command:

Mysql > select * from temp2

+-+ +

| | id | nname |

+-+ +

| | 1 | bbb |

| | 2 | ddd |

| | 4 | fff |

| | 5 | eee |

| | 7 | ggg |

| | 8 | mmmm |

| | 10 | nnnn |

+-+ +

7 rows in set (0.00 sec)

The result of performing multiple discoveries is the same, indicating that when a master is hung up, the slave end of its connection is also removed, so data integrity can be guaranteed.

Fault summary

When configured for the first time, log-slave-updates was not configured on the maser side, resulting in no data on the node2-master side on the node3-slave.

Explanation:

If the log-bin parameter is enabled from the library, the log-bin log can be recorded if the data is written directly to the slave library, but the binlog log will not be recorded if the slave library reads the master database binary log file through the I0 thread and then writes it through the SQL thread. In other words, the data copied from the master library by the slave library is not written to the binlog log of the slave library. So when the slave library is used as the master library of other slave libraries, you need to add the log-slave-updates parameter to the configuration file.

Solution:

[mysqld]

Log-slave-updates

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.