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 quickest way to back up a database or a single table, which is completely physical, but only used to back up MyISAM storage engines and run in databases On the machine where the directory resides. 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--helpwarning:/usr/bin/mysqlhotcopy is deprecated and'll be removed in a future ve Rsion.

/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)--do not overwrite 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-no backup 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), 2014,oracleand/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)

[[email protected] ~]# cp-arp/opt/tong/var/lib/mysql/--Move the backed up data to the MySQL data root directory

[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)

[Email protected] ~]#

• This article is from: Linux Tutorial Network

Mysqlhotcopy of MySQL Backup tool