使用mysql和pgsql如何做数据库的备份和恢复备份操作

---

前言

工作中遇到了数据库迁移操作，但是使用传统navicate数据连接工具可能出现一系列问题，比如数据丢失，所以结合个人情况使用mysql以及pgsql命令的方式来做迁移，使用前前提必须安排mysql或者pgsql。

一、mysql如何做备份？

1.1、备份一个数据库

mysqldump -u用户名 -p密码 数据库名 > 导出的文件名
例如：

 mysqldump -hhostname -uroot -ppwd dataname >dataname.sql

1.2、备份所有数据库

mysqldump -h *.*.*.* -p -u username -p password --all-databases > /data/backup/mysql_db.sql

1.3、备份一个表

例如：

mysqldump -hhostname -uroot -ppwd dataname specific_tabname1 specific_tabname2> dataname_specific_tabname.sql

1.4、 备份多个数据库

mysqldump -hhostname -uusername -ppwd --databases dbname1 dbname2 dbname3 > multibackupfile.sql

1.5、导出一个数据库结构

 mysqldump -hhostname -uroot -ppwd  -–add-drop-table --no-data dataname > dataname_nodata.sql

–no-data:没有数据
–-add-drop-table 在每个create语句之前增加一个drop table命令

1.6、备份压缩

2、备份压缩

格式：mysqldump -h主机IP -P端口 -u用户名 -p密码 --database 数据库名 | gzip > 文件名.sql.gz
导出的数据有可能比较大，不好备份到远程，这时候就需要进行压缩

mysqldump -h *.*.*.* -p -u username -p password --database mysql | gzip > /data/backup/mysql.sql.gz

二、mysql恢复备份

2.1、还原MySQL数据库的命令

#database为数据库名

mysql -h ... -u username -p password database < backupfile.sql

2.2、还原压缩的MySQL数据库

database为数据库名

gunzip < backupfile.sql.gz | mysql -u username -p password database

2.3、将数据库转移到新服务器

database为数据库名

mysqldump -u username -p password database | mysql –host=*.*.*.* -C database

2.4、导入数据库

常用source命令，用use进入到某个数据库，mysql>source d:\test.sql，后面的参数为脚本文件。

三、pgsql如何做备份

linux系统上的备份（此处介绍单个数据库迁移到另外一台服务器工作）

-h 连接数据库ip地址
-p 数据库端口
-U 数据库用户
-c 重新创建之前,先清除数据库对象
-d 指定数据库
-f 指定备份到本地的文件路径

pg_dump -h 120.55.163.126 -p 5432 -U postgres -c -d homestead_pengze_prod -f
/ws/db-bak/homestead_pengze_prod.bak

提示：window安装的话进入pgsql的bin目录使用cmd目录执行命令

迁移：
将备份的文件，复制到目的地服务器目录（如/ws）,在另外一台服务器创建同名数据库，目的地服务器使用docker安装pgsql
先将备份脚本拷贝到容器内

docker cp homestead_pengze_prod.bak postgresql:/

进入容器中执行下面恢复备份脚本

docker exec -it postgresql /bin/sh

四、pgsql恢复备份

还原脚本：
-d 要还原的数据库
-f 备份文件

psql -h 127.0.0.1 -p 5432 -U postgres -d homestead_pengze_prod -f
homestead_pengze_prod.bak