データベースサーバ 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にコピーして使う。
  • 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