MySQL Windows 环境连接慢的问题

Categories 学习笔记

MySQL localhost 连接慢

在 Windows 上开发应用连接到 MySQL 数据库时，若使用本地连接需要延迟 1s 后才能打开

将服务器地址 localhost 修改为 127.0.0.1 速度就变得正常了

所以问题在于 localhost 的解析问题

使用 localhost 程序解析出来的是 ipv6 地址 ::1 ，但 MySQL 监听的是 ipv4 的地址 127.0.0.1，ipv6 连不上超时后会再去连接 ipv4 地址 127.0.0.1, 这就是这个1秒钟延迟出现的原因

解决方法，使用 127.0.0.1 代替 localhost

phpMyAdmin 配置修改方法

文件目录

phpMyAdmin\libraries\config.default.php

$cfg['Servers'][$i]['host'] = '127.0.0.1';

MySQL 导出 xls

Categories 学习笔记

若查询语句为

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 服务 挂掉自动重启

Categories 学习笔记

最近 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

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

Categories 学习笔记 
//获取备注信息
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'