MySQL Enterprise Backup | Scripting MySQL

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

March 25, 2019 Leave a comment

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

MySQL Enterprise Transparent Data Encryption (TDE) – provides at-rest encryption for physical InnoDB tablespace data files

December 13, 2017 3 Comments

With MySQL version 5.7.11 and up, Oracle continues to improve MySQL’s security features by adding MySQL Enterprise Transparent Data Encryption (TDE) for InnoDB tables stored in innodb_file_per_table tablespaces. This feature provides at-rest encryption for physical tablespace data files.

MySQL Enterprise TDE uses a two-tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDB table is encrypted, a tablespace key is encrypted with the master key and the encrypted value of the tablespace key is stored in the tablespace header. When encrypting tablespace data, InnoDB transparently uses the master encryption key to decrypt the tablespace key and then uses it to encrypt (on write) and decrypt (on read).

The master encryption key is stored in memory on the MySQL keyring. Using a key vault, this key ring is both persisted and protected. The master key nor any of the tablespace keys are ever written to disk in plain text. When the the user first enables TDE, and when the server starts up or when the master key is rotated – the master key is requested and updated from the key vault. Databases and their tablespaces can get large, and had we chosen a single-tiered method, then key rotation would require re-encrypting entire tablespaces. This process is slow, costly and risky. With a two-tiered scheme, the decrypted version of a tablespace key never changes, but the master encryption key may be changed as required. This action is referred to as master-key rotation and it re-encrypts all of the tablespace keys in one atomic operation very rapidly (milliseconds).

There are various key vaults in the market and there’s also a common industry protocol supported by most vaults. The protocol is KMIP and it comes from OASIS. Losing keys (whether by accident, mismanagement, or getting hacked) means you lose your data. To ensure your keys are available, secure, auditable, etc., you will want to use a key-vault management software application such as Oracle Key Vault. Oracle Key Vault enables you to quickly deploy encryption and other security solutions by centrally managing encryption keys not only for MySQL, but across many things, including (but not limited to) Oracle Wallets, Java Keystores, and credential files.

Using the label for TDE requires:
1 – Applications aren’t aware of encryption – it is provided seamlessly and doesn’t require any changes
2 – The data is encrypted at-rest – so the operating system users or other access to the files on the media or the file system can’t read the data (without a key)
3 – The keys are secured, available and protected – you can’t leave the key in the door (or under the mat)
4 – The keys are never written to the filesystem (in plain text)

MySQL does include tablespace encryption with the community version but that is not TDE as it doesn’t meet requirements #3 and #4 – the keys aren’t necessarily secured.

MySQL Enterprise TDE supports the Advanced Encryption Standard (AES) block-based encryption algorithm. It uses Electronic Codebook (ECB) block encryption mode for tablespace key encryption and Cipher Block Chaining (CBC) block encryption mode for data encryption. (from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html)

In this post, I will show you how easy it is to use this new feature to encrypt your InnoDB tables (requires a licensed version of the MySQL Enterprise Edition MySQL 5.7.11 or higher).

Setting up MySQL TDE

Get Oracle Key Vault Credentials

First you will need to set up an account in Oracle Key Vault and create an endpoint (the key storage that keyring_okv plugin will talk to). Once you are done with that, you will have to create a configuration folder for keyring_okv. This folder will contain credentials to log into Oracle Key Vault. This, of course, needs to be stored securely. Go to this link to find information on how to accomplish this:
https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/keyring-okv-plugin.html
As the result you should have a configured endpoint in Oracle Key Vault and the configuration folder which should look like this:

# ls -l
total 8
-rw-rw-r-- 1 rob rob 427 lip 7 18:49 okvclient.ora
drwxrwxr-x 2 rob rob 4096 lip 7 18:49 ssl
# ls -l ssl
10:47 $ ls -l ssl
total 16
-rw-rw-r-- 1 rob rob 1200 lip 7 18:49 CA.pem
-rw-rw-r-- 1 rob rob 1209 lip 7 18:49 cert.pem
-rw-rw-r-- 1 rob rob 1027 lip 7 18:49 cert_req.pem
-rw-rw-r-- 1 rob rob 1675 lip 7 18:49 key.pem

From now on we will call this folder okv_enrollment.

Note: If you want to trial Oracle Key Vault it can be downloaded from the Oracle Software Delivery Cloud at https://edelivery.oracle.com/.

Install MySQL Server

Next you will need to install MySQL 5.7.11, and follow the post-installation steps, which include initializing the data directory.

Load keyring_okv plugin

To use the tablespace encryption feature, you will also need to load the keyring plugin (keyring_okv) by adding the following to your configuration file (under the [mysqld] section, let us assume that your path to the Oracle Key Vault folder is /home/user/okv_enrollment):

[mysqld]
early-plugin-load=keyring_okv.so
keyring_okv_conf_dir=/home/user/okv_enrollment

Before you attempt to create an encrypted table, you can check and make sure the plugin is active via this command:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE plugin_name='keyring_okv';
    
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_okv  | ACTIVE        |
+--------------+---------------+

Creating encrypted tables

To create a table with encryption, you only need to add ENCRYPTION = ‘Y’ to the CREATE TABLE statement. This example will create a new database and an encrypted table:

CREATE DATABASE scripting;
USE scripting;
CREATE TABLE `test_encryption` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `server_name` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y';

Technical side note (can be skipped :)
As this is the first encrypted table we are creating - InnoDB will first ask 
keyring_okv to generate master key in Oracle Key Vault. From now on this key will 
be used to encrypt tablespace keys. Next InnoDB will ask Oracle Key Vault to 
generate random key for encrypting table test_encryption. keyring_okv will use 
Oracle Key Vault to ensure that this key will get generated with high entropy. 
This random key will then get encrypted using the master key and stored alongside
the encrypted test_encryption table. The next time encrypted table gets created - 
only the tablespace key will get generated and the master key will get reused 
(to encrypt new tablespace key).

This encryption feature only works with InnoDB tables. If you try this with a MyISAM table, you will get an error:

CREATE TABLE `test_encryption2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `server_name` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y';
ERROR 1031 (HY000): Table storage engine for 'test_encryption2' doesn't have this option

You may view a list of all of the encrypted tables via this command:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
-> WHERE CREATE_OPTIONS like 'ENCRYPTION="Y"';
+--------------+-----------------+----------------+
| TABLE_SCHEMA | TABLE_NAME      | CREATE_OPTIONS |
+--------------+-----------------+----------------+
| scripting    | test_encryption | ENCRYPTION="Y" |
+--------------+-----------------+----------------+
1 row in set (0.20 sec)

Encrypting tables with different endpoints

It is possible to use different endpoints with one instance of MySQL. You can start server with (say) endpoint1 and encrypt table test_encryption with it. Then you can restart the server with some other endpoint and use it to encrypt table (for instance) test_more_encryption. However test_encryption will not be accessible. You will see the following error:

mysql> select * from test_encryption; ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded.

Also please note that, although keyring_okv_conf_dir is settable in runtime, it should be changed with care. Please see Appendix 1, at the bottom of this article, for more information.
Overall using multiple endpoints with one server is not encourage and should be done with extra care.

Encrypting existing tables

To enable encryption for an existing InnoDB table, specify the ENCRYPTION option in an ALTER TABLE statement.

mysql> ALTER TABLE t1 ENCRYPTION='Y';

To disable encryption for an InnoDB table, set ENCRYPTION=’N’ using ALTER TABLE.

mysql> ALTER TABLE t1 ENCRYPTION='N';

One warning – “Plan appropriately when altering an existing table with the ENCRYPTION option. ALTER TABLE … ENCRYPTION operations rebuild the table using ALGORITHM=COPY. ALGORITM=INPLACE is not supported.” (from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html)

In other words, it may take some time to encrypt (or decrypt) an already-existing InnoDB table.

Rotating InnoDB master key

For security reasons InnoDB master key should be rotated periodically. This operation creates a new master key in Oracle Key Vault, re-encrypts all the tablespace keys with the new master key and deactivates the old master key. In case when rotation gets interrupted by the server crash/restart it will be continued on the next server startup. To start innoDB key rotation use this command:

mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

For more information go this link (paragraph InnoDB Tablespace Encryption and Master Key Rotation):
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html

Moving encrypted tables between servers

You can also move a copy of an encrypted table to another server, following these steps. There are some limitations you will need to review.

“When exporting a tablespace that is encrypted using the InnoDB tablespace encryption feature, InnoDB generates a .cfp file in addition to a .cfg metadata file. The .cfp file must be copied to the destination server together with the .cfg file and tablespace file before performing the ALTER TABLE … IMPORT TABLESPACE operation on the destination server. The .cfp file contains a transfer key and an encrypted tablespace key. On import, InnoDB uses the transfer key to decrypt the tablespace key. For related information, see Section 14.4.10, InnoDB Tablespace Encryption.” (from: https://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html#innodb-transportable-tablespace-limitations

You now know how to use encryption for your InnoDB tables.

Troubleshooting

In case keyring_okv can not be accessed after restarting mysqld, you will see an error like this in the error log:

[ERROR] Plugin keyring_okv reported: 'Could not connect to the OKV server'
[ERROR] Plugin keyring_okv reported: 'keyring_okv initialization failure. Please check that the keyring_okv_conf_dir points to a readable directory and that the directory contains Oracle Key Vault configuration file and ssl materials. Please also check that Oracle Key Vault is up and running.

As error indicates it seems that Oracle Key Vault server is either down or there is something wrong with the credential directory. Make sure MySQL Server has privileges to access the configuration directory and that keyring_okv_conf_dir variable is set correctly (you can change it in runtime). Check also that structure of the configuration file is correct (see point Get Oracle Key Vault Credentials).

In case you forget to set keyring_okv_conf_dir variable during server startup or you install the plugin in runtime you will see the following error in the log file:

[Warning] Plugin keyring_okv reported: 'For keyring_okv to be initialized, please point keyring_okv_conf_dir variable to a directory with Oracle Key Vault configuration file and ssl materials

As you can see you have to set keyring_okv_conf_dir variable.

Appendix 1. Things to consider when changing keyring_okv_conf_dir in runtime

When server is loaded with keyring_okv – InnoDB fetches Master Key from Oracle Key Vault and uses it to decrypt each tablespace key – then it caches the decrypted tablespace keys. Thus the following code works:

CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB; 
INSERT INTO t1 VALUES(0, "aaaaa"); 
SELECT * FROM t1; 
# Changing endpoint 
SET @@global.keyring_okv_conf_dir= /home/user/okv_enrollment2';
# Table should accessible,keys in memory. 
SELECT * FROM t1;

Although, we have changed the endpoint, table t1 is accessible because its decrypted tablespace key was cached. This seems nice, but you have to be aware that changing endpoint in runtime does not implicitly mean blocking access to tables encrypted with the previously loaded endpoints. Also, we can get into troubles when endpoint we are changing to does not contain master key created by the server instance we are using. Then the following is not possible:

mysql> create table `test_encryption_4` (`id` int(10) unsigned) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y';
ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded.

This is because InnoDB tries to fetch Master Key from the endpoint and it fails. This is different from starting the server with keyring_okv – then, when InnoDB cannot find Master Key in the keyring it asks keyring to generate a new one.

Thanks to Robert Golebiowski for help with this blog.

Here are some various links regarding MySQL Enterprise TDE:

https://www.mysql.com/products/enterprise/tde.html
https://dev.mysql.com/doc/refman/5.7/en/faqs-tablespace-encryption.html
https://dev.mysql.com/doc/refman/5.7/en/keyring-okv-plugin.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
https://dev.mysql.com/doc/refman/5.7/en/keyring-installation.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_keyring_okv_conf_dir
http://www.mysql.com/news-and-events/web-seminars/mysql-security-transparent-data-encryption/

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.

Filed under MySQL, MySQL Enterprise Backup Tagged with Encryption at rest, mysql, MySQL Enterprise Edition, Transparent Data Encryption

Easy-to-use Perl scripts to backup your MySQL database with mysqldump and FTP the files to a remote server

January 6, 2017 1 Comment

Most users of MySQL utilize the mysqldump utility to backup their database. While mysqldump is handy and easy-to-use (and free), if you have data which is important to your business, then you should take a look at the MySQL Enterprise Edition – and use MySQL Enterprise Backup instead. The MySQL Enterprise Backup allows you to backup your database without the table locking you get with mysqldump. And, it is extremely fast – especially when you have to restore your database. Here is a sample speed comparison between MySQL Enterprise Backup and using mysqldump:

49x Better Performance: Backup

80x Better Performance: Backup

From the image, you can see it takes a long time to either dump or restore a fairly large (73 gigabyte) database compared to mysqldump. Even if your database isn’t this large (and most people don’t care how long their backups take to complete), when it comes time to restore your database in a production environment, the quicker you can restore your database, the better.

If your only option is to use mysqldump, here are two Perl scripts to make the task easier. The first script will backup your database(s) and send a copy of your backup to a remote server via FTP. The second script will connect to your FTP server and delete your old backup files – in case you have a storage limit on your FTP server. You can put these scripts in cron or Windows Task Scheduler, or run them manually. You can have the backup script run as often as possible (maybe once an hour) – but keep in mind there will be table-level locking. The script to delete the old backups only needs to be run once a day.

THE BACKUP SCRIPT

For the backup script, you will have to enter a few variables to match your system. You will also need to create a configuration file of all of the databases you want to backup. I could have connected to the MySQL database and ran a query (SHOW DATABASES;) to retrieve all of the databases, but I prefer to manually manage the list of databases to backup. With this method, you can skip an hourly backup of static or read-only databases and only backup the databases which are being changed. This configuration file is a text file with a list of the databases to be backed up, and you can use a # (pound sign) to comment out databases you want to skip.

NOTE:You don’t want to backup the following databases: PERFORMANCE_SCHEMA, INFORMATION_SCHEMA or SYS SCHEMA.

# set the directory where you will keep the backup files
$backup_folder = '/Users/tonydarnell/cron/mysqlbackups';

# the config file is a text file with a list of the databases to backup
# this should be in the same location as this script, but you can modify this
# if you want to put the file somewhere else
my $config_file = dirname($0) . "/mysql_backup.config";

# Here is where you will put your FTP server name (or IP address)
# and your FTP username and password
my $host = "server_name.com";
my $user = "username";
my $password = "password";

You can also modify your mysqldump command which will be use to backup your databases, or use what I have in the script. You will need to add your mysql password where I have the word “PassWord“.

`/usr/local/mysql/bin/mysqldump -R -h192.168.1.2 --events --triggers -u mysqlbackup --password=PassWord --routines --add-drop-database --set-gtid-purged=OFF --add-drop-table $database $table | compress > $folder/$file.Z`;

I created a separate FTP user which has its own home directory, so I don’t have to change the directory once I login via FTP. If you can’t do this, you will need to uncomment the last three lines of this and navigate your FTP user to the correct directory:

# uncomment the last three lines if you can't set the home directory of the FTP user to a specific directory
# the directory on the FTP server where you want to save the backup files
# my $dir = "mysqlbackups";
# print "Accessing FTP - changing to $dir folder\n";
# $f->cwd($dir) or die "Can't cwd to $dir\n";

That is all you need to modify in the first script for it to work. If you run this script on the command line, or if you run it in cron, you should see something like this:

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

It isn’t a good idea to have your password in plain text anywhere, so you should create a user which only has the limited read-only permissions needed to run mysqldump. You will need to change the value of “database_name” in the GRANT statement to match each database you want to backup. You will need to run the GRANT statement for every database you want to backup, or you can use an asterisk “*” in place of the database name.

CREATE USER 'mysqlbackup'@'192.168.1.2' IDENTIFIED WITH sha256_password BY '';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON `database_name`.* TO 'mysqlbackup'@'192.168.1.2';

Or, to grant permissions to the mysqlbackup user on all of the tables:

GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'mysqlbackup'@'192.168.1.2';

Here is a sample output from the script:

# perl mysql_backup.pl
Beginning 2017-01-06-16:35:57
Backing up database_01 - 2017-01-06-1635 ... mysqldump: [Warning] Using a password on the command line interface can be insecure.
Finished exporting database_01 - as 2017-01-06-1635/database_01.sql.Z
Backing up database_02 - 2017-01-06-1635 ... mysqldump: [Warning] Using a password on the command line interface can be insecure.
Finished exporting database_02 - as 2017-01-06-1635/database_02.sql.Z
Backing up database_03 - 2017-01-06-1635 ... mysqldump: [Warning] Using a password on the command line interface can be insecure.
Finished exporting database_03 - as 2017-01-06-1635/database_03.sql.Z
....
Backing up database_04 - 2017-01-06-1635 ... mysqldump: [Warning] Using a password on the command line interface can be insecure.
Finished exporting database_04 - as 2017-01-06-1635/database_04.sql.Z
------------------------------------------------------------------
 Compressing backup as:  2017-01-06-1635.tar.gz 
------------------------------------------------------------------
Creating a tar file from the backup...
tar -czf 2017-01-06-1635.tar.gz 2017-01-06-1635
FTP'ing the file - 2017-01-06-1635.tar.gz 
Deleting the original backup directory and files.
Finished 2017-01-06-16:36:49

The backup script creates a new directory with a timestamp as the name of the directory. In this example, the directory was named “2017-01-06-1635“. After the backup is finished, it creates a single tar file and then deletes the backup directory and files. I delete the backup directory and files as the backup is now contained in the single tar file. It is easier to copy and delete a single tar file via FTP than to do the same with a directory containing multiple files.

DELETING OLD BACKUPS

The script to delete the old backups is fairly simple to configure. You will need to enter the host, username and password of your FTP user, and specify how many backups you want to keep on the server. Since I do a backup once an hour, I keep a week’s worth (168 copies) of backups on my server. I could have checked the date/time on the files and deleted the older files which were X number of days old, but I decided to just go with a certain number of files to keep. I also included a “print_output” variable if you want to suppress any output – simply change this value to anything but “yes” and the script won’t print any output.

# Here is where you will put your FTP server name (or IP address)
# and your username and password
my $host = "server_name.com";
my $user = "username";
my $password = "password";

# how many copies of the backup do you want to keep?
$total_files_to_keep = 168;

$print_output = "yes";

Uncomment this line if you want to see a list of the other files which will not be deleted.

# optional output - remove # to have it print remaining files
# if ($print_output eq "yes") { print "| $count of $total_files_available |   Keeping: $filename\n"; }

Here is a sample output from running the script:

root# perl mysql_delete_backup.pl 
--------------------------------------------
           Total files: 194
    Total backup files: 192
   Total files to keep: 168
 Total files to delete: 24
----------------------------------------------
| x of 192 |  Skipping: .
| x of 192 |  Skipping: ..
| 1 of 192 |  Deleting: 2017-12-29-1352.tar.gz
| 2 of 192 |  Deleting: 2017-12-29-1452.tar.gz
| 3 of 192 |  Deleting: 2017-12-29-1552.tar.gz
| 4 of 192 |  Deleting: 2017-12-29-1652.tar.gz
....
| 24 of 192 |  Deleting: 2017-12-30-1252.tar.gz
----------------------------------------------
Finished 2017-01-06-15:21:58

When I run this script on my FTP server, and I do a listing of the backup directory, it shows the single-period (.) or current directory and the double-period (..) or the parent directory. I take this into account by skipping all files which do not have “20” in the name (as in the first two letters of the year).

If you login to your FTP server and you don’t see the “.” and “..“, then you will need to remove the “-2” in this part of the script, and simply have $total_files_available = $total_files:

# subtract two because of the . and ..
$total_files_available = $total_files - 2;

Here is what I see when I FTP to my server: (notice the “.” and “..“)

root# ftp backups@scripts.com
Trying 1234:f1c0:4738:5088:cb9a:dksi:ebfa:3829...
Connected to scripts.com.
220 FTP Server ready.
331 Password required for backups
Password: 
230 User backups logged in
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> dir
229 Entering Extended Passive Mode (|||58906|)
150 Opening ASCII mode data connection for file list
drwx---r-x   2 u63541528-backups ftpusers     4096 Jan  6 16:52 .
drwx---r-x   2 u63541528-backups ftpusers     4096 Jan  6 16:52 ..
-rw----r--   1 u63541528-backups ftpusers 45522630 Jan  5 22:52 2017-01-05-2252.tar.gz
-rw----r--   1 u63541528-backups ftpusers 45539118 Jan  5 23:52 2017-01-05-2352.tar.gz
-rw----r--   1 u63541528-backups ftpusers 45558328 Jan  6 01:52 2017-01-06-0152.tar.gz
-rw----r--   1 u63541528-backups ftpusers 45560794 Jan  6 07:52 2017-01-06-0752.tar.gz
....

The scripts are available on GitHub – https://github.com/ScriptingMySQL/PerlFiles. The scripts are named: mysql_backup.pl and mysql_delete_old_backups.pl.

I am not the best Perl programmer, so there may be a better/easier way to do this. Good luck with the scripts and let me know how they work for you. And follow me on Twitter at ScriptingMySQL.

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, MySQL Enterprise Backup, mysqldump, Perl, Scripting, Uncategorized

Upgrade MySQL to a new version with a fresh installation & use shell scripts and mysqldump to reload your data

August 1, 2014 1 Comment

There are several ways to upgrade MySQL. In this post, we will use a combination of shell scripts and the mysqldump application to export our MySQL data, and then re-import it back into the upgraded version of MySQL.

In this example, we will be doing a minor version upgrade. We will be going from 5.6.17 to 5.6.19. This method may not work if you are upgrading from one major release to another – from 5.1 to 5.5, or 5.5 to 5.6. You will want to check each version and review the new features/functions and also what features/functions have been deprecated. We are also assuming that no one will be using the database during the time it takes for us to do the upgrade.

If you want to upgrade from a version that is more than one major release apart from your current version, then you will want to upgrade to each successive version. For example, if you want to upgrade from 5.0 to 5.6, you will want to upgrade from 5.0 to 5.1, then 5.1 to 5.5, and then 5.5 to 5.6.

You don’t have to export all of your data when you upgrade MySQL. There are ways of upgrading without doing anything to your data. But in this post, I will be exporting the data and re-importing it, for a fresh installation. I don’t have that much data, so I don’t mind doing the export and import. If you have a lot of data, you might want to consider other options.

To get an idea of the size of your database(s), here is a quick script that you can use:

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB" 
FROM information_schema.TABLES GROUP BY table_schema ;

When I perform an export/import, I like to export each database as a separate mysqldump file, and then also export all of the databases together in one large file. By exporting/importing the individual databases, if you have an error importing one of the database dump files, you can isolate the error to a single database. It is much easier to fix the error in one smaller data dump file than with a larger all-inclusive dump file.

I am also going to create some simple shell scripts to help me create the commands that I need to make this task much easier. First, you will want to create a directory to store all of the scripts and dump files. Do all of your work inside that directory.

Next, I want to get a list of all of my databases. I will log into mysql, and then issue the show databases; command: (which is the same command as: select schema_name from information_schema.schemata;)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 12thmedia          |
| cbgc               |
| comicbookdb        |
| coupons            |
| healthcheck        |
| innodb_memcache    |
| landwatch          |
| laurelsprings      |
| ls_directory       |
| mem                |
| mysql              |
| performance_schema |
| protech            |
| scripts            |
| stacy              |
| storelist          |
| test               |
| testcert           |
| tony               |
| twtr               |
| watchdb            |
+--------------------+
22 rows in set (1.08 sec)

I can then just highlight and copy the list of databases, and put that list into a text file named “list.txt“. I do not want to include these databases in my export:

information_schema
mysql
performance_schema
test

However, I will export the mysql table later. You need to check with the MySQL manual to make sure that there haven’t been any changes to the MySQL table from one version to the next.

I will need to manually remove those databases from my list.txt file. I then want to remove all of the spaces and pipe symbols from the text file – assuming that you do not have any spaces in your database names. Instead of using spaces in a database name, I prefer to use an underline character “_“. These scripts assume that you don’t have any spaces in your database names.

If you know how to use the vi editor, you can so a substitution for the pipes and spaces with these commands:

:%s/ //g
:%s/|//g

Otherwise, you will want to use another text editor and manually edit the list to remove the spaces and pipe symbols. Your finished list.txt file should look like this:

12thmedia cbgc
comicbookdb
coupons
healthcheck
innodb_memcache
landwatch
laurelsprings
ls_directory
mem
protech
scripts
stacy
storelist
testcert
tony
twtr
watchdb

You can then create a simple shell script to help create your mysqldump commands – one command for each database. You will want to create this script and the other scripts in the directory you created earlier. Name the script export.sh. You can also change the mysqldump options to meet your needs. I am using GTID’s for replication, so I want to use this option –set-gtid-purged=OFF. You will also want to change the value of my password my_pass to your mysql password. You can also skip including the password by using the -p option, and just enter the password each time you run the mysqldump command.

# export.sh
# script to create the database export commands
k=""
for i in `cat list.txt`
do

echo "mysqldump -uroot --password=my_pass --set-gtid-purged=OFF --triggers --quick --skip-opt --add-drop-database --create-options --databases $i > "$i"_backup.sql"

k="$k $i"

done

# Optional - export the entire database
# use the file extention of .txt so that your script won't import it later
echo "mysqldump -uroot --password=my_pass --set-gtid-purged=OFF --triggers --quick --skip-opt --add-drop-database --create-options --databases $k > all_db_backup.txt"

For the individual databases, I am using the suffix of .sql. For the dump file that contains all of the databases, I am using the prefix .txt – as I use a wildcard search later to get a list of the dump files, and I don’t want to import the one dump file that contains all of the databases.

Now you can run the export.sh script to create a list of your mysqldump commands, and you are going to direct the output into another shell script named export_list.sh.

# sh export.sh > export_list.sh

We can now take a look at what is in the export_list.sh file

# cat export_list.sh
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases 12thmedia > 12thmedia_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases cbgc > cbgc_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases comicbookdb > comicbookdb_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases coupons > coupons_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases healthcheck > healthcheck_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases innodb_memcache > innodb_memcache_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases landwatch > landwatch_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases laurelsprings > laurelsprings_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases ls_directory > ls_directory_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases mem > mem_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases protech > protech_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases scripts > scripts_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases stacy > stacy_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases storelist > storelist_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases testcert > testcert_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases tony > tony_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases twtr > twtr_backup.sql
mysqldump -uroot --set-gtid-purged=OFF --password=my_pass --triggers --quick --skip-opt --add-drop-database --create-options --databases watchdb > watchdb_backup.sql

mysqldump -uroot -p --set-gtid-purged=OFF --password=my_psss --triggers --quick --skip-opt --add-drop-database --create-options --databases  12thmedia cbgc comicbookdb coupons healthcheck innodb_memcache landwatch laurelsprings ls_directory mem protech scripts stacy storelist testcert tony twtr watchdb > all_db_backup.txt

Now you have created a list of mysqldump commands that you can execute to dump all of your databases. You can now go ahead and execute your mysqldump commands by running the export_list.sh script:

# sh export_list.sh
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
....

The message “Warning: Using a password on the command line interface can be insecure.” is shown because you included the value for “–password“. If you don’t want to put your password on the command line, just change that option to “-p“, and you will have to manually enter your MySQL root user’s password after each mysqldump command.

Here is a list of the dump files that was produced:

# ls -l
total 21424
-rw-r--r--  1 root  staff    26690 Aug  1 16:25 12thmedia_backup.sql
-rw-r--r--  1 root  staff  5455275 Aug  1 16:26 all_db_backup.txt
-rw-r--r--  1 root  staff  1746820 Aug  1 16:25 cbgc_backup.sql
-rw-r--r--  1 root  staff   492943 Aug  1 16:25 comicbookdb_backup.sql
-rw-r--r--  1 root  staff     1057 Aug  1 16:25 coupons_backup.sql
-rw-r--r--  1 root  staff     3366 Aug  1 16:25 export_list.sh
-rw-r--r--  1 root  staff     1077 Aug  1 16:25 healthcheck_backup.sql
-rw-r--r--  1 root  staff     3429 Aug  1 16:25 innodb_memcache_backup.sql
-rw-r--r--  1 root  staff  1815839 Aug  1 16:25 landwatch_backup.sql
-rw-r--r--  1 root  staff   642965 Aug  1 16:25 laurelsprings_backup.sql
-rw-r--r--  1 root  staff   660254 Aug  1 16:25 ls_directory_backup.sql
-rw-r--r--  1 root  staff     1037 Aug  1 16:25 mem_backup.sql
-rw-r--r--  1 root  staff     1057 Aug  1 16:25 protech_backup.sql
-rw-r--r--  1 root  staff     2889 Aug  1 16:25 scripts_backup.sql
-rw-r--r--  1 root  staff    11107 Aug  1 16:25 stacy_backup.sql
-rw-r--r--  1 root  staff     4002 Aug  1 16:25 storelist_backup.sql
-rw-r--r--  1 root  staff     1062 Aug  1 16:25 testcert_backup.sql
-rw-r--r--  1 root  staff     4467 Aug  1 16:25 tony_backup.sql
-rw-r--r--  1 root  staff     1042 Aug  1 16:25 twtr_backup.sql
-rw-r--r--  1 root  staff    52209 Aug  1 16:25 watchdb_backup.sql

You will now want to dump your MySQL table, so you don’t have to recreate all of the MySQL information, including the users, passwords and privileges after the new install.

mysqldump -uroot --password=my_pass --set-gtid-purged=OFF mysql > mysql_user_backup.txt

I am once again using the .txt prefix for this file.

After you execute the above command, make sure that the dump file was created:

# ls -l mysql_user_backup.txt
-rw-r--r--  1 root  staff  9672 Aug  1 16:32 mysql_user_backup.txt

We have now finished exporting all of our data, including our MySQL table data. You will need to shutdown MySQL. You may use mysqladmin to shutdown your database, or here is a link on ways to shutdown MySQL.

# mysqladmin -uroot --password=my_pass shutdown
Warning: Using a password on the command line interface can be insecure.

Before continuing, you might want to check to make sure that the mysqld process isn’t still active.

# ps -ef|grep mysqld
    0 18380 17762   0   0:00.00 ttys002    0:00.00 grep mysqld

You are now going to want to change the name of your mysql directory. This will give you access to the old directory in case the upgrade fails. For my OS (Mac OS 10.9), my MySQL home directory is a symbolic link to another directory that contains the actual MySQL data. All I have to do is to remove the symbolic link. A new symbolic link will be created with the new install. Otherwise, just use the mv command to rename your old MySQL directory.

# cd /usr/local/
# ls -ld mysql* 
lrwxr-xr-x   1 root  wheel   36 Aug  9  2013 mysql -> mysql-advanced-5.6.17-osx10.6-x86_64
drwxr-xr-x  18 root  wheel  612 Jan 16  2014 mysql-advanced-5.6.17-osx10.6-x86_64

All I have to do is to remove the link, and the MySQL directory will still be there:

# rm mysql
# ls -ld mysql* 
drwxr-xr-x  18 root  wheel  612 Jan 16  2014 mysql-advanced-5.6.17-osx10.6-x86_64

Now I am ready to install the new version of MySQL. I won’t cover the installation process, but here is the link to the installation page.

Tip: After you have installed MySQL, don’t forget to run this script from your MySQL home directory. This will install your mysql database tables. Otherwise, you will get an error when you try to start the mysqld process.

# ./scripts/mysql_install_db

Now you can start the mysqld process. See this page if you don’t know how to start MySQL.

You can test to see if the new installation of MySQL is running by either checking the process table, or logging into mysql. With a fresh install of 5.6, you should not have to include a user name or password.

Note: (Future versions of MySQL may automatically create a random root password and put it in your data directory. You will then need to use that password to login to MySQL for the first time. Check the user’s manual for any MySQL versions beyond 5.6.)

# mysql
Welcome to the mysql monitor.  Commands end with ; or \g.
Your mysql connection id is 3
....

mysql>

Now that MySQL is up and running, leave the mysql terminal window open, and open another terminal window so you can import your mysql table information from your dump file:

# mysql < /users/tonydarnell/mysql_2014_0731/2014_0731_mysql_backup.sql

You won't be able to login with your old user names and passwords until you execute the flush privileges command. So, in your other terminal window with the mysql prompt:

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Open another terminal window and see if you can login with your old mysql user name and password:

# mysql -uroot -p
Enter password: 
Welcome to the mysql monitor.  Commands end with ; or \g.
Your mysql connection id is 3
....

mysql>

You can then look at your the user names and passwords in the mysql.user table:

mysql> select user, host, password from mysql.user order by user, host;
+----------------+---------------+-------------------------------------------+
| user           | host          | password                                  |
+----------------+---------------+-------------------------------------------+
| root           | 127.0.0.1     | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root           | 192.168.1.2   | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root           | 192.168.1.5   | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root           | 192.168.1.50  | *BF6F71512345332CAB67E7608EBE63005BEB705C |
| root           | localhost     | *BF6F71512345332CAB67E7608EBE63005BEB705C |
+----------------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)

OPTIONAL:
Since I am using GTID’s for replication, I can check to see how many transactions have been completed, by issuing the show master status command:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 644455
     Binlog_Do_DB: 
 Binlog_Ignore_DB: coupons,usta,ls_directory,landwatch
Executed_Gtid_Set: e1eb3f38-18da-11e4-aa44-0a1a64a61679:1-124
1 row in set (0.00 sec)

We are now ready to import the database dump files. We can use this script to create the import commands. Copy this into a text file named import.sh:

# import.sh
# script to import all of the export files
# run this script in the same directory as the exported dump files
#
> import_files.sh
directory=`pwd`
for file in `ls *sql`
do

if [[ $(grep -c '.txt' $file) != 0 ]];then

echo "# found mysql - do nothing"

else

echo "mysql -uroot -p"my_pass"  < $directory/$file"
echo "mysql -uroot -p"my_pass"  > import_files.sh

fi

done

Then run the import.sh script. The script will print the output to the terminal window as well as into a new script file named import_files.sh.

# sh import.sh
mysql -uroot -pmy_pass < 12thmedia_backup.sql
mysql -uroot -pmy_pass < cbgc_backup.sql
mysql -uroot -pmy_pass < comicbookdb_backup.sql
mysql -uroot -pmy_pass < coupons_backup.sql
mysql -uroot -pmy_pass < healthcheck_backup.sql
mysql -uroot -pmy_pass < innodb_memcache_backup.sql
mysql -uroot -pmy_pass < landwatch_backup.sql
mysql -uroot -pmy_pass < laurelsprings_backup.sql
mysql -uroot -pmy_pass < ls_directory_backup.sql
mysql -uroot -pmy_pass < mem_backup.sql
mysql -uroot -pmy_pass < protech_backup.sql
mysql -uroot -pmy_pass < scripts_backup.sql
mysql -uroot -pmy_pass < stacy_backup.sql
mysql -uroot -pmy_pass < storelist_backup.sql
mysql -uroot -pmy_pass < testcert_backup.sql
mysql -uroot -pmy_pass < tony_backup.sql
mysql -uroot -pmy_pass < twtr_backup.sql
mysql -uroot -pmy_pass < watchdb_backup.sql

Look at the contents of the new script file – import_files.sh – to make sure that it contains all of the database files. You will use this file to help you import your dump files.

# cat import_files.sh
mysql -uroot -pmy_pass < 12thmedia_backup.sql
mysql -uroot -pmy_pass < cbgc_backup.sql
mysql -uroot -pmy_pass < comicbookdb_backup.sql
mysql -uroot -pmy_pass < coupons_backup.sql
mysql -uroot -pmy_pass < healthcheck_backup.sql
mysql -uroot -pmy_pass < innodb_memcache_backup.sql
mysql -uroot -pmy_pass < landwatch_backup.sql
mysql -uroot -pmy_pass < laurelsprings_backup.sql
mysql -uroot -pmy_pass < ls_directory_backup.sql
mysql -uroot -pmy_pass < mem_backup.sql
mysql -uroot -pmy_pass < protech_backup.sql
mysql -uroot -pmy_pass < scripts_backup.sql
mysql -uroot -pmy_pass < stacy_backup.sql
mysql -uroot -pmy_pass < storelist_backup.sql
mysql -uroot -pmy_pass < testcert_backup.sql
mysql -uroot -pmy_pass < tony_backup.sql
mysql -uroot -pmy_pass < twtr_backup.sql
mysql -uroot -pmy_pass < watchdb_backup.sql

WARNING: Be sure that this script file does not contain the main dump file or the mysql user’s file that we created.

I was exporting and importing eighteen (18) database files, so I can also check the line count of the import_files.sh script to make sure it matches:

# wc -l import_files.sh
      18 import_files.sh

I am now ready to import my files.

Optional: add the -v for verbose mode – sh -v import_files.sh

# sh import_files.sh
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
....

You databases should now be imported into your new instance of MySQL. You can always re-run the script to make sure that the databases are the same size.

OPTIONAL:
Since I am using GTID’s for replication, I can check to see how many transactions have been completed after importing the dump files, by issuing the show master status command:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 16884001
     Binlog_Do_DB: 
 Binlog_Ignore_DB: coupons,usta,ls_directory,landwatch
Executed_Gtid_Set: cc68d008-18f3-11e4-aae6-470d6cf89709:1-43160
1 row in set (0.00 sec)

Your new and fresh installation of MySQL should be ready to use.

NOTE:A thank-you to Daniel Van Eeden for pointing out a mistake that I had made.

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, MySQL Enterprise Backup, MySQL Replication, mysqldump, Scripting, Uncategorized Tagged with mysql, MySQL Enterprise Backup, MySQL Enterprise Edition, mysqldump

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

April 22, 2014 11 Comments

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 is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, MySQL Enterprise Backup, MySQL Enterprise Edition, mysqldump, Scripting, Uncategorized

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

July 25, 2013 3 Comments

In part one of this post, I gave you a couple examples of how to backup your MySQL databases using mysqldump. In part two, I will show you how to use the MySQL Enterprise Backup (MEB) to create a full and partial backup.

MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris. To learn more, you may download a whitepaper on MEB.

MySQL Enterprise Backup delivers:

NEW! Continuous monitoring – Monitor the progress and disk space usage
“Hot” Online Backups – Backups take place entirely online, without interrupting MySQL transactions
High Performance – Save time with faster backup and recovery
Incremental Backup – Backup only data that has changed since the last backup
Partial Backup – Target particular tables or tablespaces
Compression – Cut costs by reducing storage requirements up to 90%
Backup to Tape – Stream backup to tape or other media management solutions
Fast Recovery – Get servers back online and create replicated servers
Point-in-Time Recovery (PITR) – Recover to a specific transaction
Partial restore – Recover targeted tables or tablespaces
Restore to a separate location – Rapidly create clones for fast replication setup
Reduce Failures – Use a proven high quality solution from the developers of MySQL

Multi-platform – Backup and Restore on Linux, Windows, Mac & Solaris(from http://www.mysql.com/products/enterprise/backup.html

While mysqldump is free to use, MEB is part of MySQL’s Enterprise Edition (EE) – so you need a license to use it. But if you are using MySQL in a production environment, you might want to look at EE, as:

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.
(from: http://www.mysql.com/products/enterprise/)

Before using MEB and backing up your database for the first time, you will need some information:

Information to gather – Where to Find It – How It Is Used

Path to MySQL configuration file – Default system locations, hardcoded application default locations, or from –defaults-file option in mysqld startup script. – This is the preferred way to convey database configuration information to the mysqlbackup command, using the –defaults-file option. When connection and data layout information is available from the configuration file, you can skip most of the other choices listed below.
MySQL port – MySQL configuration file or mysqld startup script. Used to connect to the database instance during backup operations. Specified via the –port option of mysqlbackup. –port is not needed if available from MySQL configuration file. Not needed when doing an offline (cold) backup, which works directly on the files using OS-level file permissions.
Path to MySQL data directory – MySQL configuration file or mysqld startup script. – Used to retrieve files from the database instance during backup operations, and to copy files back to the database instance during restore operations. Automatically retrieved from database connection for hot and warm backups. Taken from MySQL configuration file for cold backups.
ID and password of privileged MySQL user – You record this during installation of your own databases, or get it from the DBA when backing up databases you do not own. Not needed when doing an offline (cold) backup, which works directly on the files using OS-level file permissions. For cold backups, you log in as an administrative user. – Specified via the –password option of the mysqlbackup. Prompted from the terminal if the –password option is present without the password argument.
Path under which to store backup data – You choose this. See Section 3.1.3, of the MySQL online manual – “Designate a Location for Backup Data” for details. – By default, this directory must be empty for mysqlbackup to write data into it, to avoid overwriting old backups or mixing up data from different backups. Use the –with-timestamp option to automatically create a subdirectory with a unique name, when storing multiple sets of backup data under the same main directory.
Owner and permission information for backed-up files (for Linux, Unix, and OS X systems) – In the MySQL data directory. – If you do the backup using a different OS user ID or a different umask setting than applies to the original files, you might need to run commands such as chown and chmod on the backup data. See Section A.1, of the MySQL online manual – “Limitations of mysqlbackup Command” for details.
Size of InnoDB redo log files – Calculated from the values of the innodb_log_file_size and innodb_log_files_in_group configuration variables. Use the technique explained for the –incremental-with-redo-log-only option. – Only needed if you perform incremental backups using the –incremental-with-redo-log-only option rather than the –incremental option. The size of the InnoDB redo log and the rate of generation for redo data dictate how often you must perform incremental backups.

Rate at which redo data is generated – Calculated from the values of the InnoDB logical sequence number at different points in time. Use the technique explained for the –incremental-with-redo-log-only option. – Only needed if you perform incremental backups using the –incremental-with-redo-log-only option rather than the –incremental option. The size of the InnoDB redo log and the rate of generation for redo data dictate how often you must perform incremental backups.For most backup operations, the mysqlbackup command connects to the MySQL server through –user and –password options. If you aren’t going to use the root user, then you will need to create a separate user. Follow these instructions for setting the proper permissions.

All backup-related operations either create new files or reference existing files underneath a specified directory that holds backup data. Choose this directory in advance, on a file system with sufficient storage. (It could even be remotely mounted from a different server.) You specify the path to this directory with the –backup-dir option for many invocations of the mysqlbackup command.

Once you establish a regular backup schedule with automated jobs, it is preferable to keep each backup within a timestamped subdirectory underneath the main backup directory. To make the mysqlbackup command create these subdirectories automatically, specify the –with-timestamp option each time you run mysqlbackup.

For one-time backup operations, for example when cloning a database to set up a replication slave, you might specify a new directory each time, or specify the –force option of mysqlbackup to overwrite older backup files.

If you haven’t downloaded and installed mysqlbackup, you may download it from edelivery.oracle.com (registration is required). Install the MySQL Enterprise Backup product on each database server whose contents you intend to back up. You perform all backup and restore operations locally, by running the mysqlbackup command on the same server as the MySQL instance.

Now that we have gathered all of the required information and installed mysqlbackup, let’s run a simple and easy backup of the entire database. I installed MEB in my /usr/local directory, so I am including the full path of mysqlbackup. I am using the backup-and-apply-log option, which combines the –backup and the –apply-log options into one. The –backup option performs the initial phase of a backup. The second phase is performed later by running mysqlbackup again with the –apply-log option, which brings the InnoDB tables in the backup up-to-date, including any changes made to the data while the backup was running.

$ /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log
MySQL Enterprise Backup version 3.12.0 [2014/11/12] 
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 /usr/local/meb/bin/mysqlbackup --user=root --password 
        --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log 

Enter password: 
 mysqlbackup: INFO: MySQL server version is '5.6.9-rc-log'.
 mysqlbackup: INFO: Got some server configuration information from running server.

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'backup-and-apply-log' run mysqlbackup
           prints "mysqlbackup completed OK!".

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = /usr/local/mysql/data/
  innodb_data_home_dir = /usr/local/mysql/data
  innodb_data_file_path = ibdata1:40M:autoextend
  innodb_log_group_home_dir = /usr/local/mysql/data
  innodb_log_files_in_group = 2
  innodb_log_file_size = 5242880
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb
  innodb_undo_directory = /usr/local/mysql/data/
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /Users/tonydarnell/hotbackups/datadir
  innodb_data_home_dir = /Users/tonydarnell/hotbackups/datadir
  innodb_data_file_path = ibdata1:40M:autoextend
  innodb_log_group_home_dir = /Users/tonydarnell/hotbackups/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 5242880
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb
  innodb_undo_directory = /Users/tonydarnell/hotbackups/datadir
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

 mysqlbackup: INFO: Unique generated backup id for this is 13742482113579320

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
130719 11:36:53 mysqlbackup: INFO: Full Backup operation starts with following threads
		1 read-threads    6 process-threads    1 write-threads
130719 11:36:53 mysqlbackup: INFO: System tablespace file format is Antelope.
130719 11:36:53 mysqlbackup: INFO: Starting to copy all innodb files...
130719 11:36:53 mysqlbackup: INFO: Copying /usr/local/mysql/data/ibdata1 (Antelope file format).
130719 11:36:53 mysqlbackup: INFO: Found checkpoint at lsn 135380756.
130719 11:36:53 mysqlbackup: INFO: Starting log scan from lsn 135380480.
130719 11:36:53 mysqlbackup: INFO: Copying log...
130719 11:36:54 mysqlbackup: INFO: Log copied, lsn 135380756.

(I have truncated some of the database and table output to save space)
.....
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_index_stats.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/innodb_table_stats.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_master_info.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_relay_log_info.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/mysql/slave_worker_info.ibd (Antelope file format).
.....
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/testcert/t1.ibd (Antelope file format).
130719 11:36:56 mysqlbackup: INFO: Copying /usr/local/mysql/data/testcert/t3.ibd (Antelope file format).
.....
130719 11:36:57 mysqlbackup: INFO: Copying /usr/local/mysql/data/watchdb/watches.ibd (Antelope file format).
.....
130719 11:36:57 mysqlbackup: INFO: Completing the copy of innodb files.
130719 11:36:58 mysqlbackup: INFO: Preparing to lock tables: Connected to mysqld server.
130719 11:36:58 mysqlbackup: INFO: Starting to lock all the tables...
130719 11:36:58 mysqlbackup: INFO: All tables are locked and flushed to disk
130719 11:36:58 mysqlbackup: INFO: Opening backup source directory '/usr/local/mysql/data/'
130719 11:36:58 mysqlbackup: INFO: Starting to backup all non-innodb files in 
	subdirectories of '/usr/local/mysql/data/'
.....
130719 11:36:58 mysqlbackup: INFO: Copying the database directory 'comicbookdb'
.....
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'mysql'
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'performance_schema'
.....
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'test'
.....
130719 11:36:59 mysqlbackup: INFO: Copying the database directory 'watchdb'
130719 11:36:59 mysqlbackup: INFO: Completing the copy of all non-innodb files.
130719 11:37:00 mysqlbackup: INFO: A copied database page was modified at 135380756.
          (This is the highest lsn found on page)
          Scanned log up to lsn 135384397.
          Was able to parse the log up to lsn 135384397.
          Maximum page number for a log record 375
130719 11:37:00 mysqlbackup: INFO: All tables unlocked
130719 11:37:00 mysqlbackup: INFO: All MySQL tables were locked for 1.589 seconds.
130719 11:37:00 mysqlbackup: INFO: Full Backup operation completed successfully.
130719 11:37:00 mysqlbackup: INFO: Backup created in directory '/Users/tonydarnell/hotbackups'
130719 11:37:00 mysqlbackup: INFO: MySQL binlog position: filename mysql-bin.000013, position 85573

-------------------------------------------------------------
   Parameters Summary         
-------------------------------------------------------------
   Start LSN                  : 135380480
   End LSN                    : 135384397
-------------------------------------------------------------

 mysqlbackup: INFO: Creating 14 buffers each of size 65536.
130719 11:37:00 mysqlbackup: INFO: Apply-log operation starts with following threads
		1 read-threads    1 process-threads
130719 11:37:00 mysqlbackup: INFO: ibbackup_logfile's creation parameters:
          start lsn 135380480, end lsn 135384397,
          start checkpoint 135380756.
 mysqlbackup: INFO: InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 .... 99 Setting log file size to 5242880
Setting log file size to 5242880
130719 11:37:00 mysqlbackup: INFO: We were able to parse ibbackup_logfile up to
          lsn 135384397.
 mysqlbackup: INFO: Last MySQL binlog file position 0 85573, file name mysql-bin.000013
130719 11:37:00 mysqlbackup: INFO: The first data file is '/Users/tonydarnell/hotbackups/datadir/ibdata1'
          and the new created log files are at '/Users/tonydarnell/hotbackups/datadir'
130719 11:37:01 mysqlbackup: INFO: Apply-log operation completed successfully.
130719 11:37:01 mysqlbackup: INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!

Now, I can take a look at the backup file that was created:

root@macserver01: $ pwd
/Users/tonydarnell/hotbackups
root@macserver01: $ ls -l
total 8
-rw-r--r--   1 root  staff  351 Jul 19 11:36 backup-my.cnf
drwx------  21 root  staff  714 Jul 19 11:37 datadir
drwx------   6 root  staff  204 Jul 19 11:37 meta
$ ls -l datadir
total 102416
drwx------   5 root  staff       170 Jul 19 11:36 comicbookdb
-rw-r-----   1 root  staff   5242880 Jul 19 11:37 ib_logfile0
-rw-r-----   1 root  staff   5242880 Jul 19 11:37 ib_logfile1
-rw-r--r--   1 root  staff      4608 Jul 19 11:37 ibbackup_logfile
-rw-r--r--   1 root  staff  41943040 Jul 19 11:37 ibdata1
drwx------  88 root  staff      2992 Jul 19 11:36 mysql
drwx------  55 root  staff      1870 Jul 19 11:36 performance_schema
drwx------   3 root  staff       102 Jul 19 11:36 test
drwx------  30 root  staff      1020 Jul 19 11:36 testcert
drwx------  19 root  staff       646 Jul 19 11:36 watchdb

root@macserver01: $ ls -l meta
total 216
-rw-r--r--  1 root  staff  90786 Jul 19 11:37 backup_content.xml
-rw-r--r--  1 root  staff   5746 Jul 19 11:36 backup_create.xml
-rw-r--r--  1 root  staff    265 Jul 19 11:37 backup_gtid_executed.sql
-rw-r--r--  1 root  staff    321 Jul 19 11:37 backup_variables.txt

As you can see, the backup was created in /Users/tonydarnell/hotbackups. If I wanted to have a unique folder for this backup, I can use the –with-timestamp.

The –with-timestamp option places the backup in a subdirectory created under the directory you specified above. The name of the backup subdirectory is formed from the date and the clock time of the backup run.

I will run the same backup command again, but with the –with-timestamp option:

(I am not going to duplicate the entire output – but I will only show you the output where it creates the sub-directory under /Users/tonydarnell/hotbackups)

$ /usr/local/meb/bin/mysqlbackup --user=root --password --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log --with-timestamp
......
130719 11:49:54 mysqlbackup: INFO: The first data file is '/Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/ibdata1'
          and the new created log files are at '/Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir'
130719 11:49:54 mysqlbackup: INFO: Apply-log operation completed successfully.
130719 11:49:54 mysqlbackup: INFO: Full backup prepared for recovery successfully.

mysqlbackup completed OK!

So, I ran the backup again to get a unique directory. Instead of the backup files/directories being placed in /Users/tonydarnell/hotbackups, it created a sub-directory with a timestamp for the directory name:

$ pwd
/Users/tonydarnell/hotbackups
root@macserver01: $ ls -l
total 0
drwx------  5 root  staff  170 Jul 19 11:49 2015-05-19_11-49-48
$ ls -l 2015-05-19_11-49-48
total 8
-rw-r--r--   1 root  staff  371 Jul 19 11:49 backup-my.cnf
drwx------  21 root  staff  714 Jul 19 11:49 datadir
drwx------   6 root  staff  204 Jul 19 11:49 meta

Note: If you don’t use the backup-and-apply-log option you will need to read this: Immediately after the backup job completes, the backup files might not be in a consistent state, because data could be inserted, updated, or deleted while the backup is running. These initial backup files are known as the raw backup.

You must update the backup files so that they reflect the state of the database corresponding to a specific InnoDB log sequence number. (The same kind of operation as crash recovery.) When this step is complete, these final files are known as the prepared backup.

During the backup, mysqlbackup copies the accumulated InnoDB log to a file called ibbackup_logfile. This log file is used to “roll forward” the backed-up data files, so that every page in the data files corresponds to the same log sequence number of the InnoDB log. This phase also creates new ib_logfiles that correspond to the data files.

The mysqlbackup option for turning a raw backup into a prepared backup is apply-log. You can run this step on the same database server where you did the backup, or transfer the raw backup files to a different system first, to limit the CPU and storage overhead on the database server.

Note: Since the apply-log operation does not modify any of the original files in the backup, nothing is lost if the operation fails for some reason (for example, insufficient disk space). After fixing the problem, you can safely retry apply-log and by specifying the –force option, which allows the data and log files created by the failed apply-log operation to be overwritten.

For simple backups (without compression or incremental backup), you can combine the initial backup and the apply-log step using the option backup-and-apply-log.

MEB 3.9 and later creates two .cnf files based on the output of SHOW GLOBAL VARIABLES: server-my.cnf (non-default values) and server-all.cnf (all values).

Now that we have a completed backup, we are going to copy the backup files and the my.cnf file over to a different server to restore the databases. We will be using a server that was setup as a slave server to the server where the backup occurred. If you need to restore the backup to the same server, you will need to refer to the mysqlbackup manual. I copied the backup files as well as the my.cnf file to the new server:

# pwd
/Users/tonydarnell/hotbackups
# ls -l
total 16
drwxrwxrwx  5 tonydarnell  staff   170 Jul 19 15:38 2015-05-19_11-49-48

On the new server (where I will restore the data), I shutdown the mysqld process (mysqladmin -uroot -p shutdown), copied the my.cnf file to the proper directory, and now I can restore the database to the new server, using the copy-back option. The copy-back option requires the database server to be already shut down, then copies the data files, logs, and other backed-up files from the backup directory back to their original locations, and performs any required postprocessing on them.

# /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/Users/tonydarnell/hotbackups/2015-05-19_11-49-48 copy-back
MySQL Enterprise Backup version 3.12.0 [2014/11/12] 
Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.

 mysqlbackup: INFO: Starting with following command line ...
 /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf 
        --backup-dir=/Users/tonydarnell/hotbackups/2015-05-19_11-49-48 
        copy-back 

IMPORTANT: Please check that mysqlbackup run completes successfully.
           At the end of a successful 'copy-back' run mysqlbackup
           prints "mysqlbackup completed OK!".

--------------------------------------------------------------------
                       Server Repository Options:
--------------------------------------------------------------------
  datadir = /usr/local/mysql/data
  innodb_data_home_dir = /usr/local/mysql/data
  innodb_data_file_path = ibdata1:40M:autoextend
  innodb_log_group_home_dir = /usr/local/mysql/data
  innodb_log_files_in_group = 2
  innodb_log_file_size = 5M
  innodb_page_size = Null
  innodb_checksum_algorithm = innodb

--------------------------------------------------------------------
                       Backup Config Options:
--------------------------------------------------------------------
  datadir = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir
  innodb_data_home_dir = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir
  innodb_data_file_path = ibdata1:40M:autoextend
  innodb_log_group_home_dir = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir
  innodb_log_files_in_group = 2
  innodb_log_file_size = 5242880
  innodb_page_size = 16384
  innodb_checksum_algorithm = innodb
  innodb_undo_directory = /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir
  innodb_undo_tablespaces = 0
  innodb_undo_logs = 128

 mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
130719 15:54:41 mysqlbackup: INFO: Copy-back operation starts with following threads
		1 read-threads    1 write-threads
130719 15:54:41 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/ibdata1.
.....
130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/comicbookdb/comics.ibd.
.....
130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/innodb_index_stats.ibd.
130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/innodb_table_stats.ibd.
130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/slave_master_info.ibd.
130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/slave_relay_log_info.ibd.
130719 15:54:42 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/mysql/slave_worker_info.ibd.
.....
130719 15:54:43 mysqlbackup: INFO: Copying /Users/tonydarnell/hotbackups/2015-05-19_11-49-48/datadir/watchdb/watches.ibd.
.....
130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'comicbookdb'
.....
130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'mysql'
130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'performance_schema'
.....
130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'test'
.....
130719 15:54:43 mysqlbackup: INFO: Copying the database directory 'watchdb'
130719 15:54:43 mysqlbackup: INFO: Completing the copy of all non-innodb files.
130719 15:54:43 mysqlbackup: INFO: Copying the log file 'ib_logfile0'
130719 15:54:43 mysqlbackup: INFO: Copying the log file 'ib_logfile1'
130719 15:54:44 mysqlbackup: INFO: Copy-back operation completed successfully.
130719 15:54:44 mysqlbackup: INFO: Finished copying backup files to '/usr/local/mysql/data'

mysqlbackup completed OK!

I can now restart MySQL. I have a very small database (less than 50 megabytes). But it took less than a minute to restore the database. If I had to rebuild my database using mysqldump, it would take a lot longer. If you have a very large database, the different in using mysqlbackup and mysqldump could be in hours. For example, a 32-gig database with 33 tables takes about eight minutes to restore with mysqlbackup. Restoring the same database with a mysqldump file takes over two hours.

An easy way to check to see if the databases match (assuming that I haven’t added any new records in any of the original databases – which I haven’t), I can use one of the MySQL Utilities – mysqldbcompare. I wrote about how to do this in an earlier blog about using it to test two replicated databases, but it will work here as well – see Using MySQL Utilities Workbench Script mysqldbcompare To Compare Two Databases In Replication.

The mysqldbcompare utility “compares the objects and data from two databases to find differences. It identifies objects having different definitions in the two databases and presents them in a diff-style format of choice. Differences in the data are shown using a similar diff-style format. Changed or missing rows are shown in a standard format of GRID, CSV, TAB, or VERTICAL.” (from: mysqldbcompare — Compare Two Databases and Identify Differences)

Some of the syntax may have changed for mysqldbcompare since I wrote that blog, so you will need to reference the help notes for mysqldbcompare. You would need to run this for each of your databases.

$ mysqldbcompare --server1=scripts:scripts999@192.168.1.2   --server2=scripts:scripts999@192.168.1.123 --run-all-tests --difftype=context comicbookdb:comicbookdb
# server1 on 192.168.1.2: ... connected.
# server2 on 192.168.1.123: ... connected.
# Checking databases comicbookdb on server1 and comicbookdb on server2

                                                    Defn    Row     Data   
Type      Object Name                               Diff    Count   Check  
--------------------------------------------------------------------------- 
TABLE     comics                                    pass    pass    pass   

Databases are consistent.

# ...done

You can try and run this for the mysql database, but you may get a few errors regarding the mysql.backup_history and mysql.backup_progress tables:

$ mysqldbcompare --server1=scripts:scripts999@192.168.1.2   --server2=scripts:scripts999@192.168.1.123 --run-all-tests --difftype=context mysql:mysql
# server1 on 192.168.1.2: ... connected.
# server2 on 192.168.1.123: ... connected.
# Checking databases mysql on server1 and mysql on server2

                                                    Defn    Row     Data   
Type      Object Name                               Diff    Count   Check  
--------------------------------------------------------------------------- 
TABLE     backup_history                            pass    FAIL    SKIP    

Row counts are not the same among mysql.backup_history and mysql.backup_history.

No primary key found.

TABLE     backup_progress                           pass    FAIL    SKIP    

Row counts are not the same among mysql.backup_progress and mysql.backup_progress.

No primary key found.

TABLE     columns_priv                              pass    pass    pass    
TABLE     db                                        pass    pass    pass    
TABLE     event                                     pass    pass    pass    
TABLE     func                                      pass    pass    pass    
TABLE     general_log                               pass    pass    SKIP    

No primary key found.

TABLE     help_category                             pass    pass    pass    
TABLE     help_keyword                              pass    pass    pass    
TABLE     help_relation                             pass    pass    pass    
TABLE     help_topic                                pass    pass    pass    
TABLE     innodb_index_stats                        pass    pass    pass    
TABLE     innodb_table_stats                        pass    pass    pass    
TABLE     inventory                                 pass    pass    pass    
TABLE     ndb_binlog_index                          pass    pass    pass    
TABLE     plugin                                    pass    pass    pass    
TABLE     proc                                      pass    pass    pass    
TABLE     procs_priv                                pass    pass    pass    
TABLE     proxies_priv                              pass    pass    pass    
TABLE     servers                                   pass    pass    pass    
TABLE     slave_master_info                         pass    pass    pass    
TABLE     slave_relay_log_info                      pass    pass    pass    
TABLE     slave_worker_info                         pass    pass    pass    
TABLE     slow_log                                  pass    pass    SKIP    

No primary key found.

TABLE     tables_priv                               pass    pass    pass    
TABLE     time_zone                                 pass    pass    pass    
TABLE     time_zone_leap_second                     pass    pass    pass    
TABLE     time_zone_name                            pass    pass    pass    
TABLE     time_zone_transition                      pass    pass    pass    
TABLE     time_zone_transition_type                 pass    pass    pass    
TABLE     user                                      pass    pass    pass   

Database consistency check failed.

# ...done

For example, when you compare the mysql.backup_history tables, the original database will have two entries – as I ran mysqlbackup twice. But the second backup entry doesn’t get entered until after the backup has occurred, and it isn’t reflected in the backup files.

Original Server

mysql> select count(*) from mysql.backup_history;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

Restored Server

mysql> select count(*) from mysql.backup_history;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

For the mysql.backup_progress tables, the original database has ten rows, while the restored database has seven.

There are many options for using mysqlbackup, including (but not limited to) incremental backup, partial backup , compression, backup to tape, point-in-time recovery (PITR), partial restore, etc. If you are running MySQL in a production environment, then you should look at MySQL Enterprise Edition, which includes MySQL Enterprise Backup. Of course, you should always have a backup and recovery plan in place. Finally, if and when possible, practice restoring your backup on a regular basis, to make sure that if your server crashes, you can restore your database quickly.

Tony is the author of Twenty Forty-Four: The League of Patriots

Visit http://2044thebook.com for more information.

Filed under MySQL, MySQL Enterprise Backup, MySQL Enterprise Edition Tagged with MySQL Enterprise Backup, MySQL Enterprise Edition

Follow Tony on Twitter
Follow @scriptingmysql
Find me on LinkedIn:
info [at] ScriptingMySQL.com

	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.

Scripting MySQL

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

You say you want a Replication?

What is replication and how does it work?

Global Transaction Identifiers

Group Replication

Replicating from a member in Group Replication

Configuring the replica (slave) server

Configuring the source (master) server

Starting the replica (slave) server

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

Restoring a slave using mysqldump

Other posts on restoring backups to servers

MySQL Enterprise Transparent Data Encryption (TDE) – provides at-rest encryption for physical InnoDB tablespace data files

Setting up MySQL TDE

Get Oracle Key Vault Credentials

Install MySQL Server

Load keyring_okv plugin

Creating encrypted tables

Encrypting tables with different endpoints

Encrypting existing tables

Rotating InnoDB master key

Moving encrypted tables between servers

Troubleshooting

Easy-to-use Perl scripts to backup your MySQL database with mysqldump and FTP the files to a remote server

Upgrade MySQL to a new version with a fresh installation & use shell scripts and mysqldump to reload your data

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

Dumping and making a copy of a single database

Dumping events, routines, triggers

Only dump table definitions

Only dump the data

Using mysqldump to test a new version of mysql

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

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

About the author:

Interesting Links

Recent Posts

Archives

Categories

Blog Stats

ScriptingMySQL RSS Feds