修改linux上mysql数据库编码

问题:数据库编码引起的web服务插入数据库中文乱码问题.


1.进入mysql控制台.
注意:如果输入mysql -u root -p,出现enter password,我直接点击回车，结果出现如果下错误：
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
解决方案:不要加-u -p参数，默认使用root@localhost登录,如下:
username@password:~# cd /opt/mysql5610/mysql-5.6.12-linux-glibc2.5-x86_64/bin
username@password:~/mysql5610/mysql-5.6.12-linux-glibc2.5-x86_64/bin# ./mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2563
Server version: 5.6.12 MySQL Community Server (GPL)


Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> 


2.查看mysql版本.


mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.12    |
+-----------+
1 row in set (0.02 sec)




3.查看mysql编码.
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/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)


mysql> 


4.修改mysql数据库编码.
查看my.cnf配置文件.
username@password:~/mysql5610/mysql-5.6.12-linux-glibc2.5-x86_64# cat my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[client]
default-character-set=utf8


[mysqld]
character-set-server=utf8


[mysql]
default-character-set=utf8
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M


# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin


# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 


sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
修改my.cnf配置文件,主要修改如下：
在[client]下追加default-character-set=utf8
在[mysqld]下追加character-set-server=utf8
在[mysql]下追加default-character-set=utf8


5.mysql数据库的停止与开启.
停止服务.
username@password:~/mysql5610/mysql-5.6.12-linux-glibc2.5-x86_64/bin# ./mysqladmin shutdown
开启服务:
注意:因为MySQL为了安全,不希望root用户直接启动mysql,所以启动mysql时要声明用户.
username@password:~/mysql5610/mysql-5.6.12-linux-glibc2.5-x86_64/bin#  ./mysqld_safe --user=root 
Warning: World-writable config file '/usr/local/mysql/my.cnf' is ignored
Warning: World-writable config file '/usr/local/mysql/my.cnf' is ignored
150209 11:36:05 mysqld_safe Logging to '/usr/local/mysql/data/msrv5.err'.
150209 11:36:05 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
针对上面的警告,World-writable config file '/usr/local/mysql/my.cnf' is ignored
my.cnf的读取权限进行了设置,不允许World-writable(字面意思是全世界都可读写)
解决方法：
chmod 644 /usr/local/mysql/my.cnf
再次重新启动mysql,启动正常.
root@msrv5:~/mysql5610/mysql-5.6.12-linux-glibc2.5-x86_64/bin# ./mysqladmin shutdown
root@msrv5:~/mysql5610/mysql-5.6.12-linux-glibc2.5-x86_64/bin#  ./mysqld_safe --user=root 
150209 12:29:02 mysqld_safe Logging to '/usr/local/mysql/data/msrv5.err'.
150209 12:29:02 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data




6.进入mysql查看数据库编码.
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/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)


ok!大功告成！