Hot copy of Mysql Backup and Recovery (4) _mysql

In the last article we mentioned hot standby, which is backup when MySQL or other database services are running. This article shares another method of backup, which is hot copy. Hot copies are similar to hot spares, except that they use the mysqldump command and the hot copy uses the Mysqlhotcopy command. The advantage of hot copy is to support service running backup, fast, good performance; The disadvantage is that only myisam tables can be backed up and InnoDB tables cannot be backed up. Therefore, in the production environment should be used as appropriate.

Diagram

Hot Standby Simulation

The first step, the hot copy

[Root@serv01 databackup]# mysqlhotcopy-uroot-p123456--database larrydb > Larrydb_hostcopy.sql
Can ' t locate DBI . PM in @INC (@INC contains:/usr/local/lib64/perl5/usr/local/share/perl5/usr/lib64/perl5/vendor_perl/usr/share/ Perl5/vendor_perl/usr/lib64/perl5/usr/share/perl5.) At/usr/local/mysql/bin/mysqlhotcopy line.
BEGIN failed--compilation aborted at/usr/local/mysql/bin/mysqlhotcopy line 25.

The second step, the error. Because this command is written in Perl or this command requires Perl support, you need to install Perl

[Root@serv01 databackup]# yum install perl*-y

The third step is to larrydb a hot copy of the database

[Root@serv01 databackup]# mysqlhotcopy--help

#第一种写法

[Root@serv01 databackup]# mysqlhotcopy--user=root--password=123456 larrydb/databackup/

#第二种写法

[Root@serv01 databackup]# mysqlhotcopy-u root-p 123456 larrydb/databackup/

2 tables with read lock (' flushed B '. ' Class ', ' Larrydb '. ' Stu ') in 0 seconds.
Locked 0 Views () in 0 seconds.
Copying 5 files ...
Copying indices for 0 files ...
Unlocked tables.
Mysqlhotcopy copied 2 tables (5 files) in 0 seconds (0 seconds overall).

step fourth, simulate data loss

[Root@serv01 databackup]# ll larrydb Total 36-RW-RW----. 1 mysql mysql 8590 Sep 19:07 class.frm-rw-rw----. 1 19:07 db.opt-rw-rw----. mysql mysql-Sep 1 mysql mysql 8618 Sep 19:07 stu.frm-rw-rw----. 1 MySQL MySQL 19:07 stu. MYD-RW-RW----. 1 MySQL mysql 1024 Sep 19:07 stu.
Myi mysql> use Larrydb;
Database changed mysql> show tables; +-------------------+
|
Tables_in_larrydb | +-------------------+
| Class | |
Stu |
+-------------------+ 2 rows in Set (0.00 sec) mysql> Show create TABLE class \g; 1. Row *************************** table:class Create table:create Table ' class ' (' cid ' int () DEFAULT NULL, ' cname ' varchar default NULL ' engine=innodb default charset=latin1 1 row in Set (0.00 sec) Error:no query specified Mys
Ql> Show create table Stu \g; 1. Row *************************** table:stu Create table:create Table ' stu ' (' Sid ' Int () DEFAULT NULL, ' sname ' varchar () default NULL, ' CID ' int (one) default null) Engine=myisam default Charset=latin1 1 row in Set (0.0
0 SEC) error:mysql> drop table class,stu;
Query OK, 0 rows affected (0.01 sec) mysql> Show tables; Empty Set (0.00 sec) #这样删除会出错, do not delete [root@serv01 databackup]# rm-rf/usr/local/mysql/data/larrydb/* [root@serv01 datab

 ackup]# rm-rf/usr/local/mysql/data/larrydb/

&NBSP
Fifth step, restoring data

[ROOT@SERV01 databackup]# cp larrydb/usr/local/mysql/data/-ARVF ' larrydb '-> '/usr/local/mysql/data/larrydb ' Larrydb/stu. Myi '-> '/usr/local/mysql/data/larrydb/stu. Myi ' Larrydb/stu. MyD '-> '/usr/local/mysql/data/larrydb/stu. MyD ' larrydb/stu.frm '-> '/usr/local/mysql/data/larrydb/stu.frm ' larrydb/db.opt '-> '/usr/local/mysql/data/
Larrydb/db.opt ' larrydb/class.frm '-> '/usr/local/mysql/data/larrydb/class.frm ' mysql> use Larrydb;
Database changed mysql> show tables; +-------------------+
|
Tables_in_larrydb | +-------------------+
| Class | |
Stu |
+-------------------+ 2 rows in Set (0.00 sec) mysql> SELECT * from class;
ERROR 1146 (42S02): Table ' larrydb.class ' doesn ' t exist mysql> select * from Stu; +------+---------+------+
| Sid | sname |
CID |  +------+---------+------+
| 1 |  Larry01 |  1 | | 2 |  larry02 |
2 |
+------+---------+------+ 2 rows in Set (0.00 sec) mysql> drop database larrydb; Query OK, 2 rows affected (0.00 SEc) #再次导入 [root@serv01 databackup]# mysql-uroot-p123456 < larrydb.sql mysql> use Larrydb;
Database changed mysql> show tables; +-------------------+
|
Tables_in_larrydb | +-------------------+
| Class | |
Stu |
+-------------------+ 2 rows in Set (0.00 sec) mysql> select * from Stu; +------+---------+------+
| Sid | sname |
CID |  +------+---------+------+
| 1 |  Larry01 |  1 | | 2 |  larry02 |
2 |
+------+---------+------+ 2 rows in Set (0.00 sec) mysql> SELECT * from class; +------+--------+
| CID |
CNAME |  +------+--------+
| 1 |  Linux | | 2 |
Oracle |
 +------+--------+ 2 rows in Set (0.00 sec)

This article describes another backup-hot copy, and hot spare is different from the use of the command, thermal copy using the Mysqlhotcopy command, I hope you can do it yourself!