MySQL常用设置

安装

apt-get install mysql-server

期间输入root用户的密码。

本地root登录

mysql -uroot -pyour_pwd

用户管理

添加用户

    mysql> GRANT ALL PRIVILEGES ON *.* TO 'freebird'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
'freebird' 用户名
'123456' 密码
'%' 指从本机之外都可以连接，如果换成'localhost'，就只能从本机发起连接
ALL PRIVILEGES 赋予freebird任何权限
    You can limit the user's power on one data base:
GRANT ALL PRIVILEGES ON artsysdb.* TO 'lizhan'@'%' IDENTIFIED BY 'lizhan' WITH GRANT OPTION;
    More detail information please refer to 11.5.1.3. GRANT Syntax of the mysql document.
 
    By default,root has no power to access database remotely.

以下配置需要修改/etc/mysql/my.cnf文件

使用InnoDB

[mysqld]下面加入default-storage-engine=INNODB一句

注意，Ubuntu server 12.04 下的MySQL 5.5默认是innodb.不需要设置。

使用utf-8字符集

默认情况下，Ubuntu server 12.04的MySQL5.5

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> 

因此只需要在mysqld下面添加：

character-set-server = utf8

下面的适用于老版本。5.5会报错。

[mysqld]

default-character-set=utf8
init_connect='SETNAMES utf8'（设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行）

[client]
default-character-set=utf8默认字符集为utf8  

不区分大小写

[mysqld]
lower_case_table_names=1

远程访问权限

[mysqld]

bind-address            = 192.168.1.200

也可以将上面的IP直接换成0.0.0.0

最大连接数修改

[mysqld]
max_connection=1024

修改mysql连接超时

http://www.rackspace.com/knowledge_center/article/how-to-change-the-mysql-timeout-on-a-server

创建数据库

CREATE DATABASE redmine CHARACTER SET utf8;

创建索引

CREATE INDEX last_update_idx ON my_table(last_update);