Backup and Recovery for MySQL

Backup and recovery for MySQL:

[Email protected] mysqldump]# mysqldump-uroot-p123qwe-h10.39.3.110 sales >/data0/mysqldump/sales_bak.sql
[Email protected] mysqldump]# egrep-v "#|\*|--|^$"/data0/mysqldump/sales_bak.sql

[Email protected] mysqldump]# mysql-uroot-h10.39.3.110-p123qwe-e "use sales; drop TABLE Vendors "
[Email protected] mysqldump]# mysql-uroot-h10.39.3.110-p123qwe-e "use sales; Show tables; "
+-----------------+
| Tables_in_sales |
+-----------------+
| Produtcustomers |
| Customers |
| OrderItems |
| Orders |
| Producnotes |
+-----------------+

[[email protected] mysqldump]# mysql-uroot-h10.39.3.110-p123qwe Sales </data0/mysqldump/sales_bak.sql
[[email protected] mysqldump]# mysql-uroot-h10.39.3.110-p123qwe-e "use sales; Show tables; "
+-----------------+
| Tables_in_sales |
+-----------------+
| Produtcustomers |
| customers |
| orderitems |
| orders |
| producnotes |
| vendors |
+-----------------+

[Email protected] mysqldump]# Mysqldump-uroot-p123qwe-h10.39.3.110-b sales >/data0/mysqldump/sales_b_bak.sql
[Email protected] mysqldump]# diff sales_bak.sql Sales_b_bak.sql
18a19,26
>-Current Database: ' Sales '
>--
>
> CREATE DATABASE/*!32312 IF not exists*/' sales '/*!40100 DEFAULT CHARACTER SET UTF8 */;
>
> use ' Sales ';
>
>--
172a181,186
>-Current Database: ' Sales '
>--
>
> use ' Sales ';
>
>--
200c214
<--Dump completed on 2016-03-24 18:03:03
---
>--Dump completed on 2016-03-24 18:20:27

The-B option is to increase the option to create and connect to the database

[Email protected] mysqldump]# Mysql-uroot-h10.39.3.110-p123qwe Sales </data0/mysqldump/sales_bak.sql
ERROR 1049 (42000): Unknown database ' Sales '

[Email protected] mysqldump]# Mysql-uroot-h10.39.3.110-p123qwe </data0/mysqldump/sales_b_bak.sql
[Email protected] mysqldump]# mysql-uroot-h10.39.3.110-p123qwe-e "use sales; Show tables; "
+-----------------+
| Tables_in_sales |
+-----------------+
| Produtcustomers |
| Customers |
| OrderItems |
| Orders |
| Producnotes |
| Vendors |
+-----------------+

Optimize the size of your input to make it smaller and fit for debugging
[Email protected] mysqldump]# mysqldump-uroot-p123qwe-h10.39.3.110--compact-b sales >/data0/mysqldump/sales_com Pact_b_bak.sql

Backup compression (nearly 3 times times compression efficiency)
[Email protected] mysqldump]# mysqldump-uroot-p123qwe-h10.39.3.110-b sales|gzip >/data0/mysqldump/sales_b_bak_ Gzip.sql.gz
[email protected] mysqldump]# LL
Total 28
-rw-r--r--1 root root 7266 Mar 18:03 sales_bak.sql
-rw-r--r--1 root root 1780 Mar 18:47 sales_B_bak_gzip.sql.gz
-rw-r--r--1 root root 7456 Mar 18:20 sales_b_bak.sql
-rw-r--r--1 root root 4511 Mar 18:35 sales_compact_b_bak.sql

How the Mysqldump works:

The process of backing up data with the mysqldump command is actually the process of outputting or producing a backup file directly from the MySQL library in a logical SQL statement, so simple

Backing up multiple libraries
[Email protected] mysqldump]# mysqldump-uroot-p123qwe-h10.39.3.110-b sales cider 08day5 |gzip >/data0/mysqldump/s Ales_cider_08day5.sql.gz

How the Mysqldump works

The process of backing up data with the mysqldump command is actually the process of outputting or producing a backup file directly from the MySQL library in a logical SQL statement, so simple

Backing up multiple libraries
[Email protected] mysqldump]# mysqldump-uroot-p123qwe-h10.39.3.110-b sales cider 08day5 |gzip >/data0/mysqldump/s Ales_cider_08day5.sql.gz

[Email protected] mysqldump]# mysql-uroot-h10.39.3.110-p123qwe-e "show Databases" |grep-evi "DATABASE|INFORMATION_SC Hema|performance_schema|mysql|test|08day5 "|sed" s#^ #mysql-uroot-h10.39.3.110-p123qwe-b#g "
Mysql-uroot-h10.39.3.110-p123qwe-bcider
Mysql-uroot-h10.39.3.110-p123qwe-bkeystone
Mysql-uroot-h10.39.3.110-p123qwe-bsales

[Email protected] mysqldump]# mysql-uroot-h10.39.3.110-p123qwe-e "show Databases" |grep-evi "DATABASE|INFORMATION_SC Hema|performance_schema|mysql|test|08day5 "|sed-r" s#^ ([a-z].*$) #mysqldump-uroot-h10.39.3.110-p123qwe-b \1|gzip >/data0/\1.sql.gz#g "
Mysqldump-uroot-h10.39.3.110-p123qwe-b cider|gzip >/data0/cider.sql.gz
Mysqldump-uroot-h10.39.3.110-p123qwe-b keystone|gzip >/data0/keystone.sql.gz
Mysqldump-uroot-h10.39.3.110-p123qwe-b sales|gzip >/data0/sales.sql.gz

[Email protected] mysqldump]# mysql-uroot-h10.39.3.110-p123qwe-e "show Databases" |grep-evi "DATABASE|INFORMATION_SC Hema|performance_schema|mysql|test|08day5 "|sed-r" s#^ ([a-z].*$) #mysqldump-uroot-h10.39.3.110-p123qwe-b \1|gzip >/data0/mysqldump/bak/\1.sql.gz#g "|bash

[Email protected] mysqldump]# ls/data0/mysqldump/bak/
cider.sql.gz keystone.sql.gz sales.sql.gz

Method 1:
[Email protected] mysqldump]# mysql-uroot-h10.39.3.110-p123qwe-e "show Databases" |grep-evi "DATABASE|INFORMATION_SC Hema|performance_schema|mysql|test|08day5 "|sed-r" s#^ ([a-z].*$) #mysqldump-uroot-h10.39.3.110-p123qwe--events-b \ 1|gzip >/data0/mysqldump/bak/\1.sql.gz#g "|bash

Method 2:
For loop backup
For data_name in ' mysql-uroot-h10.39.3.110-p123qwe-e ' show Databases "|grep-evi" Database|information_schema|performa Nce_schema|mysql|test|08day5
"';d o
Mysqldump-uroot-h10.39.3.110-p123qwe-b $data _name|gzip >/data0/mysqldump/bak/${data_name}.sql.gz
Done

Backup table:
The first one is the library, which can be multiple tables later
[Email protected] mysqldump]# mysqldump-uroot-h10.39.3.110--compact-p123qwe Sales Customers > Sales_customers.sql

Backup and Recovery for MySQL