Shulou(Shulou.com)05/31 Report--

Most people do not understand the knowledge points of this article "how to back up and restore MySQL database", so the editor summarizes the following, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to backup and restore MySQL database" article.

In any database environment, there will always be uncertain accidents, such as exceptional power outages, various software and hardware failures in the computer system, man-made destruction, misoperation by administrators, and so on. these situations may lead to serious consequences such as data loss, server paralysis and so on. When there are multiple servers, the problem of data synchronization between master and slave servers occurs.

In order to effectively prevent data loss and minimize the loss, the MySQL database server should be backed up regularly. If the data in the database is lost or an error occurs, you can use the backed-up data for recovery. The problem of data synchronization between master and slave servers can be realized by replication function.

Physical backup and logical backup

Physical backup: back up data files and dump database physical files to a directory. The recovery speed of physical backup is relatively fast, but it takes up a lot of space, so xtrabackup tool can be used for physical backup in MySQL.

Logical backup: the database objects are exported by tools and summarized into the backup file. Logical backup recovery is slow, but takes up less space and is more flexible. The logical backup tool commonly used in MySQL is mysqldump. Logical backup is to back up the sql statement, and execute the backup sql statement to reproduce the database data during recovery.

Second, mysqldump to achieve logical backup

Mysqldump is a very useful database backup tool provided by MySQL.

2.1 back up a database

When the mysqldump command is executed, you can back up the database into a text file that actually contains multiple CREATE and INSERT statements that can be used to recreate tables and insert data.

Find out the structure of the table that needs to be backed up and generate a CREATE statement in the text file

Converts all records in the table into an INSERT statement.

Basic syntax:

Mysqldump-u user name-h host name-p password database name to be backed up [tbname, [tbname...]] > backup file name. Sql

For example: use root users to back up atguigu databases:

Mysqldump-uroot-p atguigu > atguigu.sql # backup files are stored in the current directory mysqldump-uroot-p atguigudb1 > / var/lib/mysql/atguigu.sql

Backup file analysis:

-- MySQL dump 10.13 Distrib 8.0.26, for Linux (x861464)-Host: localhost Database: atguigu-- Server version 8.0.26Universe 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 * /; / *! 50503 SET NAMES utf8mb4 * /; / *! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /; / *! 40103 SET TIME_ZONE='+00:00' * /; / *! 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: `atguigu`-- CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `atguigu` / *! 40100 DEFAULT CHARACTER SETutf8mb4 COLLATE utf8mb4_0900_ai_ci * / *! 80016 DEFAULT ENCRYPTION='N' * /; USE `atguigu`;-Table structure for table `student`-- DROP TABLE IF EXISTS `student`; / *! 40101 SET @ saved_cs_client = @ @ character_set_client * /; / *! 50503 SET character_set_client = utf8mb4 * / CREATE TABLE `student` (`studentno` int NOT NULL, `name` varchar (20) DEFAULT NULL, `class` varchar (20) DEFAULT NULL,PRIMARY KEY (`studentno`)) ENGINE=InnoDB DEFAULT CHARSETThe utf8mb3WTP SET character_set_client = @ saved_cs_client * / INSERT INTO `student` VALUES (Class 1 of Zhang San _ back','), (3 of Li Si, Class 1), (8 of 'Wang Wu', 'Class 2'), (15 of 'Zhao Liu', 'Class 2'), (20 of 'Qian Qi','> Class 3'), (22 of Zhang Zhang3 'updatebooks'), (24 ALTER TABLE of'Li Si', 'Class 1'); / *! 40000 ALTER TABLE `student` ENABLE KEYS * /; UNLOCK TABLES .. / *! 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 2022-01-07 9:58:23

-- begin with comments on the sQL statement

With / *! The statements that begin and end with * / are executable MySQL comments, which can be executed by MySQL but are ignored as comments in other database management systems, which can improve the portability of the database.

The file begins with the version number of the MySQLdump tool used to back up the file, followed by the name of the backup account and host information, as well as the name of the backed up database, and finally the version number of the MySQL server, in this case 8.0.26.

The next part of the backup file is some SET statements that assign some system variable values to user-defined variables to ensure that the system variables of the restored database are the same as those of the original backup, for example:

The last few lines of the backup file MySQL use the set statement to restore the original value of the server system variable, for example:

The following DROP statements, CREATE statements, and INSERT statements are all used during restore. For example, the DROPTABLE IF EXISTS 'student' statement is used to determine whether there is a table named student in the database and, if so, to delete the table; the CREATE statement is used to create the table of student; and the INSERT statement is used to restore data.

Some statements that start with a backup file begin with a number. These numbers represent the MySQL version number and tell us that these statements can only be executed if the MySQL version is established or higher. For example, 40101 indicates that these statements can only be executed if the MySQL version number is 4.01.01 or higher. The backup time is recorded at the end of the file.

2.2 back up all databases

To back up the entire instance with mysqldump, you can use the-- all-databases or-A parameter:

Mysqldump-uroot-pxxxxxx-- all-databases > all_database.sql mysqldump-uroot-pxxxxxx-A > all_database.sql2.3 backup part of the database

Use the-- databases or-B parameter, which is followed by the database name, and multiple databases are separated by spaces. If you specify the databases parameter, there is a statement to create the database in the backup file, and if you do not specify the parameter, it does not exist. The syntax is as follows:

Mysqldump-u user-h host-p-- databases [database name 1 [database name 2]] > backup file name. Sql

Give an example

Mysqldump-uroot-p-B atguigu atguigu12 > two_database.sql

Or

Mysqldump-uroot-p-B atguigu atguigu12 > two_database.sql2.4 backup partial tables

For example, make a backup before the table changes. The syntax is as follows:

The name of the mysqldump-u user-h host-p database [table name 1 [table name 2]] > backup file name. Sql

Example: backing up the book table under the atguigu database

Mysqldump-uroot-p atguigu book > book.sql# backup multiple tables mysqldump-uroot-p atguigu book account > 2_tables_bak.sql

The content of book.sql file is as follows

Mysqldump-uroot-p atguigu book > book.sql ^ C [root@node1 ~] # lskk kubekey kubekey-v1.1.1-linux-amd64.tar.gz README.md test1.sqltwo_ database.sql [root @ node1 ~] # mysqldump-uroot-p atguigu book > book.sqlEnter password: [root@node1 ~] # lsbook.sql kk kubekey kubekey-v1.1.1-linux-amd64.tar.gz README.md test1.sqltwo_ database.sql [root @ node1 ~] # vi book.sql-- MySQL dump 10.13 Distrib 8.0.26 For Linux (x86 / 64)-Host: localhost Database: atguigu-- Server version 8.0.26 Universe / 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 * /; / *! 50503 SET NAMES utf8mb4 * /; / *! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /; / *! 40103 SET TIME_ZONE='+00:00' * /; / *! 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 * /;-Table structure for table `book`-- DROP TABLE IF EXISTS `book`; / *! 40101 SET @ saved_cs_client = @ @ character_set_client * / / *! 50503 SET character_set_client = utf8mb4 * /; CREATE TABLE `book` (`bookid` int unsigned NOT NULL AUTO_INCREMENT, `card` int unsigned NOT NULL, `test` varchar) COLLATE utf8_bin DEFAULT NULL,PRIMARY KEY (`bookid`), KEY `Y` (`card`) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb3 COLATEcompliutf8 bookbinominked / 40101 SET character_set_client = @ saved_cs_client * /;-Dumping data for table `book`-LOCK TABLES `book`WRITESpacewise 40000 ALTER TABLE `book` DISABLE KEYS * / INSERT INTO `book`Null), (2) INSERT INTO `book` null), (3) (4), (4), (4), (5), (6), (11), (8), (9), (10), (17), (11, 19), (12), (13, 1,), (14, 14), (15, 5), (165, 5, 5, and 8)), (1813, 3), (1912), (2011), (2, 1, 9) null). (22 and 13), (24 and 3), (25 and 18), (26 and 5), (28 and 6), (29 and 15), (30), (31), (31), (31), (35), (35) and (36)), (37) and 17) null), (38) and 5), (39 and 16), (406) and 6), (4118)), (4212)), (436)) and (36)). (44pyrrine 12 null), (45re2 null), (46pjr 12 null), (47p15 null), (4817 null), (49 null), (50 16 null), (51 13 null), (52) null), (53) 7 null), (54 null), (56) null), (56) 1) null), (5714 null), (587 null), (59) 15 null), (601 null), (6113)), (62), (62) null), (6, 3), (6), 6, 6, 6, and 6). (6, 6 and 12), (67), (68, 4), (69, 5, and 10), (71, 16), (72, 14, null), (74, 5, 4, and 14), (74), (75, 4,), (76), (76), (78), (78, 2), (79, 2), (803), (81, 8, and 8)), (8214), (83, 5), (84, 4), (852), (8620), (8712), and (812) null). (88recollection 1 null), (89recollection null), (91pr 3 null), (92p3 null), (93pl null), (94pr 1 null), (95pl null), (96pr 17 null), (97pr 15 null), (98ma 1 null), (999920), (100,15) null). / *! 40000 ALTER TABLE `book` ENABLE KEYS * /; UNLOCK TABLES / TABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLESTABLETABLESTABLESTABLESTABLESTABLETABLESTABLETABLESTABLETABLESTABLESTABLES40103 SET TIME_ZONE=@OLD_TIME_ZONE * /

As you can see, the book file is similar to the backed-up library file. The difference is that the book file contains only the DROP, CREATE, and INSERT statements of the book table.

Back up multiple tables using the following commands, such as backing up book and account tables:

# backup multiple tables mysqldump-uroot-p atguigu book account > 2_tables_bak.sql2.5 backup part of the data of a single table

Sometimes the amount of data in a table is so large that we only need part of the data. You can use the-- where option at this point. Where is followed by conditions that need to be met.

For example: back up data with id less than 10 in the student table:

Mysqldump-uroot-p atguigu student-where= "id

< 10 " >

Student_part_id10_low_bak.sql

As shown below, the insert statement has only those parts whose id is less than 10

LOCK TABLES `student`WRITEETBN 40000 ALTER TABLE `student`student` DISABLE KEYS * /; INSERT INTO `student` VALUES (1mm 100002) (251277), (3) 80404), (4pm 100005), (240171), (5pm, 100006), (388476), (6jie, 100008, 70008), (429168, 429168), (8100009dvQQA, 61504), (91000000, 100005), (240171), (388476), (259124), (429168, 429168), (8100009dvQQA, 61504), (91000000, 100005) copies of some tables, excluding the backup of some tables.

If we want to back up a library, but some tables have a large amount of data or are not relevant to the business, we can consider excluding these tables at this time. Again, the option-- ignore-table can do this.

Mysqldump-uroot-p atguigu-- ignore-table=atguigu.student > no_stu_bak.sql

Determine that there is no student structure in the file by specifying as follows:

Grep "student" no_stu_bak.sql2.7 backs up structure only or data only

You can use the-- no-data abbreviated to-- d option for backup-only structures, and-- no-create-info for-- t option for backing up data only.

Backup only structure

Mysqldump-uroot-p atguigu-- no-data > atguigu_no_data_bak.sql# uses the grep command, and no insert-related statements are found, indicating that there is no data backup. [root@node1 ~] # grep "INSERT" atguigu_no_data_ bak.sql [root @ node1 ~] #

Back up data only

Mysqldump-uroot-p atguigu-- no-data > atguigu_no_data_bak.sql# uses the grep command, and no insert-related statements are found, indicating that there is no data backup. [root@node1 ~] # grep "INSERT" atguigu_no_data_ bak.sql [root @ node1 ~] #

Back up data only

Mysqldump-uroot-p atguigu-- no-create-info > atguigu_no_create_info_bak.sql# uses the grep command, and no create related statements are found, indicating that there is no data structure. [root@node1 ~] # grep "CREATE" atguigu_no_create_info_ bak.sql [root @ node1 ~] # 2.8 backups contain stored procedures, functions, and events

Mysqldump backups do not contain stored procedures, custom functions, and events by default. You can use the-- routines or-R options to back up stored procedures and functions, and the-- events or-E parameters to back up events.

For example: back up the entire atguigu library, including stored procedures and events:

Use the following SQL to see what stored procedures or functions are available in the current library

Mysql > SELECT SPECIFIC_NAME,ROUTINE_TYPE, ROUTINE_SCHEMA FROMinformation_schema.Routines WHERE ROUTINE_SCHEMA= "atguigu" +-+ | SPECIFIC_NAME | ROUTINE_TYPE | ROUTINE_SCHEMA | +-+ | rand_num | FUNCTION | atguigu | | rand_string | | FUNCTION | atguigu | | BatchInsert | PROCEDURE | atguigu | | insert_class | PROCEDURE | atguigu | | insert_order | PROCEDURE | atguigu | insert_stu | PROCEDURE | atguigu | insert_user | PROCEDURE | atguigu | | ts_insert | PROCEDURE | atguigu | +-+ 9 rows in set (0.02 sec) |

Let's back up the data, functions and stored procedures of the atguigu library.

Mysqldump-uroot-p-R-E-- databases atguigu > fun_atguigu_bak.sql

Query whether there is a function in the backup file, as shown below, you can see that the function is indeed included.

Grep-C 5 "rand_num" fun_atguigu_bak.sql- Dumping routines for database'atguiguyu Morelle / / 50003 DROP FUNCTION IF EXISTS `rand_ num` * /; / *! 50003 SET @ saved_cs_client = @ @ character_set_client * /; / *! 50003 SET @ saved_cs_results = @ @ character_set_results * /; / *! 50003 SET @ saved_col_connection = @ @ collation_connection * /; / *! 50003 SET character_set_client = utf8mb3 * / / *! 50003 SET character_set_results = utf8mb3 * /; / *! 50003 SET collation_connection = utf8_general_ci * /; / *! 50003 SET @ saved_sql_mode = @ @ sql_mode * /; / *! 50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_pISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' * /; DELIMITER CREATE DEFINER= `root` @ `% `FUNCTION `root` @`% `root` (from_num BIGINT, to_num BIGINT) RETURNSbigintBEGINDECLARE i BIGINT DEFAULT 0scape set I = FLOOR (from_num+ RAND () * (to_num-from_num+1)); RETURN iscape end;-- BEGINDECLARE i INT DEFAULT 0terset autocommit = 0politics REPEATSET I = I + 1tern insert INTO class (classname,address,monitor) VALUES (rand_string (8), rand_string (10), rand_num ()); UNTIL I = max_numEND REPEAT;COMMIT;END; DELIMITER; -- BEGINDECLARE i INT DEFAULT 0 position set autocommit = 0; # set manual commit transaction REPEAT # Loop SET I = I + 1; # assignment INSERT INTO order_test (order_id, trans_id) VALUES (rand_num (1line 7000000), rand_num (10000000000000000000000000000000000000000)); UNTIL I = max_numEND REPEAT;COMMIT; # commit transaction END; DELIMITER;-- BEGINDECLARE i INT DEFAULT 0transact set autocommit = 0; # set manual commit transaction REPEAT # Loop SET I = I + 1 # assign INSERT INTO student (stuno, name,age, classId) VALUES ((START+i), rand_string (6), rand_num (), rand_num ()); UNTIL I = max_numEND REPEAT;COMMIT; # commit transaction END;; DELIMITER;-- BEGINDECLARE i INT DEFAULT 0bot autocommit = 0tREPEATSET I = I + 1tern insert INTO `user` (name,age,sex) VALUES ("atguigu", rand_num (1meme 20), "male"); UNTIL I = max_numEND REPEAT;COMMIT;END; DELIMITER; 3. Mysql command to restore data

Use the mysqldump command to back up the data in the database into a text file. When you need to restore, you can use the mysql command to restore the backed up data.

The mysql command executes CREATE statements and INSERT statements in the backup file. Create databases and tables through CREATE statements. Insert the backed up data through the INSERT statement.

Basic syntax:

Mysql-u root-p [dbname]

< backup.sql 其中，dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。此时需要确保MySQL服务器中已经创建了该名的数据库。不指定数据库名时，表示还原文件中所有的数据库。此时sql文件中包含有CREATE DATABASE语句，不需要MysQL服务器中已存在这些数据库。 3.1 单库备份中恢复单库 使用root用户，将之前练习中备份的atguigu.sql文件中的备份导入数据库中，命令如下： 如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示 #备份文件中包含了创建数据库的语句mysql -uroot -p < atguigu.sql 否则需要指定数据库名称，如下所示 #备份文件中不包含了创建数据库的语句mysql -uroot -p atguigu4< atguigu.sql3.2 全量备份恢复 如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作： mysql -u root -p < all.sqlmysql -uroot -pxxxxxx < all.sql 执行完后，MySQL数据库中就已经恢复了all.sql文件中的所有数据库。 补充： 如果使用--all-databases参数备份了所有的数据库，那么恢复时不需要指定数据库。对应的sql文件包含有CREATE DATABASE语句，可通过该语句创建数据库。创建数据库后，可以执行sql文件中的USE语句选择数据库，再创建表并插入记录。 3.3 从全量备份中恢复单库 可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份。 举例： sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql >

After the separation of atguigu.sql # is completed, we can import atguigu.sql to restore a single library 3.4 to restore a single table from a single database backup

This requirement is quite common. For example, if we know which table has been misoperated, we can restore it in the form of single table recovery.

For example: we have a backup of the entire atguigu library, but due to the misoperation of the class table, we need to restore this table separately.

| cat atguigu.sql | sed-e'/. / {HutterabildCreate TABLE `class` /! dpoliq' > class_structure.sql cat atguigu.sql | grep-- ignore-case 'insert into `class` > class_data.sql # use shell syntax to separate the statements for creating tables and inserting data, and then export them sequentially to restore use atguigu; mysql > data. Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > source class_data.sql; Query OK, 1 row affected (0.01 sec) 4. Physical backup: copy the entire database directly

Copy the database files in MySQL directly. This method is the simplest and the fastest. The database directory location of the MySQL is not necessarily the same:

Under the Windows platform, the directory where MySQL 8.0 stores the database usually defaults to C:\ ProgramData\ MySQL\ MySQL Server 8.0\ Data or other user-defined directory

On Linux platforms, the database directory location is usually / var/lib/mysql/

On MAC OSX platforms, the database directory location is usually "/ usr/local/mysql/data"

But to ensure the consistency of the backup. You need to guarantee:

Method 1: stop the server before backing up.

Method 2: perform FLUSH TABLES WITH READ LOCK operations on related tables before backup. This allows other customers to continue querying the table when copying files in the database directory. At the same time, the FLUSH TABLES statement ensures that all active index pages are written to the hard disk before starting the backup.

This approach is convenient, fast, but not the best backup method, because the actual situation may not allow you to stop the MySQL server or lock tables, and this method does not apply to the tables of the InnoDB storage engine. For the tables of the MyISAM storage engine, this is convenient to back up and restore, but it is better to restore the same version of the MySQL database, otherwise there may be different file types.

Note that after the physical backup is complete, UNLOCK TABLES is executed to settle other customers' changes to the table.

Note: in the MySQL version number, the first number represents the major version number, and the MySQL database file format with the same major version number is the same.

In addition, you can also consider using related tools for backup. For example, the MySQLhotcopy tool. MySQLhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES, and cp or scp to quickly back up the database. It is the fastest way to back up a database or a single table, but it can only run on the same machine as the database directory, and can only back up tables of type MyISAM. It is mostly used before mysql5.5.

Physical recovery: copy directly to the database directory

Steps:

Demonstrate deleting data from a specified table in a backed-up database

Copy the backed up database data to the data directory and restart the MySQL server

Query whether the data of the related table is restored. You need to use the following chown operation.

Request:

You must ensure that the database where the data is backed up is the same as the major version number of the database server to be restored.

Because only if the MySQL database major version number is the same, the two MySQL database file types are guaranteed to be the same.

This approach is effective for tables of type MyISAM, but not for tables of type InnoDB.

Because the tablespace of the InnoDB table cannot be replicated directly.

Under the Linux operating system, after copying to the database directory, be sure to change the users and groups of the database into mysql, as follows:

Chown-R mysql.mysql / var/lib/mysql/dbname

Where two mysql represent groups and users, the "- R" parameter changes the users and groups of all child files under the folder, and the "dbname" parameter represents the database directory.

It is suggested that the permission setting under the Linux operating system is very strict. In general, the MySQL database can only be accessed by root users and mysql users under the mysql user group, so after copying the database directory to the specified folder, be sure to use the chown command to change the folder user group to mysql and the user to mysql.

VI. Export and Import of tables 6.1 Export of tables

1. Use SELECT... INTO OUTFILE export text file

In MySQL, you can use SELECT... The INTO OUTFILE statement exports the contents of the table to a text file.

Example: use SELECT … INTO OUTFILE exports records from the account table in the atguigu database to a text file.

(1) Select the database atguigu, and query the account table, and the execution result is as follows.

Use atguigu;select * from account;mysql > select * from account;+----+ | id | name | balance | +-- + | 1 | Zhang San | 90 | 2 | Li Si | 100 | | 3 | Wang Wu | 0 | +-+ 3 rows in set (0.01 sec)

(2) mysql has permission restrictions on exported directories by default, that is, when using the command line for export, you need to specify a directory to operate.

Query the secure_file_ privy value:

Mysql > SHOW GLOBAL VARIABLES LIKE'% secure%' +-- +-+ | Variable_name | Value | +-+ | require_secure_transport | OFF | | secure_file _ priv | / var/lib/mysql-files/ | +-+ 2 rows in set (0.02 sec)

The optional values and functions of parameter secure_file_priv are:

If set to empty, it means that the location where the file is generated is not limited, which is an unsafe setting

If set to a string that represents the path, it is required that the generated file can only be placed in this specified directory or its subdirectory

If set to NULL, it means that select execution on this MySQL instance is prohibited. Into outfile operation.

(3) as shown in the above result, the value of the secure_file_priv variable is / var/lib/mysql-files/, export directory set to this directory, and the SQL statement is as follows.

SELECT * FROM account INTO OUTFILE "/ var/lib/mysql-files/account.txt"

(4) View the file / var/lib/mysql-files/ roomt.txt`.

1 Zhang 3902 Li Si 1003 Wang Wu 0

two。 Export a text file using the mysqldump command

Example 1: use the mysqldump command to export records from the account table in the atguigu database to a text file:

Mysqldump-uroot-p-T "/ var/lib/mysql-files/" atguigu account

After the execution of the mysqldump command, the account.sql and account.txt files are generated in the specified directory / var/lib/mysql-files/.

Open the account.sql file, which contains the CREATE statement that creates the account table.

[root@node1 mysql-files] # cat account.sql-- MySQL dump 10.13 Distrib 8.0.26 For Linux (x86 / 64)-Host: localhost Database: atguigu-- Server version 8.0.26 Universe / 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 * /; / *! 50503 SET NAMES utf8mb4 * /; / *! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /; / *! 40103 SET TIME_ZONE='+00:00' * /; / *! 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' * / / *! 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /;-Table structure for table `account`-- DROP TABLE IF EXISTS `Secrett`; / *! 40101 SET @ saved_cs_client = @ @ character_set_client * /; / *! 50503 SET character_set_client = utf8mb4 * /; CREATE TABLE `Secrett` (`id`int NOT NULL AUTO_INCREMENT, `name`varchar (255i) NOT NULL, `balance`int NOT NULL,PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 / *! 40101 SET character_set_client = @ saved_cs_client * /; / *! 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /; / *! 40101 SET SQL_MODE=@OLD_SQL_MODE * /; / *! 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 2022-01-07 23:19:27

Open the account.txt file, which contains only the data in the account table.

[root@node1 mysql-files] # cat account.txt1 Zhang San 902 Li Si 1003 Wang Wu 0

Example 2: use mysqldump to export the account table in the atguigu database to a text file, and use the FIELDS option to require commas and intervals between fields. Field values of all character types are enclosed in double quotation marks:

Mysqldump-uroot-p-T "/ var/lib/mysql-files/" atguigu account-- fields-terminatedby=','-- fields-optionally-enclosed-by='\ "

After the statement mysqldump statement executes successfully, two files, account.sql and account.txt, appear in the specified directory.

Open the account.sql file, which contains the CREATE statement that creates the account table.

[root@node1 mysql-files] # cat account.sql-- MySQL dump 10.13 Distrib 8.0.26 For Linux (x86 / 64)-Host: localhost Database: atguigu-- Server version 8.0.26 Universe / 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 * /; / *! 50503 SET NAMES utf8mb4 * /; / *! 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /; / *! 40103 SET TIME_ZONE='+00:00' * /; / *! 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' * / / *! 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /;-Table structure for table `account`-- DROP TABLE IF EXISTS `Secrett`; / *! 40101 SET @ saved_cs_client = @ @ character_set_client * /; / *! 50503 SET character_set_client = utf8mb4 * /; CREATE TABLE `Secrett` (`id`int NOT NULL AUTO_INCREMENT, `name`varchar (255i) NOT NULL, `balance`int NOT NULL,PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 / *! 40101 SET character_set_client = @ saved_cs_client * /; / *! 40103 SET TIME_ZONE=@OLD_TIME_ZONE * /; / *! 40101 SET SQL_MODE=@OLD_SQL_MODE * /; / *! 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 2022-01-07 23:36:39

Open the account.txt file, which contains the data that created the account table. As you can see from the file, fields are separated by commas, and values of character types are enclosed in double quotes.

[root@node1 mysql-files] # cat account.txt1, "Zhang San", 902, "Li Si", 1003, "Wang Wu", 0

3. Export a text file using the mysql command

Example 1: use the MySQL statement to export the records in the account table in atguigu data to a text file:

Mysql-uroot-p-execute= "SELECT * FROM account;" atguigu > "/ var/lib/mysqlfiles/account.txt"

Open the account.txt file, which contains the data that created the account table.

[root@node1 mysql-files] # cat account.txtid name balance1 Zhang San 902 Li Si 1003 Wang Wu 0

Example 2: export the record in the account table of the atguigu database to a text file, and use the-veritcal parameter to divide the conditional record into multiple lines:

Mysql- uroot-p-vertical-execute= "SELECT * FROM account;" atguigu > "/ var/lib/mysql-files/account_1.txt"

Open the account_1.txt file, which contains the data that created the account table.

[root@node1 mysql-files] # cat account_1.txt** 1. Row * * id: 1name: Zhang San balance: 90 balance * 2. Row * * id: 2name: Li Si balance: 100mm * 3. Row * * id: 3name: Wang Wu balance: 0

Example 3: export the records in the account table of the atguigu database to a xml file, using the-xml parameter, as follows.

Mysql- uroot-p-- xml-- execute= "SELECT * FROM account;" atguigu > "/ var/lib/mysqlfiles/account_3.xml" [root@node1 mysql-files] # cat account_ 3.xml1 piece 3902 Li Si 1003 Wang Wu 0

Description: if you want to export table data to a html file, you can use the-- html option. You can then open it using a browser.

6.2 Import of tables

1. Import a text file using LOAD DATA INFILE

Example 1:

Use SELECT... INTO OUTFILE exports records of the account table in the atguigu database to a text file

SELECT * FROM atguigu.account INTO OUTFILE'/ var/lib/mysql-files/account_0.txt'

Delete the data in the account table:

DELETE FROM atguigu.account

To recover data from a text file account.txt:

LOAD DATA INFILE'/ var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account

Query the data in the account table:

Mysql > select * from account;+----+ | id | name | balance | +-- + | 1 | Zhang San | 90 | 2 | Li Si | 100 | 3 | Wang Wu | 0 | +-+ 3 rows in set (0.00 sec)

Example 2: select database atguigu, use SELECT … INTO OUTFILE exports records from the account table of the atguigu database to a text file, using the FIELDS option and the LINES option, requiring commas between fields, with all field values enclosed in double quotation marks:

SELECT * FROM atguigu.account INTO OUTFILE'/ var/lib/mysql-files/account_1.txt' FIELDSTERMINATED BY', 'ENCLOSED BY'\ "

Delete the data in the account table:

DELETE FROM atguigu.account

Import data from / var/lib/mysql-files/account.txt into the account table:

LOAD DATA INFILE'/ var/lib/mysql-files/account_1.txt' INTO TABLE atguigu.accountFIELDS TERMINATED BY', 'ENCLOSED BY'\ "

Query the data in account table. The specific SQL is as follows:

Select * from account;mysql > select * from account;+----+ | id | name | balance | +-- + | 1 | Zhang San | 90 | 2 | Li Si | 100 | | 3 | Wang Wu | 0 | +-+ 3 rows in set (0.00 sec)

two。 Import a text file using mysqlimport

For example:

Export the file account.txt with a comma "," interval between fields, and field values enclosed in double quotation marks:

SELECT * FROM atguigu.account INTO OUTFILE'/ var/lib/mysql-files/account.txt' FIELDSTERMINATED BY', 'ENCLOSED BY'\ "

Delete the data in the account table:

DELETE FROM atguigu.account

Use the mysqlimport command to import the contents of the account.txt file into the account table of database atguigu:

Mysqlimport-uroot-p atguigu'/ var/lib/mysql-files/account.txt'-- fields-terminatedby=','-- fields-optionally-enclosed-by='\ "'

Query the data in the account table:

Select * from account;mysql > select * from account;+----+ | id | name | balance | +-- + | 1 | Zhang San | 90 | 2 | Li Si | 100 | | 3 | Wang Wu | 0 | +-+ 3 rows in set (0.00 sec) VII. Overview of database migration

Data migration (data migration) refers to the process of selecting, preparing, extracting, and transforming data, and permanently transferring data from one computer storage system to another. In addition, verifying the integrity of migrated data and decommissioning the original old data store is also considered to be part of the entire data migration process.

There are a variety of reasons for database migration, including server or storage device replacement, maintenance or upgrade, application migration, website integration, disaster recovery, and data center migration.

Different migration schemes may be adopted according to different requirements, but generally speaking, MySQL data migration schemes can be divided into two categories: physical migration and logical migration. It is usually performed in a manner as automated as possible, freeing human resources from tedious tasks.

7.2 Migration scenario

Physical migration

Physical migration is suitable for overall migration with a large amount of data. The advantage of using the physical migration solution is that it is fast, but requires downtime migration and requires that the MySQL version and configuration must be the same as the original server, which may also cause unknown problems.

Physical migration includes copying data files and using the XtraBackup backup tool.

Physical migration can be used between different servers. We can install the same version of the database software on the new server, create the same directory, recommend that the configuration file be the same as the original database, then copy the data files and log files from the original database, configure the filegroup permissions, and then use the mysqld command on the new server to start the database.

Logical transfer

Logical migration is more applicable, whether it is partial migration or full migration, you can use logical migration. Backup tools such as mysqldump are most commonly used in logical migration.

7.3 pay attention to migration

Attention points for migration between databases of the same version of ①

Refers to database movement between MySQL databases with the same major version number.

Method 1: because the major version number of the MySQL database is the same before and after migration, database migration can be achieved by copying the database directory, but the physical migration method is only applicable to MyISAM engine tables. For InnoDB tables, you cannot back up the database by copying files directly.

Method 2: the most common and secure way is to use the mysqldump command to export the data, and then use the MySQL command to import from the target database server.

For example:

# host1 back up all databases and migrate the databases to a machine named host2 mysqldump-h host1-uroot-p-- all-databases | mysql-h host2-uroot-p

In the above statement, the | symbol indicates the pipe, and its function is to give the files backed up by mysqldump to the mysql command;-- all-databases indicates that all databases are to be migrated. Migration can be achieved directly in this way.

Attention points for migration between different versions of ② databases

For example, many servers used version 5.7 MySQL database, but after the introduction of version 8.0, many defects of version 5.7 were improved, so it is necessary to upgrade the database to version 8.0.

Old and new versions of MySQL may use different default character sets. For example, some older versions use latin1 as the default character set, while the latest version of MySQL uses utf8mb4 as the default character set. If there is Chinese data in the database, the default character set needs to be modified during the migration, otherwise the data may not be displayed properly.

Higher versions of MySQL databases are generally compatible with lower versions, so you can migrate from lower versions of MySQL databases to higher versions of MySQL databases.

Points for attention in ③ migration between different databases

Migration between different databases refers to migrating from other types of databases to MySQL databases, or from MySQL databases to other types of databases. There is no universal solution to this migration.

Before migrating, you need to understand the architecture of different databases and compare the differences between them. Keywords that define the same type of data may vary from database to database. For example, the date field in MySQL is divided into DATE and TIME, while the ORACLE date field only has data types such as ntext and Image in DATE;SQL Server database, which are not available in MySQL database; ENUM and SET types supported by MySQL are not supported in these SQL Server databases.

In addition, database manufacturers do not design database systems completely according to SQL standards, which leads to differences in SQL statements in different database systems. For example, Microsoft's SQL Server software uses T-SQL statements, and T-SQL contains non-standard SQL statements, which are not compatible with MySQL's SQL statements.

The differences between different types of databases make it difficult to migrate to each other, and these differences are actually technical barriers deliberately created by commercial companies. But migration between different types of databases is not entirely impossible. For example, you can use MyODBC to implement the migration between MySQL and SQL Server. MySQL Migration Toolkit, an official tool provided by MySQL, can also migrate data between different data. When MySQL migrates to Oracle, you need to use the mysqldump command to export the sql file, and then manually change the CREATE statement in the sql file.

7.4 Migration Summary

Eighth, delete the library and dare not run, what can you do?

The traditional high-availability architecture cannot prevent accidental deletion of data, because a drop table command of the master database will be passed to all slave libraries and cascading slave libraries through binlog, causing instances of the entire cluster to execute this command.

In order to find a more efficient way to solve the problem of mistakenly deleted data, we need to classify the mistakenly deleted data related to MySQL:

Mistakenly delete data rows using delete statement

Mistakenly delete a data table by using drop table or truncate table statements

Mistakenly delete database by using drop database statement

Use the rm command to mistakenly delete the entire MySQL instance.

8.1 delete: erroneous line deletion

Treatment measure 1: data recovery

Use the Flashback tool to restore data.

Principle: modify binlog content, take back the original library and replay. If multiple transactions are involved in the erroneous deletion of data, you need to change the order of the transactions before execution.

Use premise: binlog_format=row and binlog_row_image=FULL.

Treatment measure 2: prevention

Before the code goes online, it must be reviewed and audited by SQL.

It is recommended that you turn on safe mode and set the sql_safe_updates parameter to on. It is mandatory to add a where condition and an index field is required after where, otherwise limit must be used. Or you'll get the wrong report.

8.2 truncate/drop: mistakenly delete libraries / tables

Background:

Delete full table is very slow, need to generate rollback log, write redo, write binlog. Therefore, from a performance perspective, the use of truncatetable or drop table commands is preferred.

You can also use Flashback to recover the data deleted using the delete command. Data deleted using the truncate / drop table and drop database commands cannot be recovered through Flashback. Because, even if we configure binlog_format=row, when executing these three commands, the recorded binlog is still in statement format. There is only one truncate/drop statement in binlog, and the data cannot be recovered from this information.

Scheme:

In this case, you need to use a combination of full backup and incremental logging to restore data.

The premise of the scheme: regular full backup and real-time backup of binlog.

For example: someone deleted a library by mistake, the time is 3: 00 p. M. The steps are as follows:

Take the last full backup. Suppose the database is set up once a day, and the last backup data is at 2: 00 a.m. on the same day.

Restore a temporary library with backup; (note: select the temporary library here instead of directly manipulating the main library)

Check out the binlog log after 2: 00 a. M.

Except for the statements that mistakenly delete data, all the other statements are applied to the temporary library. (I talked about the recovery of binlog earlier)

Finally restore to the main library

8.3 Prevention of erroneous deletion of libraries / tables using truncate / drop

Above we mentioned the recovery scheme of mistakenly deleting libraries / tables using truncate / drop statements. Similar misoperations can be avoided as much as possible through the following suggested solutions in the production environment.

Separation of ① permissions

Limit account permissions, the core database, generally can not be randomly assigned write permissions, want to obtain write permissions need approval. For example, only DML permissions are given to business developers, not truncate/drop permissions. Even members of the DBA team are required to use only read-only accounts on a daily basis, and only use accounts with update permissions if necessary.

Permissions should be separated between different accounts and different data to avoid that one account can delete all libraries.

② formulates the code of practice

For example, before deleting a data table, you must rename the table (for example, add _ to_be_deleted). Then, observe for a period of time to make sure there is no impact on the business before deleting the table.

③ sets deferred replication standby library

To put it simply, delayed replication is to set a fixed delay time, such as 1 hour, so that the slave database lags behind the master database by one hour. Within 1 hour of erroneous deletion operation, execute stop slave on this repository, and then skip the misoperation command through the method described earlier, and you can recover the required data. Here, with the command CHANGE MASTER TO MASTER_DELAY = N, you can specify that the standby database has an N-second delay with the main database. For example, set N to 3600, which represents one hour.

In addition, delayed replication can be used to solve the following problems:

Used to do latency testing, such as database read-write separation, using the slave library as a read library, so you can use this feature to simulate latency if you want to know what happens when the data produces a delay.

For requirements such as queries for old data, for example, you often need to check the value of a table or field before a certain day. You may need to restore the backup and check it. If there is a delay from the database, such as an one-week delay, then similar requirements can be solved.

8.4 rm: mistakenly delete MySQL instance

For a MySQL cluster with a highly available mechanism, you don't have to worry about rm deleting data. Because only one of the node data is deleted, the HA system will select a new master database to ensure the normal operation of the whole cluster. After we recover the data on this node, we can connect it to the whole cluster.

But if the entire cluster is maliciously deleted, then you need to consider cross-room backup, cross-city backup.

The above is about the content of this article on "how to back up and restore MySQL database". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.