A database backup strategy is a critical part of any database
installation. The MySQL database backup strategy is a two-part process
of first extracting data from the database and then using a backup
tool to store the data to backup media, such as tape media or network
storage.
MySQL and other vendors provide data extraction tools, which
capture and store the data from the database. Each storage engine
in MySQL has unique characteristics that impact backup, so backup
solutions tend to be as custom as a database configuration.
This section provides an example of implementing a backup strategy
for a MySQL database installation using HP OpenView Storage Data Protector
(Data Protector). If you are currently using a different backup tool,
you can proceed with the general steps that are outlined in the backup
strategy that follows.
Installing and Configuring a Database Backup Tool |
 |
The following section shows you how to use the HP Data Protector
for testing purposes. You must install both the HP Data Protector
Cell Manager and the Data Protector agent, both of which can run on
Windows, HP-UX, and the Linux OS. In this example, the Data Protector
Cell Manager and Data Protector agent are installed separately on
one machine running Windows and on the Linux-based MySQL slave server.
HP Data Protector on Windows provides a GUI to manage resources and
perform backup or restore actions.
Before performing the following test, install the Data Protector
6.0 server on a Windows system. Use the following steps to install
and configure the Data Protector 6.0 Agent.
To install HP Data Protector for Linux Agent on the Linux-based
MySQL slave server, download the HP Data Protector for HP-UX PA-RISC
- Installation Server 1 of 2 (CD ISO image) from the following website: http://h18006.www1.hp.com/products/storage/software/dataprotector/index.html
Open the image and enter the following command:
# ./omnisetup.sh -install
da
Edit the /etc/services file and comment
out all the lines containing port 5555 by making
the following changes:
# personal-agent 5555/tcp # personal Agent
# personal-agent
5555/udp # personal Agent
To start the Data Protector agent, enter the following
commands:
# chkconfig xinetd on
# /etc/init.d/xinetd
restart
Import the MySQL slave server as a client of the Data
Protector Cell Manager as shown in Figure 7.
Backing Up Data |
|
There are two parts to backing up your data: first, the extraction
of data from the database and, second, the integration of the database
back up tool into the process.
To Extract Data from the Database
To stop the slave replication, enter the following
command:
mysql> STOP SLAVE SQL_THREAD;
After the slave thread has been stopped, flush the
tables to save the pending changes to disk by entering the following
command:
mysql> FLUSH TABLES;
Use the mysqldump command to place
the data to be backed up in a file on the slave server.
For MyISAM tables, use the following command:
# mysqldump -u
osmusr -p osmpass osmsdb customer > /tmp/customer.sql
For InnoDB tables, use the following command:
# mysqldump -u
osmusr -p osmpass --single-transaction osmsdb \
customer > /tmp/customer.sql
In the example, the database user for backup is osmusr with the password osmpass. Prior to backing up,
the user must be created and the proper privileges granted for lock tables and select on the osmdb database . The name of the test table is customer in the osmsdb database. The
backup data is stored in the /tmp/customer.sql file.
Restart the slave replication thread after the database
has been dumped by entering the following command:
mysql> START SLAVE SQL_THREAD;
To Integrate Data Protector
Add the secondary storage, which is used to hold the
backup data, as a device in Data Protector.
Add a Blank Filesystem Backup on the Data Protector
cell server, and then back up the following data to the secondary
storage device:
The data file (/tmp/customer.sql) that you dumped in step 2
The binary log files located in the /var/lib/mysql directory on the slave server
The relay-log.info and master.info files located in the /var/lib/mysql directory on the slave server
Start the backup either manually or at a scheduled
time.
After the backup is complete, restart replication
between the master server and slave servers.
For an incremental backup of a MySQL server, the dumped data
file needs to be backed up in its entirety only once, the first time
a backup is run. In all subsequent backup jobs, only the binary log
files, master.info and relay-log.info, should be backed up. Restoration of all data in the event of a
disaster is achieved by first restoring the full data backup, and
then restoring all the binary log updates, which are used to recover
the database to the point of the binary log flush.
Restoring Data |
|
Restore the data file and binary log files to both the master
server and slave server. All database data is recreated using the
extracted data file and the binary log files.
Stop the replication between master server and slave
servers. See “Configuring MySQL Server Replication” for instructions.
Restore the latest database data file and binary log
files to both master server and slave server using Data Protector.
Using the backup data file, reconstruct all database
data on the master server and slave server to the point in time when
the binary log flush occurred by entering the following command:
# mysql -uosmusr
-posmpass osmsdb < /tmp/customer.sql
Replay the binary log files on the master server and
slave server by entering the following command:
# mysqlbinlog mysql-bin.000002|mysql
-uosmusr -posmpass
On the slave server, restore the files master.info and relay-log.info so they are consistent with
the binary log files that have been restored on the master server.
Restart the replication.
Printable version
