Hot copy of MySQL backup and recovery

In the previous article, we mentioned hot standby (), which is to back up data when MySQL or other database services are running. This article shares another backup method, that is, hot copy. Hot copy is similar to hot backup, but hot backup uses the mysqldump command, and hot copy uses the mysqlhotcopy command. The advantage of hot copy is that it supports backup during service running, which is fast and has good performance. The disadvantage is that it can only back up MyIsam tables and cannot back up InnoDB tables. Therefore, it should be used in the production environment as appropriate.

 

II

Three-Hot Standby Simulation

 

 

Step 1: hot copy

[root@serv01 databackup]# mysqlhotcopy -uroot -p123456 --database larrydb > larrydb_hostcopy.sqlCan'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 25.BEGIN failed--compilation aborted at /usr/local/mysql/bin/mysqlhotcopy line 25.

 

Step 2: Report an error. Because this command is written in perl or must be supported by perl, install perl

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

 

Step 3: Perform hot copy on the database larrydb

[Root @ serv01 databackup] # mysqlhotcopy -- help # first writing method [root @ serv01 databackup] # mysqlhotcopy -- user = root -- password = 123456 larrydb/databackup/# second writing method [root @ serv01 databackup] # mysqlhotcopy-u root-p 123456 larrydb/databackup/Flushed 2 tables with read lock ('larrydb '. '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 4: Simulate data loss

[Root @ serv01 databackup] # ll larrydbtotal 36-rw-rw ----. 1 mysql 8590 Sep 10 class. frm-rw ----. 1 mysql 65 Sep 10 db. opt-rw ----. 1 mysql 8618 Sep 10 stu. frm-rw ----. 1 mysql 48 Sep 10 :07 stu. MYD-rw ----. 1 mysql 1024 Sep 10 stu. MYImysql> use larrydb; Database changedmysql> show tables; + ----------------- + | Tables_in_larrydb | + bytes + | class | stu | + ------------------- + 2 rows in set (0.00 sec) mysql> show create table class \ G; ***************************** 1. row *************************** Table: classCreate Table: create table 'class' ('cid' int (11) default null, 'cname' varchar (30) default null) ENGINE = InnoDB default charset = latin11 row in set (0.00 sec) ERROR: No query specifiedmysql> show create table stu \ G; * *************************** 1. row *************************** Table: stuCreate Table: create table 'stu' ('sid' int (11) default null, 'sname' varchar (30) default null, 'cid' int (11) default null) ENGINE = MyISAM default charset = latin11 row in set (0.00 sec) ERROR: mysql> drop table class, stu; Query OK, 0 rows affected (0.01 sec) mysql> show tables; empty set (0.00 sec) # deletion will fail, do not delete [root @ serv01 databackup] # rm-rf/usr/local/mysql/data/larrydb/* [root @ serv01 databackup] # rm-rf/usr/local/ mysql/data/larrydb/

For more details, please continue to read the highlights on the next page:

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

Disk management-LVM

1 2 Next Page