shell下执行mysql查询导出到文件,简单高效

最近在做mysql多表查询生成报表之类的事情,表数据又比较大,大到一个表有几千万行两三个G大,小到几百万行一两个G,而且还要联合查询,又是group,又是子查询,索引都用不上,这样查几个小时不一定出来结果。 无意中试了下shell中执行sql并且导出到文件,平时在Navicat这种图形客户端半天都没结果的sql,在shell小到几分钟,大到个把小时,都不会等太久,而且导出的文件可以直接以TXT导入到数据库或者全部复制,粘贴到Excel中。 刚开始想着进入mysql-client执行查询然后导出到文件,结果失败了,提示没有权限创建文件,提供的导出文件路径只能在mysql安装目录下,其他位置没有权限新建文件,后来了解到用 echo + 管道符就好了,这种方法在root下面执行,不存在权限问题。 导出为TXT 用法: echo "sql语句" | mysql -h* -P* -u* -p 数据库 > /任意位置/xxx.txt 导出为Excel 用法: mysql -h* -P* -u* -p* 数据库 -Bse "set names utf8; select name,email from test" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > /任意位置/xxx.csv 这样虽然方便,但是sql很长的时候就不那么方便了,写到shell里面再合适不过了,完整的shell示例如下: 随便建个shell文件吧,比如 export.sh #!/bin/bash MYSQL=/usr/bin/mysql DB_HOST=127.0.0.1 DB_PORT=3306 DB_USER=root DB_PASSWORD=root DB_DATABASE=test #OUT_FILE=/www/test.txt OUT_FILE=/www/test.csv SQL="select * from mysql" # 导出为txt #echo ${SQL} | ${MYSQL} -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_DATABASE} > ${OUT_FILE} # 导出为csv ${MYSQL} -h${DB_HOST} -P${DB_PORT} -u${DB_USER} -p${DB_PASSWORD} ${DB_DATABASE} -Bse "set names utf8; ${SQL}" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > ${OUT_FILE} 给sh文件执行权限

Nginx反向代理远程MySQL

背景 情况是这样的,我们一个项目的客户是AWS的RDS,因为业务需要,我们要连接客户的RDS,但是客户那边只能添加一台我们机器的IP白名单。 所以我们在AWS这台RDS的同区域买了一台EC2云服务器,然后提供公网IP,这样RDS那边就添加了我们的IP白名单,可以在这台EC2上面连接那台RDS了。。。 但是ubuntu命令行操作数据库始终不太方便啊,比如:查询结果导出为Excel,导出来的文件表头是没有问题,每一条数据也都按行区分,但是多列合并放到第一列里面了,这样数据就没发操作和查看了。。。 所以打算用nginx的反向代理试一下。 准备工作 使用的是nginx的stream模块,测试前需要查看nginx是否在编译时开启了stream模块: nginx -m 如果看到 ngx_stream_module 字样就是已经编译了stream模块(我用的是Tengine)。 反向代理配置 编辑nginx配置文件: vim /etc/nginx/conf/nginx.conf 注意 stream 位于配置文件顶层,和 http 同级。 # nginx 代理 stream { # 设置代理超时时间,设的大一些,避免长连接因为超时时间而中断 proxy_timeout 3d; server { listen 3307; listen [::]:3307; proxy_pass xxx.com:3306; } } 重启nginx # 使修改后的配置文件生效 nginx -c /etc/nginx/conf/nginx.conf # 平滑重启nginx nginx -s reload 测试 客户端连接你的反代机器IP:3307,实际上相当于连接到了xxx.com:3306这个数据库。 如果使用了云服务器,注意在策略组放行 tcp 3307端口。

MySQL每日分表定时备份shell

最近有个需求:项目是21G的mysql数据库,需要做每日备份归档,本来考虑做增量备份的,从时间安排考虑,还是先做个每日全量备份吧。 思路是先连接数据库,切换到要备份的库,然后取出所有表名,接下来遍历每张表,挨个导出为 .sql 文件,放到日期为命名的文件夹里,最后压缩为 .tar.gz 压缩包。 使用的是crontab做每日定时任务。 #!/bin/sh # 临时备份路径(未压缩的文件) OUT_DIR=/www/backup/tmp # 压缩后的备份存放路径 TAR_DIR=/www/backup/data # 数据库信息 URL=127.0.0.1 PORT=3306 USERNAME=root PASSWORD=root DBNAME=your_dbname # 当前系统时间 DATE=`date +%Y-%m-%d` # 指定命令所用的全路径 MYSQL=/usr/bin/mysql MYSQLDUMP=/usr/bin/mysqldump MYSQLADMIN=/usr/bin/mysqladmin # 日志文件 logfile=/www/backup/data.txt [ -d ${OUT_DIR} ] || mkdir -p ${OUT_DIR} [ -d ${TAR_DIR} ] || mkdir -p ${TAR_DIR} # 最终保存的数据库备份文件 TAR_BAK="your_dbname_$DATE.tar.gz" cd ${OUT_DIR} rm -rf ${OUT_DIR}/* echo "${DBNAME} backup start - ${DATE}\n" >>${logfile} # ${MYSQL} -h $URL:$PORT -u $USERNAME -p $PASSWORD -d $DBNAME -o $OUT_DIR/`$DATE +%Y-%m-%d` TABLE=$(${MYSQL} -h${URL} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "use ${DBNAME};show tables;"|sed '1d') #依次列出需要备份的表的名字 for tname in ${TABLE} do #echo "${DBNAME}_${tname}_${DATE}" # 创建备份表的目录 [ -d ${OUT_DIR}/${DATE} ] || mkdir ${OUT_DIR}/${DATE} ${MYSQLDUMP} --set-gtid-purged=off --single-transaction --column-statistics=0 --default-character-set=utf8 --compress-output=LZ4 --default-parallekism=3 -h${URL} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} ${tname} >${OUT_DIR}/${DATE}/${tname}.

MySql按中文(汉字)拼音首字母检索

实现按拼音首字母检索一种是直接增加字段存储名称首字母,但是这样会使表都一个字段,每次录入都要转换 这是通常的做法,另一种是接下来介绍的这种,按照汉字编码排序来实现的,无需给表多增字段。 首先我们有一个这样的数组: array( 'A'=>'吖', 'B'=>'八', 'C'=>'嚓', 'D'=>'咑', 'E'=>'妸', 'F'=>'发', 'G'=>'旮', 'H'=>'铪', 'J'=>'丌', 'K'=>'咔', 'L'=>'垃', 'M'=>'嘸', 'N'=>'拏', 'O'=>'噢', 'P'=>'妑', 'Q'=>'七', 'R'=>'呥', 'S'=>'仨', 'T'=>'他', 'W'=>'屲', 'X'=>'夕', 'Y'=>'丫', 'Z'=>'帀' ); 查出姓名拼音首字母是a的 SELECT * FROMtbl WHERE CONVERT( name USING gbk ) COLLATE gbk_chinese_ci >='吖' AND CONVERT( name USING gbk ) COLLATE gbk_chinese_ci < '八' 查出姓名拼音首字母是b的 SELECT * FROMtbl WHERE CONVERT( name USING gbk ) COLLATE gbk_chinese_ci >='八' AND CONVERT( name USING gbk ) COLLATE gbk_chinese_ci < '嚓' …