MySQL5.6 master-slave Replication Based on GTID, mysql5.6gtid master-slave

MySQL5.6 master-slave Replication Based on GTID, mysql5.6gtid master-slave

One of the new features of MySQL 5.6 is the addition of a global transaction ID (GTID) to enhance database master-slave consistency, fault recovery, and fault tolerance capabilities.

What is GTID?

Official document: the official definition of upload ID is: GTID = source_id: transaction_id

In MySQL 5.6, each GTID represents a database transaction. In the preceding definition, source_id indicates the uuid (server_uuid) of the master database that executes the transaction, and transaction_id indicates a self-increasing count starting from 1, indicating the nth transaction executed on the master database. MySQL will ensure the 1: 1 ing between transactions and GTID.

I. Environment preparation

Operating System: CentOS6.5 64-bit

Database Version: MySQL5.6.23

The topology is as follows:

3. Install the master database (masterdb.example.com)

1. Prepare a data storage directory and create a user

[Root @ masterdb ~] # Mkdir/data/mysqldata-p # create a data storage directory [root @ masterdb ~] # Mkdir/data/mysqlLog/logs-p # create a log storage directory [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. Install and initialize 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. output the mysql man manual to the man command search path:

Edit/etc/man. config and add the following lines:
MANPATH/usr/local/mysql/man

4. output the mysql header file to the system header file path/usr/include:

This can be achieved through simple creation links:

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

5. output the mysql database file to the system database search path:

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

Then let the system reload the system database:

[root@masterdb ~]# ldconfig

6. Modify the PATH environment variable so that the system can directly use mysql commands:

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

The Installation Process of the database is skipped.

4. Provide the configuration file/etc/my. cnf for the master slave database.

To use the replication function in MySQL 5.6, the following options should be defined in the service configuration section [mysqld:
Binlog-format:Binary log formats: row, statement, and mixed;
Note:Yes: when you set the isolation level to READ-COMMITED, you must set the binary log format to ROW, and now MySQL officially thinks STATEMENT is no longer suitable for continued use; but the mixed type is at the default transaction isolation level, the Master/Slave Data may be inconsistent;
Log-slave-updates, gtid-mode, enforce-gtid-consistency, report-port, and report-host: Used to start GTID and meet other ancillary needs;
Master-info-repository and relay-log-info-repository: enable these two functions to ensure binary and slave server security during crash;
Sync-master-info:Enabling it ensures no information is lost;
Slave-paralles-workers:Sets the number of SQL threads on the slave server. 0 indicates that the multi-thread replication function is disabled;
Binlog-checksum, master-verify-checksum, and slave-SQL-verify-checksum: All replication-related verification functions are enabled;
Binlog-rows-query-log-events:This feature can be used to record event-related information in binary logs to reduce the complexity of troubleshooting;
Log-bin:Enabling binary logs is the basic prerequisite for ensuring the replication function;
Server-id:The IDS of all servers in the same replication topology must be unique;

On the primary database:

[client]port = 3306socket = /tmp/mysql.sockdefault-character-set = utf8[mysql]no-auto-rehashdefault-character-set = utf8[mysqld]server-id = 1port = 3306user = mysqlbasedir = /usr/local/mysqldatadir = /data/mysqldatasocket = /tmp/mysql.sockdefault-storage-engine = INNODBcharacter-set-server = utf8connect_timeout = 60interactive_timeout = 28800wait_timeout = 28800back_log = 500event_scheduler = ONskip_name_resolve = ON;###########binlog##########log-bin = /data/mysqlLog/logs/mysql-binbinlog_format = rowmax_binlog_size = 128Mbinlog_cache_size = 2Mexpire-logs-days = 5log-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=4#rpl_semi_sync_master_enabled = 1slow_query_log = 1slow_query_log_file = /data/mysqlLog/logs/mysql.slowlong_query_time = 1log_error = /data/mysqlLog/logs/error.logmax_connections = 3000max_connect_errors = 32767log_bin_trust_function_creators = 1transaction_isolation = READ-COMMITTED

From the database:

[client]port = 3306socket = /tmp/mysql.sockdefault-character-set = utf8[mysql]no-auto-rehashdefault-character-set = utf8[mysqld]server-id = 205port = 3306user = mysqlbasedir = /usr/local/mysqldatadir = /data/mysqldatasocket = /tmp/mysql.sockdefault-storage-engine = INNODBcharacter-set-server = utf8connect_timeout = 60wait_timeout = 18000back_log = 500event_scheduler = ON###########binlog##########log-bin = /data/mysqlLog/logs/mysql-binbinlog_format = rowmax_binlog_size = 128Mbinlog_cache_size = 2Mexpire-logs-days = 5log-slave-updates=truegtid-mode=on enforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=4#rpl_semi_sync_slave_enabled = 1skip-slave-startslow_query_log = 1slow_query_log_file = /data/mysqlLog/logs/mysql.slowlong_query_time = 2log-error = /data/mysqlLog/logs/error.logmax_connections = 3000max_connect_errors = 10000log_bin_trust_function_creators = 1transaction_isolation = READ-COMMITTED

5. Start the mysqld service on the Master/Slave database respectively.

[root@masterdb ~]# service mysqld startStarting MySQL......          [ OK ][root@masterdb ~]# [root@slavedb ~]# service mysqld startStarting MySQL......          [ OK ][root@slavedb ~]# 

6. Create and copy users on the primary database
Copy codeThe Code is as follows: mysql> grant replication slave on *. * TO repluser@172.16.88.205 identified by 'replpassword ';
Note: 172.16.88.205 is a slave node server. If you want to authorize more nodes at a time, you can modify them as needed;

7. Start the 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;

8. view the copy 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_Master_Log_Pos: 191 Relay_Log_File: slavedb-relay-bin.000003 Relay_Log_Pos: 401 runtime: mysql-bin.000002 runtime: Yes # IO threads are running properly: yes # the SQL thread is running normally. Failed: 0 Last_Error: Skip_Counter: 0 Timeout: 191 Relay_Log_Space: 1899 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 primary: No primary: Primary: Master_SSL_Cert: Primary: Master_SSL_Key: Primary: No primary: 0 Last_IO_Error: Primary: 0 Last_ SQL _Error: Primary: Master_Server_Id: 1 Master_UUID: Primary Master_Info_File: mysql. slave_master_info SQL _Delay: 0 rows: NULL rows: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: rows: Master_SSL_Crl: rows: retrieved_Gtid_Set: Memory: 1-6 Executed_Gtid_Set: Memory: 1-3, memory: 1-6 Auto_Position: 11 row in set (0.00 sec) ERROR: No query specifiedmysql>

9. Test

Create a database on the master database:

Mysql> create database log_statics; Query OK, 1 row affected (0.11 sec) mysql> use log_statics; Database changed to the database to check whether log_statics has copied mysql> show 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 slave database.

The above is all the content of this article, hoping to help you learn.

Articles you may be interested in:

• Install and configure MySQL5.6 in windows
• Install and configure Mysql5.6.11 for Windows
• Installation and configuration process of linux mysql5.6
• Shell monitoring script instance-monitor mysql master-slave Replication
• Detailed steps for implementing mysql Hot Backup in linux (mysql master-slave replication)
• Principles and configuration methods of MySQL master-slave Replication)
• Basic Optimization Configuration of MySQL5.6
• MySQL master-slave replication configuration heartbeat function Introduction
• Mysql master-slave replication (master-slave) case
• Tutorial on using GTIDs replication protocol and interrupt protocol in MySQL