Skip to main content
Pure1 Support Portal

MySQL Database Cloning With FlashArray Copy

Problem

Businesses require fully operational compute environments to develop, test, maintain, and troubleshoot applications. Creating from scratch and populating a test or development database is possible but in most cases not practical. With the need for rapid provisioning, IT personnel usually opt for database cloning. MySQL provides numerous utilities to copy the database. It is also possible to use commercial data protection applications to restore a database to an alternate location - however, these procedures are time and resource consuming. 

Solution

FlashArray™ offers nearly instantaneous volume copies which can be utilized to quickly and easily clone an existing database environment. Whether the new database will be attached to a new MySQL host or new MySQL instance on the same host, the cloning process is almost identical so long as the database data is on a FlashArray volume. Database cloning requires the following steps:

  1. Create Crash Consistent or Application Consistent copy of database volume
  2. Connect volume created in previous step to target host
  3. Mount volume
  4. Modify MySQL datadir configuration setting 
  5. Start MySQL Database

Crash Consistent FlashArray Volume Copy

To copy FlashArray volume perform the following:

Graphical User Interface

Select:

Storage > Volumes <Volume>  '⋮' (Vertical Ellipses) > Copy.

fig1b.png

Command Line Interface

To copy a volume from an existing volume, use the purevol command. The following is the command syntax for the purevol

purevol copy [--overwrite] SOURCE TARGET

where:

--overwrite: overwrite an existing volume

For instance:

purevol copy  m20mysqld01 m20mysqld01C

This command will copy the m20mysql01 volume to m20mysql01C volume.

Application Consistent FlashArray Volume Copy

The process of creating an application-consistent volume copy on FlashArray is identical to the Crash Consistent FlashArray Volume Copy described in the previous section; however, before copying the volume, database operations should be halted. The required steps to prepare the database for an application-consistent snapshot are described in Protecting MySQL Database With FlashArray Snapshots article. 

Connect Volume To Target MySQL Host 

To connect the volume to the target host, perform the following:

Graphical User Interface

Select:

Storage > Volumes > <Volume> > '⋮' (Vertical Ellipses) > Connect.

fig2b.png

Command Line Interface

To connect  volume to the host, use the purevol command. The following is the command syntax for purevol: 

purevol connect {--host HOST } VOL...

For instance:

purevol connect --host server01 m20mysql01C

This command connects the m20mysql01C volume to the host server01. 

Depending on whether the new volume is connected to the existing host or a new host, different procedures must be followed.

Configuring New MySQL Host

When the database clone target is on the new host, mount the volume previously attached and modify the datadir variable in MySQL my.cnf configuration file to reflect the mount point. MySQL database will need to be restarted.

datadir=/data/mysql

Configuring Existing MySQL Host

When the database clone target is the same MySQL host as the source, before mounting the file system, disk UUID must be changed. Furthermore, another MySQL instance should be created.

Mount Volume

When attempting to mount another disk with the same UUID as an already mounted volume, the mount command will fail with the following error:

mount: /clone: wrong fs type, bad option, bad superblock on /dev/sdd, missing codepage or helper program, or other error.

The tune2efs command must be used to modify UUID for ext2, ext3, ext4 file systems and xfs_admin for XFS file system. To generate random UUID and change it for ext family of file systems use:

tune2efs -U random 

to generate random UUID and change it for XFS file system use:

xfs_admin -U generate

Once the UUID has been modified, the new volume can be mounted.

Create MySQL Instance

MySQL instances are defined in my.cnf file. To create another database instance, simply add a group to the configuration file reflecting new instance parameters including datadir configuration which needs to be set to the file system mounted in the previous step. See an example my.cnf file with two database instances called tpcc1 and tpcc2.

[mysqld@tpcc1]
datadir=/data/mysql
socket=/var/lib/mysql/mysql1.sock
log-error=/var/log/mysqld1.log
pid-file=/var/run/mysqld/mysqld1.pid
bind-address=10.21.211.40
innodb_buffer_pool_size=2147483648
log-bin=/logs/mysql/binlog1

[mysqld@tpcc2]
datadir=/clone/mysql
socket=/var/lib/mysql/mysql2.sock
port=3307
log-error=/var/log/clone.log
pid-file=/var/run/mysqld/mysqld2.pid
bind-address=10.21.211.40
log-bin=/logs/mysql/binlog2

Starting MySQL Instance

The method to restart MySQL depends on how the database was installed as well as the Linux distribution. For systems that support the systemd systemctl command may be used.

For a single instance:

shell > systemctl restart mysql

For multiple instances:

shell >  systemctl restart mysqld@<group_name>

for example:

shell >  systemctl restart mysqld@tpcc2

To verify the status of a particular MySQL instance execute the following:

shell > systemctl status mysqld@tpcc2

Conclusion

Modern shared storage such as FlashArray - with a rich set of data services including volume copying and snapshots - allows for convenient cloning of production databases. Because the time required to copy the FlashArray volume is NOT dependent on its size, even very large MySQL databases can be quickly and easily duplicated for test, development, and retention purposes.

 © 2019 Pure Storage, the Pure P Logo, and the marks on the Pure Trademark List at https://www.purestorage.com/legal/pr...duserinfo.html are trademarks of Pure Storage, Inc. Other names are trademarks of their respective owners. 

THIS DOCUMENTATION IS PROVIDED "AS IS" AND ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT, ARE DISCLAIMED, EXCEPT TO THE EXTENT THAT SUCH DISCLAIMERS ARE HELD TO BE LEGALLY INVALID. PURE STORAGE SHALL NOT BE LIABLE FOR INCIDENTAL OR CONSEQUENTIAL DAMAGES IN CONNECTION WITH THE FURNISHING, PERFORMANCE, OR USE OF THIS DOCUMENTATION. THE INFORMATION CONTAINED IN THIS DOCUMENTATION IS SUBJECT TO CHANGE WITHOUT NOTICE.