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:
[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.
[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.
[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.
[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.
[local-host]# /usr/bin/mysqlhotcopy -u root -p My2Secure$Password sugarcrm /home/backup/database --allowold --keepold install_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.
[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.
[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 mismatch error: cannot open Packages index using db3 - (-30974) warning: cannot open Solve database in /usr/lib/rpmdb/i386-redhat-linux/redhat rpmdb: 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.
[local-host]# rpm -ivh MySQL-shared-compat-5.1.25-0.rhel5.i386.rpm Preparing... ########################################### [100%] 1:MySQL-shared-compat ########################################### [100%]
Do you use any other methods to backup or restore your MySQL database? Please leave your comments.
My name is Ramesh Natarajan. I will be posting instruction guides, how-to, troubleshooting tips and tricks on Linux, database, hardware, security and web. My focus is to write articles that will either teach you or help you resolve a problem. Read more about
Comments on this entry are closed.
Hi,
Nice article.
I tried taking the backup and restoring using this mysqlhotcopy.
I issued the following cmd.
mysql> /usr/bin/mysqlhotcopy -u root -p pass appdb /tmp -allowold -keepold
Locked 16 tables in 0 seconds.
Flushed tables (`all the tables`) in 0 seconds.
Copying 48 files…
Copying indices for 0 files…
Unlocked tables.
mysqlhotcopy copied 16 tables (48 files) in 0 seconds (0 seconds overall).
NOTE: Here it dnt show 16*3+1. i dnt know why?
then i stopped and copied these backup under new database..
when i connected to new database it is showing error
Didn’t find any fields in table ‘Ar’
Didn’t find any fields in table ‘Arch’
Didn’t find any fields in table ‘Arist’
Didn’t find any fields in table ‘BITE’
If i described any table in that it is saying error
Can’t find file: ‘./newdb/Ar.frm’ (errno: 13)
Do u have any idea how to resolve this?
I got it working. Its because, when restoring the files, the owner of those files should be mysql. Thanks.
When copying the files back.
cp –preserve=mode,ownership [path/to/backup/files] /var/lib/mysql/[db_name]
Using preserve will stop you having to use chmod and chown.
while executing mysqldump
mysql > mysqldump -u root -p dwr1 > dwr2.sql
the arrow comes on sceeen
–>
with flasing cursor
????
what is to be changed to exe to dump command to take backup of my file in sql at command prompt