MySQL Tips

  • 授权远程访问：

mysql> grant all privileges on *.* to 'root'@'%'  identified by '123456';
mysql> FLUSH PRIVILEGES;

或

mysql> grant select on *.* to 'user'@'%'  identified by '123456';
mysql> FLUSH PRIVILEGES;

  • 修改密码：

mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;

  • 创建数据库：

GBK: create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;  
UTF8: CREATE DATABASE `test2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

  • 修改MYSQL支持的最大连接数：

方法一：打开MYSQL配置文件 my.ini 或 my.cnf查找:

 max_connections=100
修改为:
 max_connections=1000

重起MYSQL即可

方法二：客户端登录：mysql -uusername -ppassword

设置新的最大连接数为200：

mysql> set GLOBAL max_connections=200

  • 显示当前运行的Query：

mysql> show processlist

  • 显示当前状态：

mysql> show status

  • 设置MYSQL忽略表名大小写：

缺省时在LINUX上MYSQL的表名是区分大小写的，如果希望MYSQL忽略表名的大小写需要修改my.cnf，在[mysqld]中增加下面一行内容，然后重新启动MYSQL服务即可。

lower_case_table_names=1

  • 解决windows上错误： Can’t connect to MySQL server on ‘localhost’ (10055)”

修改注册表：
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\Tcpip\Parameters
Value Name: MaxUserPort
Value Type: DWORD
Value data: 65534
Valid Range: 5000-65534 (decimal)
Default: 0x1388 (5000 decimal)

  • 导入/导出CSV文件

load data infile '/tmp/test.csv'   into table test_info    
fields terminated by ','  optionally enclosed by '"' escaped by '"'   
lines terminated by '\r\n';   

select * from test_info   into outfile '/tmp/test.csv'   
fields terminated by ',' optionally enclosed by '"' escaped by '"'   
lines terminated by '\r\n';

  • 增加/删除字段

alter table TABLE_NAME add FIELD_NAME varchar(50) null  comment '创建者ID' after ANOTHER_FIELD_NAME;

alter table TABLE_NAME drop FIELD_NAME;

  • 排错 mysqldump: error: 1449

在通过mysqldump进行数据库备份时，有可能会遇到如下错误：

$  /opt/mysql/bin/mysqldump --opt aaa_db > aaa.sql
...
mysqldump: Got error: 1449: The user specified as a definer ('xxx'@'%') does not exist when using LOCK TABLES
...

原因可能有两种：

  1. xxx@% 没有lock tables权限

对于这种情况，通过grant命令授予相应权限就可以了。

  1. xxx@%已经被删除

对于这种情况，有两种解决措施：

* 重新创建相应的帐号

* 重新创建 owner是“xxx@%”的所有数据库对象，如：table、view、function等