New mysql multi-thread master-slave replication _ PHP Tutorial

The new version of mysql builds multi-threaded master-slave replication. New mysql build multi-thread master-slave replication 1: yuminstall-ymysql-community-cl new mysql build multi-thread master-slave replication

I. 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]

Obtain the initial password:
Grep 'temporary password'/var/log/mysqld. log
Mysql-p Press enter and enter the initial password

Change password:
Alter user root @ localhost identified by 'redhat007! '
Password Rules: must be greater than eight characters long and contain uppercase letters, lowercase letters, numbers, and special characters.

Create a database xp1 in it and use it for testing.
Mysql> create database xp1;
Then I created add. SQL under/mnt (for later use) code as follows:

 
 

  
  
Create table usertb (
 
  
  
Id serial,

  
  
Uname varchar (20 ),

  
  
Ucreatetime datetime,

  
  
Age int (11)

  
  
)

  
  
ENGINE = MYISAM

  
  
Default character set = utf8 COLLATE = utf8_general_ci

  
  
AUTO_INCREMENT = 1

  
  
ROW_FORMAT = COMPACT;

  
  


  
  
Delimiter $

  
  
Set autocommit = 0 $

  
  


  
  
Create procedure test1 ()

  
  
Begin

  
  
Declare v_cnt decimal (10) default 0;

  
  
Dd: loop

  
  
Insert into usertb values

  
  
(Null, 'user 1', '2017-01-01 00:00:00 ', 20 ),

  
  
(Null, 'user 2', '2017-01-01 00:00:00 ', 20 ),

  
  
(Null, 'user 3', '2017-01-01 00:00:00 ', 20 ),

  
  
(Null, 'user 4', '2017-01-01 00:00:00 ', 20 ),

  
  
(Null, 'user 5', '2017-01-01 00:00:00 ', 20 ),

  
  
(Null, 'user 6', '2017-01-01 00:00:00 ', 20 ),

  
  
(Null, 'user 7', '2017-01-01 00:00:00 ', 20 ),

  
  
(Null, 'user 8', '2017-01-01 00:00:00 ', 20 ),

  
  
(Null, 'user 9', '2017-01-01 00:00:00 ', 20 ),

  
  
(Null, 'user 0', '2017-01-01 00:00:00 ', 20)

  
  
;

  
  
Commit;

  
  
Set v_cnt = v_cnt + 10;

  
  
If v_cnt = 10000000 then leave dd;

  
  
End if;

  
  
End loop dd;

  
  
End; $

  
  


  
  
Delimiter;
 
 

Then execute the code in the newly created database.

Trigger the stored procedure in the code
Call test1 inserts 10 million rows of data in the usertb table
The following shows that the data has been inserted.

Modify the code, create usertb1 in the table, and then continue triggering. write 10 million rows of data to it.
(Create two tables to make the difference between mysqldump and mysqlpump more obvious)
The creation is successful as follows


Test mysqldump and mysqlpump
Mysqldump:

Mysqlpump:
Time mysqlpump-p xp1> xp1. SQL

As shown above, it is much faster than 10 seconds, and the default number of threads of mysqlpump is 2.
Time mysqlpump-pRedhat006! -- Default-parallelism = 4 db1> db1. SQL
-- Default-parallelism = 4 you can modify the number of threads by 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!
Run the following command to check whether the operation is successful:

Create database test;

Authorization:
Mysql> grant replication slave on *. * to xpp @ '2017. 25.254.11 'identified by 'redhat007! ';
Query OK, 0 rows affected, 1 warning (0.40 sec)
/Etc/init. d/mysqld restart

Slave end: 172.25.254.11
Vim/etc/my. cnf
Server-id = 2 is different from master.
Then install the latest mysql version on slave.
First, verify that the master is successfully authorized.
Mysql-pRedhat007! -Uxpp-h172.25.254.10 if you can log on to it, it will prove successful.

Mysql-pRedhat007!
Create a test database because the content of the two databases must be consistent before synchronization.
Then: chang master to master_host = '192. 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 is the addition of a global transaction ID (GTID) to enhance database master/slave consistency, fault recovery, and fault tolerance capabilities.
Vim/etc/my. cnf add the following two lines to enable gtid mode

Gtid-mode = on
Enforce-gtid-consistency = on

/Etc/init. d/mysqld restart

And then on slave
Vim/etc/my. cnf is added as follows
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

First get the 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-cl...