MySQL Learning Journey-Automatic database backup and manual recovery

1. Import Test Database data

Build Library
show databases;
CREATE datbase Easthome;
Build table
CREATE TABLE TJ (name varchar (8), Tel Int (8));
CREATE TABLE Sex (name varchar (8), sex int (1));
Inserting information
INSERT into EASTHOME.TJ (Name,tel) VALUES (' Liyan ', 88888888);
INSERT into EASTHOME.TJ (Name,tel) VALUES (' Zy ', 77777777);
Insert into Easthome.sex (name,sex) VALUES (' Zy ', 1);
mysql> INSERT INTO Easthome.sex (name,sex) VALUES (' Liyan ', 0);
Query OK, 1 row Affected (0.00 sec)
Insert into Easthome.sex (name,sex) VALUES (' Yangchen ', 1);
INSERT INTO Sex (name,sex) VALUES (' Zhangyun ', 0);
Mysql> Show tables;
+--------------------+
| Tables_in_easthome |
+--------------------+
| sex |
| TJ |
+--------------------+
2 rows in Set (0.00 sec)

Mysql> select * from sex;
+----------+------+
| name | sex |
+----------+------+
|    Zy | 1 |
|    Liyan | 0 |
|    Yangchen | 1 |
|    Zhangyun | 0 |
+----------+------+
4 rows in Set (0.00 sec)

Mysql> SELECT * from EASTHOME.TJ;
+-------+----------+
| name | Tel |
+-------+----------+
| Liyan | 88888888 |
| Zy | 77777777 |
+-------+----------+
2 rows in Set (0.00 sec)


2. Start Backup
mysqldump Backup to dump file
==============
MySQL Installation Location: D:\app\mysql57
The database name is: easthome
MySQL Root Password: redhat
Database backup destination: D:\db_backup\

Script:

REM *******************************code start*****************************
@echo off
Set "ymd=%date:~,4%%date:~5,2%%date:~8,2%"
D:\app\mysql57\bin\mysqldump.exe-uroot-predhat--default-character-set=binary-f easthome > D:\db_bakup\easthome _%ymd%.dump
@echo on
REM *******************************code end*****************************

When you save the above code as Backup_db.bat debugging, you can add pause at the end to see if it performs properly
You can then use the Windows scheduled task to execute the script at timed intervals. (ex: Daily 5 o'clock in the morning execution of Back_db.bat)
Description: This method does not have to close the database and can back up files by name every day.
By%date:~5,2% to combine the current date, the combined effect of the yyyymmdd,date command gets the date format default to YYYY-MM-DD (if not this format can be paused by the Pause Command Line window to see through the%date:~,20% Get the current computer date format), so you can get the two characters starting with the fifth character in the date by%date:~5,2%, for example, today is 2009-02-05, and by%date:~5,2% you get 02. (The index of the date string is starting from 0)

3, import into the cold standby library #在这里卡了半天, the original mysqldump is only export tool, import must use MySQL

Log in to MySQL in cmd
Mysql-uroot-p
Create DATABASE MyTest
Use MyTest
SOURCE D:\db_bakup\easthome_20160901.dump
Show tables; #检查是否导入成功

The effect is as follows:
Mysql> Source D:\db_bakup\easthome_20160901.dump
Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows affected, 1 Warning (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| sex |
| TJ |
+------------------+
2 rows in Set (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| MyTest |
| Performance_schema |
| SYS |
+--------------------+
5 rows in Set (0.00 sec)

MySQL Learning Journey-Automatic database backup and manual recovery