CentOS-5/MySQL/初期設定

2009年09月01日 (火)

[ CentOS/MySQL ]

MySQL 初期設定

*** MySQL root パスワード設定 [#q63618ef]
[root@linux ~]# mysqladmin -u root password '設定するパスワード'
[root@linux ~]#

MySQL 接続

[root@linux ~]# mysql -u root -p
Enter password:　　←■ パスワード入力
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select version();　　←■ バージョン確認
+-----------+
| version() |
+-----------+
| 5.0.22    |
+-----------+
1 row in set (0.00 sec)

mysql> quit　　←■ 接続終了
Bye
[root@linux ~]#

匿名ユーザの削除

[root@linux ~]# mysql -u root -p
Enter password:
mysql> select user, password, host from mysql.user;　　←■ ユーザ一覧表示
+------+------------------+-----------+
| user | password         | host      |
+------+------------------+-----------+
| root | 1a************99 | localhost |
| root |                  | linux     |
|      |                  | linux     |
|      |                  | localhost |
+------+------------------+-----------+
4 rows in set (0.00 sec)

mysql> use mysql;　　←■ データベース変更
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> delete from user where user = '' or ( user = 'root' and host != 'localhost' );　　←■ 匿名ユーザ削除
Query OK, 3 rows affected (0.00 sec)

mysql> flush privileges;　　←■ 変更内容をメモリ上のキャッシュに反映させる
Query OK, 0 rows affected (0.00 sec)

mysql> select user, password, host from mysql.user;　　←■ ユーザ再確認
+------+------------------+-----------+
| user | password         | host      |
+------+------------------+-----------+
| root | 1a************99 | localhost |
+------+------------------+-----------+
1 row in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)　　←■ 設定を反映

mysql> quit　　←■ 終了

adminユーザの追加（rootユーザと同じ権限）

[root@linux ~]# mysql -u root -p
Enter password:

mysql> grant all on *.* to admin@localhost identified by 'パスワード';
Query OK, 0 rows affected (0.00 sec)

mysql> grant grant option on *.* to admin@localhost identified by 'パスワード';
Query OK, 0 rows affected (0.00 sec)

mysql> select user, password, host from mysql.user;　　←■ ユーザー一覧確認
+-------+------------------+-----------+
| user  | password         | host      |
+-------+------------------+-----------+
| root  | 51************16 | localhost |
| admin | 51************16 | localhost |
+-------+------------------+-----------+
2 rows in set (0.00 sec)

mysql>

test データベース削除 ( 削除したい場合 )

[root@linux ~]# mysql -u root -p
Enter password:

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> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit　　←■ 終了

追加設定

データベース追加

mysql> create database example;

MySQL サーバの状態の確認

mysql 接続語

簡単な情報

mysql> status;
--------------

～ 中略 ～

Server version:         5.0.27-standard
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 58 min 5 sec
--------------
mysql>

status より詳細な内部情報

mysql> show status;
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 0        |
| Aborted_connects                  | 0        |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 563      |
| Bytes_sent                        | 7028     |
+-----------------------------------+----------+
222 rows in set (0.00 sec)

mysql>

各サーバ変数の設定値

mysql> show variables;
+---------------------------------+------------------------------------------+
| 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/               |
| collation_connection            | utf8_general_ci                          |
| collation_database              | utf8_general_ci                          |
| collation_server                | utf8_general_ci                          |

～ 中略 ～
                                    |
| version                         | 5.0.27-standard                          |
| version_comment                 | MySQL Community Edition - Standard (GPL) |
| version_compile_machine         | i686                                     |
| version_compile_os              | pc-linux-gnu                             |
| wait_timeout                    | 28800                                    |
+---------------------------------+------------------------------------------+
220 rows in set (0.00 sec)

mysql>

文字コード確認

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| 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.00 sec)

mysql>

パスワード変更

$ mysqladmin -u root -p旧パスワード password 新パスワード