mysql備份工具之mysqlhotcopy

標籤：mysql備份工具之mysqlhotcopy

     mysqlhotcopy使用lock tables、flush tables和cp或scp來快速備份資料庫.它是備份資料庫或單個表最快的途徑,完全屬於物理備份,但只能用於備份MyISAM儲存引擎和運行在資料庫目錄所在的機器上.與mysqldump備份不同,mysqldump屬於邏輯備份,備份時是執行的sql語句.使用mysqlhotcopy命令前需要要安裝相應的軟體依賴包.

 

1.安裝mysqlhotcopy所依賴的軟體包(perl-DBD,DBD-mysql)

[[email protected] ~]# yum install perl-DBD* -y

[[email protected] ~]# wget https://cpan.metacpan.org/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.029.tar.gz

[[email protected] ~]# tar xvf DBD-mysql-4.029.tar.gz

[[email protected] ~]# cd DBD-mysql-4.029
[[email protected] DBD-mysql-4.029]# perl Makefile.PL

[[email protected] DBD-mysql-4.029]# make

[[email protected] DBD-mysql-4.029]# make install

[[email protected] DBD-mysql-4.029]# echo $?
0
[[email protected] DBD-mysql-4.029]# cd
[[email protected] ~]#

 

2.查看mysqlhotcopy的協助資訊

[[email protected] ~]# vim /usr/my.cnf     --在設定檔中添加如下參數

[mysqlhotcopy]
interactive-timeout
host=localhost
user=root
password=system
port=3306

[[email protected] ~]# /etc/init.d/mysql restart      --重啟服務
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

[[email protected] ~]# mysqlhotcopy  --help
Warning: /usr/bin/mysqlhotcopy is deprecated and will be removed in a future version.

/usr/bin/mysqlhotcopy Ver 1.23

Usage: /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
[[email protected] ~]#

 

3.備份一個資料庫到一個目錄中

[[email protected] ~]# mysqlhotcopy -u root -p system tong /opt/

[[email protected] ~]# 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
[[email protected] ~]# 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
[[email protected] ~]#

 

4.備份多個資料庫到一個目錄中

[[email protected] ~]# mysqlhotcopy -u root -p system tong mysql /opt/tong

[[email protected] ~]# 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

[[email protected] ~]# 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
[[email protected] ~]#

 

5.備份資料庫中某一個表

[[email protected] ~]# mysqlhotcopy -u root -p system mysql./user*/ /opt/

[[email protected] ~]# 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

[[email protected] ~]# 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
[[email protected] ~]#

 

6.恢複資料

[[email protected] ~]# rm -rf /var/lib/mysql/tong
[[email protected] ~]# 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

[[email protected] ~]# cp -arp /opt/tong /var/lib/mysql/
[[email protected] ~]# 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

[[email protected] ~]#

 

本文出自 “一起走過的日子” 部落格，請務必保留此出處http://tongcheng.blog.51cto.com/6214144/1599337

mysql備份工具之mysqlhotcopy