mysql初始化相关

查询mysql版本
mysql -V 或 在mysql中使用：status;

创建数据库
Create DATABASE weibo default charset utf8 COLLATE utf8_general_ci;

显示默认编码设置
show variables like ‘%char%’;

修改默认编码:
SET character_set_client=’utf8′;
SET character_set_connection=’utf8′;
SET character_set_database=’utf8′;
SET character_set_results=’utf8′;
SET character_set_server=’utf8′;
SET character_set_system=’utf8′;

或直接修改 /etc/my.conf
[mysql]
default_character_set=utf8
[mysqld]
default_character_set=utf8
[client]
default_character_set=utf8
需要重启mysql,重启后 show variables like ‘%char%’; 查看默认编码是否已修正

不区分大小写
/etc/my.cnf 中的[mysqld]后添加添加lower_case_table_names=1

创建新用户并赋予相应数据库的操作权限:
insert into mysql.user(Host,User,Password) values(“localhost”,”test”,password(“test”));
flush privileges;
grant all privileges on test.* to chenhaitao@localhost identified by ‘test’;

修改用户密码:
UPDATE user SET password=PASSWORD(“test”) WHERE user=’test’;
flush privileges;

开启远程
mysql -u root –p
mysql>use mysql;
mysql>update user set host = ‘%’ where user = ‘root’;
mysql>select host, user from user;

授权
授权root用户远程访问数据库，并设置密码为123456
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ’123456′ WITH GRANT OPTION;
授权ip地址为172.10.10.10的机器，可以以jk用户的身份访问数据库,并设置密码为123456
GRANT ALL PRIVILEGES ON . TO ‘jk’@’172.10.10.10’ IDENTIFIED BY ’123456′ WITH GRANT OPTION;
授权root用户远程访问数据库，
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ WITH GRANT OPTION
授权生效
FLUSH PRIVILEGES

jdbc连接时,可以这个设置
xml:
?useUnicode=true&characterEncoding=UTF-8

root 密码丢失
mysql的my.cnf中设置
skip-grant-tables
无密码登录后,重置密码

group_concat
查询:
show variables like ‘%group_concat%’;
配置:
方法一：修改配置文件中参数，新增 group_concat_max_len = 10240
方法二：在会话中实现，全局或当前session中
SET GLOBAL group_concat_max_len=10240;
SET SESSION group_concat_max_len=10240;

max_allowed_packet
查询
show variables like ‘%max_allowed_packet%’;
方法一
在my.conf中设置 max_allowed_packet=16M
方法二
set global max_allowed_packet=20480;