Tips: 本文创建于2013年11月14日,已超过 2 年,内容或图片可能已经失效!

mysqldump 备份还原

mysqldump备份整个数据库

语法:mysqldump --all-databases -u 用户名 -p密码 -h 主机 > 文件名

--all-databases 可以用-A表示

mysqldump --all-databases -u username -ppassword -h localhost > sql.sql  

mysqldump备份单个数据库

语法:mysqldump -u 用户名 -p密码 数据库名称 > 文件名

mysqldump -u username -ppassword 数据库名 > sql.sql  

恢复数据库

mysql -uroot -ppasswd < sql.sql  
mysql -uroot -ppasswd newdatabase < sql.sql  #恢复到新的数据库
# 或
mysql -uroot -ppasswd  
mysql>use 数据库;  
mysql>source sql.sql;  

mysql 导入sql文件

mysql -uroot -ppasswd test < ./Downloads/20140514pm-new/20140514shop.sql  

mysqldump 压缩

mysqldump -hhostname -uuser -ppassword dbname | gzip -9 > dbname.gz  
gunzip < dbname.gz | mysql -uuser -ppassword dbname  

仅仅导出函数和存储过程,不导出表结构和数据

mysqldump -uuser -ppassword -ntdR --triggers=false dbname > dbname.sql  
  • -d : --no-create-db
  • -n : --no-data
  • -t : --no-create-info
  • -R : 导出function和procedure
  • --triggers : 导出时把触发器关闭

如果出现下面问题:

ErrorCode:1418
This function has none of DETERMINISTIC, NOSQL, or READS SQL DATA inits declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

在mysqld中添加参数

log-bin-trust-function-creators=1 

导出数据库的一个表

mysqldump -uuser -ppassword dbname tablename > tablename.sql 

导出多个数据库

mysqldump -hhostname -uuser -ppassword -opt --databases dbname1 dbname2 dbname3 | gzip -9 > 123db.gz
  • --add-drop-database 每个数据库创建之前添加drop数据库语句。
  • --add-drop-table 每个数据表创建之前添加drop数据表语句。(默认为打开状态,使用--skip-add-drop-table取消选项)
  • --add-locks 在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态,使用--skip-add-locks取消选项)
  • --lock-all-tables, -x 提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭--single-transaction 和--lock-tables 选项。
  • --opt 等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用--skip-opt禁用.
  • --routines, -R 导出存储过程以及自定义函数。
mysqldump -uroot -p --host=localhost --all-databases --routines

一个例子

#!/bin/bash
/data/software/mysql/bin/mysqldump -uroot -ppassword -A -F -E -R --triggers --single-transaction -S /data/software/mysql/mysqld/mysql.sock | gzip -9 > /data/mysqlbackup/`date +%Y%m%d_%H%M%S`.all.sql.gz