DB・SQLの最近のブログ記事

■PostgreSQL

【バックアップ】
$ pg_dump DB名 > backup_20110310.dump

リモートの場合
$ pg_dump -h ホスト名 -p ポート番号 DB名 > backup_20110310.dump

全部一気にバックアップ
$ pg_dumpall > backup_20110310.dump

テーブルをデータごとバックアップ
$ pg_dump -t table名 DB名 > table名.sql

テーブルのスキーマのみをバックアップ
$ pg_dump -s -t table名 DB名 > table名.sql

※その他、圧縮dumpや分離も出来る（ファイルサイズが大きくなった場合）

【リストア】
$ createdb DB名
$ psql -f backup_20110310.dump DB名
or
$ psql -d DB名 -e < backup_20110310.dump

空のクラスタ内に全部リストアの場合
$ psql template1 < backup_20110310.dump

【データベースを丸々別のところにコピー】
旧サーバ
pg_dump -h localhost -U postgres -c 旧DB名 > data20110310.dump
新サーバ
psql -h localhost -U postgres -f data20110310.dump 新DB名

■MySQL
【バックアップ】
$ mysqldump -u ユーザ名 -p DB名 > backup_20110310.dump
パスワード聞かれるので入力

全部一気にバックアップ
$ mysqldump -A -u ユーザ名 -p > backup_20110310.dump

【リストア】
DBを作った後で
$ mysql -u user -p DB名 < backup_20110310.dump

※テーブルが存在してたら怒られるので注意。

mySQLでダウンプファイルが文字化けすることがあるので、下記のように文字コード設定
mysqldump --default-character-set=binary -u ユーザ名 -p DB名 > backup_20110310
文字コードはlatin1、utf8などいろいろ。

DBでテーブルを作ったとして、あとから型を変えたい！って時にはキャストを使います。
TEXT型→INTEGER型　にするとか、よくあるお話。

psql -d DB名 -c "ALTER TABLE テーブル名 ALTER COLUMN カラム名 TYPE integer
USING CAST(カラム名 AS INTEGER)"

action scriptなんかでもMovieClip(root)とかって、rootをムービークリップとしてキャストしてるんですよね〜。

mysqlに入る

$ mysql -u ユーザ名 -p
Enter password:

mysql> show databases;
で、データベース一覧をみれます

mysql> use　DB名;
でデータベースに入ります

mysql> show tables;
でテーブル一覧を見れます。

mysql> describe テーブル名;
または
mysql> show columns from テーブル名;
でテーブルの中身を見れます。

データベース作成
mysql > create database DB名;

ERROR 1044 (42000): Access denied for user: ''@'localhost' to database

となったら、rootで入ってね
$ mysql -u root

テーブル作成例
CREATE TABLE `musical` (
`id` int(11) NOT NULL auto_increment,
`name` text,
`message` text NOT NULL,
`created` timestamp NOT NULL default 'CURRENT_TIMESTAMP',
`modified` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=0 AUTO_INCREMENT=201 ;

テストサイトから本番サイトにDBをそっくりそのままうつしたいときの処理。
もちろんバックアップにも利用できます。
db_20090101というのは何でも良いけど、日付を入れとくとバックアップ用によいでしょう。

【テストサーバにて】
■DB全体をうつす
pg_dump DB名 > db_20090101.dump

■テーブルのみうつす
pg_dump DB名 -t テーブル名 > db_20090101.dump
でdumpファイルを作ります。

【本番サーバにて】
drop table テーブル名（テーブルが存在するならまず削除）
psql -d DB名 -e < db_20090101.dump

pg_insert()

のエラーが表示されなくて、何が理由でエラーになるか解らなかったけど、こういうときは

echo pg_last_error($conn);

で、最後のエラーが取得できます。
$connはDBへの接続ね。

Warning: pg_query() [function.pg-query]: Query failed: ERROR: permission denied for relation articles in /var/www/*******/***.php on line 68

てなエラーがでてDB操作ができなかった時、テーブルに権限が設定されていないことが原因。

productslistというテーブルに対してwwwというユーザに権限を与える時

■select権限のみ与える時
grant select on productslist to www;

■insert、update、deleteも可能にするなら
grant select,insert,update,delete on productslist to www;

■全部与える時
grant all on productslist to www;

GRANTって表示されたら変更完了ってこと。

■権限を奪う時
revoke select on productslist from www;

※シーケンスの場合、USAGE, SELECT, UPDATEしかないので注意。

全てのユーザに権限を与える/奪う場合は、ユーザ名（例でいうwww）のかわりにpublicでOK

■テーブルではなくDBへは以下
databaseがつくだけ。
grant all on database dbname to www;
revoke all on database dbname from www;

バックスラッシュコマンド

\h　ヘルプ表示
\copy tablename from /tmp/data.csv　データをテーブルにインポート（成功すると\.と表示される）
\copy tablename to /tmp/data.csv　データをテキストにエクスポート
\l　データベースの一覧を表示
\dn　スキーマの一覧を表示
\dt　ユーザーテーブル一覧を表示
\d tablename　テーブルの定義
\dT　型の一覧

\q ポストグレスを終了

などなど。

Postgresでデータベースを作るときの文字コード設定
データベースごとに文字コードを変えることが可能。

createdb データベース名 --encoding unicode
とか
createdb データベース名 -E UTF-8

古いバージョンで上記が効かないならオプション位置を前に変えたら動くかも。

createdb -E UTF-8 データベース名

■select（取り出す）
select * from テーブルの名前 where status='xxx' order by id;

statusの値がxxxデータをidの順に取り出します。order by id descだと、大きい順。
limit 2だと2個だけ。（PostgreSQL特有）
offset10だと10番目から（PostgreSQL特有）
取り出すデータは*（全部）です。（もちろん指定できる）

□便利な応用
select count(*) from テーブルの名前 where created > '2009-01-01';
2009年1月1日以降のデータの数を取得

■insert（登録する）
insert into DBの名前 values(データ1,データ2,データ3);

□実際のところ
insert into DBの名前 values(データ1,データ2,データ3);
insert into DBの名前 values(データ1,データ2,データ3);
......
とかかれたテキストファイル_insert_table1.sqlを作ってしまって、
psql -d DB名 -f _insert_table1.sql
で実行した方が楽、だし確実（実行したものが残せるし）。

■update（データ更新）
update テーブルの名前 set status = '×××' where id='xxx'

idの値がxxxの箇所のfileの値を×××にします。

■delete（データ削除）
delete from テーブルの名前 where id='xxx'

放っておくとごみがたまります。
/usr/bin/vacuumdb/vacuumdb -a -z
を毎日。
/usr/bin/vacuumdbvacuumdb -a -f
を月一回程度。

確認は
/usr/bin/vacuumdb/vacuumdb -a -z >! /tmp/vacuumdb_result.txt
/tmp/vacuumdb_result.txt
にVACUUMと記録されたらOK

■cronで自動化
postgres権限でcronetabを編集
emacs crontab

内容
00 3 * * * /usr/bin/vacuumdbvacuumdb -a -z
00 3 1 * * /usr/bin/vacuumdbvacuumdb -a -f

毎日午前3時と、毎月1日の午前3時に行われます。