700字范文 > mysql数据库mysqldump还原_mysqldump数据库备份与恢复

mysql数据库mysqldump还原_mysqldump数据库备份与恢复

时间：2019-04-17 04:34:02

mysqldump -u 用户名 -p 数据库名> 备份的文件名

本文中因服务器为多实例，所以在执行登陆等命令时指定了-S参数，即指定其中一个数据库

备份：

mysqldump -u root -p'root' oldboy>/opt/oldboy.sql[root@MySQL opt]# mysql -S /data/3307/mysql.sock -uroot -p -e "show variables like '%character%'"

Enter password:+--------------------------+--------------------------------------------------------------+

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql-5.7.9-linux-glibc2.5-x86_64/share/charsets/ |

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

指定字符集备份:

[root@MySQL opt]# mysqldump -u root -p'root' --default-character-set=utf8 oldboy>/opt/oldboy.sql

备份参数：

-e : 后面可以执行mysql 命令用;分开-B ：参数的作用是增加创建数据库和连接数据库的命令，即create database，use database

-d : 备份表结构-t : 备份数据-A ：alldatabases-F : 刷新binlog日志文件，切割binlog-x,--lock-all-tables 锁表

-l,--lock-tables 只读锁表--master-data=1(=2) : 增加binlog日志文件名及对应的位置点--compact ：去掉注释，适用于debug，生产不用--single-transaction : 适合innodb事务数据库备份

InnoDB表在备份时，通常启用选项 --single-transaction来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了的数据。

库名|gzip：压缩备份(mysql数据都是文本，压缩效率高)

myisam备份时，将锁整个表，导致表无法进行访问

还原数据库：

[root@MySQL opt]# mysql -uroot -p'root' oldboy

查看备份内容：

[root@MySQL opt]# egrep -v "#|\*|--|^$" /opt/oldboy.sql

查看字符集：

[root@MySQL opt]# mysql -uroot -p'root' -e "show variables like '%character%'"

myqsl多库备份：

[root@MySQL opt]# mysqldump -uroot -p'root' -B (库名)oldboy (库名)ClasssInfo (库名)oldboy_gbk|gzip >/opt/mysql_bak.sql.gz

mysql分库备份：

每个库一个备份文件

shell脚本：

for dbname in 'mysql -uroot -p'root'-e "show databases;"|grep -Evi "database|info|perfor"'

domysqldump-uroot -p'root' -S /data/3307/mysql.sock --events -B $dbname|gzip >/opt/bak/${dbname}_bak.sql.gzdone

备份单个表和多表：

语法：mysqldump -u 用户名 -p 数据库名表名表名表名>备份的文件名

mysqldump -u root -p ClassInfo score>/opt/table1.sql

分库分表备份：

shell脚本：

#！/bin/bash

USER=root

PASSWD=root

SOCK=/data/3306/mysql.sock

dump="mysqldump -u$USER -p$PASSWD -S $SOCK"

for database in$DATABASESdo[! -d /back/$database ] && mkdir -p /back/$database

TABLE=`$LOGIN -e "show tables from $database;"|sed '1d'`for table in$TABLEdo$dump $database $TABLE|gzip >/back/$database/${database}_${table}_$(date +%F).sql.gzdone

只备份表结构和只备份数据：

参数-d 备份表结构

参数-t 备份数据

实例：

[root@MySQL bak]# mysqldump -uroot -p'root' -S /data/3307/mysql.sock --compact -d abbott t2

mysqldump: [Warning] Using a password on the command line interface can be insecure./*!40101 SET @saved_cs_client = @@character_set_client*/;/*!40101 SET character_set_client = utf8*/;

CREATE TABLE `t2` (

`id` int(11) DEFAULT NULL,

`name` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;/*!40101 SET character_set_client = @saved_cs_client*/;

[root@MySQL bak]# mysqldump-uroot -p'root' -S /data/3307/mysql.sock --compact -t abbott t2

mysqldump: [Warning] Using a password on the command line interface can be insecure.

INSERT INTO `t2` VALUES (1,'abbott'),(2,'zx'),(3,'bb');

备份全部数据库：

mysqldump -u root -p'root' -A -B --events|gzip >/opt/all.sql.gz

myisam与innodb备份的区别：

myisam推荐:

mysqldump-uroot -p'root' -A -B --master-data=2 -x|gzip >/opt/all.sql.gz

mysqldump-uroot -p'root' -A -B --all-databases --flush-privileges --lock-all-tables --master-data=1 --flush-logs --triqqers --routines --events --hex-blob|gzip > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql.gz

innodb推荐:

mysqldump-uroot -p'root' -A -B --master-data=2 --singe-transaction|gzip >/opt/all.sql.gz

mysqldump-uroot -p'root' --all-databases --flush-privileges --master-data=1 --singe-transaction --flush-logs --triqqers --routines --events --hex-blob|gzip >$BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql.gz

混合引擎，建议使用myisam的备份方式

恢复数据库实战：

利用source命令恢复数据库

进入mysql数据库控制台,mysql -u root -p 登录后

mysql>use 数据库名

然后使用命令source 命令,后面参数为脚本文件,可以利用system ls 查看文件路径

mysql>source /opt/all.sql.gz

批量恢复脚本实例：

#!/bin/bashgzip -d /opt/*.gz

for dbname in `ls *.sql|sed 's#_bak.sql##g'`;

mysql -u root -p'root'

done