mysqlhotcopy

mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,
但 只能用于备份MyISAM存储引擎和运行在数据库目录所在的机器上.与mysqldump备份不同,mysqldump属于逻辑备份,备份时是执行的sql语句.
使用mysqlhotcopy命令前需要要安装相应的软件依赖包.
安装包包括：（perl-DBI, perl-DBD-MySQL）
注意点：在命令行中指定用户名和密码时，-u不能和用户名连着写，中间要有空格，-p和密码中间也要有空格，而且必须在命令行明文指定

环境：

[root@langkeziju132 ~]# cat /etc/redhat-release
CentOS release 5.4 (Final)
[root@langkeziju132 ~]# uname -a
Linux langkeziju132 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

mysql版本：mysql 5.6.22

使用mysqlhotcopy备份实录：
[root@langkeziju132 mysql]# cd /usr/local/mysql/bin/
[root@langkeziju132 bin]# ls
innochecksum       mysql             mysql_client_test           mysqld_safe           mysqlimport                mysqltest_embedded   resolveip
msql2mysql         mysqlaccess       mysql_client_test_embedded  mysqldump             mysql_plugin               mysql_tzinfo_to_sql  resolve_stack_dump
myisamchk          mysqlaccess.conf  mysql_config                mysqldumpslow         mysql_secure_installation  mysql_upgrade
myisam_ftdump      mysqladmin        mysql_config_editor         mysql_embedded        mysql_setpermission        mysql_waitpid
myisamlog          mysqlbinlog       mysql_convert_table_format  mysql_find_rows       mysqlshow                  mysql_zap
myisampack         mysqlbug          mysqld                      mysql_fix_extensions  mysqlslap                  perror
my_print_defaults  mysqlcheck        mysqld_multi                mysqlhotcopy          mysqltest                  replace
[root@langkeziju132 bin]# ./mysqlhotcopy -uroot -p mysql /usr/local/bk/                  --缺少perl-DBI（注意大小写）导致报错
Can't locate DBI.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at ./mysqlhotcopy line 25.
BEGIN failed--compilation aborted at ./mysqlhotcopy line 25.
[root@langkeziju132 bin]# yum install perl-DBI -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * c5-media:
 * rpmforge: mirrors.neusoft.edu.cn
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-DBI.x86_64 0:1.52-2.el5 set to be updated
--> Finished Dependency Resolution


Dependencies Resolved


========================================================================================================================================================================
 Package                                 Arch                                  Version                                    Repository                               Size
========================================================================================================================================================================
Installing:
 perl-DBI                                x86_64                                1.52-2.el5                                 c5-media                                600 k


Transaction Summary
========================================================================================================================================================================
Install      1 Package(s)
Update       0 Package(s)
Remove       0 Package(s)


Total download size: 600 k
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : perl-DBI                                                                                                                                         1/1


Installed:
  perl-DBI.x86_64 0:1.52-2.el5


Complete!
[root@langkeziju132 bin]# ./mysqlhotcopy -uroot -p mysql /usr/local/bk/                    --注意 -u 和用户名不能连着写，这和平时登陆mysql 时有所区别
Unknown option: uroot
Invalid option
./mysqlhotcopy Ver 1.23


Usage: ./mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]


  -?, --help           display this help-screen and exit
  -u, --user=#         user for database login if not current user
  -p, --password=#     password to use when connecting to server (if not set
                       in my.cnf, which is recommended)
  -h, --host=#         hostname for local server when connecting over TCP/IP
  -P, --port=#         port to use when connecting to local server with TCP/IP
  -S, --socket=#       socket to use when connecting to local server
      --old_server     connect to old MySQL-server (before v5.5) which
                       doesn't have FLUSH TABLES WITH READ LOCK fully implemented.


  --allowold           don't abort if target dir already exists (rename it _old)
  --addtodest          don't rename target dir if it exists, just add files to it
  --keepold            don't delete previous (now renamed) target when done
  --noindices          don't include full index files in copy
  --method=#           method for copy (only "cp" currently supported)


  -q, --quiet          be silent except for errors
  --debug              enable debug
  -n, --dryrun         report actions without doing them


  --regexp=#           copy all databases with names matching regexp
  --suffix=#           suffix for names of copied databases
  --checkpoint=#       insert checkpoint entry into specified db.table
  --flushlog           flush logs once all tables are locked
  --resetmaster        reset the binlog once all tables are locked
  --resetslave         reset the master.info once all tables are locked
  --tmpdir=#           temporary directory (instead of /tmp)
  --record_log_pos=#   record slave and master status in specified db.table
  --chroot=#           base directory of chroot jail in which mysqld operates


  Try 'perldoc ./mysqlhotcopy' for more complete documentation
[root@langkeziju132 bin]# ./mysqlhotcopy -u root -p mysql /usr/local/bk/                    --缺少perl-DBD-MySQL（注意大小写）所致
Warning: ./mysqlhotcopy is deprecated and will be removed in a future version.
install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-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 ./mysqlhotcopy line 199
[root@langkeziju132 bin]# yum install perl-DBD
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * c5-media:
 * rpmforge: mirrors.neusoft.edu.cn
Setting up Install Process
No package perl-DBD available.
Nothing to do


[root@langkeziju132 bin]# yum install perl-DBD-MySQL -y
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * c5-media:
 * rpmforge: mirrors.neusoft.edu.cn
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package perl-DBD-MySQL.x86_64 0:3.0007-2.el5 set to be updated
--> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15)(64bit) for package: perl-DBD-MySQL
--> Processing Dependency: libmysqlclient.so.15()(64bit) for package: perl-DBD-MySQL
--> Running transaction check
---> Package mysql.x86_64 0:5.0.77-3.el5 set to be updated
--> Finished Dependency Resolution


Dependencies Resolved


========================================================================================================================================================================
 Package                                     Arch                                Version                                    Repository                             Size
========================================================================================================================================================================
Installing:
 perl-DBD-MySQL                              x86_64                              3.0007-2.el5                               c5-media                              148 k
Installing for dependencies:
 mysql                                       x86_64                              5.0.77-3.el5                               c5-media                              4.8 M


Transaction Summary
========================================================================================================================================================================
Install      2 Package(s)
Update       0 Package(s)
Remove       0 Package(s)


Total download size: 4.9 M
Downloading Packages:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                   1.8 GB/s | 4.9 MB     00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing     : mysql                                                                                                                                            1/2
  Installing     : perl-DBD-MySQL                                                                                                                                   2/2


Installed:
  perl-DBD-MySQL.x86_64 0:3.0007-2.el5


Dependency Installed:
  mysql.x86_64 0:5.0.77-3.el5


Complete!


[root@langkeziju132 bin]# ./mysqlhotcopy -u root -p  mysql /root/bak/
Warning: ./mysqlhotcopy is deprecated and will be removed in a future version.
DBI connect(';host=localhost;mysql_read_default_group=mysqlhotcopy','root',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at ./mysqlhotcopy line 199


[root@langkeziju132 bin]# ln -s /tmp/mysqld.sock /var/lib/mysql/mysql.sock      
或者
[root@langkeziju132 bin]# ./mysqlhotcopy -u root -p -S /tmp/mysqld.sock  mysql /root/bak/


[root@langkeziju132 bin]# ./mysqlhotcopy -u root -p mysql /usr/local/bk/
Warning: ./mysqlhotcopy is deprecated and will be removed in a future version.
DBI connect(';host=localhost;mysql_read_default_group=mysqlhotcopy','root',...) failed: Access denied for user 'root'@'localhost' (using password: YES) at ./mysqlhotcopy line 199
[root@langkeziju132 bin]# ./mysqlhotcopy -u root -p 123456 mysql /usr/local/bk/                --必须在命令行明文指定密码，否则会报：failed: Access denied for user 'root'@'localhost' ……
Warning: ./mysqlhotcopy is deprecated and will be removed in a future version.
Flushed 26 tables with read lock (`mysql`.`columns_priv`, `mysql`.`db`, `mysql`.`event`, `mysql`.`func`, `mysql`.`help_category`, `mysql`.`help_keyword`, `mysql`.`help_relation`, `mysql`.`help_topic`, `mysql`.`innodb_index_stats`, `mysql`.`innodb_table_stats`, `mysql`.`ndb_binlog_index`, `mysql`.`plugin`, `mysql`.`proc`, `mysql`.`procs_priv`, `mysql`.`proxies_priv`, `mysql`.`servers`, `mysql`.`slave_master_info`, `mysql`.`slave_relay_log_info`, `mysql`.`slave_worker_info`, `mysql`.`tables_priv`, `mysql`.`time_zone`, `mysql`.`time_zone_leap_second`, `mysql`.`time_zone_name`, `mysql`.`time_zone_transition`, `mysql`.`time_zone_transition_type`, `mysql`.`user`) in 0 seconds.
Locked 0 views () in 0 seconds.
Copying 79 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 26 tables (79 files) in 1 second (1 seconds overall).
[root@langkeziju132 bin]# ll /usr/local/bk/
total 4
drwxr-x--- 2 mysql mysql 4096 Jun 28 17:41 mysql




若备份某个库中某个表，方法如下：
[root@langkeziju132 bin]# ./mysqlhotcopy -u root -p 123456 mysql./user/ /root/bak/     --备份mysql下的user表，此处也支持正则。比如备份mysql库下以use开头的表，可以这样：mysql./use*/          
Warning: ./mysqlhotcopy is deprecated and will be removed in a future version.
Flushed 1 tables with read lock (`mysql`.`user`) in 0 seconds.
Locked 0 views () in 0 seconds.
Copying 3 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 1 tables (3 files) in 0 seconds (0 seconds overall).