MySQL备份工具之mysqlhotcopy

mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,但只能用于备份MyISAM存储引擎和运行在数据库目录所在的机器上.与mysqldump备份不同,mysqldump属于逻辑备份,备份时是执行的sql语句.使用mysqlhotcopy命令前需要要安装相应的软件依赖包.1.安装mysqlhotcopy所依赖的软件包（perl-DBD,DBD-mysql）[root@tong2 ~]# yum install perl-DBD* -y[root@tong2 ~]# wget https://cpan.metacpan.org/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.029.tar.gz[root@tong2 ~]# tar xvf DBD-mysql-4.029.tar.gz[root@tong2 ~]# cd DBD-mysql-4.029
[root@tong2 DBD-mysql-4.029]# perl Makefile.PL[root@tong2 DBD-mysql-4.029]# make[root@tong2 DBD-mysql-4.029]# make install[root@tong2 DBD-mysql-4.029]# echo $？
0
[root@tong2 DBD-mysql-4.029]# cd
[root@tong2 ~]# 2.查看mysqlhotcopy的帮助信息[root@tong2 ~]# vim /usr/my.cnf --在配置文件中添加如下参数[mysqlhotcopy]
interactive-timeout
host=localhost
user=root
password=system
port=3306[root@tong2 ~]# /etc/init.d/mysql restart --重启服务
Shutting down MySQL.. SUCCESS！
Starting MySQL. SUCCESS！[root@tong2 ~]# mysqlhotcopy --help
Warning: /usr/bin/mysqlhotcopy is deprecated and will be removed in a future version./usr/bin/mysqlhotcopy Ver 1.23Usage: /usr/bin/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 --一旦锁表重置binlog文件
--resetslave reset the master.info once all tables are locked --一旦锁表重置master.info文件 --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 /usr/bin/mysqlhotcopy" for more complete documentation
[root@tong2 ~]# 3.备份一个数据库到一个目录中[root@tong2 ~]# mysqlhotcopy -u root -p system tong /opt/[root@tong2 ~]# ll /opt/tong/
total 112
-rw-rw----. 1 mysql mysql 15 Jan 5 14:35 q.isl
-rw-rw----. 1 mysql mysql 8554 Jan 4 18:03 t.frm
-rw-rw----. 1 mysql mysql 98304 Jan 4 18:03 t.ibd
[root@tong2 ~]# ll /var/lib/mysql/tong
total 112
-rw-rw----. 1 mysql mysql 15 Jan 5 14:35 q.isl
-rw-rw----. 1 mysql mysql 8554 Jan 4 18:03 t.frm
-rw-rw----. 1 mysql mysql 98304 Jan 4 18:03 t.ibd
[root@tong2 ~]# 4.备份多个数据库到一个目录中[root@tong2 ~]# mysqlhotcopy -u root -p system tong mysql /opt/tong[root@tong2 ~]# ll /opt/
total 8
drwxr-x---. 2 mysql mysql 4096 Jan 5 15:29 mysql
drwxr-x---. 2 mysql mysql 4096 Jan 5 15:29 tong[root@tong2 ~]# ll /var/lib/mysql/{mysql,tong} -d
drwxr-xr-x. 2 mysql mysql 4096 Jan 5 15:29 /var/lib/mysql/mysql
drwxr-xr-x. 2 mysql mysql 4096 Jan 5 15:29 /var/lib/mysql/tong
[root@tong2 ~]# 5.备份数据库中某一个表[root@tong2 ~]# mysqlhotcopy -u root -p system mysql./user*/ /opt/[root@tong2 ~]# ll /opt/mysql/
total 20
-rw-r--r--. 1 mysql mysql 10684 Jan 4 16:49 user.frm
-rw-r--r--. 1 mysql mysql 784 Jan 4 16:49 user.MYD
-rw-r--r--. 1 mysql mysql 2048 Jan 4 16:49 user.MYI[root@tong2 ~]# ll /var/lib/mysql/mysql/user.*
-rw-r--r--. 1 mysql mysql 10684 Jan 4 16:49 /var/lib/mysql/mysql/user.frm
-rw-r--r--. 1 mysql mysql 784 Jan 4 16:49 /var/lib/mysql/mysql/user.MYD
-rw-r--r--. 1 mysql mysql 2048 Jan 4 16:49 /var/lib/mysql/mysql/user.MYI
[root@tong2 ~]# 6.恢复数据[root@tong2 ~]# rm -rf /var/lib/mysql/tong
[root@tong2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 29
Server version: 5.6.21-log MySQL Community Server （GPL）Copyright （c） 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type "help;" or "h" for help. Type "c" to clear the current input statement.mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set （0.00 sec）mysql> exit
Bye[root@tong2 ~]# cp -arp /opt/tong /var/lib/mysql/ --将备份的数据移到mysql数据根目录
[root@tong2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 30
Server version: 5.6.21-log MySQL Community Server （GPL）Copyright （c） 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type "help;" or "h" for help. Type "c" to clear the current input statement.mysql> u tong
Database changed
mysql> show tables;
+----------------+
| Tables_in_tong |
+----------------+
| t |
+----------------+
2 rows in set （0.00 sec）mysql> exit
Bye[root@tong2 ~]#MySQL备份和恢复具体实施 http://www.linuxidc.com/Linux/2012-12/76257.htmMySQL备份：mylvmbackup介绍与使用 http://www.linuxidc.com/Linux/2013-11/92423.htmLinux下通过mysqldump备份MySQL数据库成sql文件 http://www.linuxidc.com/Linux/2013-02/79793.htmLinux中使用mysqldump对MySQL数据库进行定时备份 http://www.linuxidc.com/Linux/2012-03/56063.htm本文永久更新链接地址