MySQL5.6 master-slave replication based on Gtid _mysql

One of the new features of MySQL 5.6 is the addition of a global transaction ID (Gtid) to enhance the database's primary provisioning consistency, failback, and fault tolerance.

What is Gtid?

Official documentation:http://dev.mysql.com/doc/refman/5.6/en/replication-gtids.html In this document, we can see that the official definition of the global transaction ID is: Gtid = source_id:transaction_id

In MySQL 5.6, each Gtid represents a database transaction. In the above definition, SOURCE_ID represents the primary library UUID (SERVER_UUID) that executes the transaction, and TRANSACTION_ID is a 1-based self-increment count representing the nth transaction executed on this main library. MySQL will guarantee a 1:1 mapping between transactions and Gtid.

I. Environmental preparedness

Operating system: CentOS6.5 64 bits

Database version: MySQL5.6.23

The topology is as follows:

Third, the installation of the main database (masterdb.example.com)

1, the preparation of data storage directory, create users

[Root@masterdb ~] #mkdir/data/mysqldata-p #创建数据存放目录
[root@masterdb ~] #mkdir/data/mysqllog/logs-p #创建日志存放目录
[Root@masterdb ~] #groupadd-R MySQL
[Root@masterdb ~] #useradd-G mysql-r-s/sbin/nologin-m-d/data/mysqldata MySQL
[Root@masterdb ~] #chown-R mysql:mysql/data/mysqldata
[Root@masterdb ~] #chown-R mysql:mysql/data/mysqllog/logs

2, installation and initialization of mysql5.6.23

[Root@masterdb ~]# tar xf mysql-advanced-5.6.23-linux-glibc2.5-x86_64.tar.gz-c/usr/local/
[Root@masterdb ~]# CD/ usr/local/
[Root@masterdb ~]# ln-sv mysql-advanced-5.6.23-linux-glibc2.5-x86_64 mysql
[Root@masterdb ~]# Chown-r root.mysql MySQL
[root@masterdb ~]# cd mysql
[Root@masterdb ~]# CP SUPPORT-FILES/MYSQL.SERVER/ETC/RC.D /init.d/mysqld
[Root@masterdb ~]# cp support-files/my-default.cnf/etc/my.cnf
[Root@masterdb ~]# chmod-+x/etc /rc.d/init.d/mysqld
root@masterdb ~]# chkconfig--add mysqld [Root@masterdb ~]# chkconfig mysqld on
[ Root@masterdb ~]#./scripts/mysql_install_db--user=mysql--datadir=/data/mysqldata/ 

3, the output of the man manual mysql to the man Command search path:

Edit/etc/man.config, add the following line:
Manpath/usr/local/mysql/man

4, output MySQL header file to the System header file path/usr/include:

This can be achieved by simply creating a link:

[Root@masterdb ~] #ln-sv/usr/local/mysql/include/usr/include/mysql

5, output MySQL library files to the system library to find the path:

[Root@masterdb ~] #echo '/usr/local/mysql/lib ' >/etc/ld.so.conf.d/mysql.conf

And then let the system reload into the system library:

[Root@masterdb ~]# Ldconfig

6, modify the PATH environment variables, so that the system can directly use the relevant commands MySQL:

[Root@masterdb ~]# vim/etc/profile.d/mysql.sh
export path= $PATH:/usr/local/mysql/bin
[Root@masterdb ~]# source/etc/profile.d/mysql.sh

From the database installation IBID., the specific process skipped.

Four, the main from the database to provide configuration files/etc/my.cnf

To use the replication feature in MySQL 5.6, the following options should be defined in the service configuration segment [Mysqld]:
binlog-format: binary log format, there are row, statement and mixed several types;
Note that when you set the isolation level to read-commited you must set the binary log format to row, and now MySQL officials think statement this is no longer suitable for continued use; But mixed type is under the default transaction isolation level. may result in inconsistent master-slave data;
Log-slave-updates, Gtid-mode, Enforce-gtid-consistency, Report-port and Report-host: For the start of the Gtid and to meet other ancillary requirements;
Master-info-repository and Relay-log-info-repository: enable both to implement the ability to guarantee binary and server security during crashes;
Sync-master-info: enable to ensure no information loss;
slave-paralles-workers: Set the number of SQL threads from the server; 0 to turn off the multi-threaded replication feature;
Binlog-checksum, Master-verify-checksum, and Slave-sql-verify-checksum: Enable replication-related checksum capabilities;
binlog-rows-query-log-events: enable it can be used in binary logging event-related information to reduce the complexity of troubleshooting;
log-bin: enable binary logging, which is the basic prerequisite to ensure the replication function;
Server-id: The ID number of all servers in the same replication topology must be unique;

On the primary database:

[Client] Port = 3306 Socket =/tmp/mysql.sock Default-character-set = UTF8 [MySQL] no-auto-rehash default-character-set = UTF8 [mysqld] Server-id = 1 Port = 3306 user = MySQL Basedir =/usr/local/mysql DataDir =/data/mysqldata socket =/tmp/ Mysql.sock Default-storage-engine = INNODB Character-set-server = UTF8 Connect_timeout = interactive_timeout = 28800 WA

It_timeout = 28800 Back_log = + Event_scheduler = on skip_name_resolve = on; ########## #binlog ########## log-bin =/data/mysqllog/logs/mysql-bin Binlog_format = Row Max_binlog_size = 128M Binlog_
Cache_size = 2M expire-logs-days = 5 log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true Master-info-repository=table relay-log-info-repository=table sync-master-info=1 slave-parallel-workers=4 #rpl_semi _sync_master_enabled = 1 Slow_query_log = 1 Slow_query_log_file =/data/mysqllog/logs/mysql.slow Long_query_time = 1 log _error =/data/mysqllog/logs/error.log Max_connections = 3000 Max_connect_errors = 32767 log_bin_Trust_function_creators = 1 Transaction_isolation = read-committed
 

From database:

[Client] Port = 3306 Socket =/tmp/mysql.sock Default-character-set = UTF8 [MySQL] no-auto-rehash default-character-set = UTF8 [mysqld] Server-id = 205 Port = 3306 user = MySQL Basedir =/usr/local/mysql DataDir =/data/mysqldata socket =/t Mp/mysql.sock Default-storage-engine = INNODB Character-set-server = UTF8 Connect_timeout = (wait_timeout = 18000 back_l og = Event_scheduler = on ########## #binlog ########## log-bin =/data/mysqllog/logs/mysql-bin Binlog_format = row max _binlog_size = 128M Binlog_cache_size = 2M expire-logs-days = 5 log-slave-updates=true gtid-mode=on enforce-gtid-consiste Ncy=true master-info-repository=table relay-log-info-repository=table sync-master-info=1 slave-parallel-workers=4 # rpl_semi_sync_slave_enabled = 1 Skip-slave-start slow_query_log = 1 Slow_query_log_file =/data/mysqlLog/logs/ Mysql.slow Long_query_time = 2 Log-error =/data/mysqllog/logs/error.log Max_connections = 3000 Max_connect_errors = 1000 0 log_bin_trust_function_creators = 1 TranSaction_isolation = read-committed

 

Start the Mysqld service on the master-slave database respectively

[Root@masterdb ~]# Service mysqld start
starting MySQL          ... [OK]
[Root@masterdb ~]# 


[root@slavedb ~]# service mysqld start
starting MySQL          ... [OK]
[Root@slavedb ~]# 

Vi. creating replication users on the primary database

Copy Code code as follows:

Mysql> GRANT REPLICATION SLAVE on *.* to repluser@172.16.88.205 identified by ' Replpassword ';

Description: 172.16.88.205 is a server from the node, if you want to authorize more nodes, you can modify according to need;

Vii. Start a replication thread from the database

mysql> change MASTER to master_host= ' masterdb.example.com ', master_user= ' repluser ', master_password= ' Replpassword ', master_auto_position=1;
Mysql>start slave;

Eight, viewing the replication status from the database

Mysql> show Slave status\g; 1.
     Row *************************** slave_io_state:waiting for master to send event master_host:masterdb.56xyl.com Master_user:repluser master_port:3306 connect_retry:60 master_log_file:mysql-bin.000002 Read_Mast er_log_pos:191 relay_log_file:slavedb-relay-bin.000003 relay_log_pos:401 relay_master_log_file:mysql-bin.000 002 Slave_io_running:yes #IO线程已正常运行 Slave_sql_running:yes #SQL线程已正常运行 replicate_do_db:replicate_ignore_d B:replicate_do_table:replicate_ignore_table:replicate_wild_do_table:replicate_wild_ignore_table:las t_errno:0 last_error:skip_counter:0 exec_master_log_pos:191 relay_log_space:1899 Until_Conditio  
    N:none until_log_file:until_log_pos:0 master_ssl_allowed:no Master_ssl_ca_file:master_ssl_ca_path: Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_master:0 master_ssl_verify_server_cert:no last_io_errno:0 last_io_error:last_sql_errno:0 Last_SQL_
    Error:replicate_ignore_server_ids:master_server_id:1 MASTER_UUID:971D7245-C3F8-11E5-8B6B-000C2999E5A5 Master_Info_File:mysql.slave_master_info sql_delay:0 Sql_remaining_delay:null Slave_sql_running_state:slave has read all relay log; 
  Waiting for the slave I/O thread to update it master_retry_count:86400 Master_bind:last_io_error_timestamp: last_sql_error_timestamp:master_ssl_crl:master_ssl_crlpath:retrieved_gtid_set:971d7245-c3f8-11e5-8b6b-0 00c2999e5a5:1-6 Executed_gtid_set:89e78301-c3f4-11e5-8b51-00505624d26a:1-3, 971d7245-c3f8-11e5-8b6b-000c2999e5a5 
 : 1-6 auto_position:1 1 row in Set (0.00 sec) Error:no query specified mysql>

ix. Testing

To create a database on the main library:

mysql> CREATE database log_statics;
Query OK, 1 row affected (0.11 sec)
 
mysql> use log_statics;
Database changed
to see if Log_statics has replicated the past mysql> show
databases from databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| log_statics | | mysql | |
performance_schema
| +--------------------+
4 rows in Set (0.01 sec)

mysql>

You can see that the Log_statics database already exists on the database.

The above is the entire content of this article, I hope to help you learn.