mysql 之数据导出导入

一. 导出并压缩:

  1. 只导出建表语句:
    mysqldump -uroot -p -d db_name | gzip > /root/DB/db_name.sql.gz

  2. 同时导出表结构及表数据(即:去掉 -d 参数):
    mysqldump -uroot -p db_name | gzip > /root/DB/db_name.sql.gz

  3. 只导出数据,不导出结构:
    mysqldump -uroot -p -t db_name table_name | gzip > /root/DB/tbl_name.sql.gz

二. 导入:

解压命令:gunzip db_name.sql.gz

导入sql文件:
mysql -u root -P3306 -p db_name < ./db_name.sql

授权:
grant all privileges on . to 'db_user'@'%' identified by 'xxxxxxx';

FLUSH PRIVILEGES;

参考文档来源

三.直接命令行导入:

在命令行执行 source crt_tbl.sql

补充:
快速导入千万级的记录:
https://blog.csdn.net/qq_40298902/article/details/89336086:
参考命令:
load data infile ‘D:/import_file.csv’ into table dest_table_name fields terminated by ‘,’ optionally enclosed by ‘"’ escaped by ‘"’ lines terminated by ‘\n’;

  1. 导入文件需要放在 show variables like "%secure_file_priv%"; 这个变量指定的路径
  2. (实际效果存疑)导入文件的大小要在这个变量允许的范围内: max_allowed_packet=500M , show variables like "%max_allowed_packet%" (数字的单位为 bytes)

四. Windows环境直接执行 sql 文件

命令行执行: mysql -u username -p -D dbname < ./filename.sql

五. 使用事务

begin;

select …;
update …;
insert …;

commit; [Or: rollback;]

mysql 异常处理之:too many connections

在批量执行多个脚本同时连接NAS数据库时,windows 机器上经常会报“too many connections”而异常退出。

原因及解决方案:

一. 修改连接数限制:
NAS 默认是150个最大连接,这里改成500
set GLOBAL max_connections=500;

这个命令用于查询设置的最大连接数:
show variables like "max_connections";

这个命令用于查询当前占用连接数的进程:
show processlist;

二.及时释放长期 sleep 的连接
要从源头解决问题,本质上,应该要让数据库自动杀死那些sleep的进程。

查询连接超时时间:
show global variables like ‘wait_timeout’;

设置连接超时的时间, NAS默认超时时间:28800s,这里改成300s
set global wait_timeout=500;

修改这个数值,表示mysql在关闭一个连接之前要等待的秒数
NAS默认超时时间:28800s
set global interactive_timeout=500;

或者直接改配置文件,然后重启mysql服务:
打开配置文件 添加一下配置 vi /etc/my.cnf
wait_timeout = 500
interactive_timeout = 500

参考资料

mysql 生成 日期 序列

生成 2019-05-01 至 2019-05-31 起止的日期序列:

set @i = -1;
set @sql = repeat(" select 1 union all",-datediff(‘2019-05-01′,’2019-05-31’)+1);
set @sql = left(@sql,length(@sql)-length(" union all"));
set @sql = concat("select date_add(‘2019-05-01’,interval @i:=@i+1 day) as date from (",@sql,") as tmp");
prepare stmt from @sql;

execute stmt

参考来源:mysql生成日期序列

方法二:直接使用excel生成填充序列,然后针对选中区域指定以工作日填充

mysqld.log 日志报错及应对

一. 日志配置介绍:

两条很有用的命令:
查看服务器中有哪些日志
[root@dev ~]# lsof -nc mysqld | grep -vE ‘(.so(..*)?$|.frm|.MY?|.ibd|ib_logfile|ibdata|TCP)’

通过PID来查看
[root@dev ~]# sudo lsof -p 26379 | grep -vE ‘(.so(..*)?$)’

二. 遇到的报错处理案例:

案例1:
2019-06-08T10:07:40.096212Z 8 [Warning] [MY-010055] [Server] IP address ‘xxx.xxx.xxx.xxx’ could not be resolved: Name or service not known
这个警告信息几乎每次查询都会报
原因:
mysql默认会进行反向解析,把IP解析成DNS。
影响:如果机器所在网络对DNS的响应比较慢,频繁地查询数据库和权限检查,这大大增加了数据库的压力,导致数据库连接缓慢,严重的时候甚至死机。

解决办法:禁用dns反查即可
进入/etc 找到mysql的配置文件my.cnf(linux环境下)或者my.ini(windows环境下)进行编辑加入如下一行即可:
[mysqld]
skip-name-resolve

后续: 禁用域名反向解析后,原来使用 localhost 配置的表数据都无法正常使用, wordpress 数据库用户失效, 网页访问失败。
又取消了对域名反向解析的禁用。

mysql my.cnf配置介绍

路径: /etc/my.cnf
官方介绍:https://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
不同路径下 my.cnf的作用域: https://dev.mysql.com/doc/refman/8.0/en/option-files.html

列出系统中所有 my.cnf, 命令: locate my.cnf
默认加载 my.cnf 读取优先级: mysql –help |grep my.cnf

配置文件默认内容:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#skip-grant-tables
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Mysql Lost connection to MySQL server during query

如何解决 MYSQL 经常遇到 Lost connection to MySQL server 报错
报错信息:"Lost connection to MySQL server during query"

官方文档说明:https://dev.mysql.com/doc/refman/5.7/en/error-lost-connection.html

有3种可能的情况:

  1. 网络连接问题导致:报错信息中包含“during query”基本上就是这种原因导致
    一般会出现这种情况是因为一个或多个查询里发送了很多的行
    解决方案:把参数 net_read_timeout 从默认的30秒调整为60秒或者更多,保证数据有足够的时间传输完成
  2. 另外一种可能是发生在客户端与服务端建立连接的时间过长,超时后连接中断。可通过执行:SHOW GLOBAL STATUS LIKE ‘Aborted_connects’ 来检查这种情况发生的次数。
    解决方案: 把参数 connect_timeout 调整到大一些的数值
  3. 如果上述的情况都不符合,那有可能是 BLOB 字段的数据超过了: max_allowed_packet 这个参数的限制。有时会提示 ER_NET_PACKET_TOO_LARGE。
    解决方案:调大参数 max_allowed_packet

附相关命令:
show variables like ‘%timeout%’;
SHOW VARIABLES like ‘net_read_timeout’; — default: 30
SHOW VARIABLES like ‘connect_timeout’; — default: 10
SHOW VARIABLES like ‘max_allowed_packet’; — default: 67108864
SHOW VARIABLES like ‘net_write_timeout’; — default: 60
SHOW GLOBAL STATUS LIKE ‘Aborted_connects’; — default: 53

SET net_read_timeout = 60;
SET GLOBAL connect_timeout = 30;

发现用数据库的root用户在命令行设置的在db重启过后会恢复成默认
还是直接改 /etc/my.cnf, 在 [mysql] 模块下新增

connect_timeout=30
net_read_timeout=60

重启后生效: /bin/systemctl restart mysqld.service