New MySQL build multi-threaded master-slave copy _php tutorial

New 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 entering the carriage

Change Password:
ALTER USER root@localhost identified by ' redhat007! '
Password rules: must be greater than eight digits, with uppercase, lowercase, numeric, special characters

And then create the database in it XP1 back to the test.
mysql> CREATE DATABASE Xp1;
Then I created the Add.sql under/mnt (handy for later use) with the following code:

 
 

  
  

  
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 (+) default 0;

  
  

  
Dd:loop

  
  

  
INSERT INTO USERTB values

  
  

  
(NULL, ' User 1 ', ' 2010-01-01 00:00:00 ', 20),

  
  

  
(NULL, ' User 2 ', ' 2010-01-01 00:00:00 ', 20),

  
  

  
(NULL, ' User 3 ', ' 2010-01-01 00:00:00 ', 20),

  
  

  
(NULL, ' User 4 ', ' 2010-01-01 00:00:00 ', 20),

  
  

  
(NULL, ' User 5 ', ' 2011-01-01 00:00:00 ', 20),

  
  

  
(NULL, ' User 6 ', ' 2011-01-01 00:00:00 ', 20),

  
  

  
(NULL, ' User 7 ', ' 2011-01-01 00:00:00 ', 20),

  
  

  
(NULL, ' User 8 ', ' 2012-01-01 00:00:00 ', 20),

  
  

  
(NULL, ' User 9 ', ' 2012-01-01 00:00:00 ', 20),

  
  

  
(null, ' user 0 ', ' 2012-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 that code in the newly created database.

To trigger stored procedures in code
Call Test1 inserts 10 million rows of data into the USERTB table
The following proves that the data has been inserted

Modify the code, create a table usertb1 to it, and then continue to trigger, write 10 million rows of data inside
(Create two tables to make the difference between mysqldump and mysqlpump more obvious)
The following proves the creation success


Test mysqldump and Mysqlpump
Mysqldump

Mysqlpump:
Time Mysqlpump-p xp1 > Xp1.sql

As above, obviously 10 seconds faster, this also uses the default thread number of Mysqlpump 2
Time mysqlpump-predhat006! --default-parallelism=4 db1 > Db1.sql
--default-parallelism=4 use the number of threads to modify it yourself

Make A-b copy of MySQL
Configuration environment: master:172.25.254.10
slave:172.25.254.11
First, in the master mainframe.
Vim/etc/my.cnf
Server-id=1
Log-bin=mysql-bin
Binlog-do-db=test
/etc/init.d/mysqld restart
mysql-predhat007!
Take a look at the following command to see if the success

New Test Library: Create database test;

Authorized:
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 End: 172.25.254.11
Vim/etc/my.cnf
Server-id=2 can be different from master
And then slave install the latest version of MySQL
First verify that Master is authorized for success
mysql-predhat007! -uxpp-h172.25.254.10 If you can sign in, it proves successful.

mysql-predhat007!
Create a new test library because the contents of the two databases must be consistent before synchronizing
Then: Chang 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 is the addition of a global transaction ID (GTID) to enhance the database's primary and standby consistency, fault recovery, and fault tolerance
VIM/ETC/MY.CNF Add the following two lines to open Gtid mode

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

/etc/init.d/mysqld restart

and then Slave.
VIM/ETC/MY.CNF Add 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

http://www.bkjia.com/PHPjc/1114327.html www.bkjia.com true http://www.bkjia.com/PHPjc/1114327.html techarticle new MySQL build multi-threaded master-slave copy 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-cl ...

