mysqldump 备份数据
2020-10-15 06:19 youxin 阅读(503) 评论(0) 编辑 收藏 举报https://www.jianshu.com/p/7b43e6481c75
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
如果不要锁表用
mysqldump -u username -ppassword --skip-triggers --skip-lock-tables -d --databases dbname1 dbname2>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、基于老表新建新表！
create table sell_new like sell;2、插入数据（几千万的数据量一定要分批插入，一次50万为最佳，毕竟mysql的数据处理能力有限），可以按ID查询后插入！
insert into sell_new select * from sell where itemid>500000 and itemid<=5500000;新表中只保留有用的数据，硬盘空间得以释放！
3、drop删除掉老表
drop table sell;4、重命名新表为“sell”
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"
"xxx"
)
for elem in ${tableArr[@]}
do
$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;set global net_buffer_length=100000;set global interactive_timeout=28800000;set global wait_timeout=28800000;
以上语句的解释：
max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小
net_buffer_length=XXX TCP/IP 和套接字通信缓冲区大小,创建长度达 net_buffer_length 的行
interactive_timeout 对后续起的交互链接有效时间
wait_timeout 对当前交互链接有效时间。
