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

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.

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

MySQL’s InnoDB Cluster was released on Apr 12, 2017, with version 5.7 and is also included in MySQL version 8.0.

MySQL InnoDB cluster provides a complete high availability solution for MySQL. MySQL Shell includes AdminAPI which enables you to easily configure and administer a group of at least three MySQL server instances to function as an InnoDB cluster. Each MySQL server instance runs MySQL Group Replication, which provides the mechanism to replicate data within InnoDB clusters, with built-in failover. AdminAPI removes the need to work directly with Group Replication in InnoDB clusters, but for more information see Chapter 18, Group Replication which explains the details. MySQL Router can automatically configure itself based on the cluster you deploy, connecting client applications transparently to the server instances. In the event of an unexpected failure of a server instance the cluster reconfigures automatically. In the default single-primary mode, an InnoDB cluster has a single read-write server instance – the primary. Multiple secondary server instances are replicas of the primary. If the primary fails, a secondary is automatically promoted to the role of primary. MySQL Router detects this and forwards client applications to the new primary. Advanced users can also configure a cluster to have multiple-primaries. (source: Introducing InnoDB Cluster )

The following diagram shows an overview of how these technologies work together:

I am not going to go into any more details about how the InnoDB Cluster works, as there are plenty of articles over on the MySQL Server Team and MySQL High Availability blogs.

This post will walk you through setting up MySQL InnoDB Cluster in a sandbox environment. I used a Mac running 10.13, but these commands should also work on Linux. The only differences will be the location of the root directory and where MySQL is installed.

To begin, you will need to install the MySQL server (version 8.0), MySQL Router and MySQL Shell. You may download these from the MySQL downloads page. And, I am not going to walk you through the steps of installing these – as on the Mac, the installation is very easy. For any other OS, you will need to refer to the man pages for each package.

Okay – so I have installed all three components of the MySQL InnoDB Cluster – the MySQL server, MySQL Router and MySQL Shell (all three were versions 8.0.11). For ease of going through this tutorial, I would recommend opening five terminal windows – one for the MySQL Shell, one for regular OS access, and three for each of the sandboxes that I will create. The sandboxes will all run on one server, and each sandbox will have a different port number for the MySQL instance. In this example, I am using ports 3310, 3320 and 3330.

To make this tutorial easy to follow, simply enter/use the commands which appear as bold red. I have included the output from these commands separately.

One note: before you start, make sure that all of the directories in the the mysql home directory – /usr/local – are owned by the mysql user. You don’t have to create the mysql user separately, but you will notice the user name will be _mysql.

cd /usr/local
chown -R mysql mysql*

Let’s begin by logging in as root on my Mac and open the MySQL shell:

mysqlsh

~:root # mysqlsh
MySQL Shell 8.0.11

Copyright (c) 2016, 2018, 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.

I will be creating three sandbox instances to demo how MySQL InnoDB Cluster works. I will deploy the first sandbox instance using port 3310 and will need to enter a root password

dba.deploySandboxInstance(3310);

 MySQL  JS > dba.deploySandboxInstance(3310);
A new MySQL sandbox instance will be created on this host in 
/var/root/mysql-sandboxes/3310

Warning: Sandbox instances are only suitable for deploying and 
running on your local machine for testing purposes and are not 
accessible from external networks.

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3310 successfully deployed and started.
Use shell.connect('root@localhost:3310'); to connect to the instance.

Next, deploy the second sandbox instance using port 3320 – and all three instances will need to have the same root password.

dba.deploySandboxInstance(3320);

 MySQL  JS > dba.deploySandboxInstance(3320);
A new MySQL sandbox instance will be created on this host in 
/var/root/mysql-sandboxes/3320

Warning: Sandbox instances are only suitable for deploying and 
running on your local machine for testing purposes and are not 
accessible from external networks.

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3320 successfully deployed and started.
Use shell.connect('root@localhost:3320'); to connect to the instance.

Finally, deploy the third sandbox instance using port 3330.

dba.deploySandboxInstance(3330);

 MySQL  JS > dba.deploySandboxInstance(3330);
A new MySQL sandbox instance will be created on this host in 
/var/root/mysql-sandboxes/3330

Warning: Sandbox instances are only suitable for deploying and 
running on your local machine for testing purposes and are not 
accessible from external networks.

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3330 successfully deployed and started.
Use shell.connect('root@localhost:3330'); to connect to the instance.

From within the mysql shell, while in Javascript mode, connect to the first host on port 3310.

shell.connect(“root@localhost:3310”);

 MySQL  JS > shell.connect("root@localhost:3310");
Please provide the password for 'root@localhost:3310': 
Creating a session to 'root@localhost:3310'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 20
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use  to set one.

I will create the cluster starting with the 3310 sandbox – and the name of the cluster will be simply ‘mycluster’.

cluster = dba.createCluster(‘mycluster’);

MySQL localhost:3310 ssl JS > cluster = dba.createCluster('mycluster');
A new InnoDB cluster will be created on instance 'root@localhost:3310'.

Validating instance at localhost:3310...
Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as MacVM151.local

Instance configuration is suitable.
Creating InnoDB cluster 'mycluster' on 'root@localhost:3310'...
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.


Next, add the other two sandboxes to the cluster – the ones on ports 3320 and 3330.

Adding 3320…

cluster.addInstance(“root@localhost:3320”);

 MySQL  localhost:3310 ssl  JS > cluster.addInstance("root@localhost:3320");
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.

Please provide the password for 'root@localhost:3320': 
Adding instance to the cluster ...

Validating instance at localhost:3320...
Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as MacVM151.local

Instance configuration is suitable.
The instance 'root@localhost:3320' was successfully added to the cluster.

And 3330…

cluster.addInstance(“root@localhost:3330”);

 MySQL  localhost:3310 ssl  JS > cluster.addInstance("root@localhost:3330")
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.

Please provide the password for 'root@localhost:3330': 
Adding instance to the cluster ...

Validating instance at localhost:3330...
Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as MacVM151.local

Instance configuration is suitable.
The instance 'root@localhost:3330' was successfully added to the cluster.

And now I can check the cluster status – and you can see 3310 is read-write, and the other two are read-only.

cluster.status();

 MySQL  localhost:3310 ssl  JS > cluster.status();
{
    "clusterName": "mycluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@localhost:3310"
}

I have opened another terminal window, where I can also check the processes for mysqld, and you can see all three sandbox instances are running.

ps -ef|grep mysqld

~:root # ps -ef|grep mysqld
  501  2124   853   0  9:25PM ttys000    0:00.00 grep mysqld
  501  2078     1   0  8:55PM ttys002    0:07.28 /usr/local/mysql/bin/mysqld --defaults-file=/Users/tonydarnell/mysql-sandboxes/3310/my.cnf
  501  2098     1   0  9:16PM ttys002    0:03.98 /usr/local/mysql/bin/mysqld --defaults-file=/Users/tonydarnell/mysql-sandboxes/3320/my.cnf
  501  2106     1   0  9:16PM ttys002    0:03.67 /usr/local/mysql/bin/mysqld --defaults-file=/Users/tonydarnell/mysql-sandboxes/3330/my.cnf

From the terminal, go to /var/root and you can see the mysql-sandboxes directory, and the contents of each directory

cd /var/root
pwd
ls -ld mysql-sandboxes
ls -ld mysql-sandboxes/*
ls -ld mysql-sandboxes/3310/*

~:root # cd /var/root

~:root # pwd
/var/root

~:root # ls -ld mysql-sandboxes
drwxr-xr-x  5 _mysql  wheel  170 Apr 24 11:25 mysql-sandboxes

~:root # ls -ld mysql-sandboxes/*
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 12:07 mysql-sandboxes/3310
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 mysql-sandboxes/3320
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 mysql-sandboxes/3330

~:root # ls -ld mysql-sandboxes/3310/*
-rw-r-----   1 root    wheel     5 Apr 24 12:07 mysql-sandboxes/3310/3310.pid
-rw-------   1 _mysql  wheel   746 Apr 24 12:07 mysql-sandboxes/3310/my.cnf
drwxr-xr-x   2 _mysql  wheel    68 Apr 24 11:24 mysql-sandboxes/3310/mysql-files
drwxr-x---  41 _mysql  wheel  1394 Apr 24 12:07 mysql-sandboxes/3310/sandboxdata
-rwx------   1 _mysql  wheel   126 Apr 24 11:24 mysql-sandboxes/3310/start.sh
-rwx------   1 _mysql  wheel   196 Apr 24 11:24 mysql-sandboxes/3310/stop.sh

I want make sure I change ownership of the sandboxes to the mysql user.

cd /var/root
chown -R mysql mysql-sandboxes/
ls -ld mysql-sandboxes/

~:root # cd /var/root
~:root # chown -R mysql mysql-sandboxes/
~:root # ls -ld mysql-sandboxes/
drwxr-xr-x  5 _mysql  wheel  170 Apr 24 11:25 mysql-sandboxes/

Now, I want to verify that mysql router isn’t running.

ps -ef|grep router

~:root # ps -ef|grep router
    0  2766  2356   0 11:31AM ttys002    0:00.00 grep router

Before I start mysqlrouter I will want to include the router bin directory in the $PATH for root
I will edit root’s .profile file and add: /usr/local/mysql-router/bin to the $PATH.

I can now start the mysqlrouter in bootstrap mode. If you run this as root you will need to specify the user with the –user variable.

mysqlrouter –bootstrap localhost:3310 –directory /usr/local/myrouter –user=mysql

/usr/local:root # mysqlrouter --bootstrap localhost:3310 --directory /usr/local/myrouter --user=mysql
Please enter MySQL password for root: 

Bootstrapping MySQL Router instance at '/usr/local/myrouter'...
MySQL Router  has now been configured for the InnoDB cluster 'mycluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

/usr/local/myrouter/start.sh

Next, I will start the router.

/usr/local:root # /usr/local/myrouter/start.sh

I want to check the processes to make sure that router is running.

ps -ef|grep router

/usr/local:root # ps -ef|grep router
    0  2145     1   0  9:32PM ttys000    0:00.02 sudo ROUTER_PID=/usr/local/myrouter/mysqlrouter.pid /usr/local/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf --user=mysql
   74  2146  2145   0  9:32PM ttys000    0:00.22 /usr/local/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf --user=mysql
    0  2148  2136   0  9:32PM ttys000    0:00.00 grep router

I can now connect to the router and see which of the sandbox instances I connect to from the router.

If you already have a mysql shell window open – use this command: shell.connect(“root@localhost:6446”).

Or, from the command prompt – use this mysqlsh –uri root@localhost:6446

/usr/local/myrouter:root # mysqlsh --uri root@localhost:6446
Creating a session to 'root@localhost:6446'
Enter password: 
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 135
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use  to set one.
MySQL Shell 8.0.11

Copyright (c) 2016, 2018, 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  localhost:6446 ssl  JS > 

Switch to sql mode and check to see which port is being used.

\sql

select @@port;

 MySQL  localhost:6446 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
MySQL  localhost:6446 ssl  SQL > select @@port;
+--------+
| @@port |
+--------+
|   3310 |
+--------+
1 row in set (0.0005 sec)

I can see that I am connected to port 3310 – which is the read/write instance of the InnoDB cluster.

If you want to check the status of the cluster – you can’t do that from router – you will have to connect to ports 3310, 3320 or 3330.

If you try to check the status of the cluster while in SQL mode, you get this error:

 MySQL  localhost:6446 ssl  SQL > cluster.status();
ERROR: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cluster.status()' at line 1

If you try while in javascript mode, you get this error:

 MySQL  localhost:6446 ssl  JS > cluster.status();
ReferenceError: cluster is not defined

So, I will want to connect back to the cluster itself – but first I need to go to javascript mode.

\js
shell.connect(“root@localhost:3310”);

 MySQL  localhost:6446 ssl  SQL > \js
Switching to JavaScript mode...

 MySQL  JS > shell.connect("root@localhost:3310");
Please provide the password for 'root@localhost:3310': 
Creating a session to 'root@localhost:3310'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 193
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use  to set one.

I can check the status of the cluster again from javascript mode.

cluster=dba.getCluster();
cluster.status();

 MySQL  localhost:3310 ssl  JS > cluster.status();
{
    "clusterName": "mycluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@localhost:3310"
}

I am going to create a database, a table and then insert data into the table so I can see how group replication will replicate the changes from the read-write server to the other two servers.

I am opening three terminal windows each open to a separate port – 3310, 3320, and 3330.

mysql -uroot -p -P3310 -h127.0.0.1

/usr/local/myrouter:root #  mysql -uroot -p -P3310 -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 219
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> 

mysql -uroot -p -P3320 -h127.0.0.1

~:root # mysql -uroot -p -P3320 -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 109
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> 

mysql -uroot -p -P3330 -h127.0.0.1

/usr/local:root # mysql -uroot -p -P3330 -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 99
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> 

On each of the three instances – 3310, 3320 and 3330 – I will look at what databases I already have on each instance (they should only contain the default mysql databases).

show databases;

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

On the read-write server, which is on port 3310, I will create a database named test_01.

create database test_01;

mysql> create database test_01;
Query OK, 1 row affected (0.05 sec)

Now, I can check to see if the database was created on 3310, and then check on the other two to see that it has been replicated. I will run this on all three instances.

show databases;

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

I can see the test_01 database, and the new database doesn’t have any tables, so I will run this on all three run this to show zero tables:

use test_01;show tables;

mysql> use test_01;show tables;
Database changed
Empty set (0.01 sec);

I am going to create a table named “employees” on 3310.

CREATE TABLE `employees` (
`id_emp` int(11) NOT NULL AUTO_INCREMENT,
`name_first` varchar(45) DEFAULT NULL,
`name_middle` varchar(45) DEFAULT NULL,
`name_last` varchar(45) DEFAULT NULL,
`phone_home` varchar(45) DEFAULT NULL,
`phone_cell` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id_emp`)
) ENGINE=InnoDB AUTO_INCREMENT=10000;

mysql> CREATE TABLE `employees` (
    ->   `id_emp` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name_first` varchar(45) DEFAULT NULL,
    ->   `name_middle` varchar(45) DEFAULT NULL,
    ->   `name_last` varchar(45) DEFAULT NULL,
    ->   `phone_home` varchar(45) DEFAULT NULL,
    ->   `phone_cell` varchar(45) DEFAULT NULL,
    ->   PRIMARY KEY (`id_emp`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=10000;
Query OK, 0 rows affected (0.10 sec)

On all three instances – 3310, 3320 and 3330 – I will run this to show that the employee table creation was propagated to the other two servers via replication.

use test_01;show tables;

mysql> use test_01;show tables;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
+-------------------+
| Tables_in_test_01 |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

And, on all three instances – 3310, 3320 and 3330 – I will run this to show that the employee table is empty.

select * from employees;

mysql> select * from employees;
Empty set (0.01 sec)

Now I can insert a row into the employees table on 3310.

INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES (‘John’, ‘H’, ‘Smith’, ‘404-555-1212’, ‘404-555-2020’);

mysql> INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES ('John', 'H', 'Smith', '404-555-1212', '404-555-2020');
Query OK, 1 row affected (0.00 sec)

And, on all three instances – 3310, 3320 and 3330 – I will run this to show that the insert statement was propagated to the other two servers.

select * from employees;

mysql> select * from employees;
+--------+------------+-------------+-----------+--------------+--------------+
| id_emp | name_first | name_middle | name_last | phone_home   | phone_cell   |
+--------+------------+-------------+-----------+--------------+--------------+
|  10006 | John       | H           | Smith     | 404-555-1212 | 404-555-2020 |
+--------+------------+-------------+-----------+--------------+--------------+
1 row in set (0.00 sec)

On another terminal – login as root and let’s take a look at the sandbox files to see what was created.

cd /var/root
pwd
ls -ld mysql-sandboxes/*

~:root # cd /var/root
~:root # pwd
/var/root
~:root # ls -ld mysql-sandboxes/*
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:24 mysql-sandboxes/3310
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 mysql-sandboxes/3320
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 mysql-sandboxes/3330

Now from within shell – connect to the router. You might have to start a new shell – quit the old one if you have it open – otherwise, you might still be connected to 3310.

mysqlsh

~:root # mysqlsh
MySQL Shell 8.0.11

Copyright (c) 2016, 2018, 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.

shell.connect(“root@localhost:6446”);

 MySQL  JS > shell.connect("root@localhost:6446");
Please provide the password for 'root@localhost:6446': 
Creating a session to 'root@localhost:6446'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 146
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use  to set one.

I am going to kill the first box sandbox (using port 3310) which was the read-write instance.

dba.killSandboxInstance(3310);

 MySQL  localhost:6446 ssl  JS > dba.killSandboxInstance(3310);
The MySQL sandbox instance on this host in 
/var/root/mysql-sandboxes/3310 will be killed


Killing MySQL instance...

Instance localhost:3310 successfully killed.

# switch to sql mode

\sql

 MySQL  localhost:6446 ssl  JS > \sql
Switching to SQL mode... Commands end with ;

Now, I can check to see which port is now being used by the router.

select @@port;

 MySQL  localhost:6446 ssl  SQL > select @@port;
+--------+
| @@port |
+--------+
|   3320 |
+--------+
1 row in set (0.0004 sec)

I will switch to another terminal window and despite killing the sandbox, the sandbox files for 3310 weren’t removed.

ls -ld /var/root/mysql-sandboxes/*

~:root # ls -ld /var/root/mysql-sandboxes/*
drwxr-xr-x  7 _mysql  wheel  238 Apr 24 11:58 /var/root/mysql-sandboxes/3310
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 /var/root/mysql-sandboxes/3320
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 /var/root/mysql-sandboxes/3330

I will switch back the mysqlsh window, and switch to javascript mode.

\js

 MySQL  localhost:6446 ssl  SQL > \js
Switching to JavaScript mode...

I can now check the status of the cluster.

cluster=dba.getCluster();

 MySQL  localhost:6446 ssl  JS > cluster=dba.getCluster();

cluster.status();

You can see how the instance for 3310 is now labeled as MISSING and how 3320 is now the read-write instance.

 MySQL  localhost:6446 ssl  JS > cluster.status();
{
    "clusterName": "mycluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3320", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@localhost:6446"
}

Let’s add 3310 back into the cluster – and after you do this, if you quickly do another cluster status, you will see it is in recovery mode.

dba.startSandboxInstance(3310);

 MySQL  localhost:6446 ssl  JS > dba.startSandboxInstance(3310);
The MySQL sandbox instance on this host in 
/var/root/mysql-sandboxes/3310 will be started


Starting MySQL instance...

Instance localhost:3310 successfully started.

# if you do another cluster.status(); very quickly
# you can now see that 3310 is in RECOVERING mode

cluster.status();

 MySQL  localhost:6446 ssl  JS > cluster.status();
{
    "clusterName": "mycluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3320", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "RECOVERING"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@localhost:6446"
}

And then do another cluster.status() – and you can see how 3310 has rejoined the cluster, but it is now a read-only node.

cluster.status();

 MySQL  localhost:6446 ssl  JS > cluster.status();
{
    "clusterName": "mycluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3320", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@localhost:6446"
}

Now that 3310 is back online, if I try and do an insert from 3310 – I get an error – because it is a read-only node.

INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES (‘John’, ‘H’, ‘Smith’, ‘404-555-1212’, ‘404-555-2020’);

mysql> INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES ('John', 'H', 'Smith', '404-555-1212', '404-555-2020');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

I can do an insert on 3320 as it is the read-write node, and check to see if it was replicated to the other two servers.

INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES (‘Susan’, ‘K’, ‘James’, ‘912-555-8565’, ‘912-555-9986’);

mysql> INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES ('Susan', 'K', 'James', '912-555-8565', '912-555-9986');
Query OK, 1 row affected (0.09 sec)

And now I can check the employees table to see both rows of data – on all of the nodes.

use test_01; select * from employees;

mysql> select * from employees;                                                              
+--------+------------+-------------+-----------+--------------+--------------+
| id_emp | name_first | name_middle | name_last | phone_home   | phone_cell   |
+--------+------------+-------------+-----------+--------------+--------------+
|  10003 | John       | H           | Smith     | 404-555-1212 | 404-555-2020 |
|  10004 | Susan      | K           | James     | 912-555-8565 | 912-555-9986 |
+--------+------------+-------------+-----------+--------------+--------------+
2 rows in set (0.00 sec)

The InnoDB Cluster (sandbox version) is now up and running!


If you want to start over, kill all three mysqld processes, where pid1 and pi2 are the process ID’s from the ps -ef statement.

ps -ef| grep mysqld
kill -9 pid1 pid2

Double-check to make sure you killed the mysqld processes:

ps -ef| grep mysqld

~:root # ps -ef| grep mysqld
    0  2342     1   0 10:05PM ttys000    2:34.77 /usr/local/mysql/bin/mysqld --defaults-file=/var/root/mysql-sandboxes/3320/my.cnf --user=root
    0  2347     1   0 10:05PM ttys000    2:29.65 /usr/local/mysql/bin/mysqld --defaults-file=/var/root/mysql-sandboxes/3330/my.cnf --user=root
    0  2706     1   0  9:58AM ttys000    0:41.80 /usr/local/mysql/bin/mysqld --defaults-file=/var/root/mysql-sandboxes/3310/my.cnf --user=root
    0  2721  2356   0 11:17AM ttys002    0:00.00 grep mysqld
~:root # kill -9 2342 2347 2706
~:root # ps -ef| grep mysqld
    0  2723  2356   0 11:17AM ttys002    0:00.00 grep mysqld

Remove the sandbox files.

cd /var/root
ls -ld mysql-sandboxes/
rm -r mysql-sandboxes/
ls -ld mysql-sandboxes/

~:root # cd /var/root
~:root # ls -ld mysql-sandboxes/
drwxr-xr-x  5 _mysql  wheel  170 Apr 23 22:05 mysql-sandboxes/
~:root # rm -r mysql-sandboxes/
~:root # ls -ld mysql-sandboxes/
ls: mysql-sandboxes/: No such file or directory

ps -ef|grep router
kill -9 2645 2646 (whatever the PIDs are for router)
ps -ef|grep router

~:root # ps -ef|grep router
    0  2645     1   0  9:27AM ttys000    0:00.01 sudo ROUTER_PID=/usr/local/myrouter/mysqlrouter.pid /usr/local/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf --user=mysql
   74  2646  2645   0  9:27AM ttys000    0:39.63 /usr/local/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf --user=mysql
    0  2764  2356   0 11:31AM ttys002    0:00.00 grep router
~:root # kill -9 2646

Remove the /usr/local/myrouter directory.

cd /usr/local/
ls -ld myrouter
rm -r /usr/local/myrouter

/usr/local:root # cd /usr/local/
/usr/local:root # ls -ld myrouter
drwx------  10 _mysql  _mysql  340 Apr 24 11:40 myrouter
/usr/local:root # rm -r /usr/local/myrouter
/usr/local:root # ls -ld myrouter
ls: myrouter: No such file or directory

You can now start the whole process over again.

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.

MySQL Enterprise Edition Database Firewall – Control and Monitor SQL Statement Executions

As of MySQL 5.6.24, MySQL Enterprise Edition includes MySQL Enterprise Firewall, an application-level firewall (it runs within the mysql database process) that enables database administrators to permit or deny SQL statement execution based on matching against whitelists of accepted statement patterns. This helps harden MySQL Server against attacks such as SQL injection or attempts to exploit applications by using them outside of their legitimate query workload characteristics.

Each MySQL account registered with the firewall has its own whitelist of statement patterns (a tokenized representation of a SQL statement), enabling protection to be tailored per account. For a given account, the firewall can operate in recording or protecting mode, for training in the accepted statement patterns or protection against unacceptable statements. The diagram illustrates how the firewall processes incoming statements in each mode.

MySQL Enterprise Firewall Operation

(from https://dev.mysql.com/doc/refman/5.6/en/firewall.html)

If you do not have a MySQL Enterprise Edition license, you may download a trial version of the software via Oracle eDelivery. The MySQL Firewall is included in the MySQL Product Pack, specifically for MySQL Database 5.6.24 or higher.

MySQL Enterprise Firewall has these components:

  • A server-side plugin named MYSQL_FIREWALL that examines SQL statements before they execute and, based on its in-memory cache, renders a decision whether to execute or reject each statement.
  • Server-side plugins named MYSQL_FIREWALL_USERS and MYSQL_FIREWALL_WHITELIST implement INFORMATION_SCHEMA tables that provide views into the firewall data cache.
  • System tables named firewall_users and firewall_whitelist in the mysql database provide persistent storage of firewall data.
  • A stored procedure named sp_set_firewall_mode() registers MySQL accounts with the firewall, establishes their operational mode, and manages transfer of firewall data between the cache and the underlying system tables.
  • A set of user-defined functions provides an SQL-level API for synchronizing the cache with the underlying system tables.
  • System variables enable firewall configuration and status variables provide runtime operational information.

(from https://dev.mysql.com/doc/refman/5.6/en/firewall-components.html)

Installing the Firewall

Installing the firewall is fairly easy. After you install MySQL version 5.6.24 or greater, you simply execute an SQL script that is located in the $MYSQL_HOME/share directory. There are two versions of the script, one for Linux and one for Windows (the firewall isn’t supported on the Mac yet).

The scripts are named win_install_firewall.sql for Windows and linux_install_firewall.sql for linux. You may execute this script from the command line or via MySQL Workbench. For the command line, be sure you are in the directory where the script is located.

shell> mysql -u root -p mysql < win_install_firewall.sql
Enter password: (enter root password here)

The script create the firewall tables, functions, stored procedures and installs the necessary plugins. The script contains the following:

# Copyright (c) 2015 Oracle and/or its affiliates. All rights reserved.
# Install firewall tables
USE mysql;
CREATE TABLE IF NOT EXISTS mysql.firewall_whitelist( USERHOST VARCHAR(80) NOT NULL, RULE text NOT NULL) engine= MyISAM;
CREATE TABLE IF NOT EXISTS mysql.firewall_users( USERHOST VARCHAR(80) PRIMARY KEY, MODE ENUM ('OFF', 'RECORDING', 'PROTECTING', 'RESET') DEFAULT 'OFF') engine= MyISAM;

INSTALL PLUGIN mysql_firewall SONAME 'firewall.dll';
INSTALL PLUGIN mysql_firewall_whitelist SONAME 'firewall.dll';
INSTALL PLUGIN mysql_firewall_users SONAME 'firewall.dll';

CREATE FUNCTION set_firewall_mode RETURNS STRING SONAME 'firewall.dll';
CREATE FUNCTION normalize_statement RETURNS STRING SONAME 'firewall.dll';
CREATE AGGREGATE FUNCTION read_firewall_whitelist RETURNS STRING SONAME 'firewall.dll';
CREATE AGGREGATE FUNCTION read_firewall_users RETURNS STRING SONAME 'firewall.dll';
delimiter //
CREATE PROCEDURE sp_set_firewall_mode (IN arg_userhost VARCHAR(80), IN arg_mode varchar(12))
BEGIN
IF arg_mode = "RECORDING" THEN
  SELECT read_firewall_whitelist(arg_userhost,FW.rule) FROM mysql.firewall_whitelist FW WHERE FW.userhost=arg_userhost;
END IF;
SELECT set_firewall_mode(arg_userhost, arg_mode);
if arg_mode = "RESET" THEN
  SET arg_mode = "OFF";
END IF;
INSERT IGNORE INTO mysql.firewall_users VALUES (arg_userhost, arg_mode);
UPDATE mysql.firewall_users SET mode=arg_mode WHERE userhost = arg_userhost;

IF arg_mode = "PROTECTING" OR arg_mode = "OFF" THEN
  DELETE FROM mysql.firewall_whitelist WHERE USERHOST = arg_userhost;
  INSERT INTO mysql.firewall_whitelist SELECT USERHOST,RULE FROM INFORMATION_SCHEMA.mysql_firewall_whitelist WHERE USERHOST=arg_userhost;
END IF;
END //
delimiter ;

After you run the script, the firewall should be enabled. You may verify it by running this statement:

mysql> SHOW GLOBAL VARIABLES LIKE 'mysql_firewall_mode';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| mysql_firewall_max_query_size |  4096 |
| mysql_firewall_mode           |    ON |
| mysql_firewall_trace          |   OFF |
+-------------------------------+-------+

Testing the Firewall

To test the firewall, you may use a current mysql user, but we are going to create a test user for this example – webuser@localhost. (The user probably doesn’t need all privileges, but for this example we will grant everything to this user)

CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'Yobuddy!';
'GRANT ALL PRIVILEGES ON *.* TO 'webuser'@'localhost' WITH GRANT OPTION'

OPTIONAL: For our test, we will be using the sakila schema provided by MySQL. You may download the sakila database schema (requires MySQL 5.0 or later) at http://dev.mysql.com/doc/index-other.html. If you don’t want to use the sakila database, you may use your own existing database or create a new database.

After downloading the sakila schema, you will have two files, named sakila-schema.sql and sakila-data.sql. Execute the sakila-schema.sql first, and then sakila-data.sql to populate the database with data. If you are using the command line, simply do the following: (substitute UserName for a mysql user name)

# mysql -uUserName -p < sakila-schema.sql
# mysql -uUserName -p < sakila-data.sql

After creating the sakila schema and importing the data, we now set the firewall to record those queries which we want to allow:

mysql> CALL `mysql`.`sp_set_firewall_mode`("webuser@localhost","RECORDING")
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0  Imported rules: 0          |
+-----------------------------------------------+
1 row in set (0.14 sec)

+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.22 sec)
Query OK, 5 rows affected (0.28 sec)

We can check to see the firewall mode via this statement, to be sure we are in the recording mode:

mysql> SELECT * FROM MYSQL.FIREWALL_USERS;
+-------------------+------------+
| USERHOST          | MODE       |
+-------------------+------------+
| webuser@localhost |  RECORDING |
+-------------------+------------+
1 row in set (0.02 sec)

Now that we have recording turned on, let’s run a few queries:

mysql> use sakila
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

mysql> select * from actor limit 2;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.13 sec)

mysql> select first_name, last_name from actor where first_name like 'T%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| TIM        | HACKMAN   |
| TOM        | MCKELLEN  |
| TOM        | MIRANDA   |
| THORA      | TEMPLE    |
+------------+-----------+
4 rows in set (0.00 sec)

We turn off the recording by turning on the protection mode:

mysql> CALL `mysql`.`sp_set_firewall_mode`("webuser@localhost","PROTECTING");
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

We can check to see the firewall mode via this statement:

mysql> SELECT * FROM MYSQL.FIREWALL_USERS;
+-------------------+------------+
| USERHOST          | MODE       |
+-------------------+------------+
| webuser@localhost | PROTECTING |
+-------------------+------------+
1 row in set (0.02 sec)

And we can look at our whitelist of statements:

mysql>  SELECT * FROM MYSQL.FIREWALL_WHITELIST;
+-------------------+-------------------------------------------------------------------+
| USERHOST          | RULE                                                              |
+-------------------+-------------------------------------------------------------------+
| webuser@localhost | SELECT * FROM actor LIMIT ?                                       |
| webuser@localhost | SELECT SCHEMA ( )                                                 |
| webuser@localhost | SELECT first_name , last_name FROM actor WHERE first_name LIKE ?  |
| webuser@localhost | SHOW TABLES                                                       |
+-------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

The firewall is now protecting against non-whitelisted queries. We can execute a couple of the queries we previously ran, which should be allowed by the firewall.

mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.01 sec)

Now we run two new queries, which should be blocked by the firewall.

mysql> select * from rental;
ERROR 1045 (42000): Firewall prevents statement

mysql> select * from staff;
ERROR 1045 (42000): Firewall prevents statement

The server will write an error message to the log for each statement that is rejected. Example:

2015-03-21T22:59:05.371772Z 14 [Note] Plugin MYSQL_FIREWALL reported:
'ACCESS DENIED for webuser@localhost. Reason: No match in whitelist.
Statement: select * from rental '

You can use these log messages in your efforts to identify the source of attacks.

To see how much firewall activity you have, you may look look at the status variables:

mysql> SHOW GLOBAL STATUS LIKE 'Firewall%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Firewall_access_denied  | 42    |
| Firewall_access_granted | 55    |
| Firewall_cached_entries | 78    |
+-------------------------+-------+

The variables indicate the number of statements rejected, accepted, and added to the cache, respectively.

The MySQL Enterprise Firewall Reference is found at https://dev.mysql.com/doc/refman/5.6/en/firewall-reference.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.

MySQL Enterprise Audit – parsing audit information from log files, inserting into MySQL table via LOAD DATA INFILE and Perl script

The MySQL Enterprise Audit plug-in is part of the MySQL Enterprise Edition (available through a paid license). Basically, Enterprise Audit tracks everything that is happening on your MySQL server, and can be used to protect/detect the misuse of information, and to meet popular compliance regulations including HIPAA, Sarbanes-Oxley, and the PCI Data Security Standard.

MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the Oracle audit specification, MySQL Enterprise Audit provides an out of box, easy to use auditing and compliance solution for applications that are governed by both internal and external regulatory guidelines.

When installed, the audit plugin enables MySQL Server to produce a log file containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.
(from https://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin.html)

When you enable MySQL Enterprise Audit, log files are generated in your MySQL data directory. You can use tools like MySQL Workbench (Enterprise Edition) or Oracle Audit Vault to import the log data, to view the information and to generate reports.

I was talking with a client, and he wanted to know if the audit data could be stored in a table. Currently (as of MySQL 5.6.25), the audit information is stored as XML in the audit log files. There are several ways to do this, and I will cover two methods.

The first is to use the LOAD XML [LOCAL] INFILE command. You will need to create a table to store the audit information:

CREATE TABLE audit_log (
   RECORD_ID varchar(40) NOT NULL,
   NAME varchar(64),
   TIMESTAMP timestamp,
   COMMAND_CLASS varchar(64),
   CONNECTION_ID bigint unsigned,
   DB varchar(64),
   HOST varchar(60),
   IPv4 int unsigned,
   IPv6 varbinary(16),
   MYSQL_VERSION varchar(64),
   OS_LOGIN varchar(64),
   PRIV_USER varchar(16),
   PROXY_USER varchar(16),
   SERVER_ID int unsigned,
   SQLTEXT longtext,
   STARTUP_OPTIONS text,
   STATUS int unsigned,
   STATUS_CODE int unsigned,
   USER varchar(168),
   VERSION int unsigned,
   PRIMARY KEY(RECORD_ID)
) DEFAULT CHARSET utf8mb4;

You can then load the data as:

LOAD XML LOCAL INFILE 'audit.log'
    INTO TABLE audit_log
    CHARACTER SET utf8mb4
    ROWS IDENTIFIED BY ''
         (RECORD_ID, NAME, @TIMESTAMP, COMMAND_CLASS, CONNECTION_ID, DB, HOST, @IP, MYSQL_VERSION, OS_LOGIN, PRIV_USER, PROXY_USER, SERVER_ID, SQLTEXT, STARTUP_OPTIONS, STATUS, STATUS_CODE, USER, VERSION)
     SET TIMESTAMP = CONVERT_TZ(STR_TO_DATE(@TIMESTAMP, '%Y-%m-%dT%H:%i:%s UTC'), 'UTC', 'Australia/Sydney'),
         IPv4 = IF(IS_IPV4(@IP), INET_ATON(@IP), NULL),
         IPv6 = IF(IS_IPV6(@IP), INET6_ATON(@IP), NULL);

Important notes for the above example:

  • The example converts the IP address to the numeric version and stores it in IPv4 or IPv6 depending on the type of IP address. It is also possible to store the IP address in a shared varchar() column.
  • Replace the target time zone in CONVERT_TZ() with the time zone of you system.
  • The use of CONVERT_TZ() requires named time zones to be loaded or that you use a numeric offset such as +10:00.

RECORD_ID is guaranteed unique with the following limitations:

  • If you change audit_log_format, the counter will reset. However as the timestamp is also included in the RECORD_ID this will in general not be an issue.
  • It is only unique for a given MySQL instance. If you want to import the audit logs for multiple instances, you can for example add the server_uuid to the table and use as part of the primary key.
  • server_uuid is available starting from MySQL 5.6.
  • The matching between the elements in the XML file and the column names is case sensitive. All the elements in the audit log are in upper case.
  • The ROWS IDENTIFIED BY ” clause is required unless the table is named AUDIT_RECORD as the table name is the default elements to look for as rows.

Here are some links for more reading on using LOAD XML [LOCAL] INFILE:

https://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin-options-variables.html#sysvar_audit_log_format
https://dev.mysql.com/doc/refman/5.6/en/load-xml.html
https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_convert-tz
https://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html
https://dev.mysql.com/doc/refman/5.6/en/replication-options.html#sysvar_server_uuid


For the second option, I wrote a quick Perl script that would parse the XML log files and insert the information into a MySQL database. You will need to set the size of your audit log files in your my.cnf or my.ini configuration file via the audit_log_rotate_on_size variable. You might need to adjust the size of your log files based upon database activity and how well the script parses the log files. If your log files are very large, the Perl script might have issues processing it, and you might want to decrease the size of your log files and run the script more frequently.


CAVEAT
Enterprise Audit does require a license from MySQL. If you are interested in an Enterprise subscription, contact me via the comment section below. If you are an Enterprise customer, you will need to configure Enterprise Audit first. See the Enterprise Audit online documentation page for more information, or contact MySQL Support.


For the data fields, I used the audit log file format information found at The Audit Log File page on MySQL.com.

My MySQL server doesn’t have a lot of activity, so I tried to configure the size of the data fields as best as possible to accommodate the possible size of the data in each field. There may be instances where you will have to increase the size of these fields or change their data types. The largest field is the SQL_TEXT field which will contain your SQL statements. Every table has a max row size of 65,535 bytes. So, the largest possible size of the SQL_TEXT field could be for this example is around 63,200 bytes (65,535 bytes minus the sum of the size of all of the other fields, and minus the 1-byte or 2-byte length prefix used for each varchar field). In this example, the SQL_TEXT field is set to 8,096 bytes, so you may need to increase or decrease this value.

I used varchar data types for each field, excluding the primary key field named ID. I did not spend a lot of time on the database schema, so you might want to modify it a little. I am sure that some of the fields are integers, but I did not have enough data in my log files to positively determine all of the possible values for each field. I did read the online manual, and it stated that CONNECTION_ID, SERVER_ID, STATUS, STATUS_CODE and VERSION were unsigned integers – but I left them as varchar.


NOTICE
This script requires the use of the new format for the audit log files, which is available in MySQL versions 5.6.20 or later.


I created a database along with two tables; one to store the log file information, and a history table to keep track of what files had already been parsed and inserted into MySQL, as well as the number of log file entries. The CREATE DATABASE and CREATE TABLE syntax is as follows:

CREATE DATABASE `audit_information` /*!40100 DEFAULT CHARACTER SET latin1 */

CREATE TABLE `audit_parsed` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `COMMAND_CLASS` varchar(64) DEFAULT NULL,
  `CONNECTIONID` varchar(32) DEFAULT NULL,
  `DB_NAME` varchar(64) DEFAULT NULL,
  `HOST_NAME` varchar(256) DEFAULT NULL,
  `IP_ADDRESS` varchar(16) DEFAULT NULL,
  `MYSQL_VERSION` varchar(64) DEFAULT NULL,
  `COMMAND_NAME` varchar(64) DEFAULT NULL,
  `OS_LOGIN` varchar(64) DEFAULT NULL,
  `OS_VERSION` varchar(256) DEFAULT NULL,
  `PRIV_USER` varchar(16) DEFAULT NULL,
  `PROXY_USER` varchar(16) DEFAULT NULL,
  `RECORD_ID` varchar(64) DEFAULT NULL,
  `SERVER_ID` varchar(32) DEFAULT NULL,
  `SQL_TEXT` varchar(8096) DEFAULT NULL,
  `STARTUP_OPTIONS` varchar(1024) DEFAULT NULL,
  `COMMAND_STATUS` varchar(64) DEFAULT NULL,
  `STATUS_CODE` varchar(11) DEFAULT NULL,
  `DATE_TIMESTAMP` varchar(24) DEFAULT NULL,
  `USER_NAME` varchar(128) DEFAULT NULL,
  `LOG_VERSION` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

CREATE TABLE `audit_history` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `AUDIT_LOG_NAME` varchar(64) DEFAULT NULL,
  `PARSED_DATE_TIME` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `LOG_ENTRIES` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

The Perl script finds the non-active log files (which end in .xml – example: audit.log.14357895017796690.xml), parses the data, creates an SQL file with INSERT statements, imports the data via the mysql command-line program, and then moves the log file(s) and SQL file(s) to a directory. The history table records what files have been processed, so you don’t accidentally process the same file twice.

In the beginning of the Perl script, there are several values you need to replace to match your system. The values are under the section titled “values needed”. Here is the Perl script (named audit.pl):

#!/usr/bin/perl
# audit.pl

use DBI;
use CGI;
use XML::Simple;

#----------------------------------------------------------
# values needed
$Database = "audit_information";
$MYSQL_DATA_DIR = "/usr/local/mysql/data";
$MySQL_Host_IP_Name = "192.168.1.2";
$mysql_user = "root";
$mysql_password = "password_needed";

# directory to store old audit files after parsing
$audit_directory = "$MYSQL_DATA_DIR/audit_files";

# make an audit_files directory if one does not exist
mkdir($audit_directory) unless(-d $audit_directory);
#----------------------------------------------------------


#----------------------------------------------------------
#for each file do this
@files = @files = ;;
foreach $file_name_to_parse (@files) {

	#----------------------------------------------------------
	# check to see if file has already been parsed
	$dbh1 = ConnectToMySql($Database);
	$query1 = "select AUDIT_LOG_NAME from audit_history where AUDIT_LOG_NAME = '$file_name_to_parse'";
	$sth1 = $dbh1->prepare($query1);
	$sth1->execute();

          while (@data = $sth1->fetchrow_array()) {
            
         	   $audit_log_name = $data[0];

			}

	# if length of audit_log_name is less than 1, process file
	if (length($audit_log_name) $PARSED_FILE") or die print "Couldn't open log_file: $!";
		
		$count = 0;
		
		# XML::Simple variable - SuppressEmpty => 1   ignore empty values
		$xml = XML::Simple->new(SuppressEmpty => 1);
		$data = $xml->XMLin("$file_name_to_parse");
		
		foreach $info (@{$data->{AUDIT_RECORD}})
		{
			# replace tick marks ' with \' in the SQL TEXT
			$info->{"SQLTEXT"} =~ s/'/\\'/g;
		
			print LOGOUT "INSERT INTO audit_information.AUDIT_PARSED (COMMAND_CLASS, CONNECTIONID, DB_NAME, HOST_NAME, IP_ADDRESS, MYSQL_VERSION, COMMAND_NAME, OS_LOGIN, OS_VERSION, PRIV_USER, PROXY_USER, RECORD_ID, SERVER_ID, SQL_TEXT, STARTUP_OPTIONS, COMMAND_STATUS, STATUS_CODE, DATE_TIMESTAMP, USER_NAME, LOG_VERSION) values ('" . $info->{"COMMAND_CLASS"} . "', '" . $info->{"CONNECTION_ID"} . "', '" . $info->{"DB"} . "', '" . $info->{"HOST"} . "', '" . $info->{"IP"} . "', '" . $info->{"MYSQL_VERSION"} . "', '" . $info->{"NAME"} . "', '" . $info->{"OS_LOGIN"} . "', '" . $info->{"OS_VERSION"} . "', '" . $info->{"PRIV_USER"} . "', '" . $info->{"PROXY_USER"} . "', '" . $info->{"RECORD_ID"} . "', '" . $info->{"SERVER_ID"} . "', '" . $info->{"SQLTEXT"} . "', '" . $info->{"STARTUP_OPTIONS"} . "', '" . $info->{"STATUS"} . "', '" . $info->{"STATUS_CODE"} . "', '" . $info->{"TIMESTAMP"} . "', '" . $info->{"USER"} . "', '" . $info->{"VERSION"} . "');\n";
			$count++;
		
		# end foreach $info (@{$data->{AUDIT_RECORD}})
		}
		
		# load parsed file into MySQL - hide warnings
		system("mysql -u$mysql_user -p$mysql_password  /dev/null 2>&1");
										
		$dbh2 = ConnectToMySql($Database);
		$query2 = "insert into audit_information.audit_history (AUDIT_LOG_NAME, LOG_ENTRIES) values ('$file_name_to_parse', '$count')";
		
		# optional print output - uncomment if desired
		# print "$query2\n";
																													
		$sth2 = $dbh2->prepare($query2);
		$sth2->execute();

		# close audit log file
		close(INFILE);

		# optional print output - uncomment if desired
		# print "Moving audit log ($file_name_to_parse) and log file ($PARSED_FILE) to $audit_directory.\n";
		
		# strip directories off $file_name_to_parse
		@file_name_to_move_array = split("\/",$file_name_to_parse);
		$directory_count = $#file_name_to_move_array;
		$file_name_to_move = $file_name_to_move_array[$directory_count];
		
		
		# optional print output - uncomment if desired
		# print "mv $file_name_to_move $file_name_to_parse\n";
		# print "mv $PARSED_FILE $audit_directory\n";

		# move audit log files and parsed log files to $audit_directory
		system("mv $file_name_to_parse $audit_directory");
		system("mv $PARSED_FILE $audit_directory");

	# end - if (length($audit_log_name) < 1)
	}

	else

	{
		# optional print output - uncomment if desired
		# print "$audit_log_name already processed\n";
		system("mv $file_name_to_parse $audit_directory");
	}

# end - foreach $file_name_to_parse (@files) 
}

sub ConnectToMySql {

   $connectionInfo="dbi:mysql:$Database;$MySQL_Host_IP_Name:3306";

   # make connection to database
   $l_dbh = DBI->connect($connectionInfo,$mysql_user,$mysql_password);
   return $l_dbh;

}

It should not matter where you execute audit.pl, as long as you have correctly entered the required values in the script. You might get errors if you try to run this script on a log file that has not been rotated, which is the current log file in your MySQL data directory. The current log file is named audit.log.

# pwd
/usr/local/mysql/data
# ls -l audit.log
-rw-rw----  1 mysql  _mysql  9955118 Jul  2 15:25 audit.log

The script will only work on files ending in .xml. For testing, I used four small (and identical) audit log files:

# pwd
/usr/local/mysql/data
# ls -l *xml
-rw-rw----  1 mysql  wheel   15508 Jul  2 12:20 audit.log.14357895017796690.xml
-rw-r-----  1 mysql  _mysql  15508 Jul  2 13:46 audit.log.14357895017796691.xml
-rw-r-----  1 mysql  _mysql  15508 Jul  2 13:46 audit.log.14357895017796692.xml
-rw-r-----  1 mysql  _mysql  15508 Jul  2 13:46 audit.log.14357895017796693.xml

I have commented-out the print statements in the Perl script, but if you uncomment them, running the script gives you this output for each log file:

# perl audit.pl
Parsing - /usr/local/mysql/data/audit.log.14357895017796690.xml
insert into audit_information.audit_history (AUDIT_LOG_NAME, LOG_ENTRIES) values ('/usr/local/mysql/data/audit.log.14357895017796690.xml', '34')
Moving audit log (/usr/local/mysql/data/audit.log.14357895017796690.xml) and log file (/usr/local/mysql/data/audit.log.14357895017796690_parsed.sql) to /usr/local/mysql/data/audit_files.
mv audit.log.14357895017796690.xml /usr/local/mysql/data/audit.log.14357895017796690.xml
mv /usr/local/mysql/data/audit.log.14357895017796690_parsed.sql /usr/local/mysql/data/audit_files
....

After running my test script, the following data is what is in the audit_history table:

mysql> use audit_information
Database changed
mysql> select * from audit_history;
+----+-------------------------------------------------------+---------------------+-------------+
| ID | AUDIT_LOG_NAME                                        | PARSED_DATE_TIME    | LOG_ENTRIES |
+----+-------------------------------------------------------+---------------------+-------------+
|  1 | /usr/local/mysql/data/audit.log.14357895017796690.xml | 2015-07-02 15:25:07 | 34          |
|  2 | /usr/local/mysql/data/audit.log.14357895017796691.xml | 2015-07-02 15:25:08 | 34          |
|  3 | /usr/local/mysql/data/audit.log.14357895017796692.xml | 2015-07-02 15:25:08 | 34          |
|  4 | /usr/local/mysql/data/audit.log.14357895017796693.xml | 2015-07-02 15:25:09 | 34          |
+----+-------------------------------------------------------+---------------------+-------------+
4 rows in set (0.00 sec)

And here is an example of one line from the audit_parsed table.

mysql> select * from audit_parsed limit 1 \G
*************************** 1. row ***************************
             ID: 1
  COMMAND_CLASS: select
   CONNECTIONID: 10093
        DB_NAME: 
      HOST_NAME: localhost
     IP_ADDRESS: 127.0.0.1
  MYSQL_VERSION: 
   COMMAND_NAME: Query
       OS_LOGIN: 
     OS_VERSION: 
      PRIV_USER: 
     PROXY_USER: 
      RECORD_ID: 1614933_2015-07-01T22:08:58
      SERVER_ID: 
       SQL_TEXT: SELECT (UNIX_TIMESTAMP(now()) - CAST(variable_value AS SIGNED))*1000 as serverStartMillis 
  FROM information_schema.global_status 
 WHERE variable_name='uptime'
STARTUP_OPTIONS: 
 COMMAND_STATUS: 0
    STATUS_CODE: 0
 DATE_TIMESTAMP: 2015-07-01T22:08:58 UTC
      USER_NAME: root[root] @ localhost [127.0.0.1]
    LOG_VERSION: 
1 row in set (0.00 sec)

After parsing the log files, you can then write your own queries for searching through your audit data. You can even include this script in cron, so it runs and parses your files automatically. But as always, test this script and use it with caution before putting it in a production environment. You could also modify the Perl script to filter out values you did not want or need to store.

If you do use this script or if you have any suggestions or other questions, please leave a comment below.

Thanks to Jesper Krogh for providing the information on the LOAD XML [LOCAL] INFILE.

 


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.

MySQL Dumping and Reloading the InnoDB Buffer Pool

MySQL’s default storage engine as of version 5.5 is InnoDB. InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. By keeping the frequently-accessed data in memory, related searches are retrieved much faster than reading from disk.

When you stop or restart MySQL, you lose the cached data stored in the buffer pool. There is a feature in MySQL 5.6 which allows you to dump the contents of the buffer pool before you shutdown the mysqld process. Then, when you start mysqld again, you can reload the contents of the buffer pool back into memory. You may also dump the buffer pool at any time for reloading later.

To see information about the buffer pool, use the SHOW ENGINE INNODB STATUS command:

mysql> SHOW ENGINE INNODB STATUS\G
....
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 274726912; in additional pool allocated 0
Dictionary memory allocated 308740
Buffer pool size   16384
Free buffers       15186
Database pages     1195
Old database pages 421
....

This example shows the buffer pool contains 1195 database pages (this example is a very small one from my home server). When you dump the buffer pool to disk, only the database pages are recorded. When you restart mysqld, the data from these pages will be loaded back into memory.

You may dump the buffer pool with this command:

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

The buffer pool dump file is stored in your MySQL data directory.

# pwd
/usr/local/mysql/data
# ls -l ib_buffer_pool
-rw-rw----  1 mysql  wheel  7122 Feb 13 13:58 ib_buffer_pool

The dump is a plain-text file, and we can see the file is 1195 lines long and contains only the database page references.

# file ib_buffer_pool
ib_buffer_pool: ASCII text
# wc -l ib_buffer_pool
    1195 ib_buffer_pool
# head ib_buffer_pool
0,7
0,1
0,3
0,2
0,4
0,11
0,5
0,6
0,301
0,522

If you have a large buffer pool, you can check on the status of the dump with this command:

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

If you want to save the buffer pool when MySQL is shutdown or restarted, use this command:

SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;

To restore the buffer pool when starting MySQL, append this statement to your mysqld command:

--innodb_buffer_pool_load_at_startup=ON;

Or, to load the buffer pool file while mysqld is running, use this command:

SET GLOBAL innodb_buffer_pool_load_now=ON;

Reloading the buffer pool is very fast, and is performed in the background so the users will not be effected. More information about preloading the buffer pools may be found at http://dev.mysql.com/doc/refman/5.6/en/innodb-preload-buffer-pool.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.

Using mysqldump and the MySQL binary log – a quick guide on how to backup and restore MySQL databases

Be sure to check out my other posts on mysqldump:
Scripting Backups of MySQL with Perl via mysqldump
Splitting a MySQL Dump File Into Smaller Files Via Perl
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


I have already written several posts on using mysqldump. This post will be a quick guide to using mysqldump to backup and restore your databases. And we will look at using the MySQL binary log (binlog) along with mysqldump for point-in-time recovery. If your database is extremely large, and for databases in a production environment, you might want to consider using MySQL Enterprise Backup (mysqlbackup), which is part of the MySQL Enterprise Edition.

For those of you that aren’t familiar with mysqldump:

The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MySQL database for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

The best feature about mysqldump is that it is easy to use. The main problem with using mysqldump occurs when you need to restore a database. When you execute mysqldump, the database backup (output) is an SQL file that contains all of the necessary SQL statements to restore the database – but restoring requires that you execute these SQL statements to essentially rebuild the database. Since you are recreating your database, the tables and all of your data from this file, the restoration procedure can take a long time to execute if you have a very large database.


NOTE: If you are using GTID’s (global transaction identifiers) in your database, you will need to include the –set-gtid-purged=OFF option, otherwise you will receive this error:

Warning: A partial dump from a server that has GTIDs will by default include the
GTIDs of all transactions, even those that changed suppressed parts of the database.
If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete 
dump, pass --all-databases --triggers --routines --events. 

For these examples, I will not include the –set-gtid-purged=OFF option.


Dumping and making a copy of a single database

To dump/backup a single database:

mysqldump -uroot -p database_name > db_dump.sql

To load the dump file back into mysql, you will need to create the new database first. If you use the –databases option before the database name, mysqldump will also dump the CREATE DATABASE and USE statements that you need prior to inserting the data from the dump.

You can either use mysqladmin to create the new database, or create it from a MySQL prompt:

# mysqladmin create new_database_name

mysql> CREATE DATABASE new_database_name;

Next, you can simply import the dump file into mysql.

# mysql new_database_name < db_dump.sql

You can also use the dump file to move the database to another server. If you did not use the –databases option, then you will need to create the database first.

Dumping events, routines, triggers

Here are the options for mysqldump to also dump event scheduler events, stored procedures or functions. If you want to include these, use these options:

–routines – dumps stored procedures and functions
–events – dumps Event Scheduler events
–triggers – dumps triggers

When you use mysqldump, triggers are dumped by default. If you want to disable any of these functions, use the “skip” versions: https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_events–skip-events, –skip-routines, or –skip-triggers.

Only dump table definitions

If you want to just dump the CREATE TABLE statements without any data, use the –no-data option.

# mysqldump --no-data database_name > db_definitions_dump.sql

You might want to add the –routines and –events options to include stored routines and event definitions.

# mysqldump --no-data --routines --events database_name > db_definitions_dump.sql

Only dump the data

If you want to just dump the data without any table definitions, you can use the –no-create-info option:

# mysqldump --no-create-info database_name > db_data_dump.sql

Using mysqldump to test a new version of mysql

Let’s say that you need to upgrade to a new version of mysql. In order to test to see if your database objects are handled properly by the new version, you can simply dump the data definitions and import them into the new version of MySQL (preferably on a separate computer).

On the computer with the old version of MySQL:

mysqldump --all-databases --no-data --routines --events > db_definitions.sql

Then, on the upgraded server, you can just import this dump file:

mysql -uroot -p < db_definitions.sql

This will help you spot any potential incompatibilities with the new version of MySQL. If you don’t receive any errors, you can then dump the data and load into the new server. Be sure to run some test queries on the new upgraded server as well.

Point-in-time recovery using mysqldump and the binary logs

The MySQL binary logs (binlogs) contains all of the SQL statements or “events” that could change the database (I say “could” because a delete statement that does not delete any rows would still be entered into the binary log – unless you are using row-based logging).

For more information about the binary log, see: http://dev.mysql.com/doc/refman/5.6/en/binary-log.html.

Since the binlog contains all of the events that happen to the database, you can use the binlog to apply these same changes to a different database. If you started your MySQL instance with the binlogs enabled, and you have never flushed the binlogs, then the binlogs contain all of the SQL statements for all of the data that is in your database. The binlog itself is like a backup of your database.

If you want to use the binary logs in addition to mysqldump to restore your database, you need to have the binary logs (binlogs) enabled. There are many options for the binlogs (see http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html, but the only two that you really need for this example are:

 --log-bin[=base_name]
 --log-bin-index[=file_name]

One other option is to use the –binlog-format. You can set this value to STATEMENT (default), ROW or MIXED. For more information about these options, see http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_format.

These variables need to go into your my.cnf or my.ini file under the [mysqld] section, and this will require a restart of mysqld.

Once you have the binary log enabled, you will need to do a few things differently when you use mysqldump. You will need to:

– flush the tables and place a READ lock on the tables
– check to see what binlog is being used
– check the position of the binlog
– dump your data with mysqldump
– release the lock

By placing a read lock on the tables, you are stopping anyone from modifying the data in the database. By having the binlog and binlog position, these will allow you use the binary logs to restore any statements that happened after the mysqldump. Open two terminal windows – one with a MySQL prompt, and another with a root prompt:

In the MySQL prompt, issue the READ lock and SHOW MASTER STATUS:

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.47 sec)

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

Now you are ready to dump the database with whatever options you need:

# mysqldump --all-databases > db_000008_191_dump.sql

Once the dump has finished, you can release the lock:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

It is extremely important that you write down the binlog file and position from the SHOW MASTER STATUS statement and somehow associate it with the dump file. One way to do this is to insert the binlog file name and position into the dump file name. In my example above, I did this by adding both to the dump file name db_000008_191_dump.sql.

When you have to restore the database, you will need to load the dump file first, and then apply the binlog(s) to the database.

Let’s assume that we need to restore the entire database. First, we will import the dump file:

# mysql -uroot -p < db_000008_191_dump.sql

Next, you will need to load the information from the binlog(s). To load information from the binlogs, you need to use the mysqlbinlog utility. You can check your MySQL data directory to see how many binlogs you have used since the one that was in the SHOW MASTER STATUS statement:

$ ls -l mysql-bin*
-rw-rw----  1 mysql  wheel     67110 Apr  4 16:22 mysql-bin.000001
-rw-rw----  1 mysql  wheel   1230893 Apr  4 16:24 mysql-bin.000002
-rw-rw----  1 mysql  wheel  13383907 Apr  4 17:03 mysql-bin.000003
-rw-rw----  1 mysql  wheel  13383907 Apr  4 19:03 mysql-bin.000004
-rw-rw----  1 mysql  wheel  13383907 Apr  4 19:07 mysql-bin.000005
-rw-rw----  1 mysql  wheel  13383907 Apr 18 16:48 mysql-bin.000006
-rw-rw----  1 mysql  wheel  13383907 Apr 21 13:37 mysql-bin.000007
-rw-rw----  1 mysql  wheel  13383907 Apr 21 13:37 mysql-bin.000008
-rw-rw----  1 mysql  wheel    154847 Apr 21 13:37 mysql-bin.000009
-rw-rw----  1 mysql  wheel       171 Apr 21 13:37 mysql-bin.index

You can also just look at the mysql-bin.index file (located in your MySQL data directory), which contains a list of all of the binary files in use:

 # cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009

In this example, we will need to apply the changes from the binlog file mysql-bin.000008 after position 191, and then all of the mysql-bin.000009 binlog. You will need to add the correct data directory PATH to your mysqlbinlog statement.

mysqlbinlog --start-position=191 $DATA_DIR_PATH/mysql-bin.000008 | mysql -u root -p

After you have inserted all of mysql-bin.000008 after position 191, you can insert the entire mysql-bin.000009 binlog file:

mysqlbinlog $DATA_DIR_PATH/mysql-bin.000009 | mysql -u root -p

Note: During the restore process, you do not want anyone inserting any data into the database.

Your database should now be back to the state when the database crashed or was deleted. It isn’t mandatory, but it is also a good idea to copy the binlogs to a separate location as part of your backup routine. You can use mysqlbinlog to do this as well – see: http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-backup.html.

For more information on using the binary logs for point-in-time recovery, see https://dev.mysql.com/doc/refman/5.6/en/point-in-time-recovery.html. There are a lot of other options for using binlogs. The best option for backing up and restoring your MySQL database is to use the MySQL Enterprise Backup (mysqlbackup), which is part of the MySQL Enterprise Edition subscription, which includes 24×7 MySQL Support and the other Enterprise features.

 


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.