Author: threedayman

Source: Hang Seng LIGHT Cloud Community

Back up the commands used for restoration

The mysqldump, mysql,

About the mysqldump command more content See dev.mysql.com/doc/refman/…

The preparatory work

Create two tables user and his_user

CREATE TABLE 'user' ('id' bigint NOT NULL AUTO_INCREMENT COMMENT 'id', 'name' varchar(100) NOT NULL COMMENT 'id', PRIMARY KEY (' id ')) ENGINE=InnoDB DEFAULT CHARSET= UTf8MB4 COLLATE= UTf8MB4_0900_ai_ci COMMENT=' 表'; CREATE TABLE 'his_user' ('id' bigint NOT NULL AUTO_INCREMENT COMMENT 'id', 'name' varchar(100) NOT NULL COMMENT 'id', PRIMARY KEY (' id ') ENGINE=InnoDB DEFAULT CHARSET= UTf8MB4 COLLATE= UTf8MB4_0900_ai_ci COMMENT=' historical user table ';Copy the code

Insert data

INSERT INTO user(name) VALUES('three'); INSERT INTO his_user(name) VALUES('wang'); mysql> select * from user; + - + -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- + | 1 | three | + - + -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC) mysql > select * from his_user; + - + -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- + | 1 | wang | + + -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

The backup

Whole database backup

mysqldump -uroot -p123456 datax >dataxAll.sql
Copy the code

Insert data

INSERT INTO user(name) VALUES('four'); INSERT INTO his_user(name) VALUES('li'); mysql> select * from user; + - + -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- + | 1 | three | | 2 | four | + - + -- -- -- -- -- -- -- + 2 rows in the set (0.00 SEC) mysql > select * from his_user; + - + -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- + | 1 | wang | | 3 | li | + + -- -- -- -- -- -- - + 2 rows in the set (0.00 SEC)Copy the code

reduction

Restore data

mysql   -uroot -p123456 datax < dataxAll.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
Copy the code

View table data

mysql> select * from user; + - + -- -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- -- + | 1 | three | + - + -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC) mysql > select * from his_user; + - + -- -- -- -- -- -- + | | id name | + - + -- -- -- -- -- -- + | 1 | wang | + + -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

Data has been restored to its pre-backup state.

To filter backup by table name, refer to the following statements

mysqldump -uroot -p123456 datax $(mysql -N -uroot -p123456 -e "show tables from datax like 'tc%'") >t.sql
Copy the code