データベースサーバ MySQL サーバ†

↑

ライセンス†

  • フリーなとき
    • 個人用途
    • 社内用途
    • GPLライセンスでのシステム開発
    • 教育機関
  • ライセンスが必要なとき
    • MySQLを含めた製品を販売
    • MySQLを利用した有料サービス
↑

MariaDB†

  • MySQLがオラクルの中で不穏な感じなので、フォークしてオープンソースで作り直された。
  • 基本的に動かし方はMySQLと同じで、phpやperlからも全く同じ。
    • systemctl start mariadb
↑

サーバセットアップ†

  • TurboLinux 10 server インストール済みのもの
  • CentOS 5.5
    • yum install mysql-server
      • mysql-develも入れておいた方がメンテの時に良いかも。
  • CentOS 7
    • yum install mariadb-server perl-DBD-MySQL php56-php-mysqlnd
      • perlとphp56のモジュールもついでに
  • vi /etc/my.cnf
    • MariaDBでは/etc/my.cnf.d/server.cnfでも良い。
    • 言語と文字コード 
      [mysqld]
language=/usr/share/mysql/japanese
default-character-set=sjis
character-set-server = utf8
      • 指定しなくても特に問題はない。
      • 最近のバージョンではおまじないでcharacter-set-server = utf8だけ設定している所存。
    • データベースの実態をどこに配置するか 
      [mysqld]
datadir = /var/lib/mysql
      • 後述のバックアップ項目で、別ディレクトリにデータを配置し、バックアップをとりやすくなる。
      • ただし、コマンドラインでmysqlに接続する際にソケットを指定する必要がある。 
        mysql --user=hoge -p -S /var/lib/mysql/mysql.sock
      • もしくはcnfにsocket = /var/lib/mysql/mysql.sock
    • 下手なチューニングをするぐらいなら
      • /usr/share/mysql/my-large.cnfとかmy-huge.cnfを/etc/my.cnfにコピーして使う。
    • バイナリログの記述形式（特にレプリケーション時） 
      binlog_format=mixed
      • OwnCloudでmixedじゃないとダメだと言われたので。
      • バイナリログを命令単位(statement)や変更値単位(row)ではなくて、実行状況に応じて切り替える方式(mixed)とする。
  • vi /etc/hosts.allow 
    # mysql
mysqld : 172.16.0.0/255.255.0.0
mysqld : 202.171.139.208/255.255.255.248
    • アクセス許可
      • CentOSはここでは指定しなくても動く。
  • /etc/init.d/mysqld start
  • chkconfig mysqld on
↑

バックアップ/リストア†

  • データベースの実態のファイルをコピーすることでデータベースをバックアップ
  • データベースタイプで MyISAM を選んでいる場合のみ可能
↑

バックアップ†

  • データベース停止
    • /etc/init.d/mysqld stop
  • データベースファイルをコピー
    • cp -a /var/lib/mysql /home/BACKUP/
      • コピー元は my.cnf の datadir 参照
  • テータベース再開
    • /etc/init.d/mysqld start
↑

リストア†

  • データベース停止
    • /etc/init.d/mysqld stop
  • データベースファイルをコピー
    • cp -a /home/BACKUP/mysql /var/lib/
  • テータベース再開
    • /etc/init.d/mysqld start
↑

ユーザー作る†

  • 基本的には phpMyAdmin におまかせ
  • 全権限与える
    • mysql --user=root mysql
    • GRANT ALL ON *.* TO root@'%' WITH GRANT OPTION;
      • ALL で GRANT 除く全権限
      • root がユーザ名
      • % は「全ホスト」
      • WITH GRANT OPTION で GRANT 権限を付ける
↑

レプリケーション†

  • とりあえず手軽にバックアップサーバを作りたい。
    • レプリケーション
    • mysqldump
    • ZRM
  • ここではサーバ負荷は高いかもしれないけど、簡単にできるレプリケーション。
↑

マスタサーバ側†

  • vi /etc/mysql.cf 
    [mysqld]
log-bin
expire_logs_days=14
server-id=1
    • log-binでバイナリログを取得
    • expire_logs_days=14で14日以上前のログを自動削除
      • 当然だが、14日以上スレーブが追従できなくなったら、そのスレーブは置いてけぼりになる。
    • server-idでサーバIDを指定（他のサーバと同じ番号を使わないこと）
  • /etc/init.d/mysql restart
  • mysql --user=root -p
  • GRANT REPLICATION SLAVE ON *.* TO replication_s@192.168.0.2 IDENTIFIED BY 'password';
    • ユーザ'replication_s'をスレーブサーバ(192.168.0.2)からREPLICATION SLAVE権限でアクセスさせる。
  • flush tables with read lock;
    • テーブルをロック
  • show master status; 
    +-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 |    70214 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
    • FileとPositionの数値をメモ（バイナリログのズレ(?)をもとに同期させるらしい）
  • exit
  • cd /var/lib/mysql/
  • tar cvzf ~/mysql001.tar.gz DB1/ DB2/ ... DB3/
    • /var/lib/mysqlにあるDB名のテーブルを全てスレーブサーバにコピーするために圧縮。
  • mysql --user=root -p
  • unlock tables;
    • ロック解除
  • exit;
↑

slave側†

  • tarのDBディレクトリを/var/lib/mysqlに展開
    • 所有者がmysqlになっていることを確認
  • vi /etc/mysql.cf 
    [mysqld]
server-id=2
expire_logs_days=14
read_only
    • マスタと同様にIDをかぶらないようにserver-idを振る
    • read_onlyで手動更新を禁止する
  • mysql --user=root -p
  • CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='replication_s', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysqld-bin.000001', MASTER_LOG_POS=70214;
    • MASTER_HOST, MASTER_USER, MASTER_PASSWORDに、マスタサーバとレプリケーション用ユーザ,パスワードを指定
    • MASTER_LOG_FILEとMASTER_LOG_POSに、マスター側で調べたバイナリログファイルとポジションを指定
  • START SLAVE;
    • レプリケーション開始
↑

メンテナンス用メモ†

  • レプリケーション開始・停止
    • START SLAVE;
    • STOP SLAVE;
  • マスタサーバの状態を表示
    • SHOW MASTER STATUS;
  • スレーブサーバの状態を表示
    • SHOW SLAVE STATUS;
  • バイナリログを表示（マスタ側のみ有効）
    • SHOW BINLOG EVENTS;
      • すげぇ大量のバイナリログの中身をはく。
  • バイナリログを表示
    • mysqlbinlog -u xxx -p /home/mysql/mysqld-relay-bin.002512 --start-position=123456
      • 指定したバイナリログのポジション123456から表示する
↑

アクセス手段を設ける†

  • 当然ながらMySQLはコマンドラインからたたくものではないので^^;
↑

MySQLコネクタ(?)†

  • Linuxサーバ上にMySQLクライアントのモジュールを入れる
    • DBD::mysqlはそのクライアントモジュールを使って見に行くらしい。
  • インストール
    • yum install mysql.x86_64
      • mysql, perl-DBIとかインストールされる
↑

phpMyAdmin - MySQLフロントエンド†

  • phpMyAdmin
    • ブラウザ上からMySQLのメンテナンスを行う
    • phpMyAdminから検索とかエクスポートしてSQL文を作らせることもできる^^
  • phpモジュールのインストール
    • yum install php-mysql php-mcrypt
    • /etc/init.d/httpd restart
      • php 5.3系（CentOS 5.x系でphp53またはCentOS6.x系使用時）はphp-mcryptが無いのでphp-mcryptだけインストールしない。phpMyAdminでずっとエラーが出続けるが、仕方ない（汗 セッションcookieに関するモジュールなので、SQL操作自体に問題はないらしい。
  • phpMyAdminのダウンロード・展開
    • cd /home/hoge/public_html/tools
      • 展開するディレクトリはボット・アタック対策として、公開フォルダ直下ではなく、何かフォルダを作って置くこと。
      • もしくは.htaccessでアクセス制限をかける。
    • wget (sorceforgeのダウンロードアドレス)
    • tar zxvf phpMyAdmin-2.11.11-all-languages.tar.gz
    • rm phpMyAdmin-2.11.11-all-languages.tar.gz
    • cd phpMyAdmin-2.11.11-all-languages
    • cp config.sample.inc.php config.inc.php
    • vi config.inc.php 
      $cfg['blowfish_secret'] = 'NANIKA NAGAI TEKITOUNA MOJIWO IRERU'
  • アクセスする
  • やっておくべきこと（当たり前だけど）
    • phpMyAdminに簡単にアクセスできなくする。
      • phpMyAdminのディレクトリは何かのディレクトリ配下に置く。
      • もしくは.htaccessでアクセス制限をかける。
    • MySQLに簡単にアクセスできなくする
      • rootの特権を削りまくる（ rootはホストlocalhostからパスワード有りのみにする）
      • 一般ユーザはCGIのあるホストからのみパスワード有りで特権USAGE, INSERT, UPDATE, DELETEのみで権限委譲無しにする、または、データベースに対してのみALL PRIVILAGEするとか。
      • iptablesでtcp/3306をCGIのあるホストと127.0.0.1のみアクセス可能にする
↑

MySQL Connector/ODBC†

  • Windows の Access とかから MySQL にリンクをかける
  • MySQL Connector/ODBC 5.1 のバグ？
    • Access 2007 から ODBC 経由で MySQL にデータを取りに行くと #DELETED
    • どうも CHAR とか VARCHAR を主キーにしていると バグる？
    • 主キーをINTEGERにするとか主キーデータをASCIIのみすると直るっぽい？
↑

DBD::mysql†

  • PerlからSQLを利用する
    • DBIとかDBD::mysqlが必要
      • DBIは大抵デフォルトでインストールされている
      • DBD::mysqlがインストールされていないと Perl で次のようなエラーが出る。 
        install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: 
/usr/lib/perl5/5.8.5/i386-linux-thread-multi /usr/lib/perl5/5.8.5 /usr/lib/perl5
/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5 /usr/lib
/perl5/site_perl .) at (eval 2) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Pg, Proxy, Sponge.
 at (スクリプトパス) line 17
  • Perlから DBI::mysql を実行させる 
    # 接続

# DBI:DBMS名(MySQL,Oracle...):データベース名:ホスト名:ポート名
$dsn = "DBI:$DBI_dbtype:$DBI_dbname:$DBI_sqlname:$DBI_sqlport";
#$dsn = 'DBI:mysql:iptag:mysql.jamfunk.jp:3306';
$dbh = DBI->connect($dsn, $DBI_user, $DBI_password);
if(!$dbh) {
  &error("CANNOT CONNECT DB server");
}

# SQL投げ

# 文字列クォート
$strsql = $dbh->quote($strsql);
# SQL文
$strsql = "select * from tbl_ipseg where ip_seg rlike '192.168.';";
# SQL文実行
$sth = $dbh->prepare($strsql);
$sth->execute;
# カラム数取得
$fcnt = $sth->{NUM_OF_FIELDS};
# カラム名への配列リファレンス
$ref_field_name = $sth->{NAME};

# 返ってきた結果をHTMLで出力

# カラム名表示
print "<TABLE><TBODY>\n<TR>\n";
for($i = 0; $i < $fcnt; $i++) {
  print "<TH>$$ref_field_name[$i]</TH>";
}
print "\n</TR>\n";
# データ表示
while(@rows = $sth->fetchrow_array) {
  print "<TR>\n";
  foreach(@rows) {
    print "<TD>$_</TD>";
  }
  print "</TR>\n";
}
print "</TBODY></TABLE>\n";
↑

Tips†

↑

暴走したSQLを止める†

  • プロセスリストの表示
    • mysql -u （ユーザ名） -p でMySQLのコンソールにログイン
    • show processlist;
  • プロセスを停止する
    • kill （プロセス番号）;
↑

アップグレード†

  • MySQLやMariaDBのバージョンを上げたり、サーバ移行をしたときに。
    • 基本的には/var/lib/mysql/を移行すれば良いが、時折mysqlデータベースの仕様が変更になるらしい。 
      mysql_upgrade -f -p