【１】rootのパスワード設定
（１）rootでログイン
初期状態ではパスワードが設定されていないので、パスワードなしでログインすることができる。
# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.0.37 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.0.37 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
（２）登録ユーザの確認
rootユーザと匿名ユーザが登録されている。
mysql> select user,host,password from mysql.user;
+------+----------------------+----------+
| user | host | password |
+------+----------------------+----------+
| root | localhost | |
| root | higashimokoto.ddo.jp | |
| | higashimokoto.ddo.jp | |
| | localhost | |
+------+----------------------+----------+
4 rows in set (0.00 sec)
+------+----------------------+----------+
| user | host | password |
+------+----------------------+----------+
| root | localhost | |
| root | higashimokoto.ddo.jp | |
| | higashimokoto.ddo.jp | |
| | localhost | |
+------+----------------------+----------+
4 rows in set (0.00 sec)
（３）接続元ホストがlocalhostのrootユーザにパスワード設定
mysql> set password for root@localhost=password('XXXXXXXX');
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+----------------------+------------------+
| user | host | password |
+------+----------------------+------------------+
| root | localhost | XXXXXXXXXXXXXXXX |
| root | higashimokoto.ddo.jp | |
| | higashimokoto.ddo.jp | |
| | localhost | |
+------+----------------------+------------------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+----------------------+------------------+
| user | host | password |
+------+----------------------+------------------+
| root | localhost | XXXXXXXXXXXXXXXX |
| root | higashimokoto.ddo.jp | |
| | higashimokoto.ddo.jp | |
| | localhost | |
+------+----------------------+------------------+
4 rows in set (0.00 sec)
（４）接続元ホストが自ホストのrootユーザにパスワード設定
mysql> set password for root@higashimokoto.ddo.jp=password('XXXXXXXX');
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+----------------------+------------------+
| user | host | password |
+------+----------------------+------------------+
| root | localhost | XXXXXXXXXXXXXXXX |
| root | higashimokoto.ddo.jp | XXXXXXXXXXXXXXXX |
| | higashimokoto.ddo.jp | |
| | localhost | |
+------+----------------------+------------------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+------+----------------------+------------------+
| user | host | password |
+------+----------------------+------------------+
| root | localhost | XXXXXXXXXXXXXXXX |
| root | higashimokoto.ddo.jp | XXXXXXXXXXXXXXXX |
| | higashimokoto.ddo.jp | |
| | localhost | |
+------+----------------------+------------------+
4 rows in set (0.00 sec)
（５）rootでログインできることを確認
いったんログアウトし、設定したパスワードでログインできることを確認。（自ホスト指定のときはなぜかログインできず？？）
mysql> exit
Bye
# mysql -u root -pXXXXXXXX
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 11
Server version: 5.0.37 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> exit
Bye
# mysql -u root -h higashimokoto.ddo.jp -pXXXXXXXX
Bye
# mysql -u root -pXXXXXXXX
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 11
Server version: 5.0.37 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> exit
Bye
# mysql -u root -h higashimokoto.ddo.jp -pXXXXXXXX
【２】匿名ユーザ削除
# mysql -u root -pXXXXXXXX
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 5.0.37 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> select user,host from mysql.user;
+------+----------------------+
| user | host |
+------+----------------------+
| | higashimokoto.ddo.jp |
| root | higashimokoto.ddo.jp |
| | localhost |
| root | localhost |
+------+----------------------+
4 rows in set (0.00 sec)
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+----------------------+
| user | host |
+------+----------------------+
| root | higashimokoto.ddo.jp |
| root | localhost |
+------+----------------------+
2 rows in set (0.00 sec)
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 5.0.37 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> select user,host from mysql.user;
+------+----------------------+
| user | host |
+------+----------------------+
| | higashimokoto.ddo.jp |
| root | higashimokoto.ddo.jp |
| | localhost |
| root | localhost |
+------+----------------------+
4 rows in set (0.00 sec)
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+----------------------+
| user | host |
+------+----------------------+
| root | higashimokoto.ddo.jp |
| root | localhost |
+------+----------------------+
2 rows in set (0.00 sec)
【３】テストデータベース削除
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
【４】新規ユーザ作成
# mysql -u root -pmysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 13
Server version: 5.0.37 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> grant all privileges on dbname.* to username@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> select user from mysql.user where user='username';
+----------+
| user |
+----------+
| username |
+----------+
1 row in set (0.00 sec)
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 13
Server version: 5.0.37 Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> grant all privileges on dbname.* to username@localhost identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> select user from mysql.user where user='username';
+----------+
| user |
+----------+
| username |
+----------+
1 row in set (0.00 sec)