Linux下Mysql编码修改为UTF-8

以CentOS6.3示例，查看当前系统环境变量：

[root@cloudmaster ~]# mysql -uroot -p

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

mysql> quit;

Bye

 

修改方法：

1.查找my-large.cnf文件

[root@cloudmaster ~]# find / -name "my-large.cnf"
/usr/share/mysql/my-large.cnf
/usr/share/doc/mysql-server-5.1.66/my-large.cnf

 

2.拷贝修改my.cnf(即my-large.cnf)

[root@cloudmaster ~]# cp /usr/share/doc/mysql-server-5.1.66/my-large.cnf /etc/my.cnf

[root@cloudmaster ~]# vi /etc/my.cnf

 

在[client]下增加一行：default-character-set=utf8

在[mysqld]下增加一行：default-character-set=utf8

 

如果让mysql数据库为UTF-8运行，使连接mysql数据库时使用UTF-8编码则：

在[mysqld]下增加一行：init_connect='SET NAMES utf8'

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

…………………… # The following options will be passed to all MySQL clients [client] #password       = your_password port            = 3306 socket          =  /var/lib/mysql/mysql .sock default-character- set =utf8            # Here follows entries for some specific programs            # The MySQL server [mysqld] port            = 3306 socket          =  /var/lib/mysql/mysql .sock skip-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 default-character- set =utf8 init_connect= 'SET NAMES utf8'            # Don't listen on a TCP/IP port at all. This can be a security enhancement, ……………………

[root@cloudmaster ~]# service mysqld restart3.重启Mysql服务

 

4.查看系统环境变量

[root@cloudmaster ~]# mysql -uroot -p

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

mysql> quit;

Bye

 

5.数据库创建注意事项

   做过如上操作，如果直接数据库再创建表，然后存入中文，取出来的会是问号。

   解决的办法是：创建数据库的时候指明默认字符集为utf8，如： 

mysql> create database dbname default character set utf8;

其他：

允许MYSQL数据库远程连接：

[root@cloudmaster ~]# mysql -uroot -ppassword

mysql>show databases;       //查看当前数据库；

mysql>use mysql;            //进入mysql数据库；

mysql>show tables;          //查看数据库表；

mysql>GRANT ALL PRIVILEGES ON *.* TO myuser@'IP' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

授权mysql数据库可从某个IP连接，IP字段可改为%，表示任何IP。

mysql> FLUSH PRIVILEGES;         //使配置生效；

mysql> quit;                            //退出数据库；