Tagged: Server server multithreading MySQL style
Gtid,global Transaction Identifiers, the global transaction identifier consists of a unique identifier for the server's UUID and transaction ID number. After MySQL 5.6, the transaction header records the server UUID, which is very simple to track.
Uuiduniversally unique Identifier, globally unique identifier.
A is master,b, C is slave, and when a goes down, B becomes new Master. c You need to copy the transaction you have and B does not have to B, and then B to become master. B and c the consultative Process for both parties ' affairs, as Gtid can do by itself. Increased MySQL downtime, service recovery speed, to a certain extent, improve the high availability of MySQL. 650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/83/A2/wKioL1d5GpygsPMIAABJeFsZe4w786.png "border=" 0 " Name= "" "261" height= ">"
Slave Multi-thread Slave multithreaded replication, where multiple databases are involved, one thread is started for each database, and multithreaded replication is meaningless when there is only one database. Slave-parallel-works=0 Disabling multithreaded replication
650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/83/A3/wKiom1d5Gpzg4ysfAABkHknb_G8353.jpg "border=" 0 " Name= "" "278" height= "276" >
MySQL 5.6 provides a number of replication management tools, dependent on python2.7 and above, and Rhel 6 on Python2.7,rhel 5 python is 2.4https://launchpad.net/ Mysql-utilities follows the GPL agreement, but does not belong to the official MySQL 650) this.width=650; "Src=" http://s3.51cto.com/wyfs02/M01/83/A2/ Wkiol1d5gpzyyl8waaa8sl555fa282.jpg "border=" 0 "name=" "461" height= ">"
Mysqlreplicate quickly initiates replication by tracking Gtid, skipping transactions that have already been replicated.
Mysqlcheck Tools to check if the replication environment meets requirements
Simple verification of deployment, rapid resolution of failures; Check if Binlog is turned on, display configuration error message; Check the connection status and replication permissions for slave
Mysqlrplshow check and display the topology map of MySQL replication
Mysqlfailover failover tool to quickly promote slave to master
Mysqlrpladmin RPL Management Tools
1 Preparation Work 1.1 Time Sync # ntpdate cn.pool.ntp.org
1.2 Host name Resolution # Vim/etc/hosts
192.168.8.30 node1.test.com192.168.8.31 node2.test.com
1.3 Creating MySQL Data Catalog # mkdir/data/mysql-p# useradd-r MySQL# chown-r Mysql.mysql/data/mysql
2 initializing MySQL# tar-xf Mysql-5.6.31-linux-glibc2.5-x86_64.tar.gz-c/usr/local/# cd/usr/local/# LN-SV mysql-5.6.31-linux-glibc2.5-x86_64/mysql# cd MySQL# Chown-r Root.mysql./*
# scripts/mysql_install_db--user=mysql--datadir=/data/mysql/
3 Copy the MySQL service script to INIT.D and join the service list# CP support-files/mysql.server/etc/init.d/mysqld# Chkconfig--add mysqld
# vim/etc/profile.d/mysql.shexport path= $PATH:/usr/local/mysql/bin# source/etc/profile.d/mysql.sh
4 Initial configuration, test can start normally, use/usr/local/mysql/my.cnf# vim here my.cnf
Datadir=/data/mysqlinnodb-file-per-table=1server-id=1 #此处slave用11log-bin=master-binsocket=/tmp/mysql.sock
Normal start
Description IB_LOGFILE0 is the InnoDB transaction log with a default size of 48M
The above configuration is the same master.
5 master5.1 Add Config # vim my.cnf
binlog-format=rowlog-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository= tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers=2binlog-checksum= crc32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-port=3306port= 3306report-host=192.168.8.30
Start Normal
5.2 See if Gtid is normal mysql> show global variables like '%gtid% '; +---------------------------------+-------+| variable_name | Value |+---------------------------------+-------+| enforce_gtid_consistency | On | | Gtid_mode | On |+---------------------------------+-------+
5.2 Viewing the Master status
Mysql> Show Master status;+---------------+----------+--------------+------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+---------------+----------+--------------+------------------+-------------------+| master.000002 | 151 | | | |+---------------+----------+--------------+------------------+-------------------+1 row in Set (0.00 sec)
5.3 View Master'sServer_uuid
Mysql> show global variables like '%uuid% '; +---------------+--------------------------------------+| variable_name | Value |+---------------+--------------------------------------+| Server_uuid | 5bf1de82-40fd-11e6-baa4-000c2989f319 |+---------------+--------------------------------------+
5.4 Licensingslave Replicationmysql> grant replication Slave on * * to ' RPL ' @ ' 192.168.8.31 ' identified by ' RPL ';mysql> flush privileges;
6 slave6.1 Add Configuration# vim My.cnfbinlog-format=rowlog-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository= tablerelay-log-info-repository=tablesync-master-info=1slave-parallel-workers=2binlog-checksum= crc32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log_events=1report-port=3306port= 3306report-host=192.168.8.31
6.2 See if Gtid is OK
Mysql> show global variables like '%gtid% '; +---------------------------------+-------+| variable_name | Value |+---------------------------------+-------+| enforce_gtid_consistency | On | | Gtid_mode | On |+---------------------------------+-------+
6.3 View Slave'sServer_uuid
Mysql> Show global variables like "%uuid%", +---------------+--------------------------------------+| variable_name | Value |+---------------+--------------------------------------+| Server_uuid | 81d29785-40fd-11e6-baa5-000c29635658 |+---------------+--------------------------------------+
mysql> Change Master to master_host= ' 192.168.8.30 ', master_user= ' rpl ', master_password= ' RPL ', master_auto_position = 1;
Description: When specifyingMaster_log_file,Master_log_pos, you need to usemaster_auto_position=0mysql>Change Master to master_host= ' 192.168.8.30 ', master_user= ' rpl ', master_password= ' rpl ', master_log_file= ' Master-bin.000006 ', master_log_pos=191, Master_auto_position=0;
6.4 Start slave copy mysql> start slave;mysql> show slave status\g*************************** 1. Row *************************** slave_io_state:waiting for Master to send event master_host:192.168.8.30 master_user:rpl master_port:3306 connect_retry:60 master_log_file:master-bin.000007 read_master_log_pos:191 relay_log_file:node2-relay-bin.000004 relay_log_pos:403 Relay_master_log_file : master-bin.000007 Slave_io_running:yes Slave_sql_running:yes
7 master view slave info mysql> show slave hosts;+-----------+--------------+------+-----------+----------------------- ---------------+| server_id | Host | Port | master_id | Slave_uuid |+-----------+--------------+------+-----------+------------------------------------- -+| 11 | 192.168.8.31 | 3306 | 1 | 81d29785-40fd-11e6-baa5-000c29635658 |+-----------+--------------+------+-----------+--------------------------- -----------+
From for notes (Wiz)
MySQL master-slave replication--mysql-5.6 based on Gtid and multi-threaded replication
