MYSQL/MARIADB database backup and recovery mysqlpump Getting Started

To create a test table:
MariaDB [music]>CREATE TABLE summary (ID int,info char);
Query OK, 0 rows affected (0.05 sec)

MariaDB [music]>show tables;
+-----------------+
| Tables_in_music |
+-----------------+
| Summary |
| Test |
+-----------------+
2 rows in Set (0.00 sec)

Insert test data:
MariaDB [music]>INSERT INTO summary values (1, ' Eric Gao is a Oracle DBA ');
Query OK, 1 row Affected (0.00 sec)

MariaDB [music]>INSERT INTO summary values (2, ' Eric Gao is a MySQL DBA ');
Query OK, 1 row affected (0.05 sec)

MariaDB [music]>INSERT INTO summary values (3, ' Eric Gao is a AIX SA ');
Query OK, 1 row Affected (0.00 sec)

MariaDB [music]>INSERT INTO summary values (4, ' Eric Gao is a Linux SA ');
Query OK, 1 row affected (0.01 sec)

MariaDB [music]>select * from summary;
+------+--------------------------+
| ID | info |
+------+--------------------------+
| 1 | Eric Gao is a Oracle DBA |
| 2 | Eric Gao is a MySQL DBA |
| 3 | Eric Gao is a AIX SA |
| 4 | Eric Gao is a Linux SA |
+------+--------------------------+
4 rows in Set (0.00 sec)

Perform a mysqldump backup:
[Email protected] ~]#mysqldump-u root--databases music--lock-all-tables--flush-logs >/tmp/music.sql
[Email protected] ~]#cd/tmp
[Email protected] tmp]#Ls-lt
Total 86744
-rw-r--r--. 1 root root 1948 Mar 4 05:58 Music.sql


Delete the database and verify that the restore effect is later:
MariaDB [(None)]>drop database music;
Query OK, 2 rows affected (0.03 sec)

MariaDB [(None)]>show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

you can see that the music database is gone.


To restore an entire library:
MariaDB [(None)]>Source/tmp/music.sql
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, 1 row Affected (0.00 sec)

Database changed
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.04 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, 4 rows Affected (0.00 sec)
Records:4 duplicates:0 warnings:0

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows affected (0.01 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, 4 rows affected (0.01 sec)
Records:4 duplicates:0 warnings:0

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)

MariaDB [music]>

Verify that the data has been retrieved:
MariaDB [music]>select * from summary;
+------+--------------------------+
| ID | info |
+------+--------------------------+
| 1 | Eric Gao is a Oracle DBA |
| 2 | Eric Gao is a MySQL DBA |
| 3 | Eric Gao is a AIX SA |
| 4 | Eric Gao is a Linux SA |
+------+--------------------------+
4 rows in Set (0.00 sec)

OK, the data has been restored! ~~~

MYSQL/MARIADB database backup and recovery mysqlpump Getting Started