MySQL backup and recovery +error 1046

mysqldump -uroot-p123456 --default-character-set=utf8 Quan >/opt/quan.sql//Backup

grep -ev "#|\/|--|^$" quan.sql//View files

DROP TABLE IF EXISTS ' test '; Delete an old table

LOCK TABLES ' test ' WRITE; Lock

UNLOCK TABLES; Unlock

mysql-uroot-p123456 Quan </opt/quan.sql//recovery

Add more of the following two lines to the database to enter the table when backing up

Debug Debug --compact //Reduce output comment optimize backup size no lock added

> CREATE DATABASE/*!32312 IF not exists*/' Quan '/*!40100 DEFAULT CHARACTER SET GBK */;

> Use ' Quan ';

Recovery mysql-uroot-p123456 </OPT/QUAN_B.SQL//Direct recovery on line

mysqldump-uroot-p123456 Quan | gzip >/OPT/QUAN.SQL.GZ// compression

Gunzip quan.sql.gz//decompression, re-recovery

--databases,-B: For backing up multiple databases, if there is no option, Mysqldump takes the first name parameter as the database name, followed by the table name. With this option, Mysqldump takes each name as the database name.

mysqldump-uroot-p123456- b Quan He | gzip >/opt/quan.sql.gz //Backup multiple libraries

mysqldump-uroot-p123456 Quan Test test1 | gzip >/opt/quan.sql.gz//Backup single table

Sub-Library Backup command

Mysql-uroot-p123456-e "show databases;" |  Grep-evi "Database|infor|perfor" | Sed ' s#^ #mysqldump-uroot-p123456-b #g '

Mysql-uroot-p123456-e "show databases;" |  Grep-evi "Database|infor|perfor" | Sed-r ' s#^ ([a-z].*$) #mysqldump-uroot-p123456-b \1 | gzip >/opt/\1.sql.gz#g ' | Bash

MySQL full-volume export encountered the following alarm: The default is not to back up the event table, only add--events will not warn

Warning:skipping The data of table mysql.event. Specify the--events option explicitly

Workaround:

--events--ignore-table=mysql.event

Script Loop Backup sub-Library

#!/bin/bash

For name in ' Mysql-uroot-p123456-e ' show databases; ' | Grep-evi "Database|infor|perfor"

Do

mysqldump-uroot-p123456--events--ignore-table=mysql.event-b $name | gzip >/opt/${name}.sql.gz

Done

Backup table Structure

mysqldump-uroot-p123456- D Quan >/opt/quan1.sql// --no-data,-D: Export only table structure

Back up data only

mysqldump-uroot-p123456- t Quan >/opt/quan1.sql//- t = --no-create-info< /c2>

Export all databases

--all-databases,-A

Export all table spaces

--all-tablespaces, Y

mysqldump-uroot-p123456 -A- b--events >/opt/quan1.sql//Backing up the entire database

Bin-log Log

Vim/etc/my.cnf

#Log-bin=mysql-bin//Off

mysql/data/mysql-bin.000001

Mysqldump- F //Refresh Bin-log Log

--master-data=2/1//Add Bin-log log file name and corresponding location point

First comment out my.cnf [client] #default-character-set=utf8

Mysqlbinlog /applicatiom/mysql/data/mysql-bin.000001

-B--compact-a-b-f-X Lock Table-L read-only lock table-d-t

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Recovery

①use Quan;

mysql> Source /root/quan.sql

②# MySQL -uroot-p123456 Hequan </opt/hequan.sql

Cat. MY.CNF Settings Login

[Client]

User=root

Host=localhost

password=123456

Ifconfig eth0 | Awk-f "[:]+" ' nr==2 {print $4} '

192.168.10.11

Multi-Library File recovery

For name in ' LS *.sql| Sed ' s#.sql# #g '; Do mysql-uroot-p123456 < ${name}.sql; Done

ERROR 1046 (3d000) at line 22:no database selected

Modify. sql to precede 22 lines with the use library name;

mysql-e ""//execute MySQL command outside

Mysql> Show full processlist; Connection conditions

Mysql> Show variables like "%log_bin%";

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| Log_bin | OFF |

| log_bin_trust_function_creators | OFF |

| Sql_log_bin | On |

+---------------------------------+-------+

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

This article is from the "what-all" blog, please be sure to keep this source http://hequan.blog.51cto.com/5701886/1774511

MySQL backup and recovery +error 1046