This is the 24th day of my participation in Gwen Challenge

A lifelong learner, practitioner and sharer committed to the path of technology, a busy and occasionally lazy original blogger, an occasionally boring and occasionally humorous teenager.

Welcome to dig friends wechat search “Jie Ge’s IT journey” attention!

The original link: MySQL | MySQL database system (4) – database backup and recovery

preface

In our daily work, backup data is actually one of the important work of information security management. In this article, we will cover database backup and recovery. There are several ways to back up a MySQL database simultaneously. First: package the database folder /etc/local/mysql.data directly, or we use a tool to backup.

Backup database

You can export a specified library, a specified table, or a full library as an SQL script with the mysqldump command. If you need to upgrade the MySQL server, you can export the original database information and import it directly from the upgraded MySQL server.

Performing an export operation

When you run the mysqldump command to export data, it is displayed on the terminal by default. To save to a file, you also need to combine the > Redirect output operation.

Basic syntax format

Exports some of the tables in the specified library
Mysqldump [options] database name [table name 1] [table name 2] ··· > / backup path/backup file nameCopy the code
Export one or more complete libraries, including all tables
Mysql database dump [option] --databases database name 1 [database name 2] ···Copy the code
Back up all libraries on the MySQL server
Mysqldump [option] --all-databases > / backup path/backup file nameCopy the code

Common options include -u and -p to specify the database user name and password, respectively.

The above basic syntax format 1 will be used to export the user table in MySQL library as mysql-user. SQL file, and the basic syntax format 2 will be used to export the entire auth library as auth.sql file. All operations in the two basic syntax formats need to be verified by root user.

[root@localhost ~]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password: 
[root@localhost ~]# mysqldump -u root -p --databases auth > auth.sql
Enter password: 
Copy the code

When you want to back up all the libraries in the MySQL server, you can use the basic syntax 3 above. When the amount of data to be exported is large, you can add the option –opt to optimize the execution speed.

The backup file all-data.sql will be created and contains all the libraries in the MySQL server.

[root@localhost ~]# mysqldump -u root -p --opt --all-databases > all-data.sql
Enter password:
Copy the code

View the contents of backup files

SQL scripts exported through mysqldump are text files, and the beginning of /*···*/ or — represents comment information.

You can use grep, less, and cat to view detailed scripts.

Database operation statements in auth.sql scripts will be filtered out.

[root@localhost ~]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$" CREATE DATABASE /*! 32312 IF NOT EXISTS*/ `auth` /*! 40100 DEFAULT CHARACTER SET utf8 */; USE `auth`; DROP TABLE IF EXISTS `servers`; CREATE TABLE `servers` ( `Server_name` char(64) NOT NULL, `Host` char(64) NOT NULL, `Db` char(64) NOT NULL, `Username` char(64) NOT NULL, `Password` char(64) NOT NULL, `Port` int(4) DEFAULT NULL, `Socket` char(64) DEFAULT NULL, `Wrapper` char(64) NOT NULL, `Owner` char(64) NOT NULL, PRIMARY KEY (`Server_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table'; LOCK TABLES `servers` WRITE; UNLOCK TABLES; DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `user_name` char(16) NOT NULL, `user_passwd` char(48) DEFAULT '', PRIMARY KEY (`user_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; LOCK TABLES `users` WRITE; INSERT INTO `users` VALUES ('jacktian','*B2B366CA5C4697F31D4C55D61F0B17E70E5664EC'); UNLOCK TABLES;Copy the code

2. Restore the database

During recovery, you can run the mysql command to import it.

Basic syntax format

Mysql [options] [database name] [table name] < / backup path/backup file nameCopy the code

If the backup file contains only the backup of tables and does not contain statements of libraries, the library name must be specified and the target library must exist during import.

You can import tables into the test library from the backup file mysql-user.sql.

[root@localhost ~]# mysql -u root -p test < mysql-user.sql Enter password: [root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 5.5.22- Log Source Distribution Copyright (C) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test; Database changed mysql> show tables; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Tables_in_test | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | user | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC) mysql >Copy the code

If the backup file contains complete library information, you can restore the Auth library from the auth. SQL backup file without specifying the library name.

[root@localhost ~]# cd /usr/local/mysql/data/ [root@localhost data]# ls auth ib_logfile0 localhost.err mongodb mysql-bin.000001 mysql-bin.000003 mysql-bin.index performance_schema ibdata1 ib_logfile1 localhost.pid mysql Log test [root@localhost data]# mv auth/TMP / ## [root@localhost data]# ls -ld auth [root@localhost data]# mysql -u root -p < ~/auth. SQL Enter password: ERROR 1050 (42S01) at line 63: Table 'auth'. 'users' already exists [root@localhost data]# ls -ld auth ## DRWX ------. 2 mysql mysql 4096 8月 1 05:17 auth [root@localhost data]#Copy the code

Recommended reading

How to deploy MySQL database in Linux

MySQL | MySQL database system (2) – the basic operations of a SQL statement

MySQL | MySQL database system (3) – database user authorization

Original is not easy, if you think this article is useful to you, please kindly like, comment or forward this article, because this will be my power to output more high-quality articles, thank you!

By the way, please give me some free attention! In case you get lost and don’t find me next time.

See you next time!