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

Mysql AB replication:

Three hosts: MASTER IP:172.25.35.21

SLAVE1 IP:172.25.35.22

SLAVE2 IP:172.25.35.19

MYSQL version: mysql5.7

Download the installation package

Mysql-community-client-5.7.11-1.el6.x86_64.rpm

Mysql-community-common-5.7.11-1.el6.x86_64.rpm

Mysql-community-libs-5.7.11-1.el6.x86_64.rpm

Mysql-community-libs-compat-5.7.11-1.el6.x86_64.rpm

Mysql-community-server-5.7.11-1.el6.x86_64.rpm

Installation

Yum install *-y installs all

When starting mysql version 5.7, the initial root password will be randomly generated when it starts.

Cat / var/log/mysqld.log | grep password filters out the initial password

The initial password is: wy+egrCeB1Ul

Mysql_secure_installation initializes the database

Changing the database root password version 5.7 requires passwords to include uppercase and lowercase letters, special symbols, and numbers

Mysql-uroot-p log in with the newly changed password

On the master host:

Vim / etc/my.cnf

Add:

Server-id=1

Log-bin=mysql-bin

The database synchronized by binlog-do-db=moto is moto

Binlog-ignore-db=mysql is not synchronized with mysql

/ etc/init.d/mysqld restart restart mysqld every time you change the my.cnf file

Log in to mysql

Mysql > CREATE DATABASE moto; add database

Mysql > SHOW DATABASES; View the database

Mysql > GRANT REPLICATION SLAVE ON *. * TO pop@'172.25.35.22' IDENTIFIED BY 'Poiu@123'; authorizes slave1 host 172.25.35.22 to log in as pop user

Mysql > SHOW MASTER STATUS; to view MASTER status

Write down the file name and position number

On the slave1 host

Use pop user login to verify whether the authorization is successful

[root@mysql2 mysql] # mysql-h 172.25.35.21-u pop-p

Enter password: enter the pop user password

Mysql > SHOW DATABASES

Vim / etc/my.cnf

Add:

Server-id=2

/ etc/init.d/mysqld restart restart the database

Log in to the mysql of the slave1 host

Before mysql > CREATE DATABASE moto; synchronization, make sure that the database information of master is consistent with that of slave host, so create a moto database for slave1.

Mysql > CHANGE MASTER TO MASTER_HOST='172.25.35.21',MASTER_USER='pop'

MASTER_PASSWORD='Poiu@123',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1817

Mysql > START SLAVE

Mysql > SHOW SLAVE STATUS\ G; View slave status

The information you see is the same as the one you just set.

It is normal to see IO/SQL as yes.

Test:

In the master host database

Mysql > USE moto

Mysql > CREATE TABLE GP (NUM VARCHAR (15) NOT NULL,TEAM VARCHAR (25) NOT NULL); create table GP

Mysql > INSERT INTO GP VALUES ('93century dada'); add information to the table

Mysql > INSERT INTO GP VALUES ('46th mother Yamaha')

On the slave1 host

Log in to database

Mysql > USE moto

Mysql > SELECT * FROM GP

You can see that the information just added by master has been synchronized to the slave host.

One-way synchronization completed

One-way master > slave1 > slave2

Principle: master is both the master,slave1 of slave1 and the master of slave2. Step-by-step synchronization slave2 only synchronizes the data on the slave1 instead of directly fetching the data on the master.

Configuration:

Keep master unchanged and add slave2 172.25.35.19 host

Install the required software on slave2, start, and change the database root user password

Vim / etc/my.cnf

Add:

Server-id=3 the server-id of each host is unique, and a file function is enabled for Meimei.

Restart mysqld

# Don't forget that the host for synchronization and the initial database state must be consistent. Before configuring slave2, copy a copy of the database data on master to slave2 and import it into the database.

On master

Mysqldump-u root-p moto > moto.back back up the information of the moto database

Scp moto.back 172.25.35.19:/mnt copy a copy to slave2

On slave2

Mysql-uroot-p moto

< /mnt/moto.back 将备份的导入数据库中 注意slave2本地数据库重要先创建moto数据库 这时slave2与master数据库信息一致 在slave1上 vim /etc/my.cnf 添加： log-bin=mysql-bin 定义二进制日志 binlog-do-db=moto binlog-ignore-db=mysql log-slave-updates 保存后重启mysqld服务 登陆数据库 授权： mysql>

GRANT REPLICATION SLAVE ON *. * TO pop@'172.25.35.19' IDENTIFIED BY 'Poiu@123'; authorizes the slave2 host

Mysql > SHOW MASTER STATUS

On the slave2 host

Enter the database:

Mysql > CHANGE MASTER TO MASTER_HOST='172.25.35.21',MASTER_USER='pop',MASTER_PASSWORD='Poiu@123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=447; specifies that the master host for synchronizing data is 172.25.35.21

Mysql > START SLAVE

Mysql > SHOW SLAVE STATUS\ G

It is successful to see that IO/SQL is yes and there is no error.

When you change the database information on the master side, you can see that both the slave1 and slave2 host information are changing.

GTID AB replication:

Use master with slave1

On master:

Vim / etc/my.cnf

Add:

Gtid-mode=on opens the gtid module

Enforc e-gtid-consistency=on

On slave1

Vim / etc/my.cnf

Comment out what was just added as master:

# log-bin=mysql-bin

# binlog-do-db=westos

# binlog-ignore-db=mysql

# log-slave-updates

Add:

Gtid-mode=on opens the gtid module

Enforc e-gtid-consistency=on settings force synchronization to ensure the consistency of master-slave data.

Restart mysqld on both hosts

AB replication with GTID requires a new change master operation

On slave1

Access to the database for execution

Mysql > change master to master_host='172.25.35.21',master_user='pop',master_password='Poiu@123',master_auto_position=1

On master

After entering the database and adding to the table of the shared database twice, you can see on the slave1

GTID multithreaded replication:

On slave1:

Vim / etc/my.cnf

Add:

Slave-parallel-type=LOGICAL_CLOCK

The number of threads specified by slave-parallel-workers=16 is officially recommended to be 160, which means that AB replication is used instead of multithreading.

Master_info_repository=TABLE

Relay_log_info_repository=TABLE

/ etc/init.d/mysqld restart restart the database every time you change the file

Log in to database

Mysql > show processlist

You will see sixteen threads. The multithread parallel replication of mysql5.7 solves the problem of replication delay which has been criticized by mysql in the past, and greatly improves the replication efficiency of the slave computer.

* when the synchronization is done, the data on the slave host cannot be changed, only the data of master can be changed.

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.