MySQL backup and recovery-hot standby (3) _ MySQL

Hot backup uses the mysqldump command for backup. This tool is a built-in MySQL backup and recovery tool with powerful functions. it can back up the entire database and back up multiple databases, you can back up a single table or several tables. For more information, see the last two articles (cold backup for MySQL backup and recovery, mySQL backup and recovery in the real environment using cold backup), we mention the cold backup and real environment using cold backup. From this article, let's look at hot standby. Obviously, hot standby and cold standby are two opposite concepts. cold standby refers to stopping database services, such as MySQL and Oracle, and then backing up the data Directory using the copy, package, or compression commands; it is easy to think that hot backup is performed when MySQL or other database services are running. However, there is a problem here, because when the production database is running, there is read/write to the database, the read/write frequency may be high or low, regardless of the frequency, the data backed up is inconsistent with the data in the production database. During the hot standby period, it is unrealistic for others to operate, because you cannot terminate user access to Web programs. To solve this problem, you can use the specified backup policy, such as the time range for backup and the data to be backed up. In short, to ensure data integrity and consistency, remember that backup is more important than everything else !!!
Hot backup can be used to back up multiple databases. you can back up a single table or several tables. However, you cannot back up multiple tables in multiple databases at the same time, but only backup separately. Next we will take a look at the hot standby and conduct hot standby simulation.

Hot standby simulation

1. back up a single database
Step 1: Remove LVM snapshots. (Skip this step if no one is created)

[root@serv01 data]# lvremove /dev/data/smydata Do you really want to remove active logical volume smydata? [y/n]: y Logical volume "smydata" successfully removed

Step 2: Set the MySQL password

mysql> set password=password("123456");Query OK, 0 rows affected (0.00 sec)

Step 3: Check whether MySQL is started. Because it is a hot standby, MySQL service is required to start

[root@serv01 data]# /etc/init.d/mysqld status SUCCESS! MySQL running (2664)

Step 4: export a single database

[Root @ serv01 data] # cd/databackup/# The essence is to export SQL [root @ serv01 databackup] # mysqldump-uroot-p123456 -- database larrydb -- MySQL dump 10.13 Distrib 5.5.29, for Linux (x86_64) ---- Host: localhost Database: larrydb -- -------------------------------------------------------- Server version 5.5.29-log /*! 40101 SET @ OLD_CHARACTER_SET_CLIENT = @ CHARACTER_SET_CLIENT */;/*! 40101 SET @ OLD_CHARACTER_SET_RESULTS = @ CHARACTER_SET_RESULTS */;/*! 40101 SET @ OLD_COLLATION_CONNECTION = @ COLLATION_CONNECTION */;/*! 40101 set names utf8 */;/*! 40103 SET @ OLD_TIME_ZONE = @ TIME_ZONE */;/*! 40103 SET TIME_ZONE = '+ '*/;/*! 40014 SET @ OLD_UNIQUE_CHECKS = @ UNIQUE_CHECKS, UNIQUE_CHECKS = 0 */;/*! 40014 SET @ OLD_FOREIGN_KEY_CHECKS = @ FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0 */;/*! 40101 SET @ OLD_ SQL _MODE = @ SQL _MODE, SQL _MODE = 'no _ AUTO_VALUE_ON_ZERO '*/;/*! 40111 SET @ OLD_ SQL _NOTES = SQL _notes, SQL _NOTES = 0 */; ---- Current Database: 'larrydb' -- CREATE DATABASE /*! 32312 if not exists */'larrydb '/*! 40100 default character set latin1 */; USE 'larrydb'; ---- Table structure for table 'class' -- drop table if exists 'class ';/*! 40101 SET @ saved_cs_client = @ character_set_client */;/*! 40101 SET character_set_client = utf8 */; create table 'class' ('CID' int (11) default null, 'cname' varchar (30) default null) ENGINE = InnoDB default charset = latin1 ;/*! 40101 SET character_set_client = @ saved_cs_client */; ---- Dumping data for table 'class' -- lock tables 'class' WRITE ;/*! 40000 alter table 'class' disable keys */; insert into 'class' VALUES (1, 'Linux '), (2, 'Oracle ');/*! 40000 alter table 'class' enable keys */; unlock tables; ---- Table structure for table 'Stu' -- drop table if exists 'Stu ';/*! 40101 SET @ saved_cs_client = @ character_set_client */;/*! 40101 SET character_set_client = utf8 */; create table 'Stu' ('Sid 'int (11) default null, 'sname' varchar (30) default null, 'CID' int (11) default null) ENGINE = MyISAM default charset = latin1 ;/*! 40101 SET character_set_client = @ saved_cs_client */; ---- Dumping data for table 'Stu' -- lock tables 'Stu' WRITE ;/*! 40000 alter table 'std' disable keys */; insert into 'std' VALUES (1, 'larry01', 1), (2, 'larry02', 2 );/*! 40000 alter table 'Stu' enable keys */; unlock tables ;/*! 40103 SET TIME_ZONE = @ OLD_TIME_ZONE */;/*! 40101 SET SQL _MODE = @ OLD_ SQL _MODE */;/*! 40014 SET FOREIGN_KEY_CHECKS = @ OLD_FOREIGN_KEY_CHECKS */;/*! 40014 SET UNIQUE_CHECKS = @ OLD_UNIQUE_CHECKS */;/*! 40101 SET CHARACTER_SET_CLIENT = @ OLD_CHARACTER_SET_CLIENT */;/*! 40101 SET CHARACTER_SET_RESULTS = @ OLD_CHARACTER_SET_RESULTS */;/*! 40101 SET COLLATION_CONNECTION = @ OLD_COLLATION_CONNECTION */;/*! 40111 SET SQL _NOTES = @ OLD_ SQL _NOTES */; Dump completed on 18:56:06 # save the output result to the file [root @ serv01 databackup] # mysqldump-uroot-p123456 -- database larrydb> larrydb. SQL

Step 5: simulate data loss, enter MySQL, and delete the database

[root@serv01 data]# mysql -uroot -p123456Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.5.29-log Source distributionCopyright (c) 2000, 2012, 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> show databases;+--------------------+| Database  |+--------------------+| information_schema || crm  || game  || hello  || larrydb  || mnt  || mysql  || performance_schema || test  |+--------------------+9 rows in set (0.00 sec)mysql> drop database larrydb;Query OK, 2 rows affected (0.01 sec)mysql> show databases;+--------------------+| Database  |+--------------------+| information_schema || crm  || game  || hello  || mnt  || mysql  || performance_schema || test  |+--------------------+8 rows in set (0.00 sec)mysql> exitBye

Step 6: import data

[Root @ serv01 databackup] # mysql-uroot-p123456
 
  

  
 
Step 7: log on to MySQL and check whether the data is normal.

  

  
[root@serv01 data]# mysql -uroot -p123456Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.5.29-log Source distributionCopyright (c) 2000, 2012, 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> show database;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1mysql> show databases;+--------------------+| Database  |+--------------------+| information_schema || crm  || game  || hello  || larrydb  || mnt  || mysql  || performance_schema || test  |+--------------------+9 rows in set (0.00 sec)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)
  

  
Back up multiple databases
Step 1: check the databases

  

  
Mysql> show databases; + -------------------- + | Database | + -------------------- + | information_schema | crm | game | hello | larrydb | mnt | mysql | performance_schema | test | + ---------------- + 9 rows in set (0.00 sec) mysql> use game; Database changedmysql> show tables; + ---------------- + | Tables_in_game | + ---------------- + | country | fight | hero | + ---------------- + 3 rows in set (0.00 sec) mysql> select * from country; + ----- + --------- + ---------- + | cno | cname | location | + ----- + --------- + ---------- + | 10 | caowei | luoyang | 20 | shuhan | chengdou | 30 | sunwu | nanjing | 40 | houhan | luoyang | 50 | beisong | kaifeng | 60 | Wei Guo | luoyang | + ----- + --------- + ---------- + 6 rows in set (0.00 sec)
  

  
Step 2: Back up multiple databases

  

  
[root@serv01 databackup]# mysqldump -uroot -p123456 --databases larrydb game > larrydb_game.sql[root@serv01 databackup]# ll larrydb_game.sql -rw-r--r--. 1 root root 6159 Sep 10 19:05 larrydb_game.sql
  

  
Step 3: simulate data loss.

  

  
mysql> drop database game;Query OK, 3 rows affected (0.01 sec)mysql> drop database larrydb;Query OK, 2 rows affected (0.00 sec)mysql> use crm;Database changedmysql> show tables;+---------------+| Tables_in_crm |+---------------+| test  |+---------------+1 row in set (0.00 sec)mysql> select * from test;Empty set (0.00 sec)mysql> drop database crm;Query OK, 1 row affected (0.00 sec)
  

  

  
Step 4: restore data

  

  
[root@serv01 databackup]# mysql -uroot -p123456 < larrydb_game.sql 
  

  
 
Step 5: Check whether the data is normal

  

  
[Root @ serv01 data] # mysql-uroot-p123456Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 9 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> show databases; + Rule + | Database | + ------------------ + | information_schema | game | hello | larrydb | mnt | mysql | performance_schema | test | + ------------------ + 8 rows in set (0.00 sec) mysql> use game; Database changedmysql> select * from country; + ----- + --------- + ---------- + | cno | cname | location | + ----- + --------- + ---------- + | 10 | caowei | luoyang | 20 | shuhan | chengdou | 30 | sunwu | nanjing | 40 | houhan | luoyang | 50 | beisong | kaifeng | 60 | Wei Guo | luoyang | + ----- + --------- + ---------- + 6 rows in set (0.00 sec) mysql> use larrydb; Database changedmysql> select * from class; + ------ + -------- + | cid | cname | + ------ + -------- + | 1 | linux | 2 | oracle | + ------ + -------- + 2 rows in set (0.00 sec)
  

  
 
Back up all databases

  

  
[root@serv01 databackup]# mysqldump --help | grep all-databaseOR mysqldump [OPTIONS] --all-databases [OPTIONS] -A, --all-databases Dump all the databases. This will be same as --databases   --databases= or --all-databases), the logs will be   --all-databases or --databases is given.all-databases   FALSE[root@serv01 databackup]# mysqldump -uroot -p123456 --all-databases > all_databases.sql[root@serv01 databackup]# ll all_databases.sql -h-rw-r--r--. 1 root root 506K Sep 10 19:16 all_databases.sql
  

  
Back up one or more tables
Step 1: back up a table and several tables

  

  
[root@serv01 databackup]# mysqldump game hero country -uroot -p123456 > game_hero_country.sql[root@serv01 databackup]# ll game_hero_country.sql -rw-r--r—. 1 root root 3955 Sep 10 19:11 game_hero_country.sql
  

  
Step 2: simulate data loss

  

  
mysql> use game;Database changedmysql> show tables;+----------------+| Tables_in_game |+----------------+| country || fight  || hero  |+----------------+3 rows in set (0.00 sec)mysql> drop table hero;Query OK, 0 rows affected (0.00 sec)mysql> drop table country;Query OK, 0 rows affected (0.00 sec) 
  

  
Step 3: Check whether the data is normal

  

  
[Root @ serv01 databackup] # mysql-uroot-p123456 <game_hero_country. SQL ERROR 1046 (3D000) at line 22: no database selected [root @ serv01 databackup] # mysql-uroot-p123456 -- database game <game_hero_country. SQL [root @ serv01 databackup] # mysql-uroot-p123456-e "select * from game. country "+ ----- + --------- + ---------- + | cno | cname | location | + ----- + --------- + ---------- + | 10 | caowei | luoyang | 20 | shuhan | chengdou | 30 | sunwu | nanjing | 40 | houhan | luoyang | 50 | beisong | kaifeng | 60 | Wei Guo | luoyang | + ----- + --------- + ---------- +
  

  

  
After studying MySQL hot standby and cold standby over the past two days, do you have a better understanding of MySQL backup and recovery, both cold backup and hot backup ensure data integrity and consistency. Remember, backup is more important than everything !!!

  
I believe that what I learned today will be helpful to you in the subsequent work.