MySQL Server Deployment with Docker – Basic Installation Instructions for Both the Community and Enterprise Versions

An easy way to setup one or multiple MySQL server deployments on a single server is to use Docker – a computer program that performs operating-system-level virtualization. Docker is simple-to-use and allows you to run multiple containers at once.

A container is a standard unit of software that packages up code and all its dependencies so the application runs quickly and reliably from one computing environment to another. A Docker container image is a lightweight, standalone, executable package of software that includes everything needed to run an application: code, runtime, system tools, system libraries and settings. (Source: https://www.docker.com/resources/what-container)

In other words, think of a container as a virtual machine without the graphical user interface (GUI). There are third-party GUI’s available, but for this post, I am going to use a terminal window.

I am not a Docker expert, so I did have to spend some time figuring out the basics. But with this tutorial, you should be able to install the Docker software and a MySQL server in less than a fifteen minutes.


Let’s get started

First, you will need to download and install Docker. I am not going to cover this part, but installation is fairly straightforward. I downloaded and installed the Docker Desktop for my Mac.

I already have a MySQL instance installed on my server, so I will install this new instance using a different port number than the default port of 3306. On the server-side, I will use port 3307 to connect to the default MySQL port of 3306 inside the Docker container. By using a different external port number, I can install multiple MySQL instances on one server, but still use the default port for the MySQL instance. MySQL has their own set of Docker container images on github, and I can install MySQL directly from the command line. I don’t have to download anything separately. You can create your own local repository, but for this example, Docker will pull the latest version from MySQL’s github page.

To install MySQL, I opened a terminal window and ran the following command – changing the first port number (the external port) to 3307. The second port number is the port for the MySQL instance inside the container. You will notice that Docker first checks the local repository, and then once it can’t locate it, it goes out to github. Installation is done via the Docker run command.

$ docker run -p 3307:3306 -d --name mysql -e MYSQL_ROOT_PASSWORD=password mysql/mysql-server
Unable to find image 'mysql/mysql-server:latest' locally
latest: Pulling from mysql/mysql-server
35defbf6c365: Pull complete 
e13cf68584a3: Pull complete 
259d03b6a792: Pull complete 
892ac46af8c0: Pull complete 
Digest: sha256:8dd16a45d0e3e789f2006b608abb1bb69f1a8632a338eef89aec8d6fccda7793
Status: Downloaded newer image for mysql/mysql-server:latest
d8695b074a014f31c65112fb00ec1e5ad79d4c5ba94eb3be1d0fa424f14f414c

I can then verify to see if the MySQL container is up and running via the Docker container command:

$ docker container ls
CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS                            PORTS                               NAMES
0b55334fedcb        mysql/mysql-server   "/entrypoint.sh mysq…"   2 minutes ago      Up 3 seconds (health: starting)   33060/tcp, 0.0.0.0:3307->3306/tcp   mysql

Note:To start or stop the container, simply type use the Docker start/stop command, where mysql is the name of the container – and not the application being run inside the container:

$ docker stop mysql
mysql
$ docker start mysql
mysql

Note: If the container isn’t running and you need to start it, you will see an error like this when you try and connect to the container:

$ docker exec -it mysql bash
Error response from daemon: Container d8695b074a014f31c65112fb00ec1e5ad79d4c5ba94eb3be1d0fa424f14f414c is not running

I now have a copy of the MySQL container image stored locally on my server. I can look at all of the Docker images installed so far with the Docker images command:

$ docker images -a
REPOSITORY           TAG                 IMAGE ID            CREATED             SIZE
mysql/mysql-server   latest              39649194a7e7        2 weeks ago         289MB

I can verify if MySQL is running by using the Docker container command:

$ docker container ls -a
CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS                     PORTS               NAMES
0b55334fedcb        mysql/mysql-server   "/entrypoint.sh --ip…"   23 seconds ago      Exited (1) 22 seconds ago                       mysql

Now that I have MySQL installed and I have verified that the container is running, I can connect to the container using the Docker exec command: (The word mysql is the container name, and not the mysql database instance)

$ docker exec -it mysql bash
bash-4.2# 

After connecting, I am now at a regular Linux prompt. The MySQL data directory is stored in /var/lib/mysql, and the configuration file is in /etc/my.cnf.

bash-4.2# cd /var/lib/mysql
bash-4.2# ls -l
total 174160
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 #innodb_temp
-rw-r----- 1 mysql mysql       56 May  9 17:10 auto.cnf
-rw-r----- 1 mysql mysql      178 May  9 17:10 binlog.000001
-rw-r----- 1 mysql mysql      155 May  9 17:10 binlog.000002
-rw-r----- 1 mysql mysql       32 May  9 17:10 binlog.index
-rw------- 1 mysql mysql     1676 May  9 17:10 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 May  9 17:10 ca.pem
-rw-r--r-- 1 mysql mysql     1112 May  9 17:10 client-cert.pem
-rw------- 1 mysql mysql     1676 May  9 17:10 client-key.pem
-rw-r----- 1 mysql mysql     5456 May  9 17:10 ib_buffer_pool
-rw-r----- 1 mysql mysql 50331648 May  9 17:10 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 May  9 17:10 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 May  9 17:10 ibdata1
-rw-r----- 1 mysql mysql 12582912 May  9 17:10 ibtmp1
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 mysql
-rw-r----- 1 mysql mysql 29360128 May  9 17:10 mysql.ibd
srwxrwxrwx 1 mysql mysql        0 May  9 17:10 mysql.sock
-rw------- 1 mysql mysql        2 May  9 17:10 mysql.sock.lock
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 performance_schema
-rw------- 1 mysql mysql     1676 May  9 17:10 private_key.pem
-rw-r--r-- 1 mysql mysql      452 May  9 17:10 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 May  9 17:10 server-cert.pem
-rw------- 1 mysql mysql     1676 May  9 17:10 server-key.pem
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 sys
-rw-r----- 1 mysql mysql 12582912 May  9 17:10 undo_001
-rw-r----- 1 mysql mysql 10485760 May  9 17:10 undo_002
bash-4.2# ls -l /etc/my.cnf
-rw-r--r-- 1 root root 1239 May  9 17:10 /etc/my.cnf

I can log into MySQL the same way as if it was a regular MySQL instance. (When I created the container, I used “password” as the password, but you will want a more secure password)

bash-4.2# mysql -uroot -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Remember – since I am not connecting to the instance from outside of Docker, I don’t have to use port 3307. But, I will have to do that if I want to connect via MySQL Workbench.

Before I connect via MySQL Workbench, I will want to create a different user for this connection, and use this user for my Workbench connection:

mysql> CREATE USER 'docker'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON * . * TO 'docker'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

I can now create a MySQL Workbench connection. I will open Workbench, and click on the plus symbol to create a new connection.

I need to provide a connection name (Docker Container 3307), the hostname (127.0.0.1) and I need to specify port 3307. If you don’t have another installation of MySQL on your server, you can use the default port of 3306. I will store the password in my keychain by clicking on “Store in Keychain”.

To test and see if you have the correct information, click the “Test Connection” button

I can now use MySQL Workbench to connect to the MySQL Docker container:

That’s it. I now have MySQL installed as a Docker container, and I can access it via a terminal window or via Workbench.


MySQL Enterprise Version

The MySQL Community Edition is a great database server, but if you are going to run a database in a production environment, I would recommend you purchasing a MySQL Enterprise Edition license.

The MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime. It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications. (Source)

The steps for installing the Enterprise Edition is almost the same as the Community, but you have to download the Docker image from the Oracle Support portal (My Oracle Support). And, you will need a license to access support. If you are already a customer, login to the support web site, and go to the “Patches and Updates” tab, click on “Patch Search” and then on under “Product or Family (Advanced)”, search for the MySQL version you want and enter the description of “Docker”, then click the “Search” button.

MySQL only has the Linux version of the Enterprise Edition, but I can still install and run it on my Mac. The download file contains a tar file and a README file. For this example, the tar file is named mysql-enterprise-server-8.0.16.tar. I placed this file in my home directory, and from a terminal window, I will need to load the file into the repository using the Docker load command:

$ docker load -i mysql-enterprise-server-8.0.16.tar
d6b2dcf96e3d: Loading layer [==================================================>]  220.6MB/220.6MB
b84b6c2a237e: Loading layer [==================================================>]  8.704kB/8.704kB
141e4cf4cec5: Loading layer [==================================================>]  2.048kB/2.048kB
Loaded image: mysql/enterprise-server:8.0

I can now see the Enterprise Edition image along with the Community Edition image:

$ docker images -a
REPOSITORY                TAG                 IMAGE ID            CREATED             SIZE
mysql/mysql-server        latest              39649194a7e7        2 weeks ago         289MB
mysql/enterprise-server   8.0                 d4410562024a        2 weeks ago         337MB

To install the Enterprise Edition, I only need to change a few of the variables from before. I will also want to use port 3308, since 3306 and 3307 are in use. (Remember – the first port number is the “external” server port number and the second is the port number inside the container)

$ docker run -p 3308:3306 -d --name mysqlEE -e MYSQL_ROOT_PASSWORD=password mysql/enterprise-server:8.0

I can now see the Enterprise Edition container:

$ docker container ls -a
CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS                            PORTS                               NAMES
5b4df641d044        mysql/enterprise-server:8.0   "/entrypoint.sh mysq…"   6 seconds ago       Up 4 seconds (health: starting)   33060/tcp, 0.0.0.0:3308->3306/tcp   mysqlEE
0b55334fedcb        mysql/mysql-server            "/entrypoint.sh mysq…"   2 hours ago         Up 2 hours (healthy)              33060/tcp, 0.0.0.0:3307->3306/tcp   mysql

I can connect to docker, and open MySQL – using the container name of mysqlEE:

$ docker exec -it mysqlEE bash

bash-4.2# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

I will want to create a new user like before, and then I can create a Workbench connection as well, using port 3308.

That’s it. I now have two instances of MySQL running in two separate containers on my server.


Deleting images and containers

Here are the commands to delete any images or containers. To remove one or more specific images, use the Docker images command to see what images are available:

$ docker images -a
REPOSITORY                TAG                 IMAGE ID            CREATED             SIZE
mysql/mysql-server        latest              39649194a7e7        2 weeks ago         289MB
mysql/enterprise-server   8.0                 d4410562024a        2 weeks ago         337MB

And you can delete the image by deleting the IMAGE ID, by using the Docker rmi command:

$ docker rmi 39649194a7e7
Untagged: mysql/mysql-server:latest
Untagged: mysql/mysql-server@sha256:8dd16a45d0e3e789f2006b608abb1bb69f1a8632a338eef89aec8d6fccda7793
Deleted: sha256:39649194a7e780713ee5681d3bc5ff9e1fddaca744113d4a64ed61f67b7de601
Deleted: sha256:46837581982573a52d3af65de8ac243749c3f8bdf16043541e1a3cfcac721f6b
Deleted: sha256:e311a637abb5186c3bafe967fbb4d10c16258b4b878258ed0ceaff9a07969930
Deleted: sha256:348e9a791d8deb3d6f7ea979c768db0086dbd5172fdbe065649aebfebe509c46
Deleted: sha256:c4a7cf6a6169fb6af5316b4917b6f3417d419b5b5c1e5befd74746996088fc57

To remove a container, use the Docker container command to get a list of containers:

$ docker container ls -a
CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS                            PORTS                               NAMES
5b4df641d044        mysql/enterprise-server:8.0   "/entrypoint.sh mysq…"   6 seconds ago       Up 4 seconds (health: starting)   33060/tcp, 0.0.0.0:3308->3306/tcp   mysqlEE
0b55334fedcb        mysql/mysql-server            "/entrypoint.sh mysq…"   2 hours ago         Up 2 hours (healthy)              33060/tcp, 0.0.0.0:3307->3306/tcp   mysql

And you can delete the container by deleting the CONTAINER ID via the the Docker container command:

$ docker container rm 5b4df641d044
5b4df641d044

For more information on installing MySQL with Docker, see Deploying MySQL on Linux with Docker.

 


 

Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots 
Visit http://2044thebook.com for more information.
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition 
Visit https://amzn.to/2oPFLI0 for more information.
Advertisements

Using MySQL Shell to create a three-node MySQL InnoDB Cluster

MySQL InnoDB Cluster was introduced in MySQL version 5.7 and consists of three parts – Group Replication, MySQL Shell and MySQL Router. MySQL InnoDB Cluster provides a complete high availability solution for MySQL. In this post, I am going to explain how to setup a three-node cluster using the MySQL Shell.

Note: Visit this page to learn more about MySQL InnoDB Cluster.
Other blogs on Group Replication and InnoDB Cluster:
MySQL 8.0 Group Replication – Three-server installation
Adding a replicated MySQL database instance using a Group Replication server as the source
Replicating data between two MySQL Group Replication sets using “regular” asynchronous replication with Global Transaction Identifiers (GTID’s)
MySQL 8.0 InnoDB Cluster – Creating a sandbox and testing MySQL Shell, Router and Group Replication

To begin, I am going to install three instances of the MySQL database and MySQL Shell (both version 8.0.15) on three separate virtual machines with the IP addresses of 192.168.1.161, 192.168.1.162 and 192.168.1.163. I will do most of the work on 192.168.1.161 via the MySQL Shell.

I am not going to change the configuration file (my.cnf or my.ini) except to add a unique value for server_id for each of the three servers. I will use the last three digits of the IP addresses for the server_id. So, for 192.168.1.161, my configuration file only has this in it:

# MySQL Configuration File
[mysqld]
server_id=161   # each server needs to have a unique server number

Let’s get started

From a terminal window on 192.168.1.161, I will open the MySQL Shell using the command: mysqlsh. MySQL Shell has three modes – SQL, Javascript and Python. For this post, I will be using the SQL and JavaScript modes. Once you are in the MySQL Shell console, here are the commands to switch between the modes:

SQL mode - \sql
JavaScript Mode - \js
Python mode - \py

Each mode is highlighted with a different color. Here is a screenshot to show you what each mode looks like:

When you first open MySQL Shell, you aren’t connected to a database. I will start Shell (via mysqlsh), connect to the local instance, and then switch to SQL mode.


# mysqlsh

MySQL Shell 8.0.15-commercial

Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.


Type '\help' or '\?' for help; '\quit' to exit.

 MySQL  JS    \connect root@localhost:3306

Creating a session to 'root@localhost:3306'
Please provide the password for 'root@localhost:3306': 
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): N
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 12
Server version: 8.0.15-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use  to set one.

 MySQL  JS    \sql

Switching to SQL mode... Commands end with ;

I am starting with three fresh installations of MySQL. I shouldn’t have any databases or tables other than the default ones. And, I want to double-check to make sure there haven’t been any transactions already executed. I can do this with the SHOW MASTER STATUS\G command:

 MySQL  SQL    show master status\G

*************************** 1. row ***************************
             File: binlog.000001
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.0003 sec)

Note: I installed these servers a few times, and sometimes when I ran this command, it would show binlog.000002 instead of binlog.000001. This doesn’t matter – as long as the Executed_GTID_Set is blank.

From an OS command prompt, you can take a look at the binary log file binlog.000001, which is located inside your MySQL data directory. And the binlog.index file contains a list of the active binary logs, which in this case, is only binlog.000001.


MacVM161:data root# ls -l bin*
-rw-r—– 1 _mysql _mysql 151 Apr 3 20:48 binlog.000001
-rw-r—– 1 _mysql _mysql 16 Apr 3 20:48 binlog.index
MacVM161:data root# cat binlog.index
./binlog.000001

Since this is a new installation, I should only see the four default MySQL databases, and the four default MySQL users:

 MySQL  SQL    show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.0013 sec)

 MySQL  SQL    select user, host from mysql.user;

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.0004 sec)

When you use MySQL Shell to build your InnoDB Cluster, you can use pre-built administration commands to configure and manage the cluster. Before adding an instance to the cluster, I can check to see if the instance is suitable for InnoDB Cluster by running the dba.checkInstanceConfiguration command. To use the commands, I will switch to JavaScript mode:

 MySQL  SQL    \js

Switching to JavaScript mode…

 MySQL  JS    dba.checkInstanceConfiguration('root@localhost:3306')

Please provide the password for 'root@localhost:3306': 
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as MacVM163.local
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...

Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server.
Please use the dba.configureInstance() command to repair these issues.

{
    "config_errors": [
        {
            "action": "server_update", 
            "current": "CRC32", 
            "option": "binlog_checksum", 
            "required": "NONE"
        }, 
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "enforce_gtid_consistency", 
            "required": "ON"
        }, 
        {
            "action": "restart", 
            "current": "OFF", 
            "option": "gtid_mode", 
            "required": "ON"
        }, 
    ], 
    "status": "error"
}

From the above output (under the header ““Some configuration options need to be fixed:”“) – and normally I would need to add these variables and their correct values to the MySQL configuration file and reboot MySQL. But with MySQL Shell, I can change these variables dynamically on the server and reboot the server using the dba.configureInstance() command.

 MySQL  JS    dba.configureInstance('root@localhost:3306')

Please provide the password for 'root@localhost:3306': 
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): 
Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as MacVM161.local
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

WARNING: User 'root' can only connect from localhost.
If you need to manage this instance while connected from other hosts, new account(s) with the proper source address specification must be created.

1) Create remotely usable account for 'root' with same grants and password
2) Create a new admin account for InnoDB cluster with minimal required grants
3) Ignore and continue
4) Cancel

Please select an option [1]: 2
Please provide an account name (e.g: icroot@%) to have it created with the necessary
privileges or leave empty and press Enter to cancel.
Account Name: cluster_adm

Some configuration options need to be fixed:
+--------------------------+---------------+----------------+--------------------------------------------------+
| Variable                 | Current Value | Required Value | Note                                             |
+--------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum          | CRC32         | NONE           | Update the server variable                       |
| enforce_gtid_consistency | OFF           | ON             | Update read-only variable and restart the server |
| gtid_mode                | OFF           | ON             | Update read-only variable and restart the server |
+--------------------------+---------------+----------------+--------------------------------------------------+

Some variables need to be changed, but cannot be done dynamically on the server.
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y

Cluster admin user 'cluster_adm'@'%' created.
Configuring instance...
The instance 'localhost:3306' was configured for InnoDB cluster usage.
Restarting MySQL...

I am going to need to start Shell on the other two servers, connect to the database, and execute the dba.configureInstance command – creating the cluster_adm user as well. All three instances should also be rebooted (if they weren’t rebooted automatically).

After the reboot of the MySQL instance (mysqld), I still have MySQL Shell open, but I will need to reconnect again to the database. Notice – if I have lost my connection and I attempt to do anything – like switch to SQL mode, Shell will re-connect to the database for me:

 MySQL  JS    \sql


Switching to SQL mode... Commands end with ;
Error during auto-completion cache update: ClassicSession.runSql: Lost connection to MySQL server during query
The global session got disconnected..
Attempting to reconnect to 'mysql://root@localhost:3306'..
The global session was successfully reconnected.

I can then run dba.checkInstanceConfiguration again on all three servers to see if the instance is ready for InnoDB Cluster. (Remember to switch to JavaScript mode (\js) if you aren’t already in JavaScript mode)

 MySQL  SQL    \js

Switching to JavaScript mode…

 MySQL  JS    dba.checkInstanceConfiguration('root@localhost:3306')

Please provide the password for 'root@localhost:3306': 
Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): N
Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster...

This instance reports its own address as MacVM161.local
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 'localhost:3306' is valid for InnoDB cluster usage.

{
    "status": "ok"
}

The above status line of “status”: “ok”, confirms this instance is ready for InnoDB Cluster.

As I am doing these installs, I like to check the master status as I go along, so I will do that again. First I need to switch to SQL mode.

 MySQL  JS    \sql

Switching to SQL mode... Commands end with ;

 MySQL  SQL    show master status\G

*************************** 1. row ***************************
             File: binlog.000002
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.0002 sec)

The master status hasn’t changed, so nothing was written to the binary log.

One immediate problem from not using the default root user is you aren’t given the option to assign a password for the cluster_adm user. I can check this by querying the mysql.user table:

 MySQL  SQL    select user, host, authentication_string from mysql.user where user = 'cluster_adm';

*************************** 1. row ***************************
+-------------+------+-----------------------+
| user        | host | authentication_string |
+-------------+------+-----------------------+
| cluster_adm | %    |                       |
+-------------+------+-----------------------+

Since there isn’t a password for cluster_adm (bug 94977), I will go ahead and set it. But – I don’t want to write this to the binary log, as then it would get replicated to the other servers once I start the cluster. I will need to modify this user and set a password on the other two servers as well. I can suppress writing to the binary log with SET SQL_LOG_BIN=0; and then turn it back on with SET SQL_LOG_BIN=1;. (Don’t forget to substitute the value of new_password for your actual password)

SET SQL_LOG_BIN=0;
ALTER USER 'cluster_adm'@'%' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;


 MySQL  SQL    SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.0001 sec)

 MySQL  SQL    ALTER USER 'cluster_adm'@'%' IDENTIFIED BY 'new_password';
Query OK, 0 rows affected (0.0001 sec)

 MySQL  SQL    FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.0001 sec)

 MySQL  SQL    SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.0001 sec)

I can check again to see if the cluster_adm user now has a password:

 MySQL  SQL    select user, host, authentication_string from mysql.user where user = 'cluster_adm';

+-------------+------+------------------------------------------------------------------------+
| user        | host | authentication_string                                                  |
+-------------+------+------------------------------------------------------------------------+
| cluster_adm | %    | $A$005$',q+B<%=JJ|Mz.WH!XXX/iQ4rvG/3DzX/UharambelivesYp1oODqtNZk25     | 
+-------------+------+------------------------------------------------------------------------+

NOTICE: The cluster_adm user must have the same password on all servers in the cluster!

InnoDB Cluster uses Global Transaction Identifiers (GTIDs). “A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (the master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology.” Source

Each server in the three-node cluster will have its own GTIDs, which is composed of a Universal Unique Identifier (UUID), a colon (:) and an incremental number. To see the UUID for each server, this value is stored in the MySQL data directory in the auto.cnf file:

 # cat auto.cnf
[auto]
server-uuid=ae1a6186-5672-11e9-99b4-80e6004d84ae

Therefore, each of these three servers will have their own UUID being used in the GTIDs. The cluster itself will have a separate UUID being used in its own GTID, and this UUID is generated when the cluster is created. I have the following UUID’s for the three servers:

IP Address Server UUID
192.168.1.161 ae1a6186-5672-11e9-99b4-80e6004d84ae
192.168.1.162 cd287ef0-5672-11e9-be9a-b79ce5a797fd
192.168.1.163 d85f6086-5672-11e9-ad64-c08d80ddd285
InnoDB Cluster – to be determined –

Now I am ready to create the cluster. From the first server (192.168.1.161), I will switch to JavaScript mode, and then I will want to re-connect as the cluster_adm user.

 MySQL  JS    \js

Switching to JavaScript mode...

 MySQL  JS    \connect cluster_adm@192.168.1.161:3306

Creating a session to 'cluster_adm@192.168.1.161:3306'
Please provide the password for 'cluster_adm@192.168.1.161:3306': 
Save password for 'cluster_adm@192.168.1.161:3306'? [Y]es/[N]o/Ne[v]er (default No): N
Fetching schema names for autocompletion... Press ^C to stop.
Closing old connection...
Your MySQL connection id is 19
Server version: 8.0.15-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use  to set one.

I can create the cluster using the dba.createCluster command:

 MySQL  JS    dba.createCluster('myCluster');

A new InnoDB cluster will be created on instance 'cluster_adm@192.168.1.161:3306'.

Validating instance at 192.168.1.161:3306...

This instance reports its own address as MacVM161.local

Instance configuration is suitable.
Creating InnoDB cluster 'myCluster' on 'cluster_adm@192.168.1.161:3306'...
Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

The InnoDB Cluster was successfully created. Now when I do a SHOW MASTER STATUS\G, I will see some values under the Executed_Gtid_Set section: (I will need to switch back to the SQL mode)

 MySQL  JS    \sql

Switching to SQL mode... Commands end with ;

 MySQL  SQL    show master status\G

*************************** 1. row ***************************
             File: binlog.000001
         Position: 12406
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: ae1a6186-5672-11e9-99b4-80e6004d84ae:1-12,
c446e75c-5674-11e9-bf50-753fdb914192:1-2
1 row in set (0.0003 sec)

Under the Executed_Gtid_Set section, I have two sets of GTID’s. One is for the 192.168.1.161 server (ae1a6186-5672-11e9-99b4-80e6004d84ae), and the other is for the cluster (c446e75c-5674-11e9-bf50-753fdb914192).

Executed_Gtid_Set: ae1a6186-5672-11e9-99b4-80e6004d84ae:1-12,
c446e75c-5674-11e9-bf50-753fdb914192:1-2

The Executed_Gtid_Set shows which transactions have been applied to this server (192.168.1.161). There have been 12 transactions for the server (ae1a6186-5672-11e9-99b4-80e6004d84ae:1-12) and two transactions for the cluster (c446e75c-5674-11e9-bf50-753fdb914192:1-2). The GTID’s for the cluster should be the same on each server, as we add them to the server. Also, once you add servers to the cluster, the read-only servers will be changed to SUPER_READ_ONLY to prevent write-transactions from being applied to the read-only servers in a single-primary mode cluster. If you want to view the transactions which were executed, you can use the mysqlbinlog utility.

I can reference my earlier table of the UUID’s for all of the servers, and I can now add the UUID for the cluster (in red:

IP Address Server UUID
192.168.1.161 ae1a6186-5672-11e9-99b4-80e6004d84ae
192.168.1.162 cd287ef0-5672-11e9-be9a-b79ce5a797fd
192.168.1.163 d85f6086-5672-11e9-ad64-c08d80ddd285
InnoDB Cluster c446e75c-5674-11e9-bf50-753fdb914192

I can check the status of the cluster. (after switching back to JavaScript mode)

 MySQL  JS    \js

Switching to JavaScript mode...

 MySQL  JS    var cluster = dba.getCluster()
 MySQL  JS    cluster.status()

{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.161:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "192.168.1.161:3306": {
                "address": "192.168.1.161:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "MacVM161.local:3306"
}

Under the topology section above, we can see server 192.168.1.161 is in the cluster and has the status of ONLINE.

I am now ready to add 192.168.1.162 and 192.168.1.163 to the cluster. But first I need to make sure each server is ready to be added to the cluster using the dba.checkInstanceConfiguration command. I can run these commands from any one of the servers, but I am going to run this from the first server – 192.168.1.161. Prior to this, I went ahead and made the same changes to the MySQL configuration file (my.cnf or my.ini) as I did on 192.168.1.161.

 MySQL  JS    dba.checkInstanceConfiguration('cluster_adm@192.168.1.162:3306')

Please provide the password for 'cluster_adm@192.168.1.162:3306': 
Save password for 'cluster_adm@192.168.1.162:3306'? [Y]es/[N]o/Ne[v]er (default No): 
Validating MySQL instance at 192.168.1.162:3306 for use in an InnoDB cluster...

This instance reports its own address as MacVM162.local
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance '192.168.1.162:3306' is valid for InnoDB cluster usage.

{
    "status": "ok"
}

 MySQL  JS    dba.checkInstanceConfiguration('cluster_adm@192.168.1.163:3306')

Please provide the password for 'cluster_adm@192.168.1.163:3306': 
Save password for 'cluster_adm@192.168.1.163:3306'? [Y]es/[N]o/Ne[v]er (default No): 
Validating MySQL instance at 192.168.1.163:3306 for use in an InnoDB cluster...

This instance reports its own address as MacVM162.local
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance '192.168.1.163:3306' is valid for InnoDB cluster usage.

{
    "status": "ok"
}

Both servers are ready to go, as indicated by the “status”: “ok”.

Now I can add the other two servers. (I have already set the variable “cluster” earlier – and you only need to set “var cluster = dba.getCluster()” once per session. If you get an error, you might have to set it again)

 MySQL  JS    cluster.addInstance('cluster_adm@192.168.1.162:3306')

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Please provide the password for 'cluster_adm@192.168.1.162:3306': 
Save password for 'cluster_adm@192.168.1.162:3306'? [Y]es/[N]o/Ne[v]er (default No): 
Validating instance at 192.168.1.162:3306...

This instance reports its own address as MacVM162.local

Instance configuration is suitable.
The instance 'cluster_adm@192.168.1.162:3306' was successfully added to the cluster.

 MySQL  JS    cluster.addInstance('cluster_adm@192.168.1.163:3306')

A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Please provide the password for 'cluster_adm@192.168.1.163:3306': 
Save password for 'cluster_adm@192.168.1.163:3306'? [Y]es/[N]o/Ne[v]er (default No): 
Validating instance at 192.168.1.163:3306...

This instance reports its own address as MacVM162.local

Instance configuration is suitable.
The instance 'cluster_adm@192.168.1.163:3306' was successfully added to the cluster.

The SHOW MASTER STATUS\G should now be the same on all three servers.

 MySQL  SQL    SHOW MASTER STATUS\G

*************************** 1. row ***************************
             File: binlog.000005
         Position: 17098
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: ae1a6186-5672-11e9-99b4-80e6004d84ae:1-12,
c446e75c-5674-11e9-bf50-753fdb914192:1-14
1 row in set (0.0002 sec)

I can also check the status of the cluster, to see if all three nodes are ONLINE.

 MySQL  JS    cluster.status()

{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "192.168.1.161:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "192.168.1.161:3306": {
                "address": "192.168.1.161:3306", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.162:3306": {
                "address": "192.168.1.162:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "192.168.1.163:3306": {
                "address": "192.168.1.163:3306", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "MacVM161.local:3306"
}

All three nodes have the status of ONLINE, so the cluster is up and ready to use.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots 
Visit http://2044thebook.com for more information.
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition 
Visit https://amzn.to/2oPFLI0 for more information.

Replicating data between two MySQL Group Replication sets using “regular” asynchronous replication with Global Transaction Identifiers (GTID’s)

MySQL introduced Group Replication (GR) in version 5.7, and GR is part of the InnoDB Cluster high-availability solution. InnoDB Cluster consists of Group Replication, MySQL Shell and MySQL Router.

I am not going to explain InnoDB Cluster or Group Replication in this post. So, if you aren’t familiar with either one, I have some previous posts in which I have explained how to work with both. See:

MySQL 8.0 Group Replication – Three-server installation

MySQL 8.0 InnoDB Cluster – Creating a sandbox and testing MySQL Shell, Router and Group Replication

Adding a replicated MySQL database instance using a Group Replication server as the source


A customer wanted to know how to replicate data between two separate three-node Group Replication groups. They have two data centers in different states, and they want to use each GR group for a different application. And, they wanted to replicate the data between the two (for various reasons).

For Group Replication (GR), you must have a minimum of three nodes in the group, and you can have a maximum of nine nodes. The GR group can be either in single-primary or multiple-primary mode. A five-node Group Replication Group looks like this:

For this (fictional) example, I will be using two three-node Group Replication groups. One group is in Atlanta, Georgia, and the other is in Tampa, Florida. Besides the standard group replication requirements, there are a couple of items you need to take into consideration if you decide to try this. First, this will probably not work very well if you have a very write-heavy application. Group Replication does support multi-threading, but the replicated databases may not always be up-to-date with the source database. Also, please note that you need will need a very good network connection between the two groups. And, if one GR group completely fails, there is a good chance some of the data might not have had time to replicate over to the other group. Just remember – your replication performancee may vary. For this post, I am using six virtual machines on a single server.

The topology so far…

To replicate the data between two groups, you only need to use use “regular” MySQL asynchronous replication with Global Transaction Identifiers (GTIDs)

Note: To learn more about replicating with GTIDs, I wrote two posts explaining the “how to” – see part one and part two). GTID’s are required when using Group Replication.

For the Atlanta group, I have three instances of MySQL (version 8.0.15) with IP addresses 192.168.1.151, 192.168.1.152 and 192.168.1.153. For the Tampa group, I have three instances of MySQL (version 8.0.15) with IP addresses 192.168.1.161, 192.168.1.162 and 192.168.1.163. The groups look like this:

With our two three-node groups, I will use the primary-write node in the first group to be the source (master) database for the primary-write node (replica/slave) in the second group – and vice-versa.

In the Atlanta group, I will use the primary-write node with the IP address of 192.168.1.151 (MEMBER_HOST MacVM151.local) to be the source (master) database for the primary-write in the Tampa group (which will be the slave with IP address 192.168.1.161).

In the Tampa group, I will use the primary-write node with the IP address of 192.168.1.161 (MEMBER_HOST MacVM161.local) to be the source (master) database for the primary-write in the Tampa group (which will be the slave with IP address 192.168.1.151).

The other nodes in both groups are set to SUPER READ-ONLY, so they can’t be used as a replica (slave) database source, as the replica (slave) needs to be able to perform writes. The topology will looks something like this – with the arrows showing the way the data is replicated (or “flows”):

If that is too confusing, here is a simplified layout: (ATL = Atlanta, TPA = Tampa)

The members of each group

In the Atlanta Group, I have the following members in the Group Replication group:

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

In this group, I want the first node in the list (MEMBER_HOST MacVM151.local) to be the primary, but for some reason, the second node (MacVM152.local) is the primary. To change the PRIMARY to be the first server in the list, I can issue this command:

SELECT group_replication_set_as_primary(‘member_uuid’); (where the member_uuid is equal to the MEMBER_ID of the first node (‘c727957e-4cb6-11e9-abd5-f80a484a9c32’) from the above output.)

mysql> SELECT group_replication_set_as_primary('c727957e-4cb6-11e9-abd5-f80a484a9c32');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('c727957e-4cb6-11e9-abd5-f80a484a9c32') |
+--------------------------------------------------------------------------+
| Primary server switched to: c727957e-4cb6-11e9-abd5-f80a484a9c32         |
+--------------------------------------------------------------------------+
1 row in set (0.02 sec)

I can verify this change by checking the members again (notice the MEMBER_ROLE column):

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

The Tampa Group is ready to go – as 192.168.1.161 is the PRIMARY server:

Tampa Group:

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | fdc89b12-50d7-11e9-bfa8-012cdcc95c70 | MacVM161.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 1ef93b16-50d8-11e9-b7da-7e47ebc51826 | MacVM162.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 46012962-50d8-11e9-8dc0-de7edddaaccd | MacVM163.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

(Note: Yes, I could have used the second node with IP of 192.168.1.152, but I wanted to use the first node in each group)

Checking the state of each group

I am starting with a clean install of MySQL on all six instances. You can still do this if you have existing data in your groups, but you will need to get the other group to have a beginning set of data. When you begin replication, it won’t automatically export/backup the data and restore/import it to the other group. You will have to backup and restore the data from each group, so the new replicated server will have a starting point at a particular GTID. (I explain how to do this in this post – search for “What if my Group Replication (GR) group already has data?”).

I can see from the GET MASTER STATUS\G on both groups under the Executed_Gtid_Set that I have executed seven and three transactions on the two GR groups, respectively (these transactions were from creating and modifying the group replication views):

Atlanta Group

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2217
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)

Tampa Group

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1515
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
1 row in set (0.00 sec)

But, if you want to look at the transactions (which are stored in the binary log mysql-bin.000001 on each server) for each group to verify that you didn’t have any transactions which changed data, you can use the mysqlbinlog tool:

# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
....
SET TIMESTAMP=1553731529/*!*/;
COMMIT
....

I checked both binary logs, and all of the transactions had to do with changing the state of the group replication view. All of these transactions were SET TIMESTAMP transactions (“SET TIMESTAMP=1553731529/*!*/;”). I am now ready to start replication.

Let’s get started

As shown in the above topology images, I am going to setup the Atlanta node (MacVM151.local – IP address 192.168.1.151) to be a source (master) for the Tampa node (MacVM161.local – IP address 192.168.1.161). Since I already have a replication user created for Group Replication, I can use the same user for this “regular” asynchronous replication. If you want more details on asynchronous replication using GTID’s – see this post.

Since I already have a replication channel open with the Group Replication, I will need to specify a new channel. I will name the channel “atl_tpa_replication” (for “Atlanta to Tampa replication”), meaning the Atlanta server will be the source (master) and the Tampa server will be the replica (slave). In replication, it is the job of the replica (slave) to contact the source (master) and retrieve all of the transactions (data) that has not been applied to its database. I will open a MySQL prompt on the Tampa instance with the IP address of 192.168.1.161, and issue this CHANGE MASTER TO statement:

On Tampa

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.151',
MASTER_PORT = 3306,
MASTER_USER = 'rpl_user',
MASTER_PASSWORD = 'R3plic4tion!',
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'atl_tpa_replication';
mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.151',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'atl_tpa_replication';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.00 sec)

I can now start the slave for the “atl_tpa_replication” replication channel, and take a look at the “slave status”:

mysql> start slave for channel 'atl_tpa_replication';
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status for channel 'ATL_TPA_REPLICATION'\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.151
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 893
               Relay_Log_File: MacVM161-relay-bin-atl_tpa_replication.000004
                Relay_Log_Pos: 1099
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 893
              Relay_Log_Space: 1919
              Until_Condition: 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: 151
                  Master_UUID: c727957e-4cb6-11e9-abd5-f80a484a9c32
             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 more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: atl_tpa_replication
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

On the Atlanta group, there were seven transactions which had been applied (GTIDs of 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) – and I can tell this from when I did a “SHOW MASTER STATUS\G” from the Atlanta primary-write server (192.168.1.151):

On Atlanta

mysql>  show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2568
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)

I can see which GTID’s I have retrieved (Retrieved_Gtid_Set) and executed (Executed_Gtid_Set) from the SHOW SLAVE STATUS\G command, and these seven GTID’s (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) from the server are included:

mysql> show slave status for channel 'atl_tpa_replication'\G
...
           Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
...

Since I executed the SHOW SLAVE STATUS\G from the Tampa server, I can also see the existing Tampa GTIDs also fall under the Executed_Gtid_Set section – 160f4761-c55c-422f-8684-d086f6a1db0e:1-3.

Now I can turn on replication to go from Tampa to Atlanta, where the Tampa primary-write server is the source (master) for the Atlanta primary-write server replica (slave). After I run the CHANGE MASTER TO command, I will turn on the replica (slave) with the START SLAVE command. I will then check on the status with SHOW SLAVE STATUS\G.

NOTE: I have changed the CHANNEL name to be tpa_atl_replication (Tampa to Atlanta replication)

On Atlanta

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.161',
MASTER_PORT = 3306,
MASTER_USER = 'rpl_user',
MASTER_PASSWORD = 'R3plic4tion!',
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'tpa_atl_replication';
mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.161',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'tpa_atl_replication';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>  show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2568
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.161',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'tpa_atl_replication';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.161
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3581
               Relay_Log_File: ic1-relay_log-tpa_atl_replication.000002
                Relay_Log_Pos: 1413
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3581
              Relay_Log_Space: 1631
              Until_Condition: 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: 161
                  Master_UUID: fdc89b12-50d7-11e9-bfa8-012cdcc95c70
             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 more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: tpa_atl_replication
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

From the above, I can now see the three GTIDs from the Tampa server (160f4761-c55c-422f-8684-d086f6a1db0e:1-3) have been replicated over to the Atlanta server, and the seven GTIDs (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) from Atlanta also appear in the Executed_Gtid_Set:

           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7

Finally, since I am running Group Replication, these transactions which were replicated between the primary-write servers will also be replicated to the other two servers in each group. I execute the SHOW SLAVE STATUS\G on a secondary server (IP of 192.168.1.152) and see all of the GTID’s which have been replicated between the primary servers:

On secondary server (in Atlanta) with IP of 192.168.1.152:

mysql> show slave status\G
...
           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
...

Replicate new transactions

Since both of these servers were new installs of MySQL, each instance only has these four databases:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

I will now create a database on each server, with the GR group location name as the name of the database. These databases will then replicate over to the other groups. And, you will see the executed GTIDs on the source (master) increase from seven (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) to eight (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8).

On Atlanta:

mysql> create database Atlanta;
Query OK, 1 row affected (0.01 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 2097
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Atlanta            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

When I go to any of the group replication instances in Tampa, I can see the Atlanta database has already been replicated over to the group, and each node has executed this transaction. From node three – 192.168.1.163:

On Tampa

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Atlanta            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

And the same happens when I create a database named Tampa – it will get replicated over to the Atlanta Group Replication group:

On Tampa:

mysql> create database Tampa;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Atlanta            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| Tampa              |
+--------------------+
6 rows in set (0.00 sec)

On the Tampa Group Replication group, the GTIDs increased from three (160f4761-c55c-422f-8684-d086f6a1db0e:1-3) to four (160f4761-c55c-422f-8684-d086f6a1db0e:1-4). The other GTIDs – 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8 – are from the Atlanta Group Replication group. I can confirm this with a SHOW MASTER\G statement:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 3957
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-4,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
1 row in set (0.00 sec)

Also, I can check to see if the “Tampa” database (which was GTID 160f4761-c55c-422f-8684-d086f6a1db0e:4) has been replicated over to the Atlanta group by running a SHOW SLAVE STATUS\G on any of the Atlanta nodes. I will use the second node (IP address 192.168.1.152):

On Atlanta

mysql> show slave status\G
...
           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:4
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-4,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
...
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| Atlanta            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| Tampa              |
+--------------------+
6 rows in set (0.00 sec)

I now have two different MySQL Group Replication groups replicating data between each other.

WARNING: This should only be done if you can ensure that neither group will be modifying the other group’s data. Since replication is not instant, you could make a change on one group at the same time another person is changing the same row – and this scenario would break the replication between the two groups. Or, you could try to modify data that was modified on another group, and you will be modifying stale data.

Can I change the primary-write server in this scenario?

Since I have replication channels on both of the primary-write nodes, what happens if I want to change the primary node to another server? For example, 192.168.1.151 is the PRIMARY for the Atlanta Group Replication, but I want 192.168.1.152 to be the PRIMARY.

On Atlanta, here are the members of the group replication, with 192.168.1.151 (MacVM151.local) as the PRIMARY: (see the MEMBER_ROLE column)

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

If I try and change the second node (MEMBER_ID 247898e0-4cb7-11e9-97a9-12f28adcadd1) to be the PRIMARY server, I will get an error:

From the current PRIMARY node – which is the first node in the group – 192.168.1.151, I will try and make the second node the PRIMARY:

mysql> SELECT group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1');
ERROR 13223 (HY000): The function 'group_replication_set_as_primary' failed. There is a slave channel running in the group's current primary member.

In order for me to change the PRIMARY to the second node, I will need to stop the asynchronous replication on the first node (192.168.1.151) by issuing the STOP SLAVE command. This stops the replication between 192.168.1.151 and 192.168.1.161.

I can then change the PRIMARY to be the second node (MEMBER_ID 247898e0-4cb7-11e9-97a9-12f28adcadd1 and IP address of 192.168.1.152). Once the second node is the new PRIMARY, then I can start replication on the second node by running the CHANGE MASTER TO command on 192.168.1.152.

On 192.168.1.151 (Atlanta Group): (Note: You can change the PRIMARY from any node in the Group Replication group)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1') |
+--------------------------------------------------------------------------+
| Primary server switched to: 247898e0-4cb7-11e9-97a9-12f28adcadd1         |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

I can see the second node is the new PRIMARY: (see the MEMBER_ROLE column)

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

Now I can start replication on 192.168.1.152, with 192.168.1.161 as the source (master) by using the CHANGE MASTER TO command.

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.161',
MASTER_PORT = 3306,
MASTER_USER = 'rpl_user',
MASTER_PASSWORD = 'R3plic4tion!',
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'TPA_ATL_REPLICATION';
mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.161',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'TPA_ATL_REPLICATION';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

And, the SHOW SLAVE STATUS confirms the SQL and IO threads are running:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.161
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3957
               Relay_Log_File: ic1-relay_log-tpa_atl_replication.000002
                Relay_Log_Pos: 400
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

Note: With Group Replication, when the PRIMARY goes down, the group will automatically elect a new PRIMARY server, and the group will keep on processing transactions. If you are doing “regular” asynchronous replication from the PRIMARY and it goes down, there isn’t a way to automatically change the source (master), and replication will be broken. You will need to change the replica (slave) to another server in the Group Replication group. And, when the new PRIMARY comes back online, you will need to issue a STOP SLAVE command if the replication is still active on that server. You don’t want two replicas (slaves) attached at the same time.

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots 
Visit http://2044thebook.com for more information.
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition 
Visit https://amzn.to/2oPFLI0 for more information.

MySQL 8.0 Group Replication – Three-server installation

MySQL InnoDB Cluster was introduced in MySQL version 5.7. MySQL InnoDB Cluster consists of three parts – Group Replication, MySQL Shell and MySQL Router. MySQL InnoDB Cluster provides a complete high availability solution for MySQL. I am not going to go into the details of InnoDB Cluster and how it works, as there are enough manual pages and blogs to cover these topics.

MySQL InnoDB Cluster manual
Blog sites: mysqlhighavailability.com and mysqlserverteam.com.

Instead, I will be showing you how to install Group Replication on three new installations of mysql 8.0 manually, without using the MySQL Shell.

These instructions should enable you to setup Group Replication in less than an hour. I am doing this on a Mac running 10.13, but most of these commands can easily be translated over to Linux or Windows. I will try to supply the correct commands for all three operating systems.

I will be installing Group Replication on three new installations of MySQL (without any data) with the IP addresses (host names) of 192.168.1.151 (ic-1), 192.168.1.152 (ic-2) and 192.168.1.153 (ic-3). It is important that you don’t run any other commands on the server, and that you start with a fresh install of MySQL. If you already have a server with data, you will need to export the data and import it into the other servers before you go any further here. Starting Group Replication with a server with data requires a different set of commands, and this blog might not work in that situation.

Group Replication may be setup as either a single-primary (one server to handle the writes and two servers for reads), or multi-primary (read/write to any of the servers). This post covers setting up a single-primary configuration. The server with the IP address of 192.168.1.151 will be our single-primary (read/write server) and the other two servers will be read-only.

Let’s begin.

Edit your /etc/hosts file, and add the IP addresses and host names for the three servers.

192.168.1.151 ic-1
192.168.1.152 ic-2
192.168.1.153 ic-3

Flush the directory service cache by running this as root:

Mac - dscacheutil -flushcache
Linux - /etc/rc.d/init.d/nscd restart
Windows - ipconfig /flushdns

You will need to add some variables to your MySQL options file, which is located in these directories: (see Using Option Files)

Mac and Linux - /etc/my.cnf
Windows - C:\ProgramData\MySQL\MySQL Server X (where X is the version number of MySQL)

NOTE: On Windows, the my.ini file may be hidden.

The following variables need to be in the options file under the [mysqld] section: (you may remove the # comment lines if you want, but read each line before deleting it)

You will need to change the server_id value for each server so each has a unique ID. I simply used 1, 2 and 3 for the three servers.

# All members in the group requires a unique server_id greater than zero
server_id=1

# this is the default port for MySQL - you can change it if you want, but it should be the same on all servers
port=3306

# you may specify a name for the binary log, or leave it blank to use the default name
# however, binary logging is required
log_bin=mysql-bin  

# these settings are required
log_slave_updates=ON
binlog_checksum=NONE
enforce_gtid_consistency=ON
gtid_mode=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

# Group Replication specific options

# this is the name of the plugin
# load the plugin - in Windows, the plugin will be named group_replication.dll

plugin_load_add ="group_replication.so"

# this is required
group_replication = FORCE_PLUS_PERMANENT

# you will turn this on and back off during Group Replication setup
group_replication_bootstrap_group = OFF

# the group_replication_group_name is the UUID for the entire group
# Each server has their own UUID in the file auto.cnf located in the MySQL data directory
# You can generate your own group_replication_group_name on Linux with `uuidgen -t`, 
# on a Mac use "uuidgen" 
# for Windows - The Windows SDK comes with a tool called uuidgen
# all members use this value as group_replication_group_name

# PLEASE NOTE: this group_replication_group_name must be unique for each Group Replication (GR) group
# so each server within a group will have the same value
# Example: if you have two GR groups with three nodes in each group, then three nodes in one group
# will have the same group_replication_group_name, and the other three nodes in the different
# group will have a different group_replication_group_name
group_replication_group_name = 8E2F4761-C55C-422F-8684-D086F6A1DB0E

In the config file make sure group_replication_start_on_boot is set to OFF or add a # (comment) to the line with the value of ON as shown below. You will want to uncomment this line after you setup and start Group Replication, so if the server is rebooted, Group Replication will begin automatically.

# uncomment this line AFTER you have started Group Replication
# so that Group Replication will start after a reboot

#group_replication_start_on_boot = ON

You will need to change this line to match each of the servers, and while you can change the group_replication_local_address (or port number – the recommended default value is 33061), it doesn’t matter what port you use as long as that port isn’t being used by another application.

# change this to be the local address for each server
# the port number can be anything except ports already in use
# and do not use 3306

group_replication_local_address = '192.168.1.151:33061'

This line must contain all of the servers that will be in your group. The group_replication_group_seeds is a list of group members used to establish the connection from the new member to the group.

# add all of the members of the group here, along with the same port numbers

group_replication_group_seeds = '192.168.1.151:33061,192.168.1.152:33061,192.168.1.153:33061'

That is all you need for the configuration file. After you have made the changes, reboot the MySQL instance.


NOTE: If you installed MySQL as the root user, be sure that the OS user “mysql” owns all of the mysql directories. You will need to change the directory name (in this example it is /usr/local) to be the directory where you installed the MySQL Server.

# be sure that the mysql user own the mysql directory
# if you install MySQL via root on a Mac or Linux, there is a good chance that root owns the directory

$ cd /usr/local
$ chown -R mysql mysql*


Now we are ready to install Group Replication. Be sure to restart the mysqld processes to make the /etc/my.cnf changes permanent.

Let’s start with Server #1, which will be our read-write primary server.

On Server #1 (IP 192.168.1.151)

Since we included the variable group_replication_group_seeds in the options file, each instance has already been added to the group.

The table performance_schema.replication_group_members shows network and status information for replication group members. The network addresses shown are the addresses used to connect clients to the group, and should not be confused with the member’s internal group communication address specified by group_replication_local_address. (source: https://dev.mysql.com/doc/refman/8.0/en/replication-group-members-table.html)

You can run this command from a mysql prompt on each of the servers to see if they have joined the group successfully:

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

Or, you can run the command with the \G at the end instead of the semi-colon (;)

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: OFFLINE
   MEMBER_ROLE: 
MEMBER_VERSION: 
1 row in set (0.00 sec)

The MEMBER_ID 60889f20-48ed-11e8-b6e2-0998e2a48fe0 is the UUID for this particular MySQL instance. The UUID is located in the auto.cnf file in the MySQL data directory.

# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=60889f20-48ed-11e8-b6e2-0998e2a48fe0

You can take a look at the MASTER STATUS of the primary server, and it should be relatively blank.

# command to run from MySQL prompt

SHOW MASTER STATUS\G
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

The active binary log for the server is “mysql-bin.000001“. You can take a look at the events in the log and see nothing has happened on the server.

# command to run from MySQL prompt

SHOW BINLOG EVENTS in 'mysql-bin.000001'\G
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 151
End_log_pos: 124
       Info: Server ver: 8.0.11, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 124
 Event_type: Previous_gtids
  Server_id: 151
End_log_pos: 151
       Info: 
2 rows in set (0.00 sec)


Now go to Server #2 (IP 192.168.1.152)

Confirm that Server #2 is part of the group.

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

OPTIONAL: If you want, you can run the same informational commands for Server #2 as you did on Server #1. (SHOW MASTER STATUS, SHOW BINLOG EVENTS, cat auto.cnf)


Now go to Server #3 (IP 192.168.1.153)

Confirm that Server #3 is part of the group.

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | bedc9968-48ee-11e8-9735-0a5899f91373 | MacVM153.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

OPTIONAL: If you want, you can run the same informational commands on Server #3 as you did on Server #1. (SHOW MASTER STATUS, SHOW BINLOG EVENTS, cat auto.cnf)


Make sure that the “group_replication” plugin is active on all three servers. You value of PLUGIN_STATUS should be ACTIVE. Run this command on all three servers.

# command to run from MySQL prompt

select * from information_schema.plugins where PLUGIN_NAME = 'group_replication'\G
mysql> select * from information_schema.plugins where PLUGIN_NAME = 'group_replication'\G
*************************** 1. row ***************************
           PLUGIN_NAME: group_replication
        PLUGIN_VERSION: 1.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: GROUP REPLICATION
   PLUGIN_TYPE_VERSION: 1.2
        PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.9
         PLUGIN_AUTHOR: ORACLE
    PLUGIN_DESCRIPTION: Group Replication (1.1.0)
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: FORCE_PLUS_PERMANENT
1 row in set (0.00 sec)

You should see the same output on the other two servers (IP 192.168.1.152 and 192.168.1.152) as on Server #1.


Next you will want to create the replication users. Since we will be turning on replication, we don’t want to write this to the binary logs. Execute this command on all of the servers.

# run this on all three servers
# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

NOTE: If you are using SHA-256 for your passwords, you need to specify sha256_password in your CREATE USER statement (see below). See: https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED WITH sha256_password BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

Once the users have been created, we can start execute our CHANGE MASTER statement and start Group Replication. Execute these two commands on all of the servers.

# run this on all three servers

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

We have to create the replication users, but we don’t want to write this to the binary log, as it would get replicated to the other servers and cause an error.

On Server #1 (IP 192.168.1.151)

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

Since we are only running single-primary mode, we don’t need to do a CHANGE MASTER on Server #1. In replication, the slave is responsible for connecting to the master to get the write statements. Since the secondary nodes won’t be accepting any writes, then the primary won’t need to connect to them. But, since the primary could fail, and be brought back into the group as a read-only slave, we need to go ahead and run the following CHANGE MASTER statement.

The CHANGE MASTER statement will produce two warnings – we can look at the warnings and ignore them.

# commands to run from MySQL prompt

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Now we will need to turn group_replication_bootstrap_group to ON, and then we can start Group Replication (but do this only for on Server #1). We will want to turn group_replication_bootstrap_group to OFF after we have started Group Replication for the first time.

# commands to run from MySQL prompt

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.10 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

Check the status of the MASTER server:

# commands to run from MySQL prompt

SHOW MASTER STATUS\G
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 458
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1
1 row in set (0.00 sec)

There has only been one GTID executed on the MASTER – 8e2f4761-c55c-422f-8684-d086f6a1db0e:1. Check the binlog to see what has been written to it.

# commands to run from MySQL prompt

SHOW BINLOG EVENTS in 'mysql-bin.000001';
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |       151 |         124 | Server ver: 8.0.11, Binlog ver: 4                                 |
| mysql-bin.000001 | 124 | Previous_gtids |       151 |         151 |                                                                   |
| mysql-bin.000001 | 151 | Gtid           |       151 |         229 | SET @@SESSION.GTID_NEXT= '8e2f4761-c55c-422f-8684-d086f6a1db0e:1' |
| mysql-bin.000001 | 229 | Query          |       151 |         291 | BEGIN                                                             |
| mysql-bin.000001 | 291 | View_change    |       151 |         390 | view_id=15247058086370221:1                                       |
| mysql-bin.000001 | 390 | Query          |       151 |         458 | COMMIT                                                            |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)

We still only have one member of the Group Replication, and we can see which server is a member:

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G
mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
1 row in set (0.00 sec)

You can see one member of the group (Server #1) is online. Group Replication has been started on Server #1.

Now we can add Server #2. We will do the same steps as we did for Server #1, except we don’t need to bootstrap Group Replication, as it has already been started. Oon Server #2 (IP 192.168.1.152):

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

# commands to run from MySQL prompt

SHOW MASTER STATUS\G
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Run the CHANGE MASTER statement:

# commands to run from MySQL prompt

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The CHANGE MASTER statement produced two warnings – we can look at the warnings and ignore them.
# commands to run from MySQL prompt

SHOW WARNINGS\G
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.00 sec)

Start Group Replication on Server #2:

# commands to run from MySQL prompt

START GROUP_REPLICATION;
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.10 sec)

NOTE: If you put in the wrong password in your CHANGE MASTER statement, the server will be in the group, but will be in a MEMBER_STATE of RECOVERING and it will never join the group.

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | RECOVERING   | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

Check to see that Server #2 has joined the group. You can execute this command on Server #1 or #2. (This was executed from Server #1)

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

Or you can run it with the \G at the end.

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G
mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
2 rows in set (0.00 sec)

Running the same commands from Server #2 gets the same results, as you can query the status of the Group from any member of the group.

This was executed from Server #2

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

And the option with \G at the end:
# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G
mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
2 rows in set (0.00 sec)

Group replication now has Server #1 and Server #2. Now we can add Server #3 (IP 192.168.1.153)

We run the same commands as above. Note:I only show the commands to run – I do not show the screen output here.

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';

START GROUP_REPLICATION;

If you didn’t see any errors, Group Replication is ready to go. As before, you can check the status of the group from any server.

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G
mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: bedc9968-48ee-11e8-9735-0a5899f91373
   MEMBER_HOST: MacVM153.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
3 rows in set (0.00 sec)

Or you can run:

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | bedc9968-48ee-11e8-9735-0a5899f91373 | MacVM153.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

NOTICE: Be sure to change group_replication_start_on_boot to ON in your my.ini or my.cnf configuration file for all of the servers as shown below.

(in the my.cnf or my.ini file)
group_replication_start_on_boot = ON

If the MEMBER_STATE of three servers is ONLINE, then you are finished and Group Replication has been installed!


Creating an InnoDB Cluster

If you have an existing deployment of Group Replication and you want to use it to create a cluster, pass the adoptFromGR option to the dba.createCluster() function. The created InnoDB cluster matches whether the replication group is running as single-primary or multi-primary. See: https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-from-group-replication.html


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots 
Visit http://2044thebook.com for more information.
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition 
Visit https://amzn.to/2oPFLI0 for more information.

Adding a replicated MySQL database instance using a Group Replication server as the source

You say you want a Replication?

One of the best features of MySQL is the ability to use MySQL‘s built-in database replication feature to automatically replicate data from one server (source/master) to another (slave/replica). Group Replication was added in MySQL 5.7 as a way to provide a high-availability solution using a new variation of MySQL replication.

(In some earlier posts, I explained how to setup Group Replication using three MySQL database servers and how to create a sandbox for testing MySQL 8.0 InnoDB Cluster.)

The “regular version” of MySQL replication is still very powerful and relatively easy to setup. Some advantages of replication in MySQL include:

  • Scale-out solutions – spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.
  • Data security – because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
  • Analytics – live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.
  • Long-distance data distribution – you can use replication to create a local copy of data for a remote site to use, without permanent access to the master.
  • Source: https://dev.mysql.com/doc/refman/8.0/en/replication.html

    Even if you have Group Replication installed, you still might want to replicate that data to a separate server for various reasons. This post will explain a little about MySQL replication, and show you how to connect a replica (slave) server to a single server in a Group Replication group.

    What is replication and how does it work?

    To use MySQL replication, you need to understand something called the binary log (also called the binlog).

    The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched zero rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

    • For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 17.2, “Replication Implementation”.
    • Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. <a target=new href=https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.htmlSee Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
    • Source: https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

    The replication process is simple. The replica (slave) database connects to the source (master) database via the I/O thread and retrieves the events from the binary log which have occurred since the last time it connected to the source (master) database. The source (master) database performs a binlog dump (which contains the new events), and the replica’s I/O thread transfers this dump to the replica (slave) server, and then stores these events on the replica’s MySQL instance in a “binary-log-type” file known as the relay log. Another thread (the SQL thread) then reads the events from the relay log and applies these changes to the database. This type of replication is asynchronous replication, because it is a one-way transfer of data (See the image below). There is another replication option called semi-synchronous, which ensures that the transaction on the source (master) database is written to the relay log of the replica (slave) before the transaction is committed.

    For more information about the threads, see: https://dev.mysql.com/doc/refman/8.0/en/replication-implementation-details.html

    The replica (slave) database has two options for retrieving the transactions from the source (master) database. The first is an older method, which requires the replica (slave) server to keep track of the last binary log accessed on the master for retrieving the transactions. The replica (slave) also has to keep track of the last position within the last binary log it used. For example, the last binary log was mysql-bin.000343 and the last position in that log was 49583. So, the next time the replica (slave) connects to the source (master) database, it asks for all of the transactions which occurred after this position (49583) in this binary log (mysql-bin.000343).

    Global Transaction Identifiers

    The second method involves using global transaction identifiers (GTID), which was introduced in MySQL version 5.6. A GTID is a unique identifier created and associated with each transaction committed on the server of origin (the source/master database). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology. Source: https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-concepts.html

    The GTID is comprised of a server’s UUID (a 36-character unique server identifier), a colon, and an incremental number. A GTID for a single transaction would look something like this: 3E11FA47-71CA-11E1-9E33-C80AA9429562:23 (as in: UUID:transaction number).

    With GTID replication, the replica (slave) doesn’t need to keep track of the last binary log being used, nor does it need to remember the position with that binary log. Since the GTID’s contain incremental numbers, the replica (slave) only has to remember the last GTID it processed. For example, the source (master) server may have processed a group of GTID’s such as 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-30493. If the last GTID the replica (slave) retrieved was 3E11FA47-71CA-11E1-9E33-C80AA9429562:30201, then the replica (slave) will tell the source (master) that it needs every transaction (GTID) after 3E11FA47-71CA-11E1-9E33-C80AA9429562:30201.

    Also, using GTID’s makes it much easier to see how many transactions have been executed on the source (master) database, and how many transactions have been retrieved and applied by the replica (slave) database. On the replica (slave) database, after replication has been started, the “SHOW SLAVE STATUS” command will display something like this:


    mysql> SHOW SLAVE STATUS\G

    Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-35683
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-34652

    This tells the database administrator (DBA) that the replica (slave) server has retrieved 35683 transactions from the source (master) database, but has only applied (executed) 34652 transactions.

    GTID’s also make it easier to skip bad transactions on the slave. Let’s say that the last transaction, 34652, was “bad”. The DBA would need to view that transaction by looking at it from the relay log using the mysqlbinlog tool (example: mysqlbinlog –include-gtids=8e2f4761-c55c-422f-8684-d086f6a1db0e:34652). And, let’s assume it was safe to delete this transaction, then the DBA would issue this command, effectively skipping this transaction and setting the next GTID to be used as 8e2f4761-c55c-422f-8684-d086f6a1db0e:34653:


    STOP SLAVE;
    SET GTID_NEXT=”8e2f4761-c55c-422f-8684-d086f6a1db0e:34653″;
    BEGIN; COMMIT;
    SET GTID_NEXT=”AUTOMATIC”;
    START SLAVE;

    Group Replication

    Group Replication (GR) was introduced in MySQL version 5.7, and GR allows you to have a minimum of three servers in a high-availability group (with a maximum of nine servers). Groups can operate in a single-primary mode with automatic primary election, where only one server accepts updates at a time. Alternatively, for more advanced users, groups can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently. Source: https://dev.mysql.com/doc/refman/8.0/en/group-replication.html

    Group Replication requires the use of Global Transaction Identifiers (GTID’s). With “regular” single-source replication (master/slave), you can also use GTID’s – or the older method of specifying the binary log and position in the binary log. However, for replicating from a Group Replication group member, you must use GTID’s.

    Replicating from a member in Group Replication

    To replicate the data from a member of a Group Replication group, you simply need to point the replica (slave) database to one of the nodes in the group. This is the same method you would use if you were simply replicating off a single server. However, if the Group Replication server you are using for the source (master) goes down, then you will need to point the replica (slave) database to another member of the group. Since Group Replication uses the same UUID in the GTIDs, you can simply point the replica (slave) to any of the other servers in the group and replication will continue without any problems.

    Configuring the replica (slave) server

    You will need to add the following to your MySQL configuration file (my.cnf or my.ini) under the [mysqld] section, to enable replication using GTID’s:


    gtid-mode = on
    enforce-gtid-consistency = 1
    master-info-repository=TABLE
    relay-log-info-repository=TABLE

    binlog_format=row
    server-id = 3456 # this must be a unique number for each server
    log-slave-updates
    relay-log-recovery

    For this example, I will be using a newly-created (without any data) Group Replication group, and I will be adding a new replica (slave) server to it.

    NOTE: If you have a Group Replication group with data on it, then you will need to import a copy of your current data over to the new replica (slave) server before starting replication. For more information – see “What if my Group Replication (GR) group already has data?”

    After you have added the variables above to the MySQL server and rebooted the instance, you are now ready to make the server a replica (slave) of one of the servers in the Group Replication group. But first, we need to add a replication user to the Group Replication group.

    Configuring the source (master) server

    The new replica (slave) server will be replicating off a new three-node Group Replication (GR) group. I have already started Group Replication, and the GR is in single-primary mode.

    Before I turn on replication to my new replica (slave) server, I can use the SHOW MASTER STATUS command to look at the primary server to see if any transactions have been executed yet (under the Executed_Gtid_Set heading):


    mysql> SHOW MASTER STATUS\G
    *************************** 1. row ***************************
    File: mysql-bin.000001
    Position: 1164
    Binlog_Do_DB:
    Binlog_Ignore_DB:
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
    1 row in set (0.00 sec)

    The Executed_Gtid_Set shows three transactions. When I look at these transactions using mysqlbinlog, I can see all three transactions are a SET TIMESTAMP command:

    # mysqlbinlog mysql-bin.000001
    ...
    SET TIMESTAMP=1553124003/*!*/;
    COMMIT
    ...
    

    When you start Group Replication and a single member to the group, a view of the group is created, and this SET TIMESTAMP is executed. Since I have three members in this GR group, I will have three SET TIMESTAMP transactions. These transactions will be sent over to the new slave, but the transactions are harmless. I could tell the replica (slave) to ignore the transactions, but for this example, I will use them to see if replication is working.

    When I created the Group Replication group, I already created a replication user named rpl_user on all three servers in the group. I can use this same user for my new single replica (slave). You don’t need to create this user on the replica (slave) server. However, I created the user with a domain wildcard (%) – so you might want to restrict user access via a domain or IP address.

    Starting the replica (slave) server

    To start replication, you need to tell the replica (slave) server which server you will use to retrieve data. You do this with the CHANGE MASTER command:

    mysql> CHANGE MASTER TO 
        -> MASTER_HOST = '192.168.1.152',
        -> MASTER_PORT = 3306,
        -> MASTER_USER = 'rpl_user',
        -> MASTER_PASSWORD = 'R3plic4tion!',
        -> MASTER_AUTO_POSITION = 1;
    Query OK, 0 rows affected, 2 warnings (0.04 sec)
    

    You are now ready to start the replica (slave) server with the START SLAVE command:

    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    

    Next, we need to see if replication has started. You can check this with the SHOW SLAVE STATUS command:

    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.152
                      Master_User: rpl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 1164
                   Relay_Log_File: Rep01-relay-bin.000002
                    Relay_Log_Pos: 1370
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 1164
                  Relay_Log_Space: 1570
                  Until_Condition: 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: 152
                      Master_UUID: 247898e0-4cb7-11e9-97a9-12f28adcadd1
                 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 more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)
    

    As you can see above under the Retrieved_Gtid_Set – the replica (slave) has already retrieved and processed the three transactions which were on the Group Replication group.

               Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
    

    You can also see that the I/O and SQL threads are running:

                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    

    Let’s test replication by adding a database on one of the Group Replication nodes, and then checking to see if it replicates to the replica (slave).

    I am going to create the new database on the primary write server – which has an IP address of 192.168.1.151. We pointed the replica (slave) to 192.168.1.152, so the new database will get replicated to 192.168.1.152, and then over to our replica (slave) server (which has an IP address of 192.168.1.220).

    On 192.168.1.151:

    mysql> show master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 1164
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
    1 row in set (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    4 rows in set (0.00 sec)
    
    mysql> create database rep_test;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | rep_test           |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    

    And we can see the new database has been replicated to the MySQL server on 192.168.1.152:

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | rep_test           |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    

    And it is now also on the new replica (slave) server:

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | rep_test           |
    | sys                |
    +--------------------+
    5 rows in set (0.02 sec)
    

    That’s all you need to do. Replication is now up and running!

     

    What if my Group Replication (GR) group already has data?

    If you have an existing GR group with data, you need the new replica (slave) to have a copy of the data that is already in the group – to give the new replica (slave) a starting point for replication. The binary logs will probably not have all of the transactions that have been applied to the database – and even if it did, it would take an extremely long time to replicate those events. It is much easier to restore a backup to the new replica (slave) server.

    Once you load the new replica (slave) with the data from a backup, you can then start replication from the last GTID that was executed on the source (master) before you performed the backup. To get a copy of the data to move over to the new replica (slave), you can use the mysqldump utility – or if you are an Enterprise Edition subscriber, you can use the mysqlbackup utility (which is much faster than mysqldump). Using mysqldump will take a considerable amount of time if you have a lot of data (Example: mysqldump will take about an hour to dump 15-20 gigabytes and 4-5 hours to restore 15-20 gigabytes – while mysqlbackup would take less than 10 minutes for both processes. Your actual results may vary.)

    After you restore the backup to the replica (slave) server, you can then start replication. Prior to the backup, I executed a few transactions on the Group Replication servers to increase the GTID numbers. I used this mysqlbackup command to backup one of the read-only servers in the Group Replication group:

    /usr/local/meb/bin/mysqlbackup --user=mysqlbackup --password --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log --with-timestamp --encrypt-password
    

    Also look at the screen output from the backup, and you will see that it contains the GTID information of the source (master) database:

    ...
    190322 21:07:48 MAIN    INFO: GTID_EXECUTED is 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45695
    ...
    

    This number also matches the SHOW MASTER STATUS\G output from one of the Group Replication servers:

    mysql> show master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 13044
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45695
    1 row in set (0.00 sec)
    

    I can now restore this backup to the new replica (slave) server using the mysqlbackup utility. First, I will shut down the MySQL instance, copy the backup to this server, and then I can run the copy-back command:

    # /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/Users/tonydarnell/Desktop/2019-03-22_21-07-46 copy-back
    MySQL Enterprise Backup  Ver 8.0.15-commercial for macos10.14 on x86_64 (MySQL Enterprise - Commercial)
    Copyright (c) 2003, 2019, Oracle and/or its affiliates. All rights reserved.
    ...
    mysqlbackup completed OK! with 7 warnings
    

    Here are a few things to consider when restoring data:

    • If you don’t delete the old data directory, you will need to use the force variable at the end of the command to overwrite the existing MySQL data directory.
    • The MySQL config files (my.cnf or my.ini) are not restored. If you don’t have the same settings for variables like innodb_data_file_path, you will get an error when you try and restart MySQL after the restore.
    • Be sure to change the owner of your MySQL directories. If you run mysqlbackup as root, the directories will be owned by root and you won’t be able to start MySQL.
      # cd /usr/local/mysql
      # ls -l
      total 1224
      ...
      drwxr-x---  26 root    wheel      884 Mar 22 21:30 data
      ...
      # chown -R mysql data
      

    The backup from the source (master) server has been restored to the replica (slave), and I have restarted the MySQL database instance. Now I can reset the slave, use the CHANGE MASTER TO command, start the slave, and I will have the same starting point as the source (master) database.

    mysql> reset slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> CHANGE MASTER TO 
        -> MASTER_HOST = '192.168.1.152',
        -> MASTER_PORT = 3306,
        -> MASTER_USER = 'rpl_user',
        -> MASTER_PASSWORD = 'R3plic4tion!',
        -> MASTER_AUTO_POSITION = 1;
    Query OK, 0 rows affected, 2 warnings (0.00 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW SLAVE STATUS\G
    ... 
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
    ...
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    ... 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45695
    ...
    mysql> 
    

    If you look at the Executed_Gtid_Set, you can see the replica (slave) database has 45695 transactions from the source (master) database. But, since we haven’t started replication yet, and the replica (slave) database has not connected to the source (master) database, the Retrieved_Gtid_Set is blank. Since we restored the first 45695 transactions, those will already be included in the Executed_Gtid_Set.

    After more transactions are executed on the source (master) database, when you execute the SHOW SLAVE STATUS command later, you will see something like the following – where the Retrieved_Gtid_Set doesn’t begin with the number one, but instead it begins with the next number (45696) after the original set of GTID’s (45695) that were restored from the backup. Since the replica (slave) didn’t actually retrieve the first 45695 transactions, those won’t appear in the Retrieved_Gtid_Set, but you can see all of the GTID’s are in the Executed_Gtid_Set.

     
    mysql> SHOW SLAVE STATUS\G
    ...
               Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:45696-45753
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45753
    ...
    

    And we can compare this list to the source (master) database:

    mysql> show master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 13044
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45753
    1 row in set (0.00 sec)
    

    Now we know the replica (slave) database is caught up to the source (master) database.

    Restoring a slave using mysqldump

    Using mysqldump to backup your source (master) database and restore it to a replica (slave) database isn’t as easy as using mysqlbackup. With mysqldump, you can’t backup and restore the mysql.user table, which contains all of your user information. So, you can’t backup the mysql database and restore it. Since I only have one database (rep_test) on my Group Replication source (master) database, I can use the following command to create a mysqldump backup file.

    /usr/local/mysql/bin/mysqldump --databases rep_test --set-gtid-purged=ON --master-data --add-drop-database --add-drop-table --triggers --user=root -p > /users/tonydarnell/Desktop/2019_03_22_2300_dbdump.txt
    

    This creates a backup file which also includes the GTID information from the source (master) server. If you had more databases, you would need to list them after the databases variable. I have a new install of MySQL version 8.0.15 that I will be using to restore this data, and after copying the backup file to the new replica (slave) server, the restore command is very simple:

    # mysql -uroot -p < 2019_03_22_2300_dbdump.txt
    Enter password: 
    

    I can now take a look at the transactions which have been applied to the server by using the SHOW MASTER STATUS\G command:

    mysql> show master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 155
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-26
    1 row in set (0.00 sec)
    

    And – this Executed_Gtid_Set matches the source (master) database (this was before I added thousands of records as shown in the mysqlbackup example). But – I don’t have any of the users that were created on the master, because I can’t export and import the mysql database. It is easier and probably more secure if you just add whatever users you need on this new replica (slave) database.

    If you don’t have access to the users you need, there is a way to get the information from the mysql.user table.

    mysql> select user, host from mysql.user;
    +------------------+----------------+
    | user             | host           |
    +------------------+----------------+
    | mysqlbackup      | %              |
    | root             | %              |
    | rpl_user         | %              |
    | mysql.infoschema | localhost      |
    | mysql.session    | localhost      |
    | mysql.sys        | localhost      |
    | root             | localhost      |
    | root             | macvm151.local |
    +------------------+----------------+
    8 rows in set (0.00 sec)
    

    In this example, I am only going to re-create the user named mysqlbackup. I could use the SHOW CREATE USER command, but it doesn’t give me the exact format I need to re-create the user:

    mysql> show create user mysqlbackup\G
    *************************** 1. row ***************************
    CREATE USER for mysqlbackup@%: CREATE USER 'mysqlbackup'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$:\n*~t^;+s/,+g1L6EDOPfmWHQxa/z7C.mHStg15xDyMq7UzHsi2hTKM10' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
    1 row in set (0.00 sec)
    

    Instead of trying to extract what I need from the mysql table, I have found it is easier to just re-create the user, and then re-apply the grants. BUT – I don’t really want this to be stored in the binary log, so I can prevent it from writing to the binary log with the SET SQL_LOG_BIN=0 command:

    SET SQL_LOG_BIN=0;
    CREATE USER 'mysqlbackup'@'%' IDENTIFIED WITH sha256_password BY 'new-password';
    FLUSH PRIVILEGES;
    SET SQL_LOG_BIN=1;
    

    (I turned on writing to the binary log with the SET SQL_LOG_BIN=1 command.

    As for the grants, I have found it is easier to just grab the grants from the mysql.tables_priv table:

    mysql> show grants for mysqlbackup;
    +----------------------------------------------------------------------------------------------------+
    | Grants for mysqlbackup@%                                                                           |
    +----------------------------------------------------------------------------------------------------+
    | GRANT RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO `mysqlbackup`@`%`                       |
    | GRANT INSERT, CREATE, DROP, ALTER ON `mysql`.`backup_history_new` TO `mysqlbackup`@`%`             |
    | GRANT INSERT, CREATE, DROP ON `mysql`.`backup_history_old` TO `mysqlbackup`@`%`                    |
    | GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `mysql`.`backup_history` TO `mysqlbackup`@`%` |
    | GRANT INSERT, UPDATE, CREATE, DROP ON `mysql`.`backup_progress` TO `mysqlbackup`@`%`               |
    | GRANT SELECT ON `performance_schema`.`replication_group_members` TO `mysqlbackup`@`%`              |
    +----------------------------------------------------------------------------------------------------+
    6 rows in set (0.00 sec)
    

    And, with a little editing, I can extract the GRANT commands and run these as well – and again, I am going to suppress these from writing to the binary logs.

    SET SQL_LOG_BIN=0;
    GRANT RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO `mysqlbackup`@`%`;
    GRANT INSERT, CREATE, DROP, ALTER ON `mysql`.`backup_history_new` TO `mysqlbackup`@`%`;
    GRANT INSERT, CREATE, DROP ON `mysql`.`backup_history_old` TO `mysqlbackup`@`%`;
    GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `mysql`.`backup_history` TO `mysqlbackup`@`%`;
    GRANT INSERT, UPDATE, CREATE, DROP ON `mysql`.`backup_progress` TO `mysqlbackup`@`%`;
    GRANT SELECT ON `performance_schema`.`replication_group_members` TO `mysqlbackup`@`%`;
    FLUSH PRIVILEGES;
    SET SQL_LOG_BIN=1;
    

    I now have all the users I need, and I can start replication:

    mysql> select user, host from mysql.user;
    +------------------+-----------+
    | user             | host      |
    +------------------+-----------+
    | mysqlbackup      | %         |
    | mysql.infoschema | localhost |
    | mysql.session    | localhost |
    | mysql.sys        | localhost |
    | root             | localhost |
    +------------------+-----------+
    5 rows in set (0.00 sec)
    
    mysql> CHANGE MASTER TO 
        -> MASTER_HOST = '192.168.1.152',
        -> MASTER_PORT = 3306,
        -> MASTER_USER = 'rpl_user',
        -> MASTER_PASSWORD = 'R3plic4tion!',
        -> MASTER_AUTO_POSITION = 1;
    Query OK, 0 rows affected, 2 warnings (0.00 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.152
                      Master_User: rpl_user
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000002
              Read_Master_Log_Pos: 9463
                   Relay_Log_File: Rep01-relay-bin.000002
                    Relay_Log_Pos: 408
            Relay_Master_Log_File: mysql-bin.000002
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 9463
                  Relay_Log_Space: 616
                  Until_Condition: 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: 152
                      Master_UUID: 247898e0-4cb7-11e9-97a9-12f28adcadd1
                 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 more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-26
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
           Master_public_key_path: 
            Get_master_public_key: 0
    1 row in set (0.00 sec)
    

    I am going to test replication by adding another database on the source (master):

    mysql> create database rpl_test2;
    Query OK, 1 row affected (0.01 sec)
    

    I can now see the new database on the replica (slave) server:

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | rep_test           |
    | rpl_test2          |
    | sys                |
    +--------------------+
    6 rows in set (0.00 sec)
    

    And when I check the GTID’s, I can see that GTID 27 (the transaction which created the database rpl_test2) has been retrieved and applied:

    mysql> show slave status\G
    ...
               Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:27
                Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-27
    ...
    

    Since I imported the first 26 GTID’s, those will not appear in the Retrieved_Gtid_Set. But, the Executed_Gtid_Set shows that the new replica (slave) database has all of the same transactions as the source (master) database.


    Note: For more information on mysqldump replication options, see: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-replication-options


    Other posts on restoring backups to servers

    I have two older posts which explain how to do this. Some of the MySQL variables might have changed, but these posts will explain the process behind using both utilities.

    Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 1 of 2

    Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2

     


    Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
    Tony is the author of Twenty Forty-Four: The League of Patriots 
    Visit http://2044thebook.com for more information.
    Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition 
    Visit https://amzn.to/2oPFLI0 for more information.

Using a trigger to parse non-conforming data in MySQL

In another post, I explained how to use the MySQL Query Re-write Plugin to manipulate data that didn’t exactly match SQL standards of MySQL. In this post, I am going to give you another example on how to use a trigger to parse non-conforming data so the data can be written to a MySQL database.

A customer came to me with a problem. They were using third-party software which produced multiple rows of comma-separated data (like a .csv file), but all of the data wasn’t on a single row. Instead, the data consisted of multiple rows of data, separated by commas and with line feeds after each row of data. Here is a shortened example of the data (the original data was 32-lines long):

Header information which may, or may not, contain one or more commas
Item Name,Type,Description,Value
Potato Chips - Sealed Bag,,,
Manifest Number,Number,MANIFEST_NUMBER,8480014
Manufacturer Code,Number,BRAND_CODE,80375993
Information 1,Number,INFO_ONE,15869563
Information 2,Number,INFO_TWO,6569569665
Information 3,Number,INFO_THREE,562
Planned Start Date,String,PROD_START_DATE,9/13/2018
Planned Start Time,String,PROD_START_TIME,081234

For some reason, the output couldn’t be separated, but the software could use an ODBC connection. They needed to be able to insert this data into a MySQL database, but they only needed certain values – they didn’t need a lot of the descriptive information. Of the example above, they only wanted to insert the information in yellow:

“Header information which may, or may not, contain one or more commas.”
Item Name Type Description Value
Potato Chips – Sealed Bag
Manifest Number Number MANIFEST_NUMBER 8480014
Manufacturer Code Number BRAND_CODE 80375993
Information 1 Number INFO_ONE 15869563
Information 2 Number INFO_TWO 6569569665
Information 3 Number INFO_THREE 562
Planned Start Date String PROD_START_DATE 9/13/2018
Planned Start Time String PROD_START_TIME 081234

At first, I suggested using a script (Python, Perl, PHP, etc.) to parse the data before inserting into the database. But, the software which produced the data had the ability to insert the data directly into a MySQL database – but all of the data could only be inserted into one field. They didn’t have an option to add an extra step outside of the database connection – and they were new to MySQL, so they needed some help.

I decided to write a trigger for the table, in order to parse the data as it came into the database, but before it was inserted into a row. I also wanted to keep the original data in a column as well.

The first step was to create a database and a table. I mapped the column names to the first value of each row, skipping the header information which wasn’t needed. The column names are in blue, and the data is in yellow:

“Header information which may, or may not, contain one or more commas.”
Item Name Type Description Value
Potato Chips – Sealed Bag
Manifest Number Number MANIFEST_NUMBER 8480014
Manufacturer Code Number BRAND_CODE 80375993
Information 1 Number INFO_ONE 15869563
Information 2 Number INFO_TWO 6569569665
Information 3 Number INFO_THREE 562
Planned Start Date String PROD_START_DATE 9/13/2018
Planned Start Time String PROD_START_TIME 081234

I can then match the columns I want to the data values:

“Header information which may, or may not, contain one or more commas.”
Item Name Potato Chips – Sealed Bag
Manifest Number 8480014
Manufacturer Code 80375993
Information 1 15869563
Information 2 6569569665
Information 3 562
Planned Start Date 9/13/2018
Planned Start Time 081234

Now, I can create the database and the table. I substituted the spaces in the field names with underscores (_). I also added a primary key column (id_MANIFEST_DATA) and a proper DATETIME column – as I want to combine the two columns PROD_START_DATE and PROD_START_TIME into one column to match MySQL’s DATETIME format. This way, they can perform a search on this column later (if necessary).

mysql> create database MANIFEST_DATA_DB;
Query OK, 1 row affected (0.00 sec)

mysql> use MANIFEST_DATA_DB;
Database changed

mysql> CREATE TABLE `MANIFEST_DATA_DB`.`MANIFEST_DATA_TABLE` (
    ->   `id_MANIFEST_DATA` INT NOT NULL AUTO_INCREMENT,
    ->   `MANIFEST_DATA` VARCHAR(4096) NULL,
    ->   `ITEM_NAME` VARCHAR(1024) NULL,
    ->   `MANIFEST_NUMBER` INT NULL,
    ->   `MANUFACTURER_CODE` VARCHAR(1024) NULL,
    ->   `INFO_ONE` CHAR(32) NULL,
    ->   `INFO_TWO` CHAR(32) NULL,
    ->   `INFO_THREE` CHAR(32) NULL,
    ->   `PROD_START_DATE` CHAR(10) NULL,
    ->   `PROD_START_TIME` CHAR(6) NULL,
    ->   `PROD_TIMESTAMP` DATETIME NULL,
    ->   PRIMARY KEY (`id_MANIFEST_DATA`))
    -> AUTO_INCREMENT = 1000000;
Query OK, 0 rows affected (0.00 sec)

The initial “non-conforming data” will be inserted into the MANIFEST_DATA field, so all I have to do is to create a trigger to parse this field before the data is inserted.

Even though the data is on separate lines, parsing this data will be relatively easy, since the data is comma-delimited. I can use the SUBSTRING_INDEX function to create an array to store all of the data. But, since the first line of the data may or may not contain a comma, instead of counting commas from the beginning of the data, I will start at the end. Also, in this example, they don’t need the first line of data, as it is header information.

Let’s take a look at why I want to count backwards. Here are three rows of data – where the first column may or may not contain a comma – or it might contain two or more commas. I really only want to capture the last two columns of data.

"Hello, it's me",12345,ABCDE
"Hello it's me",67890,FGHIJ
"Hello, yes, it's me",112233,A1B2C3

If I parse the data based upon commas and start at the beginning, I will get different results when counting commas from the beginning of the data when the first line of data contains a comma. And I only want the data in green:

1 2 3 4 5
“Hello it’s me” 67890 FGHIJ
“Hello it’s me” 12345 ABCDE
“Hello yes it’s me” 112233 A1B2C3

But if I count backwards, I will get the same result set regardless of how many commas are in the first line of data:

-5 -4 -3 -2 -1
“Hello it’s me” 67890 FGHIJ
“Hello it’s me” 12345 ABCDE
“Hello yes it’s me” 112233 A1B2C3

In the actual data I want to sue, I don’t want to store the first row of data anyway – so it can be ignored. If I did need the first line, I would have to search for the quotes and parse that column separately.

Since the initial data will contain a line feed, I will want to replace the line feeds with a comma, so I can have all of the data on a single line and be able to use the SUBSTRING_INDEX function. Here is the original data again, but this time, I have added a column for the line feeds. Also, I am going to count backwards from the last data value. The numbers are in (bold):

/tr>

(-37)“Header information which may, or may not, contain one or more commas.”
(-36) Item Name (-35) Type (-34) Description (-33) Value -line feed-
(-32) Potato Chips – Sealed Bag (-31) (-30) (-29) -line feed-
(-28) Manifest Number (-27) Number (-26) MANIFEST_NUMBER (-25) 8480014 -line feed-
(-24) Manufacturer Code (-23) Number (-22) BRAND_CODE (-21) 80375993 -line feed-
(-20) Information 1 (-19) Number (-18) INFO_ONE (-17) 15869563 -line feed-
(-16) Information 2 (-15) Number (-14) INFO_TWO (-13) 6569569665 -line feed-
(-12) Information 3 (-11) Number (-10) INFO_THREE (-9) 562 -line feed-
(-8) Planned Start Date (-7) String (-6) PROD_START_DATE (-5) 9/13/2018 -line feed-
(-4) Planned Start Time (-3) String (-2) PROD_START_TIME (-1) 081234

Of course, you don’t have to map out the fields like I did – you can just count backwards.

The SUBSTRING_INDEX function works similar to grabbing a value out of an array – except the count value is where you want to stop grabbing data. You specify the string you want to parse, the delimiter you want to use, and the count of the value from the string where you want to stop grabbing data.

The format is:

SUBSTRING_INDEX(string,delimiter,count) 

Note: When the count value is a positive number, the count value is the ending position within the string. It doesn’t select the element itself.

Here is an example using the phrase “Hello, I must be going, see you later, bye.” which contains four values separated by commas. I want to capture the third value of “see you later”.

1 2 3 4
Hello I must be going See you later bye

To select “see you later”, I choose the third column as the place where I will stop grabbing data – but, since this is the stopping point, I will get the rest of the phrase up until the third column.

mysql> select SUBSTRING_INDEX("Hello, I must be going, see you later, bye.",",",3) as PARSE;
+---------------------------------------+
| PARSE                                 |
+---------------------------------------+
| Hello, I must be going, see you later |
+---------------------------------------+
1 row in set (0.00 sec)

I don’t want all of the phrase, I only want “see you later”. With a positive count value of three, I am getting the ending point – it stops at the third delimited value. If I use a negative count value, then I am getting the starting point of the string. If I count backwards, which would give me a negative count value (and I want to do this anyway because the first line of data in the main example may have multiple commas), I can use the value of negative two (-2) and get this:

mysql> select SUBSTRING_INDEX("Hello, I must be going, see you later, bye.",",",-2) as PARSE;
+----------------------+
| PARSE                |
+----------------------+
|  see you later, bye. |
+----------------------+
1 row in set (0.00 sec)

That gives me a starting point for grabbing “see you later”, but I don’t want any data (the single word “bye”) after this. So, I can wrap an additional SUBSTRING_INDEX around the first one, and then only choose the first delimited set of data from the inner SUBSTRING_INDEX.

It looks like this:

The statement select SUBSTRING_INDEX(“Hello, I must be going, see you later, bye.”,”,”,-2) grabs both of these columns in blue:

1 2 3 4
Hello I must be going See you later bye

But then I can use the second SUBSTRING_INDEX command to only select the first column of the remaining two columns:

SUBSTRING_INDEX(SUBSTRING_INDEX(“Hello, I must be going, see you later, bye.”,”,”,-2), “,”, 1)

1 2
See you later bye

I can test it:

mysql> select SUBSTRING_INDEX(SUBSTRING_INDEX("Hello, I must be going, see you later, bye.",",",-2), ",", 1) as PARSE;
+----------------+
| PARSE          |
+----------------+
|  see you later |
+----------------+
1 row in set (0.01 sec)

This is essentially how I am going to parse the long line of data (after I convert the line feeds to commas). I will go backwards to the starting point, and then only select the first column beginning at that starting point.

I can then use the table I created to map put the data values I want to extract.

Column Name Backwards Value
ITEM_NAME -32
MANIFEST_NUMBER -25
MANUFACTURER_CODE -21
INFO_ONE -17
INFO_TWO -13
INFO_THREE -9
PROD_START_DATE -5
PROD_START_TIME -1

Example: To extract the PROD_START_TIME, which is at position -1, I will use this in the trigger:

SET NEW.PROD_START_TIME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -1), ",", 1);

I can now create my trigger to parse all of the data being inserted into the MANIFEST_DATA column. In the trigger, I will use some temporary variables to parse the MANIFEST_DATA, and I will also use the SUBSTRING function to parse the PROD_START_DATE and PROD_START_TIME fields to rearrange them into the correct MySQL DATETIME format. I will use the CONCAT function to combine them into a new DATETIME field. I have some notes in the stored procedure to help you figure out what I am doing.

DELIMITER $$
  
CREATE TRIGGER _convert_MANIFEST_DATA
BEFORE INSERT ON MANIFEST_DATA_DB.MANIFEST_DATA_TABLE
FOR EACH ROW
BEGIN

# Declare temporary variables

# This is the temporary field which contains all of the data
DECLARE _parse_MANIFEST_DATA varchar(4096);

# The temporary date and time fields needed to create the DATETIME field named PROD_TIMESTAMP
DECLARE _parse_TIME_DATE_DATE char(10);
DECLARE _parse_TIME_DATE_TIME char(10);

DECLARE _parse_TIME_DATE_date_final char(10);

DECLARE _parse_TIME_DATE_hour char(2);
DECLARE _parse_TIME_DATE_minutes char(2);
DECLARE _parse_TIME_DATE_sec char(2);

# Take the incoming large dataset which has line feeds and
# Replace the line feeds with a comma
set _parse_MANIFEST_DATA = replace(NEW.MANIFEST_DATA, "\n", ",");

# Parse each new column from the temporary field
SET NEW.ITEM_NAME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -35), ",", 1);
SET NEW.MANIFEST_NUMBER = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -25), ",", 1);
SET NEW.MANUFACTURER_CODE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -21), ",", 1);
SET NEW.INFO_ONE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -17), ",", 1);
SET NEW.INFO_TWO = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -13), ",", 1);
SET NEW.INFO_THREE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -9), ",", 1);
SET NEW.PROD_START_DATE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -5), ",", 1);
SET NEW.PROD_START_TIME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -1), ",", 1);

# Get the values from these two fields in order to combine them into a DATETIME field
SET _parse_TIME_DATE_DATE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -5), ",", 1);
SET _parse_TIME_DATE_TIME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -1), ",", 1);

# Convert the date from MM/DD/YYYY to YYYY-MM-DD
SET _parse_TIME_DATE_date_final = STR_TO_DATE(_parse_TIME_DATE_DATE, '%c/%d/%Y');

# Parse the time so we can add colons between the hour, minutes and seconds
SET _parse_TIME_DATE_hour = SUBSTRING(_parse_TIME_DATE_TIME, 1, 2);
SET _parse_TIME_DATE_minutes = SUBSTRING(_parse_TIME_DATE_TIME, 3, 2);
SET _parse_TIME_DATE_sec = SUBSTRING(_parse_TIME_DATE_TIME, 5, 2);

# Combine the newly-formatted date with the newly-formatted time so it conforms to the DATETIME format
SET NEW.PROD_TIMESTAMP = CONCAT(_parse_TIME_DATE_date_final, " ", _parse_TIME_DATE_hour, ":", _parse_TIME_DATE_minutes, ":", _parse_TIME_DATE_sec) ;

END$$

DELIMITER ;

Now I can insert the sample data into the database – where all of the data will go into the MANIFEST_DATA column, and the trigger will populate the rest of the columns by parsing the data.

mysql> INSERT INTO MANIFEST_DATA_TABLE (MANIFEST_DATA) VALUES ('Header information which may, or may not, contain one or more commas
    '> Item Name,Type,Description,Value
    '> Potato Chips - Sealed Bag,,,
    '> Manifest Number,Number,MANIFEST_NUMBER,8480014
    '> Manufacturer Code,Number,BRAND_CODE,80375993
    '> Information 1,Number,INFO_ONE,15869563
    '> Information 2,Number,INFO_TWO,6569569665
    '> Information 3,Number,INFO_THREE,562
    '> Planned Start Date,String,PROD_START_DATE,9/13/2018
    '> Planned Start Time,String,PROD_START_TIME,081234');
Query OK, 1 row affected (0.07 sec)

When I look at the contents of the table, I can see the trigger executed successfully. All of the “non-conforming” data is stored in the MANIFEST_DATA field, but the other fields were populated:

mysql> select * from MANIFEST_DATA_TABLE\G
*************************** 1. row ***************************
 id_MANIFEST_DATA: 1000000
    MANIFEST_DATA: Header information which may, or may not, contain one or more commas
Item Name,Type,Description,Value
Potato Chips - Sealed Bag,,,
Manifest Number,Number,MANIFEST_NUMBER,8480014
Manufacturer Code,Number,BRAND_CODE,80375993
Information 1,Number,INFO_ONE,15869563
Information 2,Number,INFO_TWO,6569569665
Information 3,Number,INFO_THREE,562
Planned Start Date,String,PROD_START_DATE,9/13/2018
Planned Start Time,String,PROD_START_TIME,081234
        ITEM_NAME: Type
  MANIFEST_NUMBER: 8480014
MANUFACTURER_CODE: 80375993
         INFO_ONE: 15869563
         INFO_TWO: 6569569665
       INFO_THREE: 562
  PROD_START_DATE: 9/13/2018
  PROD_START_TIME: 081234
   PROD_TIMESTAMP: 2018-09-13 08:12:34
1 row in set (0.00 sec)

Of course, this is only a short example of all of the parsing you can do with triggers in MySQL. And, if you want to look at your trigger, you can query the INFORMATION_SCHEMA.TRIGGERS table:

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='_convert_MANIFEST_DATA'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: MANIFEST_DATA_DB
              TRIGGER_NAME: _convert_MANIFEST_DATA
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: MANIFEST_DATA_DB
        EVENT_OBJECT_TABLE: MANIFEST_DATA_TABLE
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN

# Declare temporary variables

# This is the temporary field which contains all of the data
DECLARE _parse_MANIFEST_DATA varchar(4096);

# The temporary date and time fields needed to create the DATETIME field named PROD_TIMESTAMP
DECLARE _parse_TIME_DATE_DATE char(10);
DECLARE _parse_TIME_DATE_TIME char(10);

DECLARE _parse_TIME_DATE_date_final char(10);

DECLARE _parse_TIME_DATE_hour char(2);
DECLARE _parse_TIME_DATE_minutes char(2);
DECLARE _parse_TIME_DATE_sec char(2);

# Take the incoming large dataset which has line feeds and
# Replace the line feeds with a comma
set _parse_MANIFEST_DATA = replace(NEW.MANIFEST_DATA, "
", ",");

# Parse each new column from the temporary field
SET NEW.ITEM_NAME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -35), ",", 1);
SET NEW.MANIFEST_NUMBER = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -25), ",", 1);
SET NEW.MANUFACTURER_CODE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -21), ",", 1);
SET NEW.INFO_ONE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -17), ",", 1);
SET NEW.INFO_TWO = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -13), ",", 1);
SET NEW.INFO_THREE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -9), ",", 1);
SET NEW.PROD_START_DATE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -5), ",", 1);
SET NEW.PROD_START_TIME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -1), ",", 1);

# Get the values from these two fields in order to combine them into a DATETIME field
SET _parse_TIME_DATE_DATE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -5), ",", 1);
SET _parse_TIME_DATE_TIME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -1), ",", 1);

# Convert the date from MM/DD/YYYY to YYYY-MM-DD
SET _parse_TIME_DATE_date_final = STR_TO_DATE(_parse_TIME_DATE_DATE, '%c/%d/%Y');

# Parse the time so we can add colons between the hour, minutes and seconds
SET _parse_TIME_DATE_hour = SUBSTRING(_parse_TIME_DATE_TIME, 1, 2);
SET _parse_TIME_DATE_minutes = SUBSTRING(_parse_TIME_DATE_TIME, 3, 2);
SET _parse_TIME_DATE_sec = SUBSTRING(_parse_TIME_DATE_TIME, 5, 2);

# Combine the newly-formatted date with the newly-formatted time so it conforms to the DATETIME format
SET NEW.PROD_TIMESTAMP = CONCAT(_parse_TIME_DATE_date_final, " ", _parse_TIME_DATE_hour, ":", _parse_TIME_DATE_minutes, ":", _parse_TIME_DATE_sec) ;

END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2018-09-20 22:13:28.54
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_0900_ai_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.02 sec)

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots 
Visit http://2044thebook.com for more information.
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition 
Visit https://amzn.to/2oPFLI0 for more information.

Manipulating queries with non-conforming data via MySQL Query Rewrite Plugin, triggers and stored procedures

The MySQL database is used in thousands of third-party applications, but what can you do when you want to use MySQL with an application, but that application’s queries or data doesn’t match MySQL’s data type or SQL format?

This post will show you three ways to alter a query or mismatched data when you don’t have control of the application’s source code. Of course, there are hundreds of different ways to do what I am about to show you. In this example, I will show you how to use the MySQL Query Rewrite Plugin along with a trigger to alter the non-conforming data. I will also show you an example of manipulating data with a stored procedure.

A customer emailed me with a problem. They wanted to use MySQL for a third-party application, but they didn’t have access to the source code. Their main problem was the application’s TIMESTAMP format didn’t conform to MySQL’s TIMESTAMP format. To be specific, this application produced a TIMESTAMP value that included a trailing time zone, such as “2018-09-05 17:00:00 EDT”. MySQL has two column data types where you can store both the date and time in one column: TIMESTAMP and DATETIME – but MySQL cannot handle TIMESTAMP or DATETIME data with a trailing time zone.

When a TIMESTAMP value is being inserted into a row, MySQL converts the TIMESTAMP value from the current time zone set by the MySQL server (see Time Zone Support) to UTC (Coordinated Universal Time) for storage, and converts the data back from UTC to the current time zone (of the server) when retrieved. (This conversion does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server’s local time. The time zone can be set on a per-connection basis, and as long as the time zone setting remains constant, you will get back the same value you stored. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 5.1.12, “MySQL Server Time Zone Support”.

(From: https://dev.mysql.com/doc/refman/8.0/en/datetime.html)

The customer told me that this application would only be sending data with two different trailing time zones – Central and Eastern. With daylight-savings in use in both of these time zones, this would give us four possible trailing time zone values – CDT, CST, EDT and EST. What we want to do is to intercept the query, and write this TIMESTAMP data to a different column, and then convert the value to UTC time to be stored in the correct column in the database. Because we don’t have access to the source code, I am assuming we have full access to the MySQL database.


NOTE: Since we are using time zone information, if you want to duplicate this post, be sure to load the MySQL time zone information. See: https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

 

The MySQL Rewrite Plugin

In MySQL version 5.7, a plugin named the “Query Rewrite Plugin” was introduced. This plugin can examine SQL statements received by the server and modify those statements before the server executes them. In other words, this gives you the ability to intercept “bad” queries and re-format them to be “good” queries for use with MySQL – or to rewrite the queries to do whatever you need. Think of it as a way to change the source code without actually having the source code.

Installing the plugin is fairly easy. In MySQL version 8.0, you install (or uninstall) the plugin via an SQL script provided with your MySQL installation. The script is named install_rewriter.sql and is located in the “share” directory under your MySQL home directory.

# cd /usr/local/mysql/share  (your directory may be different)
# mysql -u root -p < install_rewriter.sql
Enter password: (enter root password here)

The script only takes a few seconds to load (The uninstall script is named uninstall_rewriter.sql). To check and make sure the plugin was installed, run this command from within MySQL:

mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
+------------------+-------+
1 row in set (0.00 sec)

The plugin was installed correctly if the column named “Value” is set to “ON“.

For this example, I am going to create a small table with three columns, and assume that this is an table from a third-party application. The date_time_value column is where the application would normally store the timestamp information.

mysql> create database test;
 Query OK, 1 row affected (0.01 sec)
mysql> use test;
 Database changed
mysql> CREATE TABLE `time_example` (
  `idtime` int(11) NOT NULL AUTO_INCREMENT,
  `action_record` varchar(30) NOT NULL,
  `date_time_value` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`idtime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8MB4;
Query OK, 0 rows affected (0.03 sec)

The date_time_value column will obviously not be able to store timestamp data with a trailing time zone, but let’s see what happens when we try and insert a row of data – and let’s pretend that this is the query the application uses.

mysql> insert into test.time_example (action_record, date_time_value) 
 values ('Arrived at work', '2018-09-05 17:00:00 EDT');
Error Code: 1292. Incorrect datetime value: '2018-09-05 17:00:00 EDT' 
 for column 'date_time_value' at row 1

Of course, we get an error because the format for the timestamp is incorrect.

What we want to do is to alter the table and add a column to store this improperly-formatted timestamp data.

mysql> ALTER TABLE `test`.`time_example` 
    -> ADD COLUMN `date_time_storage` VARCHAR(23) NULL AFTER `date_time_value`;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now that we have a column (date_time_storage) to store the “bad” timestamp data, we need to modify the incoming query so that it writes the timestamp data into the new column.


Note: In MySQL 8.0+, with the Query Rewrite Plugin, you can modify SELECT, INSERT, REPLACE, UPDATE, and DELETE statements. (Prior to MySQL 8.0.12 you could only modify SELECT statements)

 

This is the query the application is sending to the database:

insert into test.time_example (action_record, date_time_value) values (?, ?);

We want to modify the query to use the new date_time_storage column, instead of the date_time_value column. The new query would look like this:

insert into test.time_example (action_record, date_time_storage) values (?, ?);

Now that we have our old (bad) and new (good) queries, we can insert this into the rewrite_rules table of the query_rewrite database.

INSERT INTO query_rewrite.rewrite_rules
    (pattern, replacement, pattern_database) VALUES(
    'insert into test.time_example (action_record, date_time_value) values (?, ?)',
    'insert into test.time_example (action_record, date_time_storage) values (?, ?)',
    'time_example'
    );
1 row(s) affected, 1 warning(s): 1105 Query 'insert into test.time_example 
 (action_record, date_time_value) values ('Left building', '2018-09-05 17:00:00 EDT')' 
 rewritten to 'insert into test.time_example (action_record, date_time_storage) 
 values ('Left building', '2018-09-05 17:00:00 EDT')' by a query rewrite plugin

(More examples may be found on this page: Query Rewrite Plugin Usage)

We need to execute a stored procedure named flush_rewrite_rules to make this query-rewrite change permanent: (See: https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin-usage.html)

mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.00 sec)

We can confirm the INSERT INTO query_rewrite.rewrite_rules by looking at the rewrite_rules table:

mysql> SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
                id: 1
           pattern: insert into test.time_example (action_record, date_time_value) values (?, ?)
  pattern_database: time_example
       replacement: insert into test.time_example (action_record, date_time_storage) values (?, ?)
           enabled: YES
           message: NULL
    pattern_digest: e823e987338aeae6d47f7a729e78f532d3ff3721237c15981bcd11fc2607efda
normalized_pattern: insert into `test`.`time_example` (`action_record`,`date_time_value`) values (?,?)
1 row in set (0.00 sec)

Next, let’s run the same query as before, and see if it puts the timestamp data that is supposed to go into the date_time_value column into the new date_time_storage column:

mysql> insert into test.time_example (action_record, date_time_value) 
 values ('Arrived at work', '2018-09-05 17:00:00 EDT');
Query OK, 1 row affected, 1 warning (0.01 sec)

And now the table contains this data:

mysql> select * from time_example;
+--------+-----------------+-----------------+-------------------------+
| idtime | action_record   | date_time_value | date_time_storage       |
+--------+-----------------+-----------------+-------------------------+
|      1 | Arrived at work | NULL            | 2018-09-05 17:00:00 EDT |
+--------+-----------------+-----------------+-------------------------+
1 rows in set (0.00 sec)

We now have the timestamp with the time zone data stored in the MySQL database, but we need to convert this to a proper format, and put the result into the date_time_value column.

To do this, we can use a trigger.

Normally, you would want your application to produce data in the correct format, but in this example, we don’t have access to the source code. So, we can create a trigger to convert the “incorrectly-formatted” data in date_time_storage to the correct data and store it in date_time_value.


NOTE: These examples won’t work if your TIMESTAMP uses microseconds (6-digits) precision (example: ‘1970-01-01 00:00:01.000000’) – but you can modify the code to accommodate microseconds.

 

Here is the SQL to create the trigger:

DELIMITER $$
  
CREATE TRIGGER _time_zone_convert_insert2
AFTER INSERT ON time_example
FOR EACH ROW
BEGIN

DECLARE _date_time_no_tz varchar(20);

SET _date_time_no_tz = SUBSTRING(NEW.date_time_storage, 1, 20);

IF NEW.date_time_storage like '%EDT' THEN
    SET NEW.date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT');
END IF;

IF NEW.date_time_storage like '%EST' THEN
    SET NEW.date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT');
END IF;

IF NEW.date_time_storage like '%CDT' THEN
    SET NEW.date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT');
END IF;

IF NEW.date_time_storage like '%CST' THEN
    SET NEW.date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT');
END IF;

END$$

DELIMITER ;

Now that we have a trigger in place, let’s insert another line into the database – BUT, we still want to use the SQL from the application. The query will try and write to the date_time_value column, but the Query Rewrite Plugin will intercept the original query and substitute our new query instead – which will insert the timestamp data into the date_time_storage column, and then the trigger will convert the timestamp and place the correct value into the date_time_value column.

mysql> INSERT INTO time_example (action_record, date_time_value) 
 VALUES ('Lunch Break', '2018-09-05 18:00:00 EDT');
Query OK, 1 row affected (0.00 sec)

The table now contains a true timestamp column with the correct timestamp value in UTC. (The old row didn’t change)

mysql> SELECT * FROM test.time_example;
+--------+------------+---------------------+-------------------------+
| idtime | product_id | date_time_value     | date_time_storage       |
+--------+------------+---------------------+-------------------------+
|      1 | time now1  | NULL                | 2018-09-05 18:00:00 EDT |
|      2 | time now2  | 2018-09-05 22:00:00 | 2018-09-05 18:00:00 EDT |
+--------+------------+---------------------+-------------------------+
2 rows in set (0.00 sec)

But what about stored procedures?

The easiest way to handle the time zone conversion is with a trigger. But, to show you how stored procedures can do the same thing, I have an example of a stored procedure. In this example, I will be passing the values of the idtime and date_time_storage columns.

This example will be similar to the one above – I created a table named time_example, but this time, I am including the extra column:

'CREATE TABLE `time_example` (
  `idtime` int(11) NOT NULL AUTO_INCREMENT,
  `action_record` varchar(30) NOT NULL,
  `date_time_value` timestamp NULL DEFAULT NULL,
  `date_time_storage` varchar(23) DEFAULT NULL,
  PRIMARY KEY (`idtime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8'

I then inserted a row, where I am storing the time stamp with the time zone information:

mysql> insert into test.time_example (action_record, date_time_storage) 
 values ('Left work', '2018-09-05 17:00:00 EDT’);
Query OK, 1 row affected (0.00 sec)

Here is what the row looks like:

mysql> SELECT * FROM test.time_example;
+--------+------------+-----------------+-------------------------+
| idtime | product_id | date_time_value | date_time_storage       |
+--------+------------+-----------------+-------------------------+
|      1 | Left work  | NULL            | 2018-09-05 17:00:00 EDT |
+--------+------------+-----------------+-------------------------+
1 row in set (0.00 sec)

Again, the date_time_storage column is a temporary storage column. I will call the stored procedure, and provide the idtime and date_time_storage values. The stored procedure which will look at the last three characters in the date_time_storage column, and then convert the time to UTC, which is then stored in the date_time_value column.

call _check_time_zone('1','2018-09-05 17:00:00 EDT');

Now the row looks like this, where the date_time_value column is now stored as UTC:

mysql> SELECT * FROM test.time_example;
+--------+------------+---------------------+-------------------------+
| idtime | product_id | date_time_value     | date_time_storage       |
+--------+------------+---------------------+-------------------------+
|      1 | Left work  | 2018-09-05 21:00:00 | 2018-09-05 17:00:00 EDT |
+--------+------------+---------------------+-------------------------+
1 row in set (0.00 sec)

And here is the code to create the stored procedure:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `_check_time_zone`(IN _id_time INT, IN _date_time_storage VARCHAR(23))
BEGIN

DECLARE _date_time_no_tz varchar(20);

SET _date_time_no_tz = SUBSTRING(_date_time_storage, 1, 20);

IF _date_time_storage like '%EDT' THEN 
UPDATE time_example SET date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT')
WHERE idtime = _id_time;
END IF;

IF _date_time_storage like '%EST' THEN 
UPDATE time_example SET date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT')
WHERE idtime = _id_time;
END IF;

IF _date_time_storage like '%CDT' THEN 
UPDATE time_example SET date_time_value = CONVERT_TZ(_date_time_no_tz,'CST5CDT','GMT')
WHERE idtime = _id_time;
END IF;

IF _date_time_storage like '%CST' THEN 
UPDATE time_example SET date_time_value = CONVERT_TZ(_date_time_no_tz,'CST5CDT','GMT')
WHERE idtime = _id_time;
END IF;

IF _date_time_storage like '%UTC' THEN 
UPDATE time_example SET date_time_value = _date_time_no_tz
WHERE idtime = _id_time;
END IF;

END $$
DELIMITER ;

 


Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
Tony is the author of Twenty Forty-Four: The League of Patriots 
Visit http://2044thebook.com for more information.
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition 
Visit https://amzn.to/2oPFLI0 for more information.