mysqlhotcopy is a perl script that comes with MySQL installation. This locks the table, flush the table and then performs a copy of the database. You can also use the mysqlhotcopy to automatically copy the backup directly to another server using scp.
1. mysqlhotcopy command:
|
1
|
[local-host]# /usr/bin/mysqlhotcopy -u root -p My2Secure$Password
sugarcrm /home/backup/database
--allowold --keepold
|
The above example, performs a backup of sugarcrm, a MySQL database to the /home/backup/database directory.
- –allowold: This options renames the old backup to {datbase-name}_old before taking a new backup. In this example, if sugarcrm backup already existing under /home/backup/database, it will move the old backup to /home/backup/database/sugarcrm_old before creating /home/backup/database/sugarcrm.
- –keepold: This option instructs the mysqlhotcopy to keep the old backup (i.e the renamed _old) after the backup is completed.
View the mysqlhotcopy documentation using perldoc as shown below.
|
1
|
[local-host]# perldoc mysqlhotcopy
|
Following are the available options that can be passed to mysqlhotcopy command.
|
Option |
Description |
| –addtodest | Do not rename target directory (if it exists); merely add files to it |
| –allowold | Do not abort if a target exists; rename it by adding an _old suffix |
| –checkpoint=db_name.tbl_name | Insert checkpoint entries |
| –chroot=path | Base directory of the chroot jail in which mysqld operates |
| –debug | Write a debugging log |
| –dryrun | Report actions without performing them |
| –flushlogs | Flush logs after all tables are locked |
| –help | Display help message and exit |
| –host=host_name | Connect to the MySQL server on the given host |
| –keepold | Do not delete previous (renamed) target when done |
| –noindices | Do not include full index files in the backup |
| –password[=password] | The password to use when connecting to the server |
| –port=port_num | The TCP/IP port number to use for the connection |
| –quiet | Be silent except for errors |
| –regexp | Copy all databases with names that match the given regular expression |
| –resetmaster | Reset the binary log after locking all the tables |
| –resetslave | Reset the master.info file after locking all the tables |
| –socket=path | For connections to localhost |
| –tmpdir=path | The temporary directory |
| –user=user_name, | The MySQL username to use when connecting to the server |
| –version | Display version information and exit |
2. mysqlhotcopy command output:
The above mysqlhotcopy command will display an output similar to the following.
|
1
2
3
4
5
6
7
|
[local-host]# /usr/bin/mysqlhotcopy -u root -p My2Secure$Password
sugarcrm /home/backup/database
--allowold --keepold
Locked 98 tables in
0 seconds.
Flushed tables (`sugarcrm`.`accounts`, `sugarcrm`.`accounts_audit`, `sugarcrm`.`accounts_bugs`) in
0 seconds.
Copying 295 files...Copying indices for
0 files...
Unlocked tables.mysqlhotcopy copied 98 tables (295 files) in
0 seconds (0 seconds overall).
|
By default, MySQL database is located under /var/lib/mysql/{db-name}. mysqlhotcopy takes a backup of the table files from this default database location, to the backup directory. The backup directory /home/backup/database/sugarcrm, will contain exact copy of all the files from the real MySQL database /var/lib/mysql/sugarcrm directory.
|
1
2
3
4
|
[local-host]# ls -1 /var/lib/mysql/sugarcrm | wc -l
295[local-host]# ls -1 /home/backup/database/sugarcrm | wc -l
295 |
Please note that every table has three corresponding files with the extension *.frm, *.MYD and *.MYI. The database directory also contains a db.opt file that contains the database related parameter.
In the above example, you can see the mysqlhotcopy takes a backup of 98 sugarcrm database tables. So, the total number of files in the backup directory = 98 tables * 3 + 1 db configuration file = 296 files.
3. Restore from mysqlhotcopy
To restore the backup from the mysqlhotcopy backup, simply copy the files from the backup directory to the /var/lib/mysql/{db-name} directory. Just to be on the safe-side, make sure to stop the mysql before you restore (copy) the files. After you copy the files to the /var/lib/mysql/{db-name} start the mysql again.
4. Troubleshooting mysqlhotcopy
How to resolve Can’t locate DBD/mysql.pm issue? mysqlhotcopy is a perl script and it requires the perl-DBD module. You may receive the following error while executing mysqlhotcopy if perl-DBD module is not installed.
|
1
2
3
4
5
6
7
|
[local-host]# /usr/bin/mysqlhotcopy -u root -p My2Secure$Password
sugarcrm /home/backup/database
--allowold --keepoldinstall_driver(mysql) failed: Can‘t locate DBD/mysql.pm in @INC (@INC contains:
/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/usr/lib/perl5/5.8.8 .) at
(eval 7) line 3.
Perhaps the DBD::mysql perl module hasn‘t been fully installed,or perhaps the capitalisation of
‘mysql‘ isn‘t right.
Available drivers: DBM, ExampleP, File, Proxy, Sponge.at /usr/bin/mysqlhotcopy line 177
|
Make sure to install the perl-DBD package as shown below.
|
1
2
3
|
[local-host]# rpm -ivh perl-DBD-MySQL-3.0007-1.fc6.i386.rpm
Preparing... ########################################### [100%]1:perl-DBD-MySQL ########################################### [100%] |
How to resolve the issue with perl-DBD installation? While installing the perl-DBD, you may get the following error message.
|
1
2
3
4
5
6
7
8
9
10
11
|
[local-host]# rpm -ivh perl-DBD-MySQL-3.0007-1.fc6.i386.rpm
rpmdb: Program version 4.3 doesn‘t match environment version
error: db4 error(-30974) from dbenv->open: DB_VERSION_MISMATCH: Database environment version mismatcherror: cannot open Packages index using db3 - (-30974)warning: cannot open Solve database in /usr/lib/rpmdb/i386-redhat-linux/redhatrpmdb: Program version 4.3 doesn‘t match environment version
error: db4 error(-30974) from
dbenv->open: DB_VERSION_MISMATCH: Database
environment version mismatch
warning: cannot open
Solve database
in /usr/lib/rpmdb/i386-redhat-linux/redhat
error: Failed dependencies:libmysqlclient.so.15 is
needed by perl-DBD-MySQL-3.0007-1.fc6.i386
libmysqlclient.so.15(libmysqlclient_15) is
needed by perl-DBD-MySQL-3.0007-1.fc6.i386
|
Download and install the MySQL-shared-compat from mysql.com and this should resolve the above mentioned error while installing the perl-DBD package.
|
1
2
3
|
[local-host]# rpm -ivh MySQL-shared-compat-5.1.25-0.rhel5.i386.rpm
Preparing... ########################################### [100%]1:MySQL-shared-compat ########################################### [100%] |
参考：
http://www.thegeekstuff.com/2008/07/backup-and-restore-mysql-database-using-mysqlhotcopy/