There are so many ways to do this, but our need was to not have any production down time* and have the test database available ASAP.
I wrote up a little how to and I thought I might share it so others could use it. Like I said there are many ways to do this so just take a look and use the logic that works for you.
A Method to copy Data
to a test environment
This method will copy all data in a volume and MySQL a test
environment with no downtime. The method
used could also be used for backing up data or replication.
Here are the steps needed to accomplish the copy to test.
1.
Add an additional hard drive for Storage.
2.
Configure the disk using fdisk and then create a
Physical Volume with the pvcreate command.*
3.
Add the Physical Volume to the VolGroup with the
vgextend command.*
4.
Setup MySQL to run a second instance.*
5.
Run the this script.
#! /bin/sh
user=username
pwd=password
olddb=originalname
newdb=newname
socket=/tmp/mysql.sock2
#This will remove LVM and stop the second instance of
MySQL .
mysqladmin -u$user -p$pwd --socket=$socket shutdown
sleep 4
umount /dev/VolGroup01/databackup
lvremove -f /dev/VolGroup01/databackup
#This will flush mysql and Lock Tables and LVM snapshot (see
below)**
mysql -u$user -p$pwd < lvm_snapshot
#At this point the volume and MySQL files have
been copied
#This will will mount the LVM snapshot to /mnt/databackup
mount -t ext3
/dev/VolGroup01/databackup
/mnt/databackup
#This will start the second instance of MySQL on port
3305
mysqld_multi start 2
#This is needed to allow enough time for MySQL to start
and create the mysql.sock2 file
sleep 4
#This will login to the second instance of MySQL and
rename the schema to dl4test.
mysqlconn="mysql -u$user -p$pwd -S $socket -h
localhost"
$mysqlconn -e "CREATE DATABASE $newdb"
params=$($mysqlconn -N -e "SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES WHERE table_schema='$olddb'")
for name in $params; do
$mysqlconn -e
"RENAME TABLE $olddb.$name to $newdb.$name"; done;
$mysqlconn -e "DROP DATABASE $olddb"
#This will grant user saitest access to the newdatabase schema
$mysqlconn -e "GRANT ALL PRIVILEGES ON newdatabase.* TO
'username'@'%' IDENTIFIED BY 'saitest'";
This script takes about 10 seconds to run and requires no
down time in production.
Note: the schema name change does not change
any stored procedures so a script or logic would need to be added in order to
create those after renaming the schema.
*More Information on Setup:
1. Use fdisk to setup
the disk.
Type fdisk /dev/sdb1 (or whatever device your new hard drive
is) and press Enter. Then press,
m,n,e(choose the defaults), p, and w to save the changes.
2. Create a Physical
Volume.
Type “pvcreate /dev/sdb1”
3. Extend the Volume Group.
Type “vgextend VolGroup01 /dev/sdb1”
4. Add a second instance of MySQL.
Modify the /etc/my.cnf with the following changes.
a.
Add a [mysqld_multi] section that looks like
this:
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
b.
Rename [mysqld] to [mysqld1]
c.
Add a [mysqld2] section with the following
parameters:
[mysqld2]
port = 3305
datadir = /mnt/databackup/mysql
socket = /tmp/mysql.sock2
pid-file =
/mnt/databackup/mysql/mars-snapshot-test.pid2
log_error = /mnt/databackup/mysql/error2.log
To start the second instance of mysql use the following
command:
“mysqld_multi start 2”
To stop the second instance of mysql use the following
command:
Mysqladmin –uusername –ppassword –socket=/tmp/mysql.sock2
shutdown.
To login to the second instance of mysql you will need to
login using the socket:
Mysql –uusername –ppassword –S /tmp/mysql.sock2 –hlocation
DownTime - we run this at night when there are a minimal of users.
Long queries at the time of the LVM snapshot will call some downtime.
** Here is the content of the lvm_snapshot file
FLUSH TABLES WITH READ LOCK;
system lvcreate -L 220G -s -n databackup
/dev/VolGroup01/LogVol01;
UNLOCK TABLES;
QUIT
