MySQL backup and recovery-cold backup-MySQL

MySQL backup and recovery-cold backup bitsCN.com

Cold backup for MySQL backup and recovery

1. what is cold backup?

In one sentence, cold backup refers to stopping database services, such as MySQL and Oracle, and then backing up the data Directory using the copy, package, or compression commands. If the data is abnormal, you can recover the data through backup. Cold backup generally requires customization plans, such as when to back up and which data is backed up each time. However, because such backups occupy too much space and are not suitable for environments with large data volumes, the production environment is rarely used.

Secondary cold backup

3. cold standby experiment

Step 1: Create a test database and insert test data

Mysql> use larrydb; Database changedmysql> show tables; + ------------------- + | Tables_in_larrydb | + response + | access | + ----------------- + 1 row in set (0.00 sec) mysql> drop table access; query OK, 0 rows affected (0.00 sec) mysql> clearmysql> show tables; Empty set (0.00 sec) mysql> create table class (-> cid int, -> cname varchar (30); Query OK, 0 rows affected (0.01 sec) mysql> show cr Eate 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> create table stu (-> sid int,-> sname varchar (30),-> cid int) engine = myisam; Query OK, 0 rows affected (0.00 sec) m Ysql> 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 = utf81 row in set (0.00 sec) ERROR: No query specifiedmysql> insert into class values (1, 'Linux '), (2, 'Oracle '); query OK, 2 rows affected (0.00 s Ec) Records: 2 Duplicates: 0 Warnings: 0 mysql> desc 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> insert into stu values (1, 'larry01', 1), (2, 'larry02', 2); Query OK, 2 rows Ffected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from stu; + ------ + --------- + ------ + | sid | sname | cid | + ------ + --------- + ------ + | 1 | larry01 | 1 | 2 | larry02 | 2 | + ------ + --------- + ------ + Step 2, stop MySQL [root @ serv01 ~] #/Etc/init. d/mysqld stopShutting down MySQL... SUCCESS! Step 3: Create a backup directory and modify the owner and group [root @ serv01 ~] # Mkdir/databackup [root @ serv01 ~] # Chown mysql. mysql/databackup/-R [root @ serv01 ~] # Ll/databackup/-ddrwxr-xr-x. 2 mysql 4096 Sep 10/databackup/[root @ serv01 ~] # Step 4 of cd/databackup/cold standby (use the tar command) [root @ serv01 databackup] # Step 5 of tar-cvPzf mysql01.tar.gz to test whether the cold standby data is normal, delete all data [root @ serv01 databackup] # rm-rf/usr/local/mysql/data/* step 6, after deleting all data, the database cannot start [root @ serv01 databackup] #/etc/init. d/mysqld startStarting MySQL .. ERROR! The server quit without updating PID file (/usr/local/mysql/data/serv01.host.com. pid ). step 7: restore data (use the tar command) [root @ serv01 databackup] # tar-xvPf mysql01.tar.gz step 8, start MySQL, then log on to MySQL to check whether data is lost, if the data is normal, [root @ serv01 databackup] #/etc/init. d/mysqld startStarting MySQL .. SUCCESS! [Root @ serv01 ~] # MysqlWelcome to the MySQL monitor. commands end with; or/g. your MySQL connection id is 1 Server version: 5.5.29-log Source distributionCopyright (c) 2000,201 2, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or'/h' for help. type '/C' to clear the current input statement. mysql> use larrydb; Database changedmysql> 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)

BitsCN.com