在 Ubuntu 下管理與設置 UTF-8 環境的 MySQL

在 Ubuntu 下管理與設置 UTF-8 環境的 MySQL

1. 修改 my.cnf

[client]
default-character-set = utf8

[mysqld]
datadir = /home/mysql
character-set-server= utf8
collation_server = utf8_general_ci
init-connect = 'SET NAMES utf8'

2. 產生新的 MySQL Database 存放位置

shell> mysql_install_db --user=mysql

3. 執行 MySQL

shell> /etc/init.d/mysql start

出現下面錯誤訊息先不用管

/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
sokoyo@sokoyo-server:~/mysql$ /usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

4. 以 root 登入

shell> mysql -u root

5. 列出所有 host 和 user

mysql> SELECT Host, User FROM mysql.user;
+---------------+------+
| host          | user |
+---------------+------+
| 127.0.0.1     | root |
| localhost     | root |
| hostname      | root |
+---------------+------+
3 rows in set (0.00 sec)

6. 設定 root 密碼

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpwd');
mysql> SET PASSWORD FOR 'root'@'hostname' = PASSWORD('newpwd');

7. 新增使用者 debian-sys-maint，密碼參閱 /etc/mysql/debian.cnf 內的 password

mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

8. 檢視環境變數

mysql> show variables;

結果如下代表成功設置 UTF-8 環境

| 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/  |
| collation_connection            | utf8_general_ci             |
| collation_database              | utf8_general_ci             |
| collation_server                | utf8_general_ci             |

9. 重新啟動 MySQL

shell> /etc/init.d/mysql restart

出現如下代表正常啟動

 * Stopping MySQL database server mysqld	[ OK ]
 * Starting MySQL database server mysqld	[ OK ]
 * Checking for corrupt, not cleanly closed and upgrade needing tables.

接下來靠 phpMyAdmin 就可以了