Summary of mysqlhotcopy BACKUP database in MySQL

Mysqlhotcopy usage

You need to install PERL-DBI and Dbd-mysql before using mysqlhotcopy:

1, the implementation of Yum install PERL-DBI installation perl-dbi.
2, installation Dbd-mysql Please refer to the bottom material

A common example of backup:

/usr/local/mysql/bin/mysqlhotcopy-u root-p Zmh537817--addtodest database name 1 database name 2 backup directory
–addtodest means that when a backup is present, the backup is not interrupted and only new files are added.
More options are available for performing/usr/local/mysql/bin/mysqlhotcopy–help viewing.

How do I restore?

1, stop the MySQL server.
2, copy the backup database directory to the MySQL data directory.
3, change the directory owner for the MySQL server running users (such as MySQL).
4, start the MySQL server

Instance

Mysqlhotcopy is used only for backup MyISAM. It runs in Unix and NetWare

Use the method to see the following script. Join the Crotab.

The code is as follows

Copy Code

#!/bin/sh # Name:mysqlbackup.sh # ps:mysql DataBase backup,use mysqlhotcopy script. # Last Modify:2008-06-12 # define variables, please modify them as appropriate # define the directory where the script is located Scriptsdir= ' pwd ' # Data Directory for database Datadir=/var/lib/mysql # Data MySQL backup directory Tmpbackupdir=/tmp/mysqlblackup Backupdir=/backup/mysql # User name and password used to back up the database Mysqluser=root mysqlpwd= ' You password ' # If the temporary backup directory exists, empty it, and create it if it does not exist if [[E-$tmpBackupDir]]; Then RM-RF $tmpBackupDir/* Else mkdir $tmpBackupDir Fi # Create a backup directory if it does not exist if [[!-e $backupDir]];then mkdir $backupDir Fi # Get a list of database backups, where you can filter the databases you don't want to back up For databases in ' find $dataDir-type D | Sed-e "s//var/lib/mysql///" | Sed-e "s/test//"; Todo if [[$databases = = ' "]]; Then Continue Else # MySQL BACKUP Database /usr/bin/mysqlhotcopy--user= $mysqlUser--password= $mysqlPWD-Q "$databases" $tmpBackupDir Datetime= ' Date ' +%y.%m.%d%h:%m:%s ' echo "$dateTime Database: $databases backup success!" >>mysqlbackup.log Fi Done # Compress backup Files Date= ' Date-i ' CD $tmpBackupDir Tar czf $backupDir/mysql-$date. tar.gz./ #End完成

Add to Crontab set 5 run per week

The code is as follows	Copy Code
0 0 * * 5/backup/blackup.sh

Note: Restore the state of the database to the time of backup

Mysqlhotcopy Backup is the entire database directory, use can be directly copied to the mysqld specified DataDir (here is/var/lib/mysql/) directory, but also attention to the issue of permissions, the following example:

The code is as follows	Copy Code
Shell> CP-RF db_name/var/lib/mysql/ Shell> chown-r mysql:mysql/var/lib/mysql/(convert db_name directory owner to Mysqld run user)

This set of backup strategy can only restore the state of the database to the last backup, if the data lost in the crash should be more frequent MySQL backup, in order to restore the data to the state of the crash please use the master-slave replication mechanism (replication).

Mysqlhotcopy Backup Error

Error message:

code is as follows

copy code

/usr/local/mysql/bin/mysqlhotcopy-u root-p ---addtodest- Flushlog-q qeedoo/tmp/bakinstall_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/site_perl/5.8.8/usr/lib/perl5/site_perl/usr/lib/perl5/ vendor_perl/5.8.8/i386-linux-thread-multi/usr/lib/perl5/vendor_perl/5.8.8/usr/lib/perl5/vendor_perl/usr/lib/ perl5/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.

Solution:

The code is as follows	Copy Code
# Http://search.cpan.org/~capttofu/DBD-mysql-3.0008/wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/ Dbd-mysql-4.020.tar.gz tar zxvf dbd-mysql-4.020.tar.gz cd/tmp/dbd-mysql-4.020 perl makefile.pl--mysql_config=/usr/ Local/webserver/mysql/bin/mysql_configmakemake Install

Finally, after compiling the installation, I found the error:

The code is as follows

Copy Code

? 12345 install_driver (mysql) failed:can ' t load '/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/dbd/ Mysql/mysql.so ' for module Dbd::mysql:libmysqlclient.so.16:cannot Open shared object file:no such file or directory at  /USR/LIB/PERL5/5.8.8/I386-LINUX-THREAD-MULTI/DYNALOADER.PM Line 230. at (eval 7) Line 3 compilation failed in require (eval 7) Line 3. Perhaps a required shared library or DLL isn ' t installed where expected at/usr/bin/mysqlhotcopy line 177

Because of the problem with the path configuration after tar was installed on MySQL.

Vi/etc/ld.so.conf
Add MySQL file path to this library

The code is as follows	Copy Code
echo "/usr/local/webserver/mysql/lib/mysql" >>/etc/ld.so.conf ldconfig

When you perform the mysqlhotcopy, everything is fine.

One of the services has the following problems:
The following questions to see the information, first note the other question:

The code is as follows

Copy Code

Can ' t locate dbi.pm in @INC (@INC contains:/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/usr/lib/perl5/site _perl/5.8.8/usr/lib/perl5/site_perl/usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/usr/lib/perl5/ vendor_perl/5.8.8/usr/lib/perl5/vendor_perl/usr/lib/perl5/5.8.8/i386-linux-thread-multi/usr/lib/perl5/5.8.8.) At/usr/local/mysql/bin/mysqlhotcopy Line 8. BEGIN failed--compilation aborted at/usr/local/mysql/bin/mysqlhotcopy line 8.

Reason: The system did not press the install DBI component