Mysqlhotcopy of MySQL Backup tool

Mysqlhotcopy Use the lock tables, flush tables, and CP or SCP to quickly back up the database. It is the fastest way to back up a database or a single table, completely physical, but only for backup MyISAM storage engines and running in the database directory on the machine. Unlike mysqldump backups, mysqldump is a logical backup, which is a SQL statement executed at backup time. You need to install the appropriate software dependency package before using the Mysqlhotcopy command.

1. Install the package that mysqlhotcopy depends on (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. View Mysqlhotcopy's Help information

[[email protected] ~]# VIM/USR/MY.CNF--Add the following parameters to the configuration file

[Mysqlhotcopy]
Interactive-timeout
Host=localhost
User=root
Password=system
port=3306

[Email protected] ~]#/etc/init.d/mysql Restart--Restart service
Shutting down MySQL. success!
Starting MySQL. success!

[Email protected] ~]# mysqlhotcopy--help
Warning:/usr/bin/mysqlhotcopy is deprecated and would 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 has FLUSH TABLES with READ LOCK fully implemented.

 --allowold           don ' t abort if Target dir already Exists (rename it _old)     --no overwriting of previously backed up files
 --addtodest           don ' t rename target dir if it exists, just add files to it     --an incremental backup
 --keepold            don ' t delete previous (now Renamed) Target when do
 --noindices          don ' t include Full index files in copy         --do not back up index file
 --method=#            method for copy (only "CP" currently supported)

-Q,--quiet is silent except for errors
--debug Enable debug--Enables debugging output
-N,--dryrun report actions without doing them

--regexp=# copy all databases with names matching regexp--using regular expressions
--suffix=# suffix for names of copied databases
--checkpoint=# Insert checkpoint entry into specified db.table--insert Checkpoint entry
--flushlog Flush logs Once all tables is locked-refresh log after all tables are locked
--resetmaster Reset the Binlog Once all tables is locked--once the lock table resets the Binlog file
--resetslave Reset the Master.info Once all tables is locked--once the lock table resets the Master.info file

--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. Back up a database to a directory

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

[Email protected] ~]# ll/opt/tong/
Total 112
-RW-RW----. 1 MySQL MySQL 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 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. Back up multiple databases into one directory

[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. Back up a table in a database

[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. Recovering data

[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), the Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names 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), the Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names 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] ~]#

This article is from the "Days Together" blog, please be sure to keep this source http://tongcheng.blog.51cto.com/6214144/1599337

Mysqlhotcopy of MySQL Backup tool