MySQL backup tool-mysqlhotcopy

MySQL backup tool-mysqlhotcopy

Mysqlhotcopy uses 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. It is a physical backup, but it can only be used to back up the MyISAM storage engine and run on the machine where the database directory is located. different from mysqldump backup, mysqldump is a logical backup that runs SQL statements. install the corresponding software dependency package before using the mysqlhotcopy command.

1. install the software package on which mysqlhotcopy depends (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. view the help information of mysqlhotcopy

[Root @ tong2 ~] # Vim/usr/my. cnf -- Add the following parameters to the configuration file:

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

[Root @ tong2 ~] #/Etc/init. d/mysql restart -- restart the service
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.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) -- Do not overwrite the previously backed up Files
-- Addtodest don't rename target dir if it exists, just add files to it -- Incremental Backup
-- Keepold don't delete previous (now renamed) target when done
-- Noindices don't include full index files in copy -- do not back up index files
-- Method = # method for copy (only "cp" currently supported)

-Q, -- quiet be silent failed t for errors
-- Debug enable debug -- enable debug output
-N, -- dryrun report actions without doing them

-- Regexp = # copy all databases with names matching regexp -- use a regular expression
-- Suffix = # suffix for names of copied databases
-- Checkpoint = # insert checkpoint entry into specified db. table -- insert a checkpoint entry
-- Flushlog flush logs once all tables are locked -- refresh the log after all tables are locked.
-- Resetmaster reset the binlog once all tables are locked -- once the lock table resets the binlog File
-- Resetslave reset the master.info once all tables are 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
[Root @ tong2 ~] #

 

3. Back up a database to a directory

[Root @ tong2 ~] # Mysqlhotcopy-u root-p system tong/opt/

[Root @ tong2 ~] # Ll/opt/tong/
Total 112
-Rw ----. 1 mysql 15 Jan 5 q. isl
-Rw ----. 1 mysql 8554 Jan 4 t. frm
-Rw ----. 1 mysql 98304 Jan 4 t. ibd
[Root @ tong2 ~] # Ll/var/lib/mysql/tong
Total 112
-Rw ----. 1 mysql 15 Jan 5 q. isl
-Rw ----. 1 mysql 8554 Jan 4 t. frm
-Rw ----. 1 mysql 98304 Jan 4 t. ibd
[Root @ tong2 ~] #

 

4. Back up multiple databases to one directory

[Root @ tong2 ~] # Mysqlhotcopy-u root-p system tong mysql/opt/tong

[Root @ tong2 ~] # Ll/opt/
Total 8
Drwxr-x ---. 2 mysql 4096 Jan 5 mysql
Drwxr-x ---. 2 mysql 4096 Jan 5 tong

[Root @ tong2 ~] # Ll/var/lib/mysql/{mysql, tong}-d
Drwxr-xr-x. 2 mysql 4096 Jan 5/var/lib/mysql
Drwxr-xr-x. 2 mysql 4096 Jan 5/var/lib/mysql/tong
[Root @ tong2 ~] #

 

5. Back up a table in the database

[Root @ tong2 ~] # Mysqlhotcopy-u root-p system mysql./user * // opt/

[Root @ tong2 ~] # Ll/opt/mysql/
Total 20
-Rw-r --. 1 mysql 10684 Jan 4 user. frm
-Rw-r --. 1 mysql 784 Jan 4 user. MYD
-Rw-r --. 1 mysql 2048 Jan 4 user. MYI

[Root @ tong2 ~] # Ll/var/lib/mysql/user .*
-Rw-r --. 1 mysql 10684 Jan 4/var/lib/mysql/user. frm
-Rw-r --. 1 mysql 784 Jan 4/var/lib/mysql/user. MYD
-Rw-r --. 1 mysql 2048 Jan 4/var/lib/mysql/user. MYI
[Root @ tong2 ~] #

 

6. Restore data

[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,201 4, 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/-- move the backup data to the mysql DATA root directory
[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,201 4, 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 ~] #

Implementation of MySQL backup and recovery

MySQL backup: mylvmbackup introduction and use

Using mysqldump in Linux to back up a MySQL database as an SQL File

Use mysqldump in Linux to regularly back up MySQL Databases

This article permanently updates the link address: