MySQL数据导入导出

mysqldump常用于MySQL数据库逻辑备份。

最简单的用法

mysqldump -uroot -pPassword [database name] 

上述命令将指定数据库备份到某dump文件（转储文件）中，比如：

mysqldump -uroot -p123 -P3306 test > test.dump

生成的test.dump文件中包含建表语句（生成数据库结构哦）和插入数据的insert语句。

–opt

如果加上–opt参数则生成的dump文件中稍有不同：

建表语句包含drop table if exists tableName

insert之前包含一个锁表语句lock tables tableName write，insert之后包含unlock tables

跨主机备份

使用下面的命令可以将host1上的sourceDb复制到host2的targetDb，前提是host2主机上已经创建targetDb数据库：

mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb-C指示主机间的数据传输使用数据压缩

实例：

mysqldump -ubcetest -pbcetest --host 10.107.42.56 -P6666 bce_bmr > polo.r

只备份表结构

mysqldump --no-data --databases mydatabase1 mydatabase2 mydatabase3 > test.dump

或者

mysqldump　--opt　-d　数据库名　-u　root　-p　> xxx.sql

将只备份表结构。–databases指示主机上要备份的数据库。

如果要备份某个MySQL主机上的所有数据库可以使用–all-databases选项，如下：

mysqldump --all-databases > test.dump

只备份数据

mysqldump　-t　数据库名　-uroot　-p　>　xxx.sql

备份select的数据

在命令行下：

mysql -e "select * from myTable" -u myuser -pxxxxxxxx mydatabase > mydumpfile.txt

或者在mysql下：

select count(1) from table  into outfile '/tmp/test.xls';

查询都自动写入文件：

mysql> pager cat > /tmp/test.txt ;

备份insert形式数据，使用where筛选

格式：

mysqldump --host 主机 -p密码 -P端口 -u用户名 库名 表名 --where=" 筛选条件 " > 文件.sql

实例：

mysqldump -uroot -p123456 meteo sdata --where=" sensorid=11 and fieldid=0" > /home/xyx/Temp.sql

导出特定表的结构

mysqldump　-uroot　-p　-B　数据库名　--table　表名　>　xxx.sql

设置导出编码

MySQL服务器上的字符集是 latin1 的，mysqldump 出来的中文是乱码。一个简单的办法是加上默认字符集选项

--default-character-set=utf8

实例：

mysqldump -uroot -p555ttt%%%TTT --default-character-set=utf8 -P3306 -hlocalhost --no-data mis_es > mis_es.dump

导出建表语句

mysqldump -uroot -p123817 --host 180.76.144.167 -d cloud_api auth_permission auth_user auth_user_groups auth_user_user_permissions customer dashboard_userdashboardmodule django_admin_log django_content_type django_migrations django_session > createtab.sqlmysqldump -uroot -p123817 --host 180.76.144.167 -d cloud_api jet_bookmark jet_pinnedapplication > createjet.sql

从备份文件恢复数据库

mysql [database name] < [backup file name]mysql　数据库名　<　文件名 

结合Linux的cron命令实现定时备份

比如需要在每天凌晨1:30备份某个主机上的所有数据库并压缩dump文件为gz格式，那么可在/etc/crontab配置文件中加入下面代码行：

30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz

前面5个参数分别表示分钟、小时、日、月、年，星号表示任意。date ‘+%m-%d-%Y’得到当前日期的MM-DD-YYYY格式。

一个完整的Shell脚本备份MySQL数据库示例

#vi /backup/backup.sh#!bin/bashcd /backupecho "You are in backup dir"mv backup* /oldbackupecho "Old dbs are moved to oldbackup folder"File = backup-$Now.sqlmysqldump -u user -p password database-name > $Fileecho "Your database backup successfully completed"

上面脚本文件保存为backup.sh，并且系统中已经创建两个目录/olcbackup和/backup。每次执行backup.sh时都会先将/backup目录下所有名称为backup开头的文件移到/oldbackup目录。

为上述脚本制定执行计划如下

#crontab -e30 1 * * * /backup.sh

导入数据

mysql> source d:/myprogram/database/db.sql;

或者

mysql　数据库名　<　文件名;

导出特定的表（批量）

导出语句

mysqldump --no-data -ucantor_admin -p'N8>3([yvZ5YE-f' --host dx-data-mysql-cantor02 -P5002 --skip-lock-tables database table1 table2 table3 > polo.r

mysqldump -ucantor_admin -p'N8>3([yvZ5YE-f' --host dx-data-mysql-cantor02 -P5002 --skip-lock-tables cantor queue > queue.sql

导入语句

mysql -h data-mysql-scheduler-write.vip.sankuai.com -P 5002 -u union_admin  -p'G7RZt1mZZreK8d' unionreload < ./polo.r 

mysql -h data-mysql-scheduler-write.vip.sankuai.com -P 5002 -u union_admin  -p'G7RZt1mZZreK8d' unionreload <  queue.sql

遇到的问题

端口-P和密码的-p写混淆
密码写错

mysqldump access denied for user when using lock tables

这个问题见http://www.111cn.net/database/mysql/58996.htm
有两种解决方式：

--single-transaction

或者

--skip-lock-tables

锁表不能执行：就不用mysql的导入，直接执行sql