Enabling GTID in MySQL

What is GTID ?

GTID stands for Global Transaction Identifier. This is a new feature introduced in 5.6 version.

I have given a link in reference section of this article which explains the concept of GTID.

Global transaction identifier is a unique transaction ID assigned to every transaction that happens in MySQL database. This ID has specific importance in maintaining replication slave. Using this ID we can easily track which transactions has been applied to slave and which are yet to be applied. This also prevents applying same transaction again to slave.

Here is how it works. When the master commits a transaction, it generates an identifier consisting of two components:

  • The first component is the server’s server_uuid (server_uuid is a global variable introduced in MySQL 5.6 which holds a UUID, i.e. a 128-bit random number generated so that the probability that two servers in the world have the same server_uuid is so small that we can assume every UUID is unique).
  • The second component is an integer; it is 1 for the first transaction committed on the server, 2 for the second transaction, and so on. Much like an autoincrement column, it is incremented by one for each commit.

The first component ensures that two transactions generated on different servers have different GTIDs. The second component ensures that two transactions generated on the same server have different GTIDs.

In text, we write the GTID as “UUID:N”, e.g., 22096C54-FE03-4B44-95E7-BD3C4400AF21:4711.

The GTID is written as an event to the binary log prior to the transaction

The binary log including the GTID is replicated to the slave. When the slave reads the GTID, it makes sure that the transaction retains the same GTID after it has been committed on the slave. Thus, the slave does not generate a new GTID;

Just to give a brief idea about binary logs – Binary logs are basically logs written for every transactions. They are like the archive logs/online redo logs in Oracle database terminology. I also see GTID is kind of a SCN number in Oracle database terminology.

Steps to enable GTID

You need to set following parameters either in /etc/my.cnf or while starting MySQL instance to enable GTID.

enforce_gtid_consistency=true (Starting with MySQL 5.6.9, –disable-gtid-unsafe-statements is now named –enforce-gtid-consistency) 

If you are starting the instances using above parameters as arguments inline with mysqld_safe binary, you need to replace underscore(_) with hypen(-) in above parameters and include double hypens at the beginning as below

--enforce-gtid-consistency=true (Starting with MySQL 5.6.9, –disable-gtid-unsafe-statements is now named –enforce-gtid-consistency) 

I have updated /etc/my.cnf with above parameters and bounced MySQL instance

I can see GTID is now enabled

mysql> show global variables like '%GTID%';
| Variable_name | Value |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |

mysql> show variables like '%gtid_next%';
| Variable_name | Value |
| gtid_next | AUTOMATIC |

mysql> select @@global.gtid_executed;
| @@global.gtid_executed |
| |
1 row in set (0.00 sec)

I will now import a database named “deo” into this MySQL instance.

Exporting deo database from my desktop server MySQL instance:

advait.desktop$ mysqldump --databases deo -u root -p > deo.sql
Enter password:

Importing deo database into my new MySQL instance:

/usr/local/mysql/bin/mysql -u root < deo.sql

If I check in my new MySQL instance, I can see deo database

mysql> show databases;
| Database |
| information_schema |
| deo |
| mysql |
| performance_schema |
| test |
5 rows in set (0.00 sec)

If I check the GTID value now

mysql> select @@global.gtid_executed;
| @@global.gtid_executed |
| 2441a23c-b4ae-11e3-9d0b-80c16e2272cc:1-21 |
1 row in set (0.00 sec)

It means that 21 transactions has happened.


GTID concepts – http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html


Hope this helps !!


Installing MySQL Utilities

MySQL utilities are set of scripts provided for DBAs for general database administration. These are basically command line utilities which can be used to perform various tasks.

Following is the listing of MySQL utility version 1.3 that I installed in my environment

advait.desktop$ ls -lL /usr/local/bin/mysql*
-rwxr-xr-x 1 root root 10546 Mar 26 00:34 /usr/local/bin/mysqlauditadmin
-rwxr-xr-x 1 root root 13443 Mar 26 00:34 /usr/local/bin/mysqlauditgrep
-rwxr-xr-x 1 root root 10142 Mar 26 00:34 /usr/local/bin/mysqldbcompare
-rwxr-xr-x 1 root root 13509 Mar 26 00:34 /usr/local/bin/mysqldbcopy
-rwxr-xr-x 1 root root 14781 Mar 26 00:34 /usr/local/bin/mysqldbexport
-rwxr-xr-x 1 root root 12719 Mar 26 00:34 /usr/local/bin/mysqldbimport
-rwxr-xr-x 1 root root 9551 Mar 26 00:34 /usr/local/bin/mysqldiff
-rwxr-xr-x 1 root root 6339 Mar 26 00:34 /usr/local/bin/mysqldiskusage
-rwxr-xr-x 1 root root 13554 Mar 26 00:34 /usr/local/bin/mysqlfailover
-rwxr-xr-x 1 root root 16487 Mar 26 00:34 /usr/local/bin/mysqlfrm
-rwxr-xr-x 1 root root 5218 Mar 26 00:34 /usr/local/bin/mysqlindexcheck
-rwxr-xr-x 1 root root 4717 Mar 26 00:34 /usr/local/bin/mysqlmetagrep
-rwxr-xr-x 1 root root 5298 Mar 26 00:34 /usr/local/bin/mysqlprocgrep
-rwxr-xr-x 1 root root 6452 Mar 26 00:34 /usr/local/bin/mysqlreplicate
-rwxr-xr-x 1 root root 14169 Mar 26 00:34 /usr/local/bin/mysqlrpladmin
-rwxr-xr-x 1 root root 5612 Mar 26 00:34 /usr/local/bin/mysqlrplcheck
-rwxr-xr-x 1 root root 5796 Mar 26 00:34 /usr/local/bin/mysqlrplshow
-rwxr-xr-x 1 root root 7228 Mar 26 00:34 /usr/local/bin/mysqlserverclone
-rwxr-xr-x 1 root root 4881 Mar 26 00:34 /usr/local/bin/mysqlserverinfo
-rwxr-xr-x 1 root root 6194 Mar 26 00:34 /usr/local/bin/mysqluc
-rwxr-xr-x 1 root root 6667 Mar 26 00:34 /usr/local/bin/mysqluserclone

You can find the complete usage of these scripts in official documentation of mysql utilities. I have provided the link for the same in reference section of this article.

I will cover the installation part of MySQL utilities in this article.

Step 1) Download MySQL utilities from http://dev.mysql.com/downloads/tools/utilities/ location

Select the OS as “Linux – Generic” and download .tar.gz file

Step 2) Extract the file

Copy the file to the server on which you have installed MySQL or on any server where you want to install utilities and extract the tar file.

In my case I am going to install this on my desktop where MySQL is running

So my tar file is residing at /home/advait/mysql/mysql-utilities-1.3.6.tar.gz

I can extract the content in one of the directory under my MySQL installation.

So I extracted above tar file at /usr/local/mysql

cd /usr/local/mysql
tar xvfz /home/advait/mysql/mysql-utilities-1.3.6.tar.gz

Step 3) Build and install utilities

Once you extract you will see mysql-utilities-1.3.6 directory. Inside this directory we set setup.py

Use setup.py to build and install utilities

Build should be done using mysql (non root) user. In my case I am using advait user to start/stop and manage mysql. So I will build using advait user

python setup.py build
advait.desktop$ python setup.py build
running build
running build_py
creating build
creating build/lib
creating build/lib/mysql
copying mysql/__init__.py -> build/lib/mysql
creating build/lib/mysql/utilities
copying mysql/utilities/exception.py -> build/lib/mysql/utilities
copying mysql/utilities/__init__.py -> build/lib/mysql/utilities
creating build/lib/mysql/utilities/command
copying mysql/utilities/command/read_frm.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/rpl_admin.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/serverinfo.py -> build/lib/mysql/utilities/command
copying mysql/utilities/command/utilitiesconsole.py -> build/lib/mysql/utilities/command
changing mode of build/scripts-2.7/mysqlserverclone from 644 to 755
changing mode of build/scripts-2.7/mysqldbcompare from 644 to 755
changing mode of build/scripts-2.7/mysqlserverinfo from 644 to 755
changing mode of build/scripts-2.7/mysqluserclone from 644 to 755

Install should be done using “root” user since we have installed MySQL using root user. If you use any other user (other than root), that user may not have permissions on python libraries and so install can fail.

advaitd-2.desktop$ python setup.py install
running install
running build
running build_py
running build_scripts
running install_lib
creating /usr/local/lib/python2.7/site-packages/mysql
copying build/lib/mysql/__init__.py -> /usr/local/lib/python2.7/site-packages/mysql
creating /usr/local/lib/python2.7/site-packages/mysql/utilities
copying build/lib/mysql/utilities/exception.py -> /usr/local/lib/python2.7/site-packages/mysql/utilities
creating /usr/local/lib/python2.7/site-packages/mysql/utilities/common
copying build/lib/mysql/utilities/common/lock.py -> /usr/local/lib/python2.7/site-packages/mysql/utilities/common
changing mode of /usr/local/bin/mysqldbexport to 755
changing mode of /usr/local/bin/mysqldbimport to 755
changing mode of /usr/local/bin/mysqlindexcheck to 755
changing mode of /usr/local/bin/mysqlrplcheck to 755
changing mode of /usr/local/bin/mysqlserverinfo to 755
changing mode of /usr/local/bin/mysqlfrm to 755
running install_egg_info


Step 4) Testing

Once install is done, you can verify running one of the script if things are working fine.

All MySQL utilities will get installed at /usr/local/bin directory and they should be part of PATH variable. So no need to give complete path.


advait.desktop$ mysqldiskusage --server=root:<password>@localhost
# Source on localhost: ... connected.
# Database totals:
| db_name | total |
| deo | 546,720 |
| mysql | 1,542,216 |
| performance_schema | 396,071 |
Total database disk usage = 2,485,007 bytes or 2.37 MB

You might face following error if python connector module is not installed

advait.desktop$ mysqldiskusage --server=root:<password>@localhost
Traceback (most recent call last):
 File "/usr/local/bin/mysqldiskusage", line 35, in <module>
 from mysql.utilities.common.server import connect_servers
 File "/usr/local/lib/python2.7/site-packages/mysql/utilities/common/server.py", line 31, in <module>
 import mysql.connector
ImportError: No module named connector

If you face this issue, you need to install python connector from http://dev.mysql.com/downloads/connector/python/ location

Step 5) Installing python connector

Again download the connector from http://dev.mysql.com/downloads/connector/python/ location

scp the same to the machine on which you were installing MySQL utilities

Extract the .tar.gz file to one of the directories in MySQL installation. In my case I am installing them under utilities directory – /usr/local/mysql/mysql-utilities-1.3.6

Build using non-root user (advait in my case).

python setup.py build

Install using root user

python setup.py install

After this you should be good to use MySQL utilities.


MySQL Utilities download – http://dev.mysql.com/downloads/tools/utilities/

Connector download – http://dev.mysql.com/downloads/connector/python/

MySQL Utilities overview – http://dev.mysql.com/doc/mysql-utilities/1.3/en/utils-overview.html

Hope this helps !!