Backup a MySQL database using IBM Storwize V7000's FlashCopy

I wanted to backup my MySQL databases data dir using the Storwize V7000's FlashCopy functionality.
I first created a small database called address_book, and then added a table called address to it. Next I entered some data into the table address.

[root@isvx3 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.8 MySQL Community Server (GPL)

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

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

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


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

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

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


mysql> use address_book;
Database changed
mysql> create table address (id int not null auto_increment, primary key(id), name varchar(30), address varchar(60));
Query OK, 0 rows affected (0.06 sec)


mysql> insert into address (id, name, address) values (1,"amitabh", "mumbai");
Query OK, 1 row affected (0.00 sec)

mysql> insert into address (id, name, address) values (2,"de niro", "hollywood");
Query OK, 1 row affected (0.00 sec)


mysql> select * from address;
+----+---------+-----------+
| id | name    | address   |
+----+---------+-----------+
|  1 | amitabh | mumbai    |
|  2 | de niro | hollywood |
+----+---------+-----------+
2 rows in set (0.00 sec)

I started the Flashcopy(backup) process on the IBM Storwize V7000. The V7000 created another volume, and started the backup of the volume on which the data dir resided.

/dev/mapper/mpath32p1
9.9G 404M 9.0G 5% /home/mysql

The picture below shows how to backup using the GUI of the Storwize V7000.

As you see below, this has started the FlashCopy backup on the Storwize V7000. The FlashCopy has now created another volume, to which it has started copying our original volume which has the data dir for our MySQL database.

We now map the newly created volume to the MySQL server host as shown below.

While the MySQL data dir is being backed up I went back to my database, and inserted another entry into the mysql table.

mysql> insert into address (id, name, address) values (3,"sean connery", "edinburgh");
Query OK, 1 row affected (0.00 sec)

mysql> select * from address;
+----+--------------+-----------+
| id | name         | address   |
+----+--------------+-----------+
|  1 | amitabh      | mumbai    |
|  2 | de niro      | hollywood |
|  3 | sean connery | edinburgh |
+----+--------------+-----------+
3 rows in set (0.00 sec)

mysql>

As you see above I was able to make another entry into the table, which means that the volume that is being
backuped up the Stowize V7000 is still accessible for use by the MySQL database.

Next I want inject a fault onto my original MySQL data dir file system, and then test my backup volume.
Imagine this to be the fault that causes the volume to be unmounted:

[root@isvx3 ~]# umount /home/mysql
[root@isvx3 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3             255G   60G  182G  25% /
/dev/sda1              99M   33M   62M  35% /boot
tmpfs                  32G   16G   17G  49% /dev/shm
[root@isvx3 ~]#

Next, when I try to start mysqld I notice that my database "address_book" is not present.

[root@isvx3 ~]# mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.8 MySQL Community Server (GPL)

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

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

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



mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.03 sec)

mysql>

[root@isvx3 ~]# /etc/init.d/mysql stop
Stopping MySQL:                                            [  OK  ]

Edit the data dir entry in the /etc/my.cnf file to point to the backup 
[root@isvx3 ~]# vi /etc/my.cnf
[root@isvx3 ~]# /etc/init.d/mysql start
Starting MySQL:                                            [  OK  ]
[root@isvx3 ~]# ps -ef | grep mysql
root      9154     1  0 14:48 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/home/mysqlbak/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/lib/mysql/mysqld.log --pid-file=/var/lib/mysql/mysqld/mysqld.pid --user=mysql
mysql     9204  9154  0 14:48 pts/1    00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/home/mysqlbak/mysql --user=mysql --pid-file=/var/lib/mysql/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock
root      9222  8065  0 14:49 pts/1    00:00:00 grep mysql
[root@isvx3 ~]#


[root@isvx3 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.8 MySQL Community Server (GPL)

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

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

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


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

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

Database changed
mysql> select * from address;
+----+---------+-----------+
| id | name    | address   |
+----+---------+-----------+
|  1 | amitabh | mumbai    |
|  2 | de niro | hollywood |
+----+---------+-----------+
2 rows in set (0.02 sec)

mysql>

As we see we can see the backup of the first two entries on the new volume. The third entry of the table was entered after the backup was started on Storwize V7000, hence we don't see it.

We can now take another back of original volume, but this time it will be a lot faster then the earlier backup as only the new entries will be copied.

The picture below shows how to start the follow on back

The next time mounted the back up file system, and re-started MySQL I could see the up to date entries in the my MySQL table.