MySQL 导出 xls

若查询语句为

SELECT *,count(*) as count FROM `youhui_activity_log` WHERE `type`="activity_21_page_index" AND `value`="c8" AND DATE_FORMAT(`create_time`,"%Y-%m-%d")= "2018-12-06" GROUP BY `key`;

则导出语句为

SELECT *,count(*) as count FROM `youhui_activity_log` WHERE `type`="activity_21_page_index" AND `value`="c8" AND DATE_FORMAT(`create_time`,"%Y-%m-%d")= "2018-12-06" GROUP BY `key` INTO OUTFILE '/usr/outsql/ac_log_2.xls'; 

MySQL 配置文件添加指定的导出目录

secure_file_priv = E:/

否则可能出现报错:

1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

导出 SQL 文件

mysqldump -uroot -p wa_data cleancheckrule > /usr/outsql/clean14.sql

监测 MySQL 服务 挂掉自动重启

最近 MySQL 服务总是挂掉

cd ~
vim /mysqllisten.sh
#!/bin/bash
pgrep -x mysqld &> /dev/null
if [ $? -ne 0 ]
then
echo "MySQL stoped at time: `date` ">> /home/wwwlogs/mysql_messages
/etc/init.d/mysql restart
sleep 15s
MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $4}'`
    if [ "$MYSQLPORT" == "3306" ]
then  
            echo "MySQL restart successful at time: `date` ">> /home/wwwlogs/mysql_messages   
        else
            echo "MySQL restart failed at time: `date` ">> /home/wwwlogs/mysql_messages 
            mail -s "wann! yourwebsitedomain's mysql restart failed" 这里写你的邮箱地址
        fi
else
echo "MySQL server is running"
fi
chmod +x ./mysqllisten.sh

添加定时任务

crontab -e

*/10 * * * * /bin/bash /root/mysqllisten.sh

service crond restart

LNMP使用中mysql宕机挂掉后自动重启脚本

http://xiaohost.com/1580.html

重装 LNMP 环境 MySQL 5.7 的初始配置

安装 lnmp.org

wget http://soft.vpser.net/lnmp/lnmp1.5.tar.gz -cO lnmp1.5.tar.gz && tar zxf lnmp1.5.tar.gz && cd lnmp1.5 && ./install.sh lnmp

其中 MySQL 版本 5.7 安装位置 /usr/local/mysql

添加用户用于外部访问,设置 3306 防火墙白名单,安全组白名单 范围% 重启主机

vim /etc/my.cnf

在 [mysqld] 下,添加

sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

否则可能出现 [Err] 1067 – Invalid default value for ‘xxxxx’,因为 MySQL 5.7 datetime 不允许默认值为 ‘0000-00-00 00:00:00’ 

https://blog.csdn.net/achuo/article/details/54618990

导入备份的 SQL

[Err] 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes

原因是 MySQL 默认读取执行的 SQL 文件最大为 1M,有些为 16M,我这个 SQL 文件大于这个值

vim /etc/my.cnf

在 [mysqld] 下,添加或修改

max_allowed_packet=400M

ThinkPHP 3.2 获取 MySQL 数据库数据表字段的备注信息

//获取备注信息
public function _get_field() {
	$model = new \Think\Model();
	$controller_name = $this->CamelCaseToUnderScore(CONTROLLER_NAME);
	$rs = $model->query("select `column_name`,`column_comment` from information_schema.COLUMNS WHERE TABLE_SCHEMA = '".C('DB_NAME')."' AND TABLE_NAME = '".C('DB_PREFIX').$controller_name."'");
	return $rs;
}

//驼峰命名转下划线命名
public function CamelCaseToUnderScore($str){
	$dstr = preg_replace_callback('/([A-Z]+)/',function($matchs){
	  return '_'.strtolower($matchs[0]);
	},$str);
	return trim(preg_replace('/_{2,}/','_',$dstr),'_');
}

原生 SQL 语句

select * from information_schema.columns where table_schema='db' table_name='user'

解决 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ 错误

安装完 MySQL ,进行运行测试,报 Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ 错误,

[root@localhost mysql]# /usr/local/mysql/bin/mysqld_safe
131206 22:42:51 mysqld_safe Logging to ‘/var/log/mysqld.log’.
131206 22:42:51 mysqld_safe Starting mysqld daemon with databases from /data/mysql/data
131206 22:42:57 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
[root@localhost mysql]# /usr/local/mysql/bin/mysql
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

检查一下 MySQL 的运行状态

[root@localhost mysql]# ps -ef | grep mysql
root     32092  2108  0 22:43 pts/0    00:00:00 grep mysql

查看 /tmp 文件夹的确没有 mysql.sock 文件,

通过 find 命令查找

[root@localhost /]# find / -name mysql.sock
/var/lib/mysql/mysql.sock

原来安装到 /var/lib/mysql/mysql.sock 这个位置啦,我们需要修改 my.cnf 文件

[root@localhost /]# vi /usr/local/mysql/my.cnf

[client]
character-set-server = utf8
port    = 3306
socket  = /var/lib/mysql/mysql.sock
……

保存之后即出,重新启动 MySQL 。

via: http://www.chenruixuan.com/archives/517.html