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.
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.
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:
Next, when I try to start mysqld I notice that my database "address_book" is not present.
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.
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.
