MySQL

更新日 2010-11-26 (金) 21:59:44

MySQLのインストール

MySQLのソースをここの下のほうのSource downloadsからDownload.

$ su
# /usr/sbin/useradd mysql

homeディレクトリを/usr/local/varに変更

$ tar zxvf mysql-4.1.12.tar.gz
$ cd mysql-4.1.12
$ ./configure --with-charset=ujis -with-extra-charsets=all \
--with-mysqld-user=mysql
--with-charset
デフォルトで使用される文字コードを指定します。EUCならujis、Shift-JISならsjis、UTF8ならutf8
--with-extra-charset
デフォルトで指定された文字コード以外をサポートしたい場合の指定です。
"--with-extra-charset=ujis,sjis,..."のように個別に指定できますが、
ここでは“all”とします。このオプションを指定することで、
makeをし直さずとも、異なる文字コードでサーバを起動することが可能
--with-mysqld-user
MySQLデーモンを起動するユーザーの指定
$ make
$ su
# make install
# cp support-files/my-medium.cnf /etc/my.cnf  <---(1)

(1)でコピーするファイルについて

ファイル名使用用途
my-small.cnf64MB以下のメモリを搭載したPC
my-medium.cnf128MB以下のメモリを搭載したPC
my-large.cnf512MB以下のメモリを搭載したPC
my-huge.cnf1GB〜2GB以下のメモリを搭載したPC
my-innodb-heavy-4G.cnf4GBのメモリとInnoDBで作成されたデータベースによって構築されたPC

データベースの初期化

以下データベースコマンドをrootで行っているがシステム関係以外は一般ユーザでもOK

# /usr/local/bin/mysql_install_db --user=mysql
Installing all prepared tables
Fill help tables

To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system
(略)
he latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at https://order.mysql.com

# /usr/local/var(データベース保存先)のオーナを変更

# chown -R mysql.mysql /usr/local/var

起動

# /usr/local/bin/mysqld_safe --user=mysql --log &

動作確認

--logで/usr/local/var/(サーバ名).logでlogfileが作成される

以下のコマンドで結果が以下のようになればOK

# /usr/local/bin/mysqlshow
+-----------+
| Databases |
+-----------+
| mysql     |
| test      |
+-----------+

全てのDBを表示するには(rootのパスワード設定後は)

$ /usr/local/bin/mysqlshow -u root -p
Enter password:*****
+-----------+
| Databases |
+-----------+
| moodle    |
| mysql     |
| test      |
| xoops     |
+-----------+

パラメータの指定

たとえば、MySQLの最大接続数を上げるには、mysqldにオプション「--set variable=max_connections=コネクション数」としていするか

/etc/my.cnfの

[mysqld]
max_connections = 120

に追加する。

logの取り方

[mysqld]
log=/var/log/mysql.log  

を追加

# touch /var/log/mysql.log
# chown mysql.mysql /var/log/mysql.log

MySQLの停止

$ /usr/local/bin/mysqladmin -u root -p shutdown

MySQLを動かす

MySQLのroot のパスワード設定

$ /usr/local/bin/mysqladmin -u root password '******' <--(1)

上記のmysqladminでパスワードを設定したときは以下のコマンドでreloadする必要がある

$ /usr/local/bin/mysqladmin -u root -p reload
Enter password:

rootのパスワード変更

$ mysqladmin -u root -p password '******'  <--NewPassword
Enter password: ******* <--OldPassword

「mysqladmin -u root -p reload」が必要かは不明

# mysql -u root -p
Enter password: <--1(1)のパスワードを入力
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 4.1.12

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

mysql>
mysql> select host,user,password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *3*************************************** |
| open2_06  | root |                                           |
| open2_06  |      |                                           |
| localhost |      |                                           |
+-----------+------+-------------------------------------------+
4 rows in set (0.00 sec)

rootでmysqlにログイン

# mysql -u root mysql

もうとつのrootのパスワードを設定(root@open2_06) SET PASSWORD 構文でパスワードの設定。

mysql> SET PASSWORD FOR 'root'@'open2_06' = PASSWORD('*****');
Query OK, 0 rows affected (0.00 sec)

すべてのことが出来るspadminというユーザを登録(ローカルのみ)

mysql> GRANT ALL PRIVILEGES ON *.* TO spadmin@localhost IDENTIFIED BY 'mysql(パスワード)' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

リモートからもすべてのことが出来るには

mysql> GRANT ALL PRIVILEGES ON *.* TO spadmin@"%" IDENTIFIED BY 'mysql(パスワード)' WITH GRANT OPTION;
の追加が必要(リモートからの許可を与えるにはローカルも必要)

GRAN実行後は

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

を必ず実行

注)ユーザ名はmysql -uで指定しない限りloginユーザ名になる

パスワードの変更

mysql> SET PASSWORD FOR spadmin@localhost = PASSWORD('*******');
Query OK, 0 rows affected (0.00 sec)

ユーザ削除

ユーザ削除は権限を剥奪後削除する。とりあえずログインする

$ /usr/local/bin/mysql -u root -p mytest

権限を剥奪(revoke)

revoke 権限 on データベース名.テーブル名 from ユーザ名@ホスト名;

mysql> revoke all on mytest.* from okada@'192.168.1.1';
Query OK, 0 rows affected (0.00 sec)

登録ユーザの削除

mysql> delete from mysql.user where host='192.168.1.1' and user='okada';

or
(mysql> DELETE FROM mysql.user WHERE user='okada';)

mysql> flush privileges;

ユーザの権限の確認

「GRANT USAGE ON *.* TO 'okada'@'localhost'」の(1)の行はすべてに権限なしを意味する「USAGE」は権限なしの意味。

$ /usr/local/bin/mysql -u root -p
mysql> SHOW GRANTS FOR okada@localhost;
+-----------------------------------------------------------------------------------------------------------------------------+
| Grants for  okada@localhost                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'okada'@'localhost' IDENTIFIED BY PASSWORD '*DD*******44BC****************2D****8C'               | (1)
| GRANT ALL PRIVILEGES ON `xoops`.* TO 'okada'@'localhost' WITH GRANT OPTION                                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `eccubu_db`.* TO 'okada'@'localhost' WITH GRANT OPTION |
     DB名(eccubu_db)も確認できる
+-----------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

データベースの作成

# /usr/local/bin/mysqladmin -u root -p create mytest

文字コードを指定してデータベースの作成

mysql> CREATE DATABASE hoge CHARACTER SET SJIS;

データベースの一覧

# /usr/local/bin/mysqlshow -u root -p
Enter password:
+-----------+
| Databases |
+-----------+
| mysql     |
| mytest    |
| test      |
+-----------+

データベースの削除

# /usr/local/bin/mysqladmin -u root -p drop (DB名)

or

mysql> drop database (DB名);
このコマンドだけではmysql.dbテーブルに残っているようなのでデータベースを完全に 削除するには
mysql> delete from mysql.db where Db='(DB名)';

テーブルの作成

t_test.create
create table t_test (
  ID           int8,
  data2          int8,
  data3          varchar(64) character set utf8
);
# /usr/local/bin/mysql -u root -p mytest < t_test.create
Enter password:

mysql> show fields from t_test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | bigint(20)  | YES  |     | NULL    |       |
| data2 | bigint(20)  | YES  |     | NULL    |       |
| data3 | varchar(64) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

テーブル一覧

# /usr/local/bin/mysql -u root -p mytest

mysql> SHOW TABLES;
+------------------+
| Tables_in_mytest |
+------------------+
| t_test           |
+------------------+
1 row in set (0.00 sec)

データの入力等(postgresqlと同じ)

mysql> insert into t_test values(1, 1001, 'JE2ISM');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_test;
+------+-------+--------+
| ID   | data2 | data3  |
+------+-------+--------+
|    1 |  1001 | JE2ISM |
|    2 |  1002 | JF2LYU |
+------+-------+--------+
2 rows in set (0.00 sec)

データベースのバックアップ/リストア

データベースのバックアップ

# /usr/local/bin/mysqldump -u root -p --default-character-set=latin1 mytest > mytest.out

EUCのときは--default-character-set=ujisに,UTF8は utf8にする。

データベースのリストア

先に必要ならGRANTでユーザ等に許可を与える。
# /usr/local/bin/mysqladmin -u root -p create mytest
# /usr/local/bin/mysql -u root -p mytest < mytest.out

次のコマンドを実行

# /usr/local/bin/mysqladmin flush-privileges

データベースの削除

# /usr/local/bin/mysqladmin -u root -p drop mytest
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'mytest' database [y/N] y
Database "mytest" dropped

ネットワーク上から接続許可するには

$ /usr/local/bin/mysql -u root -p

で接続し、許可するユーザ、パスワード、ネットワーク(ホスト)を指定する。

mysql> GRANT ALL PRIVILEGES ON mytest.* TO okada@'192.168.1.0/255.255.255.0'IDENTIFIED BY '*****' WITH GRANT OPTION;

mysql> flush privileges;

mysql>  select host,user,password from mysql.user;
+----------------------------+------------+------------------------------------ -------+
| host                       | user       | password                                  |
+----------------------------+------------+-------------------------------------------+
| localhost                  | root       | *B*************************************** |
| uso5004.mie-chukyo-u.ac.jp | root       |                                           |  <---注)root@uso5004.mie-chukyo-u.ac.jpではパスワードなしで入れるので必ず削除しておく必ず削除しておく
| localhost                  |            |                                           | <---注)このようなレコードがあるとuser名がなんでもOKなので必ず削除しておく
| localhost                  | moodleuser | *B************************************** |
| 192.168.1.0/255.255.255.0  | okada      | *3************************************** |  <---ここに接続許可が示される
+----------------------------+------------+-------------------------------------------+
6 rows in set (0.00 sec)

リモートから

以下のコマンドで接続

$ /usr/local/bin/mysql -u okada -p mytest -h 192.168.1.1 
Enter password:

MySQLサーバーの全設定情報(変数やステータス)の表示

mysql> show variables;
mysql> show variables like 'key%';

--------------------------+---------+
| Variable_name           | Value   |

--------------------------+---------+
| key_buffer_size         | 8388600 |
| key_cache_age_threshold |     300 |
| key_cache_block_size    |    1024 |
| key_cache_division_limit|     100 |

--------------------------+---------+
4 rows in set (0.00 sec)
mysql> SHOW STATUS like 'key%';

------------------------+-------+
| Variable_name         | Value |

------------------------+-------+
| Key_blocks_not_flushed|     0 |
| Key_blocks_unused     | 28971 |
| Key_blocks_used       |    24 |
| Key_read_requests     |   231 |
| Key_reads             |    24 |
| Key_write_requests    |     0 |
| Key_writes            |     0 |

------------------------+-------+
7 rows in set (0.00 sec)

**MySQLサーバーの全設定情報(変数)の設定

/etc/my.cnfに設定することも可能だが、直接セットしてもよい。

mysql> set Global key_buffer_size= 33554432;
Query OK, 0 rows affected (0.01 sec)
SET GLOBAL sort_buffer_size = 10 * 1024 * 1024;
SET SESSION sort_buffer_size = 10 * 1024 * 1024;

システム変数には、サーバ共通の値とセッション(接続)共通の値とあり、 前者を変更すると、その後開かれる全てのセッションに影響、後者を変更すると、現在のセッションでのみ影響

テーブルのエクスポート/インポート

エクスポート

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

注)mysql実行ユーザのディレクトリでないと書き込み権限がないので、エラーするので/tmpなんかを指定する。

インポート

mysql> load data infile "/home/okada/mysqltest/t_test.csv" into table t_test fields terminated by ',';

文字コードについて
LOAD DATA INFILE(インポート)はcharacter_set_database変数のcharacter setを使う。
mysql> show variables like 'char%'; で確認

character_set_database   | ujis  <=EUC
  • Database作成時にdefault character setで指定する。
  • LOAD DATA INFILEに明示的にcharacter setを指定する.
    character set ujis
  • character_set_databaseを変更する
    mysql> set character_set_database='ujis';

DBの変更

rootの場合、他のDBに変更できる

mysql> use authdb
Database changed

簡単なチューニング

key_buffer_size
インデックスブロック用に使用するバッファのサイズ。
インデックスを使う(つまりほとんど全ての)クエリは
この値によってパフォーマンスが大きく変わります。
よって、この値は可能な限り(もちろん稼動システムの許容範囲内で)

大きくすることが推奨されています。

適切なバッファサイズを求めるには、SHOW STATUS コマンドを利用します。

ここで表示された Key_read_requests が、キャッシュからのキーブロック読み取り要求回数です。

Key_reads は、ディスクからのキーブロック読み取り要求回数です。 つまり、Key_reads / Key_read_requests が「キャッシュミスレート」になります。

この値は0.01を下回ることが良いとされています。

このときキャッシュミスレートは1%以下です。

これを上回るようだったら、 インデックスブロックのバッファサイズを大きくすることを検討

my.cnfのパラメータ

パラメータ名設定内容
port = 3306起動ポート番号
key_buffer = 256M検索に使われるインデックスをバッファに保存する際のメモリサイズを設定します。メモリに余裕がある場合はより沢山指定
max_allowed_packet = 1M入力データ保持の最大バッファサイズを設定。画像などの大きなデータを挿入する場合は、この制限に引っ掛かる可能性がある.
table_cache = 256頻繁なアクセスに対して使うデータキャッシュを設定。これによってディスクのI/O負荷を減らす。
sort_buffer_size = 1M並べ替えのバッファサイズを設定。値を大きくすることで ORDER BY や GROUP BY といった並べ替えクエリーの速度を上げる。
read_buffer_size = 1M読み込みのバッファサイズを設定。値を大きくすることでインデックスを含まないクエリーの実行速度を上げる。
myisam_sort_buffer_size = 64MMyISAMで使用する並べ替えのバッファサイズを設定。
thread_cache = 8スレッド生成のキャッシュサイズを設定。クライアントからの接続を受けると、スレッドが生成され、そのスレッドがクライアントのSQL分を処理。クライアントの接続が多い場合に有効。
query_cache_size= 16Mクエリのキャッシュサイズを設定。同じクエリを何度も実行する場合に有効。
thread_concurrency = 8同時に実行するスレッド数を指定。
join_buffer_size = 131072完全結合(インデックスを使用しない場合)のバッファサイズを設定。インデックスが使用できない場合に有効。

ODBC

Accessでテーブルリンク作成時の注意

もしも主キーが設定されていないテーブルをリンクした場合、次のような
[固有レコード識別子の選択]ダイアログが表示される。( 砲海譴蓮⊆腑ーが
設定されていない場合、テーブルの更新・削除などに際して、正しくデータを
同期することができない。このような画面が表示された場合には、
主キーとなるフィールドを選択する( 砲、MySQLサーバ上で主キーの設定する。
設定しないとテーブル上でデータの追加、修正ができない。
ReadOnlyになるので注意する。

MySQLODBC.png

Shellからいろいろ

テキストTAB形式でのエクスポート

$ /usr/bin/mysql -B --skip-column-names -u ism -p -D eccube_ism -e 'select * 
from dtb_customer;' > dtb_customer.txt
Enter password:

--skip-column-namesがない場合はcolumn-namesも出力する。

-h 192.168.30.16 でホスト指定

テキストTAB形式でのインポート

$ /usr/bin/mysqlimport --local eccube_ism2 dtb_customer.txt -u ism -p 
Enter password:
eccube_ism2.dtb_customer: Records: 1  Deleted: 0  Skipped: 0  Warnings: 3

指定されたテキストファイル名の主ファイル名に一致したテーブルにインポートされる。--fields-terminated-by="," として デリミターを「,」 を指定できる

-h 192.168.30.16 でホスト指定 --localは意味が違うので必要

$ /usr/bin/mysqlimport --local eccube_ism2 dtb_customer.txt -u ism -p -h  
192.168.30.16
Enter password:
eccube_ism2.dtb_customer: Records: 1  Deleted: 0  Skipped: 0  Warnings: 3

オプション

-l, --lock-tables

テキストファイルを処理する前に、すべてのテーブルへの書き込みをロックする。これにより、そのサーバ上のすべてのテーブルが同期化される。

-L, --local

クライアントから入力ファイルを読み取る。デフォルトでは、localhost(デフォルトホスト)に接続した場合、テキストファイルはサーバにあると想定される。

-r, --replace

--replace オプションおよび --ignore オプションは、既存レコードの値と重複するユニークキー値を持つ入力レコードの処理を制御する。--replace を指定した場合、新規レコードが同じユニークキー値を持つ既存レコードを上書きする。IGNORE を指定すると、ユニークキー値が既存のレコードの値と重複する入力レコードは無視される。どちらも指定しない場合、重複キー値が検出されるとエラーになり、テキストファイルの残りの部分が無視される。

defalt文字がNULLの場合はエクスポートされるとNULLという文字列になるが、このままインポートするとNULLという文字列になる。よって「\N」としてインポートする必要がある。

テーブルのデータ削除

$ /usr/bin/mysql -B --skip-column-names -u ism -p -D eccube_ism2 -e 'delete 
from dtb_customer;' -h 192.168.30.16
Enter password:

パスワード入れるには

--password=****とすれば良い

$ /usr/bin/mysqlimport --local eccube_ism2 dtb_customer.txt -u ism --pass
word=**** -h 192.168.30.16
eccube_ism2.dtb_customer: Records: 1  Deleted: 0  Skipped: 0  Warnings: 3

EC CUBEの複数の店のユーザを単一管理

DBからテーブルをエクスポートしてそれを別テーブルにインポート。同じユニークキーが ある時は上書きする。

tbsync.php

<?php

       $cmd = "/usr/bin/mysql -B --skip-column-names -u ism --password=*** -D eccube_ism -e 'select * from dtb_customer;' -h 192.168.30.16";
       exec($cmd, $output);
       $fp=fopen("/home/okada/temp/dtb_customer.txt","w");

       foreach ($output as $a){
               fputs($fp,$a."\n");
       }
       fclose($fp);

       $cmd ="/usr/bin/mysqlimport --local eccube_ism2 /home/okada/temp/dtb_customer.txt-u ism --password=*** -r -h 192.168.30.16";
       exec($cmd);
?>

html/entry/complete.phpとhtml/mypageのrefusal_complete.phpとchange_complete.php最後の行に

require_once("/home/okada/temp/tbsync.php");

を追加

参考

MySQLクイック・リファレンス

MySQLの変数



添付ファイル: fileMySQLODBC.png 168件 [詳細]

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2010-11-26 (金) 21:59:44 (2645d)