New version of mysql to build multi-threaded master-slave replication_PHP tutorial

The new version of mysql builds multi-threaded master-slave replication

One: First get
mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar

tar xf mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
yum install -y 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

Start ：
/etc/init.d/mysqld start
[root@vm10 mnt]# /etc/init.d/mysqld start
Initializing MySQL database: [ OK ]
Installing validate password plugin: [ OK ]
Starting mysqld: [ OK ]

Get the initial password:
grep 'temporary password' /var/log/mysqld.log
mysql -p Enter the initial password after pressing Enter

Change password:
ALTER USER root@localhost identified by 'Redhat007!'
Password rules: must be more than eight characters, uppercase, lowercase, numbers, special characters

Then Create database xp1 in it. You will need to use
mysql> create database xp1;
for later testing. Then I created add.sql under /mnt (for later use). The code is as follows:

Then execute that code in the newly created database

Trigger the stored procedure in the code
call test1 will insert 10 million rows of data into the usertb table
The following proves that the data has been inserted

Modify the code, create the table usertb1 in it, and then continue to trigger and write 10 million rows of data into it
(The main purpose of creating two tables is to make mysqldump The difference with mysqlpump is more obvious)
The following proves that the creation is successful


Test mysqldump and mysqlpump
mysqldump:

mysqlpump:
time mysqlpump -p xp1 > =4 db1 > db1.sql
--default-parallelism=4 You can modify the number of threads yourself
Make a mysql A -- B copy
Configuration environment: master: 172.25.254.10
slave: 172.25.254.11
First in the master host
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
binlog-do -db=test
/etc/init.d/mysqld restart
mysql -pRedhat007!
Use the following command to check whether it is successful


Create a new test library: create database test;

Authorization: mysql> grant replication slave on *.* to xpp@'172.25.254.11' identified by 'Redhat007!';
Query OK, 0 rows affected, 1 warning ( 0.40 sec)
/etc/init.d/mysqld restart

slave side: 172.25.254.11
vim /etc/my.cnf
server-id=2 can be different from master
Then install the latest version of mysql on the slave
First verify whether the master authorization is successful
mysql -pRedhat007! -uxpp -h172.25.254.10 If you can log in, it will prove successful

mysql -pRedhat007!
Create a new test database, because the contents of the two databases must be consistent before synchronization
Then: change master to master_host='172.25.254.10', master_user='xpp', master_password='Redhat007 !', master_log_file='mysql-bin.000001', master_log_pos=154;

/etc/init.d/mysqld restart

On the master side:
One of the new features of MySQL First, the global transaction ID (GTID) is added to strengthen the database's primary and backup consistency, fault recovery, and fault tolerance
Add the following two lines to vim /etc/my.cnf to enable gtid mode

gtid-mode=on
enforce-gtid-consistency=on

/etc/init.d/mysqld restart

and then go to slave
vim /etc/my.cnf Add the following
gtid-mode=on
enforce-gtid-consistency=on
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository =TABLE
relay_log_recovery=ON

/etc/init.d/mysqld restart