#author("2018-05-26T19:23:03+09:00","default:okkun-lab","okkun-lab")
#author("2019-07-26T08:47:44+09:00","default:okkun-lab","okkun-lab")
*データベースサーバ MySQL サーバ [#xe75ef2e]
-written by [[member/すながわひろゆき]]

----

#contents

**ライセンス [#qb9ff98e]

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

**MariaDB [#je370c5f]

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

*サーバセットアップ [#h2f7c70b]

-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

*バックアップ/リストア [#bffdc24d]

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

**バックアップ [#u594b773]
-データベース停止
--/etc/init.d/mysqld stop
-データベースファイルをコピー
--cp -a /var/lib/mysql /home/BACKUP/
---コピー元は my.cnf の datadir 参照
-テータベース再開
--/etc/init.d/mysqld start

**リストア [#b3996562]
-データベース停止
--/etc/init.d/mysqld stop
-データベースファイルをコピー
--cp -a /home/BACKUP/mysql /var/lib/
-テータベース再開
--/etc/init.d/mysqld start

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


*レプリケーション [#m4d87bb0]

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

**マスタサーバ側 [#a963efd4]
-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側 [#hf687386]

-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;
--レプリケーション開始

**メンテナンス用メモ [#h3d2a104]
-レプリケーション開始・停止
--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 レプリケーションの設定 - とみぞーノート
--http://wiki.bit-hive.com/tomizoo/pg/MySQL%20%A5%EC%A5%D7%A5%EA%A5%B1%A1%BC%A5%B7%A5%E7%A5%F3%A4%CE%C0%DF%C4%EA



*アクセス手段を設ける [#a240a3ab]

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

**MySQLコネクタ(?) [#ye3a6a0e]

-Linuxサーバ上にMySQLクライアントのモジュールを入れる
--DBD::mysqlはそのクライアントモジュールを使って見に行くらしい。

-インストール
--yum install mysql.x86_64
---mysql, perl-DBIとかインストールされる


**phpMyAdmin - MySQLフロントエンド [#nd8ae7c1]

-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のダウンロードアドレス)
---phpMyAdmin http://www.phpmyadmin.net/home_page/
--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'
-アクセスする
--http://server/tools/phpMyAdmin-2.11.11-all-languages
-やっておくべきこと(当たり前だけど)
--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 [#k2eb0e8d]

-Windows の Access とかから MySQL にリンクをかける

-ODBC本体(Ver.5.1.7)
--http://dev.mysql.com/downloads/connector/odbc/

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

**DBD::mysql [#d60c1cf3]

-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

-CPANからモジュールをダウンロード
--wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.005.tar.gz
---CPANのこのページ http://search.cpan.org/dist/DBD-mysql/
--tar zxvf DBD-mysql-4.005.tar.gz
--cd DVD-mysql-4.005
--perl Makefile.PL
--make
--make install
-yumでインストール
--yum install perl-DBD-MySQL

-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 [#t84b5c05]

**暴走したSQLを止める [#g2d884c3]
-プロセスリストの表示
--mysql -u (ユーザ名) -p でMySQLのコンソールにログイン
--show processlist;
-プロセスを停止する
--kill (プロセス番号);

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