Backup and Restore MySQL Database using mysqlhotcopy

by Ramesh Natarajan on July 16, 2008

MySQL Logo
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.


Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 4 comments… read them below or add one }

1 sasikala March 31, 2009 at 3:36 am

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?

2 sasikala March 31, 2009 at 4:30 am

I got it working. Its because, when restoring the files, the owner of those files should be mysql. Thanks.

3 chris June 26, 2009 at 3:43 am

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.

4 suman lata December 20, 2012 at 9:59 pm

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

Leave a Comment

Previous post:

Next post: