tomo.gif (1144 ﾂバﾂイﾂト)line.gif (927 ﾂバﾂイﾂト)line.gif (927 ﾂバﾂイﾂト)line.gif (927 ﾂバﾂイﾂト)To previous pageTo home pageMailing to me

MySQLの小技

Modified: 20 October 2007,
7 April 2007, 17 August 2006, 17 December 2005
Created：　7 November 2004

デフォルトの漢字コードをEUCにする方法 （7 April 2007）

主キーのための一意な連番を自動的に振る方法 （17 August 2006）

特定のデータベースをダンプ（バックアップ＆リストア）する方法 （12 December 2005）
忘れたrootのパスワードを再設定する方法 （2 December 2005）
ログを取る方法　（11 June 2005, 7 November 2004）
データをCSVで入出力する方法 （19 March 2006）
データをCSV(Excel)で入出力する方法 （20 October 2007）
アクセス制限を設定する方法 （23 May 2004）
MyODBC(MySQL用のODBC）のインストール （21 July 2003）

　デフォルトの漢字コードをEUCにする方法

"/etc/my.cnf"に設定

各セクションに、"default-character-set=ujis"を設定します。"ujis"とは、EUCのことです。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
default-character-set=ujis
character-set-server=ujis

[mysql.server]
user=mysql
basedir=/var/lib
default-character-set=ujis

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-character-set=ujis

[client] 
default-character-set=ujis

[mysqldump] 
default-character-set=ujis

[mysql] 
default-character-set=ujis

MySQLを再起動し、確認します。

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.1.20-log

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

mysql> \s
--------------
mysql  Ver 14.7 Distrib 4.1.20, for redhat-linux-gnu (i686) using readline 4.3

Connection id:          10
Current database:       
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         4.1.20-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    ujis
Db     characterset:    ujis
Client characterset:    ujis
Conn.  characterset:    ujis
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 17 min 39 sec

Threads: 3  Questions: 230  Slow queries: 0  Opens: 54  Flush tables: 1  Open tables: 18  Queries per second avg: 0.217
--------------

mysql>

全てが、ujisになっておればOKです。

　主キーのための一意な連番を自動的に振る方法 

指定方法

テーブルを作成するとき、"NOT NULL AUTO_INCREMENT PRIMARY KEY"を付加します。

create table plant (
        id integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
        nm text,
        tm integer,
        ct integer,
        fg text
);

データを挿入する場合、このフィールドは気にせず、挿入すれば、自動的に番号が振られて挿入されます。

　特定のデータベースをダンプ（バックアップ＆リストア）する方法　

ダンプ（バックアップ）する方法

# mysqldump -uユーザ名 -pパスワード --databases DB名　>　members.dump

リストアする方法

ダンプしたファイルは、以下のコマンドでリストアできます。

# mysql -uユーザ名 -pパスワード < members.dump

　忘れたrootのパスワードを再設定する方法　

設定手順

MySQLは、rootのパスワード忘れてしまった場合、いったん権限を無効にして起動すれば、パスワードを忘れてしまっても再設定できます。

いったんMySQLを停止し、以下のコマンドオプション（"--skip-grant-tables"）を付加して起動します。

# service mysqld stop
# /usr/bin/safe_mysqld --skip-grant-tables 
Starting mysqld daemon with databases from /var/lib/mysql

以下のコマンドでMySQLを立ち上げて以下のようにコマンドを入力すれば変更できます。

# mysql -u root mysql

mysql> UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root';
mysql> FLUSH PRIVILEGES;

変更後は、MySQLを落として、通常の設定で起動します。

# service mysqld start

 ログを取る方法　

すべての接続とクエリを記録する

MySQLサーバー起動時のコマンド（"safe_mysqld"）に、"--log"オプションを付加して起動します。

           :

start(){
           :

        /usr/bin/safe_mysqld  --defaults-file=/etc/my.cnf --log >/dev/null 2>&1 &

           :
}
           :


更新された内容のみ記録する

"--log-update"を付加すると。更新された内容のみ記録されます。


"my.cnf"で指定する方法

"/var/log/mysqld-48.log"にログを取ります。

"my.cnf"に以下を追加します。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log=/var/log/mysqld-48.log
     
[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

空のログファイルを作り、起動（再起動）します。

# touch /var/log/mysqld-48.log
# chown mysql.mysql /var/log/mysqld-48.log
# chmod 640 /var/log/mysqld-48.log
# service mysqld restart

接続してみて、そのログを確認する。
最初は、わざとパスワードを間違えてみました。２回目はただしく接続し、select文を実行し、終了しました。

/usr/libexec/mysqld, Version: 3.23.58-log, started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
050611 14:23:42       2 Connect     Access denied for user: 'root@localhost' (Using password: YES)
050611 14:23:58       3 Connect     root@localhost on 
050611 14:24:10       3 Init DB     mysql
                      3 Query       show databases
                      3 Query       show tables
                      3 Field List  columns_priv 
                      3 Field List  db 
                      3 Field List  func 
                      3 Field List  host 
                      3 Field List  tables_priv 
                      3 Field List  user 
050611 14:24:18       3 Query       select * from user
050611 14:24:24       3 Quit

 データをCSVで入出力する方法

CSVに出力（エクスポート）する方法

以下のように、データベースと出力ファイルを指定すれば出力されます。

mysql> select * from database into outfile "/tmp/dump.csv" fields terminated by ',';


CSVから入力（インポート）する方法

以下のように、入力ファイルとデータベースのテーブルを指定すれば入力できます。

mysql> load data local infile "/tmp/dump.csv" into table tablename fields terminated by ',';

区切りの改行コードを指定するには、"LINES TERMINATED BY"で指定します。

fields terminated by ',' LINES TERMINATED BY '\r\n';

ダブルクォーテーションで囲まれたデータの入出力すには、"ENCLOSED BY '"'"を付加します。

fields terminated by ',' ENCLOSED BY '"';

 データをCSV（Excel）で入出力する方法

CSVに出力（エクスポート）する方法

以下のように、データベースと出力ファイルを指定すれば出力されます。

mysql> set character set sjis;
mysql> select * from database into outfile "/tmp/dump.csv" 
                            fields terminated by ',' enclosed by '"' lines terminated by '\r\n';


CSVから入力（インポート）する方法

以下のように、入力ファイルとデータベースのテーブルを指定すれば入力できます。

mysql> set character set sjis;
mysql> load data (local) infile "/tmp/dump.csv" into table tablename
                            fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

入れ替える場合は、以下のように指定します。

mysql> set character set sjis;
mysql> load data (local) infile "/tmp/dump.csv" replace into table tablename
                            fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

 アクセス制限を設定する方法

 パスワードを設定する

> mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 3.23.58

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

mysql> set password for root=password('password');
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye
>


 設定を確認する

フツーに起動しようとするとエラーになります。

> mysql -u root
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
>

"-p" を指定して、パスワードを入力すると起動できます。

> mysql -p -u root
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24 to server version: 3.23.58

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

mysql> \q
Bye
>

 MyODBC(MySQL用のODBC）のインストール

MyODBCの入手

以下のダウンロードから入手できます。
http://www.mysql.com/downloads/

私は以下の、Windows版のMySQL Connector/ODBC 3.51 をダウンロードしました。
http://www.mysql.com/downloads/api-myodbc-3.51.html


MyODBCのインストール

ダウンロードしたファイルを実行し、「Next」、「Next」、「Next」、「Funish」の４回クリックでインストールは終了します。


インストールの確認

「管理ツール」から「データソース」を開き、「追加」ボタンをクリックすると、以下のようにインストールされていることが確認できます。


データソースの登録

「MySQL ODBC 3.51 Driver」を選択し、「完了」ボタンをクリックして、以下のようにデータベースを指定します。

To previous pageTo home pageMailing to meJump to Top of pageline.gif (927 ﾂバﾂイﾂト)line.gif (927 ﾂバﾂイﾂト)tomo.gif (1144 ﾂバﾂイﾂト)