Mysql备份和恢复

mysqldump -uusername -p datebase >备份文件名.sql

备份语句：　　

mysqldump -uroot -p -B test >/opt/test_bak.sql

恢复备份

mysql -uroot -p </opt/test_bak.sql

mysqldump备份是mysql的逻辑备份，-B参数是关键，表示连接多个库，并且增加use db和create database db的信息。

通过下面的命令查看备份的sql语句

[root@localhost ~]# egrep -v '#|\*|--|^$' /opt/test_bak.sql             <----这里是过滤没用的信息显示正常的备份语句 
USE `test`;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`),
  KEY `index_drop` (`dept`(8)),
  KEY `index_name_dept` (`name`,`dept`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'student1',0,NULL),(2,'student2',10,NULL),(3,'0',11,'shuxue'),(4,'007',9,NULL),(5,'008',9,NULL),(6,'student',9,'shuxue'),(7,'name',10,'yuwen'),(8,'hui',10,'meishu');
UNLOCK TABLES;
DROP TABLE IF EXISTS `student1`;
CREATE TABLE `student1` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL,
  `dept` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
LOCK TABLES `student1` WRITE;
UNLOCK TABLES;

分库备份：

mysql -uroot -p -e 'show databases;'|grep -Evi 'database|infor|perfor'|sed -r 's#^([a-z].*$)#mysqldump -uroot -p -B \1|gzip >/opt/\1.sql.gz#g'|bash

备份库需要查看当前所有库 并且要排除不必要备份的库

做排除前：

[root@localhost ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| School             |
| mysql              |
| mysql_binlogs      |
| performance_schema |
| sys                |
| test               |
+--------------------+

做排除后：sed 2d 表示从第二行以后开始，egrep -v 'School|sys|performance_schema'排除其余不用备份的库

[root@localhost ~]# mysql -uroot -pleadchina -e 'show databases;'|sed 2d |egrep -v 'School|sys|performance_schema'
mysql: [Warning] Using a password on the command line interface can be insecure.
Database
mysql
mysql_binlogs
test

 

创建数据库备份脚本

#!/bin/sh
BAKPATH=/server/backup
MYUSER=root
MYPASS=leadchina
SOCKET=/var/lib/mysql/mysql.sock                                           这个不知道位置可以通过find / -name mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYDUMP="mysqldump  -u$MYUSER -p$MYPASS -S $SOCKET -x -B -F -R"
[ ! -d $BAKPATH ] && mkdir -p $BAKPATH                                     判断是否存在BAKPATH这个目录 如果不存在则创建相应目录
DBLIST=`$MYCMD -e "show databases;"|sed 1d| egrep -v "mysql|sys"`
for dbname in $DBLIST
do
    $MYDUMP $dbname|gzip >$BAKPATH/${dbname}_$(date +%F).sql.gz
done

 

• 对于表的备份

语法：

　　mysql -uroot -p database_name table_name >/path/table_name.sql

 

[root@localhost ~]# mysql -uroot -pleadchina -e 'show tables from test;'|sed 1d 
mysql: [Warning] Using a password on the command line interface can be insecure.
school
student

 

脚本：

#!/bin/sh
BAKPATH=/server/backup
MYUSER=root
MYPASS=leadchina
SOCKET=/var/lib/mysql/mysql.sock
MYCMD="mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYDUMP="mysqldump  -u$MYUSER -p$MYPASS -S $SOCKET -x -F -R"
[ ! -d $BAKPATH ] && mkdir -p $BAKPATH
DBLIST=`$MYCMD -e "show databases;"|sed 1d| egrep -v "mysql|sys|information_schema|mysql_binlogs|performance_schema|information_schema"`
for dbname in $DBLIST
do
    TABLELIST=`$MYCMD -e "show tables from $dbname;"|sed 1d`
        for tablename in $TABLELIST
            do
              [ ! -d $BAKPATH/$dbname ] && mkdir -p $BAKPATH/$dbname
              $MYDUMP $dbname $tablename|gzip >$BAKPATH/$dbname/${tablename}_$(date +%F).sql.gz
           done
     $MYDUMP -B $dbname|gzip >$BAKPATH/${dbname}_$(date +%F).sql.gz
done

 

• -d 为备份表结构

　   语法：

　　　　mysqldump -uroot -pleadchina -b databasename >/opt/database_structure.sql

 

• 规范化备份：

myisam备份推荐使用：　　

mysqldump -uroot -pleadchina -B -A --master-data=1 --flush-privileges --events -x|gzip >/opt/db.sql.gz

innodb备份推荐使用：

mysqldump -uroot -pleadchina -B -A --master-data=1 --single-transction --flush-privileges --events -x|gzip >/opt/db.sql.gz