Mysql Backup and Recovery cold standby (1) _mysql

To summarize Lengbei in one sentence is to stop the database services, such as mysql,oracle, and then use a copy, package, or compression command to back up the data directory. If there is an exception to the data, you can restore it through backup data. Cold standby usually requires custom planning, such as when to do backups, what data to back up every time, and so on. However, because such a backup takes up too much space, it is not necessarily suitable for large amount of data, so the production environment is seldom used.

Cold standby schematic

Cold Preparation Experiment

The first step is to create a test database and insert test data

mysql> use Larrydb;
Database changed mysql> show tables; +-------------------+
|
Tables_in_larrydb | +-------------------+
|
Access |
+-------------------+ 1 row in Set (0.00 sec) mysql> drop table access;
Query OK, 0 rows Affected (0.00 sec) mysql> clear mysql> show tables;
Empty Set (0.00 sec) mysql> mysql> Create TABLE class (-> CID int,-> CNAME (30));
Query OK, 0 rows affected (0.01 sec) mysql> Show create TABLE class \g; 1. Row *************************** table:class Create table:create Table ' class ' (' cid ' int () DEFAULT NULL, ' cname ' V Archar () default NULL) Engine=innodb default charset=latin1 1 row in Set (0.00 sec) Error:no query specified mysql& Gt
CREATE TABLE Stu (-> sid int,-> sname varchar (),-> CID int) Engine=myisam;
Query OK, 0 rows Affected (0.00 sec) mysql> 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=utf8 1 row in Set (0.00 sec) error:no Query specified mysql> insert into class Val
UEs (1, ' Linux '), (2, ' Oracle ');
Query OK, 2 rows Affected (0.00 sec) Records:2 duplicates:0 warnings:0 mysql> class; +-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default |
Extra | +-------+-------------+------+-----+---------+-------+
| CID | Int (11) | YES | | NULL | | | CNAME | varchar (30) | YES | |
NULL | |
+-------+-------------+------+-----+---------+-------+ 2 rows in Set (0.00 sec) mysql> desc Stu; +-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default |
Extra | +-------+-------------+------+-----+---------+-------+
| Sid | Int (11) | YES | | NULL | | | sname | varchar (30) | YES | | NULL | | | CID | Int (11) | YES | |
NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in Set (0.00 sec) mysql> inserts into Stu values (1, ' larry01 ', 1), (2, ' larry02 ', 2);
Query OK, 2 rows Affected (0.00 sec) records:2 duplicates:0 warnings:0 mysql> select * from Stu; +------+---------+------+
| Sid | sname |
CID | +------+---------+------+
| 1 | Larry01 | 1 | | 2 | larry02 |
2 |
 

 +------+---------+------+

Step two, shut down MySQL.

[Root@serv01 ~]#/etc/init.d/mysqld stop
shutting down MySQL ... success! 

The third step is to create a backup directory and modify the owner and owning group

[Root@serv01 ~]# mkdir/databackup
[root@serv01 ~]# chown mysql.mysql/databackup/-R
[root@serv01 ~]# Ll/databa ckup/
-D drwxr-xr-x. 2 mysql mysql 4096 Sep 17:46/databackup/
[root@serv01 ~]# cd/databackup/
 

Step fourth, Lengbei (using the tar command)

[Root@serv01 databackup]# Tar-cvpzf mysql01.tar.gz 

Step Fifth, test whether the Lengbei data is normal, we delete all the data

[Root@serv01 databackup]# rm-rf/usr/local/mysql/data/*

Step sixth, the database cannot be started after all data has been deleted

[Root@serv01 databackup]#/etc/init.d/mysqld start
starting MySQL. error! The server quit without updating PID file (/usr/local/mysql/data/serv01.host.com.pid).

step seventh, recover the data (using the TAR command)

[Root@serv01 databackup]# TAR-XVPF mysql01.tar.gz 

The eighth step, start MySQL, and then log in to MySQL, see if the data is missing, if the data normally represents Lengbei success

[Root@serv01 databackup]#/etc/init.d/mysqld start
starting MySQL. Success! 

[root@serv01 ~]# MySQL
Welcome to the MySQL monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server version:5.5.29-log Source distribution

Copyright (c), Oracle and/ or its affiliates. All rights reserved.

Oracle is a registered trademark to Oracle Corporation and/or its
affiliates. The other names may is trademarks of their respective
owners.

Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> use Larrydb;
Database changed
mysql> select * from class;
+------+--------+
| cid | cname |
+------+--------+
| 1 | linux |
| 2 | Oracle |
+------+--------+
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)

The above is to achieve the entire MySQL Lengbei process, we have no general knowledge of cold, I hope this article can be helpful to everyone's study.