mysqldump 备份数据

转载

mob604756fea1c5 2020-10-15 06:19:00

文章标签 mysql sql 数据数据库表名 文章分类 代码人生

linux mysqldump单独安装：

yum -y install holland-mysqldump.noarch

mysqldump备份简单操作：

mysqldump -h[ip] -P[端口号] -u[用户名] -p[密码] 数据库名表名 >导出的文件名.sql

mysqldump -h[ip] -P[端口号] -u[用户名] -p[密码] 数据库名表名1 表名2 表名3 | gzip >导出的文件名.sql.g

转了这个后mysqldump和mysql都可以用了。

insert into xx select 可能或锁原表，备份数据最好用mysqldump.

https://help.aliyun.com/knowledge_detail/41732.html

避免表级锁等待

mysqldump默认会启用lock-tables选项，对要导出的表加表级锁，阻止表上的DML操作。

RDS MySQL实例默认支持的InnoDB和TokuDB引擎均支持事务，建议使用single-transaction选项进行导出，而不要设置lock-all-tables或lock-tables选项。

选项名称	默认值	可选值	作用
lock-all-tables	FALSE	TRUE\|FALSE	在数据导出期间设置global read lock，所有库下的所有表在导出期间为只读。自动关闭lock-tables和single-transaction选项。RDS不支持该选项。
lock-tables	TRUE	TRUE\|FALSE	导出期间在导出表上设置表级锁。默认开启。可以通过指定 skip-lock-tables选项来关闭。
single-transaction	FALSE	TRUE\|FALSE	导出操作被放置在一个事务中执行。自动关闭lock-tables选项。

更多表级锁的内容请参见RDS for MySQL表级锁等待。

所以使用线上mysqldump时要加--single-transaciton

1.导出指定表的数据
mysqldump -t database -u username -ppassword --tables table_name1 table_name2 table_name3 >D:\db_script.sql

导出数据不导出结构

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

2.导出指定表的结构
mysqldump -d database -u username -ppassword --tables table_name1 table_name2 table_name3>D:\db_script.sql

3.导出表的数据及结构
mysqldump  database -u username -ppassword --tables table_name1 table_name2 table_name3>D:\db_script.sql

4.若 数据中 ，某些表除外，其余表都需导出
mysqldump -h IP -u username -ppassword --default-character-set=utf8 --database database_name --ignore-table=database_name.table_name1
--ignore-table=database_name.table_name2 --ignore-table=database_name.table_name3 >D:\db_script.sql

mysqldump参数：

-t, --no-create-info

Don't write table creation info.

-d, --no-data No row information.

MYSQL数据库因生产原因需要删除大量数据，因数据量太大接近上亿条，用常规delete删除小数据可以，删除几千万大数据量会非常慢，并且不会释放出磁盘空间，还需要optimize或repair来压缩数据表来释放硬盘空间，时间更长，需要几天时间，太慢了！因此采用新建表，导入无需删除的数据，然后删除老表，把新表改名为老表，步骤如下：

1、基于老表新建新表！

SQL

create table sell_new like sell;

2、插入数据（几千万的数据量一定要分批插入，一次50万为最佳，毕竟mysql的数据处理能力有限），可以按ID查询后插入！

SQL

insert into sell_new select * from sell where itemid>500000 and itemid<=5500000;

新表中只保留有用的数据，硬盘空间得以释放！

3、drop删除掉老表

SQL

drop table sell;

4、重命名新表为“sell”

SQL

alter table sell_new rename to sell;

以上是mysql数据库上亿级大数据如何快速删除操作流程！

repair table用于修复被破坏的表。

optimize table用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了optimize table命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。

多数时间并不需要运行optimize table，只需在批量删除数据行之后，或定期（每周一次或每月一次）进行一次数据表优化操作即可，只对那些特定的表运行。

备份脚本

dateStr=`date -d -1day '+%Y%m%d'`

fileName=sql${dateStr}_

DUMPBIN="mysqldump dbname --no-create-info -h xxx -u xxx -p xxx --opt --default-character-set=utf8mb4 --single-transaction --skip-triggers --skip-lock-tables --tables "

tableArr=(

"xxx"

)

for elem in ${tableArr[@]}

$DUMPBIN $elem > /data/${fileName}_${elem}.sql &

done

特别注意：

如果mysql的密码带有特殊字符如@,在控制台直接运行要加引号括起来，但是在shell脚本中不用，加了返回会提示密码错误:

mysqldump: Got error: 1045: "Access denied for user 'xxx'@'xxx' (using password: YES)" when trying to connect

因为在shell脚本中如果我们加了引号会把引号当做密码的一部分。另外$符号需要转移\$,!不需要转义。

注意2：

每次mysqldump都会默认导出drop table xx,create table xx信息，不要这个可以设置;

--no-create-info参数会跳过create table 语句

mysqldump条件导出：

语法如下：
 mysqldump -u 用户名 -p 数据库名 表名 --where="条件" > 目标文件

mysqldump -uroot -p --default-character-set=utf8 --where="id=36 and post_date_*>='2013-10-25'" dbname tablename> wcj_cz.sql

--opt

该选项是速记；等同于指定 --add-drop-tables--add-locking --create-option --disable-keys--extended-insert --lock-tables --quick --set-charset。它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。该选项默认开启，但可以用--skip-opt禁用。要想只禁用确信用-opt启用的选项，使用--skip形式；例如，--skip-add-drop-tables或--skip-quick。

--quick，-q

该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。

MySQL的source命令导入SQL文件实战记录。

进入 CMD

执行 mysql -uroot -p 输入密码后进入 MySQL 命令提示符

依次执行：

use XXXdatabase;

set charset utf8;

source d:/xxx.sql;

本以为这样就可以挂机等待 sql 文件如期导入了，但是事与愿违，当过一段时间在打开时发现命令行提示链接超时,等待重新链接。

这时候需要再执行以下 sql：

set global max_allowed_packet=100000000;