一、mysql wait_timeout 时间优化
mysql> show variables like ‘%timeout%’;
set interactive_timeout=31536000;
set wait_timeout=31536000;
也就是说wait_timeout的最大值只允许2147483 (24天左右)
也可以修改my.cof,修改后重起mysql
打开/etc/my.cnf,在属性组mysqld下面添加参数如下:
[mysqld]
interactive_timeout=31536000
wait_timeout=31536000
二、用户创建 赋予权限
create user ‘root’@’%’ identified by ‘root’;
GRANT ALL PRIVILEGES ON www5.* TO root@"%" identified by “root”;
flush privileges;
三、自动备份mysql数据库 需要把下面得代码改成 autoback.bat 文件
@echo off
set errorlevel=0
set path_home_mysql=“D:\Program Files\MySQL\MySQL Server 5.1”
set path_bin_mysql=%path_home_mysql%bin\
set backup_dir=“E:\backup”
set backup_file=%backup_dir%mysql_backup_%date:~0,10%.sql
set database_mysql=budget
set user_mysql=root
set password_mysql=******
dir %backup_dir% || echo make dir &mkdir %backup_dir%
IF EXIST %backup_file% ( del %backup_file% &echo %backup_file% is deleted)
echo backup mysql database:%database_mysql%
%path_bin_mysql%mysqldump -u%user_mysql% -p%password_mysql% %database_mysql% > %backup_file%
echo end backup
四、 mysql 导出数据指定字符集
mysql -uroot -pqhdswchzh --default-character-set=GBK wlwzpt </wlwzpt.sql
五、修改log_bin_trust_function_creators
SET GLOBAL log_bin_trust_function_creators = 1;
六、慢查询
定义超过多少秒的查询算是慢查询,我这里定义的是2秒
long_query_time = 2
//5.0、5.1等版本配置如下选项
定义慢查询日志的路径
log-slow-queries = E:/mysqldb/mysqlslowquery.log
//5.5及以上版本配置如下选项
slow_query_log = ON
slow_query_log_file= E:/mysqldb/slow_query_log.txt
七、 Table ‘sys_dept’ was not locked with LOCK TABLES 的问题的解决
原因: 表sys_dept被锁
解决办法 : UNLOCK TABLES; (释放锁即可 释放的是所有的表的锁)