MySQLのダンプをタイムスタンプ付きのファイル名で作る（ちょっと更新1）

 MySQLデータベースのダンプを作るときに毎回コマンドをコピペして作るのが面倒になったので一連のコマンド作りました。
とりあえずLinux版

ダンプしたファイルを圧縮する機能を入れてみました。

仕様

• ダンプファイル名には、タイムスタンプをつける
• ワンコマンドで実行できる
• ファイルを圧縮する
• ダンプフィアルを削除する

DB_TIMESTAMP=`date +%Y%m%d_%H%M%S`
DB_HOST=192.168.0.132
DB_USER=XXXXX_user
DB_PW=XXXXX_pw
DB_NAME=XXXXX_db
mysqldump -h $DB_HOST -u$DB_USER -p$DB_PW $DB_NAME > $DB_NAME\_$DB_TIMESTAMP.dump
tar cfvj $DB_NAME\_$DB_TIMESTAMP.tar.bz2 $DB_NAME\_$DB_TIMESTAMP.dump
rm $DB_NAME\_$DB_TIMESTAMP.dump

パスワードをコマンドラインに直接書いているので「セキュアでないよ」というメッセージは出ますがまぁ、OKです。

MySQLのダンプとリストアコマンド

 MySQLのダンプとリストアコマンドを覚えられないのでメモっておく。

構造部とデータ部を分けてエクスポートします。
理由は後で追記しておきます。ちょっと検証が必要だったので。

データベースのエクスポート

>mysqldump -u hoge_user1 -p --no-data --skip-triggers hoge_db1 > hoge_db1_20150518_1000.dump

>mysqldump -u hoge_user1 -p --no-create-info --skip-triggers hoge_db1 > hoge_db1_data_20150518_1000.dump

データベースのインポート

>mysql -u hoge_user2 -p hoge_db2 < hoge_db1_20150518_1000.dump

>mysql -u hoge_user2 -p hoge_db2 < hoge_db1_data_20150518_1000.dump

トリガーを作成

トリガーがある場合は、全てのインポートが完了した後に、ソースから投入します。

ソートが無い場合は、もらってください。

MySQLテーブルのレコードサイズとか出すSQL

　データ量の見積をするために、テーブルのレコードサイズを必要があったので、SQLで出力できる方法を探しました。

SELECT
  table_name,
  engine,
  table_rows as tbl_rows,
  avg_row_length as rlen,
  floor((data_length + index_length) / 1024 / 1024) as allMB,
  floor((data_length) / 1024 / 1024) as dMB,
  floor((index_length) / 1024 / 1024) as iMB
FROM
  information_schema.tables
WHERE
  table_schema = database() AND engine IS NOT NULL
ORDER BY
  table_name
, (data_length + index_length) desc;

こちらから拝借しました。
http://d.hatena.ne.jp/sho-yamasaki/20120405/1333640589

iptablesにハマるの巻(MySQLにつながらない…)

　MySQLを使う必要があって、CentOS6.5にソースからインストールして見ました。
（なぜソースからというと新しいのが使いたかったからなんです。練習にもなるし）

　インストールが終わって（これも一時問題がありましが…）、ローカルホストからの接続は確認できたので、別のPCから接続を試みたとろこ繋がらない…。
　ちゃんと、CentOS側では、3306のポートは開けています。でも、繋がらない。

何がまずいのかわかりませんが、iptablesの設定の順番を変更することで解決しました。

filter
:INPUT DROP [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [34:5744]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m tcp --sport 3306 -j ACCEPT
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

filter
:INPUT DROP [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [34:5744]
-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m tcp --sport 3306 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

なにが違うかというとハイライトしている行の設定を書く位置を変更しました。

なるほど、-A INPUT -j REJECT --reject-with icmp-host-prohibitedの後ろに書いたのは、全てREJECTされるらしいです。
なので、REJECTの行を後ろに移動したら動いたんですね。うん、うん。

教えて!gooに同じことで悩んでる人がいました。
http://oshiete.goo.ne.jp/qa/7530019.html


ほんとにこの書き方でいいのか、何が問題かはこれから調べて自分の知識にしていく必要がありんす。
もう少し真面目に本とか呼んで意味で覚えないとダメだなこれは。

テーブル設計するときのツールはなに？

　皆さんは、テーブル設計する時にどんなツールを使っていますか？
EXCEL？Visio？

　テーブル設計するときは、テーブルレイアウトだけ作れれば良いわけではなく、少なくともER図も一緒に作っていかなければいろいろなことが整理できません。
　ER図とテーブルレイアウトは、ちゃんと一致していないと整合性をとるための無駄な作業も発生してしまいます。

テーブル設計をするためのツールは有料のもの、無料のものなどいろいろあります。

• SI Object Browser ER
  いいんだけど、個人としては高いです。あと、他の人とデータを共有しようとすると、同じツールを買ってもららないとなので
• ERMaster
  Eclipseのアドイン。無料でいいのですが、最近アップデートされていない感じが。
  ドメイン管理のところにくせがあるので、理解して使わないと大変なことが発生します。
  実際に使ってました。
• A5:SQL Mk2
  無料のSQL開発ツール
• Visio
  Microsoftの図書くツール

幾つか試したところで今使っているものを紹介（だけ）します。

A5:SQL Mk:2(高機能かつ軽量な汎用SQL開発ツール / ER図ツール / フリーソフト)

• ER図
• テーブル設計書作成
• ACCESSのようなGUIを使ったSQLの作成
• いろいろなデータベースへの接続
  MySQLの場合、SSHを使って接続ができるのでVPSとかにデータベースを作った場合、セキュリティを確保して接続ができる。
  phpMyAdminとかのインストールをしなくても良くなる。

まだ、他にも沢山の機能があります。

　新しく覚えるのがめんどくさいから使わないとか言わないで使ってみたらいいさ。

Windows2008 R2のMySQLにリモートPCからつなぐ

　わかっていたはずなのに忘れているという…。

　MySQL使った、アプリケーションのテスト環境を作ろうと思って、ファイアーウォールのことを忘れてて2時間も費やした。

MySQLをリモートPCから接続する為に、やること。

1. MySQLが適切にインストールする。
2. ファイアーウォールでプロファイルの指定を追加。
   実は、これにハマって閉まっていました。
   例えば、MySQL5.6をインストールすると、ファイアウォールの受信の規則に「MySQL56」というエントリが２つ作成されます。
   どちらでも良い(ドメイン用途、プライベート用の２つがある)ので、詳細設定のタブのプロファイルの設定の「パブリック(U)」にチェックを入れる。
   
   これしないと外から入れない。きっと、サーバーも、クライアントPCもドメインに参加していないからだと思うけど…。テスト環境だからOK.
3. rootでログインしてリモート接続するクライアント用のアカウントを作る。(当然この作業は、MySQLをインストールしたサーバで行う。)
   環境
   DB名:oreore_db
   アカウント名：oreore_user
   パスワード：oreore_pw
   リモートPC名：oreore_pc（ここをIPアドレスにもできるよ。例えば、192.168.0.%とか）
   mysql>grant all on test_db.* to 'oreore_user'@'oreore_pc' identified by 'oreore_pw':
4. リモートPCから接続してみる
   何を使って確認をしてもOK。
   ちょっと前からのDB操作のツールのトレンドは、「A5:SQL Mk-2」というものです。

　これできないと、DBを別のサーバーに入れられないので嫌なのです。（クライアントPCにサーバーアプリケーション入れたくないののろのろするから。）