MySQL
共用レンタルサーバーや自サーバーで利用するデータベースとして、MySQLは非常に多いと思います。ここでは、最初に利用するにあたって必要となるユーザ設定や些細な情報を残します。古い情報にそのまま追加していますので、新しいバージョンとは異なる場合があります。詳細は、MySQLドキュメントを確認して下さい。
初期設定ファイル「my.ini」の設定
MySQLシステムを利用するためのいろいろな設定が、「C:\WINDOWS\my.ini」ファイルで可能である。以下の設定を行った。設定が終了したらタスクバーのアイコンを右クリックして「コントロールパネル」から「管理ツール」-「サービス」を実行し、MySQLデータベースを停止、再起動する
- データベースの場所[mysqld] datadir=D:/MySQLData
「C:\Program Files\MySQL\data」フォルダにある「mysql」と「test」のフォルダを「D:\MySQLData」フォルダにコピー。
- データベースの文字コード[mysqld]
default-character-set=sjis
※MySQL5からは、Webで利用するならUTF-8の方が都合が良い(071002)。
- クライアントの文字コードの設定[mysql]
default-character-set=sjis
その他、mysqldumpコマンドも同じように設定できるようである。
※XPやVistaならクライアントもUTF-8でも良い(071002)。
※5.0.67では「utf8」だと文字化け、「sjis」も一部文字化け、「cp932」でOK。
(081015)
- クライアントのプロンプト文字列設定[mysql]
prompt=”\d-\u> ”
プロンプトを「DB名-ユーザー名> 」とした。
プロンプトに指定できるパラメータは、MySQLドキュメントの下方参照(080918)。
- 英大文字使用の有効化[mysqld]
set-variable=lower_case_table_names=0
テーブルなどの名称に、大文字を使用できるようにする(phpMyAdminのFAQでﾊｹｰﾝ）。確認は、「mysql」で
show variables like ‘lower_case_table_names’;
実のところあまり気にしてなかった(^_^); (050220)
- 英大文字使用の有効化２[mysqld] 4.0.18以降
4.0.25をインストール後、XPの管理ツールからイベントビューアを起動しアプリケーションを見たところ、MySQLのエラーが発生していた。調べたところ、テーブル名の大小文字の区別をする指定は、
set-variable=lower_case_table_names=2
のように、「2」を指定しなければならないらしい。これは、ファイル名の大小文字を区別しないシステムでテーブル名の大小文字を区別する際の指定との事だった。(050721)
補足：「my.ini」の編集は、行末がLF(0x0a)なのでXPやVistaのメモ帳で開くと、データが一続きで表示される（テキストエディタを利用した方が良い）。
パスワード設定・変更
インストール時rootユーザーには、パスワードは設定されていない。また、rootユーザーのlocalhost（MySQLをインストールした ＰＣ）以外のログオンは禁止されている。ここでは、localhostからもパスワードを入力しないと利用できないように、パスワード設定を行う。
shell>mysql -u root mysql
mysql> set password for root@localhost=password('XXXXXXXX');
次からコマンドを実行するときは、
shell>mysql -u root -p DB名 または、
shell>mysql -u root -pPASSWORD DB名
とする。上は、パスワードを聞いてくる。また、rootユーザーのパスワードの変更は次のようにする。
mysql> update user set password=password('NEW PASSWORD') where user='root';
または、root以外その他のユーザーも含めてパスワードを変更する方法。
mysql> set password for 'ユーザー名'@'ホスト名'=password('NEW PASSWORD');
ユーザの登録
- root以外のスーパーユーザーを登録するには、
shell>mysql -u root -p mysql
mysql> grant all on *.* to USER名@localhost identified by 'PASSWORD';
mysql> grant all on *.* to USER名@'%' identified by 'PASSWORD';
- 権限なしのユーザーを登録するには、
mysql>grant usage on DB名.TBL名 to USER名@localhost identified by 'PASSWORD';
- 登録ユーザーの一覧は、
mysql> select user,host from user;
- データベース「mydb」を利用するユーザーで、利用権限を付けて登録は、
mysql> grant select,insert,update,delete,create,drop,index
-> on mydb.* to USER名@localhost identified by 'PASSWORD';
- データベースユーザの権限の表示は、
mysql> show grants for USER名@'%';
- 権限の追加は、
mysql> grant alter on mydb.* to USER名@localhost;
- 権限の削除は、
mysql> revoke alter on mydb.* from USER名@localhost;
ユーザの削除
shell>mysql -u root -p mysql
mysql> select user,host from user;
mysql> delete from user where user='USER名' and host='HOST名';
mysql> flush privileges;
ユーザーの権限確認
mysql> show grants for 'USER名'@'localhost';
ユーザー名とホスト名を囲うシングルクォーテーションは省略可。
テーブルのコピー
テーブル更新プログラムを作ったので実行しようかと思ったが、プログラムミスで取り返しのつかない惨状は見たくないと思い、バックアップ代わりにテーブルを簡単にコピーできないものか考えた。
検索したところ、コマンドラインからの実行を除いて、以下の２つの方法があるようだ。
- create table new_table_name select * from copy_table_name
- create table new_table_name like copy_table_name
insert into new_table_name select * from copy_table_name
１を実行すると、auto_increment指定したカラムのauto_increment指定が外されたり、varchar型がchar型のカラムになることがあるようだ。
２の方法であれば、一旦スキーマをコピーしてからデータを挿入するので、１のようなことはなかった(MySQL version 4.1.22で確認)。また、auto_increment属性を持つカラムデータは、歯抜けであっても前詰めされずにその値で挿入されていた。
(081221)
カラムデータのアップデート
バックアップデータのカラムデータと新しいかテーブルのデータを比較して、新しいテーブルの項目の一部に抜けがあったのでコピーする必要が生じた場合の作業。
update new_table_name as n1 set n1.col1= (select b1.col1 form bkup_table_name as b1
where n1.key=b1.key)
where n1.col1 is null;
作業は注意が必要。
(130710)
テーブル名の変更
テーブル名の変更は、
mysql> alter table 変更前のテーブル名 rename to 新しいテーブル名
と実行すればよい。Ver.5.1.34 で実行確認済み。
(090801)
MySQLアップグレードの際のテーブルチェック
開発作業で利用しているMySQLのバージョンを、たまたま5.0.67から5.1.31にバージョンアップする際、MySQLのドキュメントサイトで「mysql_upgrade MySQLアップグレードのテーブルチェック」を見つけたので、利用してみました。
テーブルの構造とか新しくなったとき、このプログラムを実行すると自動でチェックして修正してくれるようで、実際使ってみたところ、何やらエラーメッセージと修復メッセージが出てきました。
バックアップも取らずにイキナリ実行、いやはや冷や汗ものでありますね(^_^;)。
(090212)
5.1.52のアップグレードでインストール後のMySQL Start でエラー
それまで5.1.41を動作させていた。アップグレードの手順は以下の通り。
１．MySQLのサービス停止
２．MySQLをアンインストール
３．5.1.41をインストールしていたディレクトリへ、5.1.52をインストール
インストール後、iniの設定を行なってサービス起動の所でエラーが発生。アンインストール後再度試みるも同じ所で停止。
設定プログラムをキャンセル終了し、my.iniを現在の環境に書き直ししてPCを再起動した所、サービスも無事に起動されて動作確認できた。なお起動後、mysql_upgradeを実行した。
my.iniの変更は、データベースエンジンをInnoDBからMYISAMにした。これが原因かは不明。
(101107)
テーブルのデータを簡単にCSV出力する
例えばusersテーブルをCSVファイルで出力したいとき、次のようなコマンドでファイル出力できる。
>SELECT * FROM users ORDER BY id INTO OUTFILE "ファイルパス名" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
こんなに簡単にできる事を今まで知らなかった。もっとも今まで必要なかったのだが。
(120912)
MySQL 5.5.31(ubuntu)のレプリケーション設定
レプリケーションに関する情報を見ると、スレーブ側のmy.ini(ubuntuではconf.d/xxx.cnf)の[mysqld]セクションに、マスターサーバーの情報(host,user,password,etc.)を登録してサービスを起動するように説明されている。
で、試したところサービスが起動しない。そこでログ(/var/log/mysql/error.log)を見ると「unknown variable ‘master-host=x.x.x.x’」のエラーが表示されていた。
どうやらレプリケーションの設定方法が代わったようだ。mysqlのプロンプトで
mysql> change master to master_host='x.x.x.x',master_user='user'@'host',master_password='password';
mysql> start slave;
とすればよい。なお、log-bin(バイナリログの書き出し指定)とserver-idは忘れず設定ファイルに登録しておく。スレーブ状態の確認は次の通り。
mysql> show slave status\G;
(130607)
キャラクターセットの確認
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
ujis
character_set_system
utf8
character_sets_dir
/usr/local/share/mysql/charsets/
(130621)
mysqldumpでSQL文出力
> mysqldump -h localhost -u USER名 --where='id>=5 and id<=10' -n -p --skip-extended-insert DB名 TABLE名 > FILE名
-h：ホスト
-u：ユーザー
–where：条件指定
-n：–no-create-db
-p：パスワード利用
–skip-extended-insert：insert文にカラム名を含めず1行1insert文
(140819)
mysqldumpでテーブルデータ出力
テーブルデータを別のデータベースへコピーしたい場合、mysqldumpでコピーしたいテーブルを出力し、mysqlを利用して別のデータベースにコピーする。
$ mysqldump -h localhost -u USER名 -p DB名 TABLE名1 TABLE名2 … > ファイル名
複数のテーブルを同時に出力する場合、データベース(DB)名の後ろにスペース区切りでテーブル(TABLE)名を並べると出力される。
出力されたSQLのテキストファイルをmysqlを利用して読み込む。同名のテーブルがあれば削除後コピーされるので注意する。
$ mysql -h HOSTのURL -u USER名 -p DB名 < ファイル名
(180427)
共用レンタルサーバーや自サーバーで利用するデータベースとして、MySQLは非常に多いと思います。ここでは、最初に利用するにあたって必要となるユーザ設定や些細な情報を残します。古い情報にそのまま追加していますので、新しいバージョンとは異なる場合があります。詳細は、MySQLドキュメントを確認して下さい。
初期設定ファイル「my.ini」の設定
MySQLシステムを利用するためのいろいろな設定が、「C:\WINDOWS\my.ini」ファイルで可能である。以下の設定を行った。設定が終了したらタスクバーのアイコンを右クリックして「コントロールパネル」から「管理ツール」-「サービス」を実行し、MySQLデータベースを停止、再起動する
- データベースの場所[mysqld] datadir=D:/MySQLData
「C:\Program Files\MySQL\data」フォルダにある「mysql」と「test」のフォルダを「D:\MySQLData」フォルダにコピー。 - データベースの文字コード[mysqld]
default-character-set=sjis
※MySQL5からは、Webで利用するならUTF-8の方が都合が良い(071002)。 - クライアントの文字コードの設定[mysql]
default-character-set=sjis
その他、mysqldumpコマンドも同じように設定できるようである。
※XPやVistaならクライアントもUTF-8でも良い(071002)。
※5.0.67では「utf8」だと文字化け、「sjis」も一部文字化け、「cp932」でOK。
(081015) - クライアントのプロンプト文字列設定[mysql]
prompt=”\d-\u> ”
プロンプトを「DB名-ユーザー名> 」とした。
プロンプトに指定できるパラメータは、MySQLドキュメントの下方参照(080918)。 - 英大文字使用の有効化[mysqld]
set-variable=lower_case_table_names=0
テーブルなどの名称に、大文字を使用できるようにする(phpMyAdminのFAQでﾊｹｰﾝ）。確認は、「mysql」で
show variables like ‘lower_case_table_names’;
実のところあまり気にしてなかった(^_^); (050220) - 英大文字使用の有効化２[mysqld] 4.0.18以降
4.0.25をインストール後、XPの管理ツールからイベントビューアを起動しアプリケーションを見たところ、MySQLのエラーが発生していた。調べたところ、テーブル名の大小文字の区別をする指定は、
set-variable=lower_case_table_names=2
のように、「2」を指定しなければならないらしい。これは、ファイル名の大小文字を区別しないシステムでテーブル名の大小文字を区別する際の指定との事だった。(050721)
補足：「my.ini」の編集は、行末がLF(0x0a)なのでXPやVistaのメモ帳で開くと、データが一続きで表示される（テキストエディタを利用した方が良い）。
パスワード設定・変更
インストール時rootユーザーには、パスワードは設定されていない。また、rootユーザーのlocalhost（MySQLをインストールした ＰＣ）以外のログオンは禁止されている。ここでは、localhostからもパスワードを入力しないと利用できないように、パスワード設定を行う。
shell>mysql -u root mysql
mysql> set password for root@localhost=password('XXXXXXXX');
次からコマンドを実行するときは、
shell>mysql -u root -p DB名 または、 shell>mysql -u root -pPASSWORD DB名
とする。上は、パスワードを聞いてくる。また、rootユーザーのパスワードの変更は次のようにする。
mysql> update user set password=password('NEW PASSWORD') where user='root';
または、root以外その他のユーザーも含めてパスワードを変更する方法。
mysql> set password for 'ユーザー名'@'ホスト名'=password('NEW PASSWORD');
ユーザの登録
- root以外のスーパーユーザーを登録するには、
shell>mysql -u root -p mysql mysql> grant all on *.* to USER名@localhost identified by 'PASSWORD'; mysql> grant all on *.* to USER名@'%' identified by 'PASSWORD';
- 権限なしのユーザーを登録するには、
mysql>grant usage on DB名.TBL名 to USER名@localhost identified by 'PASSWORD';
- 登録ユーザーの一覧は、
mysql> select user,host from user;
- データベース「mydb」を利用するユーザーで、利用権限を付けて登録は、
mysql> grant select,insert,update,delete,create,drop,index -> on mydb.* to USER名@localhost identified by 'PASSWORD'; - データベースユーザの権限の表示は、
mysql> show grants for USER名@'%'; - 権限の追加は、
mysql> grant alter on mydb.* to USER名@localhost; - 権限の削除は、
mysql> revoke alter on mydb.* from USER名@localhost;
ユーザの削除
shell>mysql -u root -p mysql mysql> select user,host from user; mysql> delete from user where user='USER名' and host='HOST名'; mysql> flush privileges;
ユーザーの権限確認
mysql> show grants for 'USER名'@'localhost';
ユーザー名とホスト名を囲うシングルクォーテーションは省略可。
テーブルのコピー
テーブル更新プログラムを作ったので実行しようかと思ったが、プログラムミスで取り返しのつかない惨状は見たくないと思い、バックアップ代わりにテーブルを簡単にコピーできないものか考えた。
検索したところ、コマンドラインからの実行を除いて、以下の２つの方法があるようだ。
- create table new_table_name select * from copy_table_name
- create table new_table_name like copy_table_name
insert into new_table_name select * from copy_table_name
１を実行すると、auto_increment指定したカラムのauto_increment指定が外されたり、varchar型がchar型のカラムになることがあるようだ。
２の方法であれば、一旦スキーマをコピーしてからデータを挿入するので、１のようなことはなかった(MySQL version 4.1.22で確認)。また、auto_increment属性を持つカラムデータは、歯抜けであっても前詰めされずにその値で挿入されていた。
(081221)
カラムデータのアップデート
バックアップデータのカラムデータと新しいかテーブルのデータを比較して、新しいテーブルの項目の一部に抜けがあったのでコピーする必要が生じた場合の作業。
update new_table_name as n1 set n1.col1= (select b1.col1 form bkup_table_name as b1
where n1.key=b1.key)
where n1.col1 is null;
作業は注意が必要。
(130710)
テーブル名の変更
テーブル名の変更は、
mysql> alter table 変更前のテーブル名 rename to 新しいテーブル名
と実行すればよい。Ver.5.1.34 で実行確認済み。
(090801)
MySQLアップグレードの際のテーブルチェック
開発作業で利用しているMySQLのバージョンを、たまたま5.0.67から5.1.31にバージョンアップする際、MySQLのドキュメントサイトで「mysql_upgrade MySQLアップグレードのテーブルチェック」を見つけたので、利用してみました。
テーブルの構造とか新しくなったとき、このプログラムを実行すると自動でチェックして修正してくれるようで、実際使ってみたところ、何やらエラーメッセージと修復メッセージが出てきました。
バックアップも取らずにイキナリ実行、いやはや冷や汗ものでありますね(^_^;)。
(090212)
5.1.52のアップグレードでインストール後のMySQL Start でエラー
それまで5.1.41を動作させていた。アップグレードの手順は以下の通り。
１．MySQLのサービス停止
２．MySQLをアンインストール
３．5.1.41をインストールしていたディレクトリへ、5.1.52をインストール
インストール後、iniの設定を行なってサービス起動の所でエラーが発生。アンインストール後再度試みるも同じ所で停止。
設定プログラムをキャンセル終了し、my.iniを現在の環境に書き直ししてPCを再起動した所、サービスも無事に起動されて動作確認できた。なお起動後、mysql_upgradeを実行した。
my.iniの変更は、データベースエンジンをInnoDBからMYISAMにした。これが原因かは不明。
(101107)
テーブルのデータを簡単にCSV出力する
例えばusersテーブルをCSVファイルで出力したいとき、次のようなコマンドでファイル出力できる。
>SELECT * FROM users ORDER BY id INTO OUTFILE "ファイルパス名" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
こんなに簡単にできる事を今まで知らなかった。もっとも今まで必要なかったのだが。
(120912)
MySQL 5.5.31(ubuntu)のレプリケーション設定
レプリケーションに関する情報を見ると、スレーブ側のmy.ini(ubuntuではconf.d/xxx.cnf)の[mysqld]セクションに、マスターサーバーの情報(host,user,password,etc.)を登録してサービスを起動するように説明されている。
で、試したところサービスが起動しない。そこでログ(/var/log/mysql/error.log)を見ると「unknown variable ‘master-host=x.x.x.x’」のエラーが表示されていた。
どうやらレプリケーションの設定方法が代わったようだ。mysqlのプロンプトで
mysql> change master to master_host='x.x.x.x',master_user='user'@'host',master_password='password'; mysql> start slave;
とすればよい。なお、log-bin(バイナリログの書き出し指定)とserver-idは忘れず設定ファイルに登録しておく。スレーブ状態の確認は次の通り。
mysql> show slave status\G;
(130607)
キャラクターセットの確認
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 | ujis |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/share/mysql/charsets/ |
(130621)
mysqldumpでSQL文出力
> mysqldump -h localhost -u USER名 --where='id>=5 and id<=10' -n -p --skip-extended-insert DB名 TABLE名 > FILE名
-h：ホスト
-u：ユーザー
–where：条件指定
-n：–no-create-db
-p：パスワード利用
–skip-extended-insert：insert文にカラム名を含めず1行1insert文
(140819)
mysqldumpでテーブルデータ出力
テーブルデータを別のデータベースへコピーしたい場合、mysqldumpでコピーしたいテーブルを出力し、mysqlを利用して別のデータベースにコピーする。
$ mysqldump -h localhost -u USER名 -p DB名 TABLE名1 TABLE名2 … > ファイル名
複数のテーブルを同時に出力する場合、データベース(DB)名の後ろにスペース区切りでテーブル(TABLE)名を並べると出力される。
出力されたSQLのテキストファイルをmysqlを利用して読み込む。同名のテーブルがあれば削除後コピーされるので注意する。
$ mysql -h HOSTのURL -u USER名 -p DB名 < ファイル名
(180427)