mysql

開発環境用にMySQL5.6を複数インスタンス起動させる(mysqld_multi)

テーブル分散などでマスター1、マスター2にそれぞれスレーブが必要な場合、本番はいいけど、開発環境までサーバーをいくつも作るのはちょっとなので、1つのサーバーにmysqld_multiを使ってMySQLを複数立ち上げます。

CentOS6.4のMySQL5.6でやります。
MySQLはOracleからrpmをWgetしたもをインストール済みで、mysql_secure_installationで最低限の設定はしてある状態。

[code language=”bash”]

mkdir -p /var/lib/mysql_multi/mysqld{1,2,3,4}
mysql_install_db –datadir=/var/lib/mysql_multi/mysqld1 –user=mysql
chown -R mysql. /var/lib/mysql_multi/

[/code]

各データディレクトリを作成して、mysql_install_dbでセットアップ

[code language=”bash”]

mysql_install_db –datadir=/var/lib/mysql_multi/mysqld1 –user=mysql
mysql_install_db –datadir=/var/lib/mysql_multi/mysqld2 –user=mysql
mysql_install_db –datadir=/var/lib/mysql_multi/mysqld3 –user=mysql
mysql_install_db –datadir=/var/lib/mysql_multi/mysqld4 –user=mysql

[/code]

my.confに[mysqld_multi]と各インスタンスの設定を行う。
mysqldはmysqld_safeを指定して、[mysqld_multi]のuser、passはmysqladminの情報
あとでmysqladminのパスワードなどはセットアップします。

[code language=”bash”]
[root@so01b ~]#vi /etc/my.conf

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /var/log/mysql/multi.log
user = root
password = 2aoSEsRs4SSp

[mysqld1]
server-id = 101
port = 33061
basedir = /var/lib/mysql
datadir = /var/lib/mysql_multi/mysqld1/
socket = /var/lib/mysql_multi/mysqld1/mysql.sock
pid-file = /var/lib/mysql_multi/mysqld1/mysql.pid
log-error = /var/log/mysql/mysqld1_error.log
mysqld = mysqld_safe

[mysqld2]
server-id = 102
port = 33062
basedir = /var/lib/mysql
datadir = /var/lib/mysql_multi/mysqld2/
socket = /var/lib/mysql_multi/mysqld2/mysql.sock
pid-file = /var/lib/mysql_multi/mysqld2/mysql.pid
log-error = /var/log/mysql/mysqld2_error.log
mysqld = mysqld_safe

[mysqld3]

[mysqld4]

[/code]

これで大体完了、mysqld_multiで状態を確認する。

分かりにくいですが、「is not running」です。

[code language=”bash”]
[root@so01b~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running
[/code]

mysqld_multiで起動

[code language=”bash”]
[root@so01b~]# mysqld_multi start
[root@so01b~]#
[root@so01b~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
[/code]

mysqld_multiで起動した、ポート別のMySQLのmysqladminのパスワード変更
インスタンス全部に適応すると、mysqld_multiでstopができるようになります。

[code language=”bash”]
[root@so01b~]# mysqladmin password 2aoSEsRs4SSp -S/var/lib/mysql_multi/mysqld4/mysql.sock -u root -p
[/code]

mysqld_multiで停止

止まらない場合、mysqladminのパスワードが違ったりして、インスタンスをmysql_multiが停止できてません。
また、mysqld_multiは標準出力にエラーは出しませんし、OKとも言いません。

[code language=”bash”]
[root@so01b~]# mysqld_multi stop
[root@so01b~]#
[root@so01b~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is not running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is not running
[/code]

mysqld_multiで起動した、ポート別のMySQLへ接続

[code language=”bash”]
[root@so01b~]# mysql -u root -p –socket=/var/lib/mysql_multi/mysqld1/mysql.sock
[/code]

CentOS release 6.3にMySQL Server5.6をrpmでインストール

CentOS release 6.3にMySQL Server5.6をrpmでインストールメモ


HandlerSocket-Plugin-for-MySQLも使いたいとの要望もあったので、HandlerSocket-Plugin-for-MySQLもインストールしてみます。
MySQL5.6だからmemcached pluginを使えばいいのに。。。



[code language=”bash”]
yum install http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQL-server-5.6.11-2.el6.x86_64.rpm \
http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQL-client-5.6.11-2.el6.x86_64.rpm \
http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQL-shared-compat-5.6.11-2.el6.x86_64.rpm \
http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQL-devel-5.6.11-2.el6.x86_64.rpm
[/code]



[code language=”bash”]
==================================================================================================
Package Arch Version Repository Size
==================================================================================================
Installing:
MySQL-client x86_64 5.6.11-2.el6 /MySQL-client-5.6.11-2.el6.x86_64 81 M
MySQL-devel x86_64 5.6.11-2.el6 /MySQL-devel-5.6.11-2.el6.x86_64 19 M
MySQL-server x86_64 5.6.11-2.el6 /MySQL-server-5.6.11-2.el6.x86_64 231 M
MySQL-shared-compat x86_64 5.6.11-2.el6 /MySQL-shared-compat-5.6.11-2.el6.x86_64 11 M
replacing mysql-libs.x86_64 5.1.67-1.el6_3
replacing mysql-libs.x86_64 5.1.67-1.el6_3

Transaction Summary
==================================================================================================
Install 4 Package(s)

Total size: 343 M
Is this ok [y/N]:
[/code]

Innodbプラグイン

[code language=”bash”]
mysql> select @@innodb_version;
+——————+
| @@innodb_version |
+——————+
| 5.6.11 |
+——————+
1 row in set (0.00 sec)

mysql>
[/code]

MySQL5.6がrpmでインストール完了しました。
下記にエラッた(自分の知識不足)を記載

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

MySQL5.6からはrootのデフォルトパスワードが設定されるようになりました。

[code language=”bash”]
[root@hogehoge ~]# cat /root/.mysql_secret
# The random password set for the root user at Wed May 29 01:22:53 2013 (local time): DYdSZgeI
[/code]


MySQL5.6でmy.cnfの読み込み順序

MySQL5.6のヘルプに書いて有りました。

[code language=”bash”]
[root@hogehoge ~]# mysql –help | less
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
–print-defaults Print the program argument list and exit.
[/code]

MySQL5.6にアップデートしたら起動しない。。

my.cnfに廃止になったオプションがるよ、きっと。

[code language=”bash”]
default-character-set = utf8
[/code]


ERROR 1126 (HY000): Can’t open shared library ‘/usr/lib64/mysql/plugin/handlersocket.so’

MySQL5.6にhandlersocketインストール失敗。。解決できませんでした。

[code language=”bash”]
mysql> mysql> install plugin handlersocket soname ‘handlersocket.so’;

ERROR 1126 (HY000): Can’t open shared library ‘/usr/lib64/mysql/plugin/handlersocket.so’ (errno: 13 /usr/lib64/mysql/plugin/handlersocket.so: undefined symbol: _ZN4Item8get_dateEP13st_mysql_timej)
mysql>
[/code]


mysqlのリカバリーでmysqlbinlog: unknown variable ‘default-character-set=utf8’

mysqlがクラッシュして、binlogを確認しようとしたら

[root@cl.051.db ~]# mysqlbinlog /var/mysqld/mybin-log.000121
mysqlbinlog: unknown variable 'default-character-set=utf8'
[root@cl.051.db ~]# 

my.cnfの[client]にdefault-character-setが指定されていることが原因のようなので下記のコマンドにしたら、OKだった。

[root@cl.051.db ~]#mysqlbinlog --no-defaults /var/mysqld/mybin-log.000121

MySQLのパスワード忘れ

本番稼動しているサーバならともかく、テストで色々やっていると
MySQLのrootのパスワードを忘れてしまったことがあります。

対策はこれをつけて、MySQLの再起動を行うと、パスワード認証なしでアクセスできます。

vi /etc/my.conf
追加→skip-grant-tables

mysql_safeの起動オプションでも指定できます。
その場合は「–skip-grant-tables」で実行します。

起動後、MySQLへログインして、パスワードのリセットを行いましょう。

SET PASSWORD FOR ユーザ@"ホスト名"=password('パスワード');

リセット後は「skip-grant-tables」は危ないので、必ず消してください。

MySQLのバイナリログへの書き込み

バックアップとしてバイナリログのことを書いたが
動きのあるサイトのバイナリログを見てたら、どうやら書き込みが遅い?というかリアルタイムではない。

環境にもよるのかわかりませんが、ある程度溜まってから書き込まれます。

答えはmy.confへの設定

sync_binlog=1

これでコミットがリアルタイムに反映されるようになった。

いろいろな資料を見ていると
InnoDBの場合はデータファイルへの書き込みやInnoDBログファイルの方が
IOは多いらしいので、書き込みドライブを変える等すれば性能低下はすくなく、対障害性をあげられそう。

MySQLのバックアップ

サーバ構築するたびに思い出しがてら調べるのが面倒なので、メモ

一番簡単?なコールドバックアップから。
インスタンスを止めた上体から全バックアップする。

MySQLのdatadirは「/data/mysql」の仮定ですすめます。

まずは、停止

①/etc/rc.d/init.d/mysqld stop
②mysqladmin shutdown --user=root --password=pass123 --soket=/data/mysql/mysql.sock
③kill 'cat /data/mysql/mysql.sock'

止まったか確認

ps -ef | grep mysqld grep -v grep

ソケットの場所などは環境に合わせてください。

止まっていれば

cp -rp /data/mysql /home/backup/mysql-backup

この場合は、ドライブの異なるhomeへとりました。
外部デバイスなどに逃がすか、とった後別のサーバへ転送したほうがいいです。

これで一式を退避させることができるので、戻す場合は

rm -rf /data/mysql
cp -rp /home/backup/mysql-backup /data/mysql 

でもこの方法だと、止めなきゃいけない+最新には戻せない(かも)なので
タイミングにもよるけど、運用には耐えられない。
なので、1週間に1回日曜にフルでとって、それ以外トランザクションログを使ってできる限り最新状態に近づける。

トランザクションファイルはmy.confに設定を追加する。
サーバ構築するたびに思い出しがてら調べるのが面倒なので、メモ

一番簡単?なコールドバックアップから。
インスタンスを止めた上体から全バックアップする。

mysqlのdatadirは「/data/mysql」の仮定ですすめます。

まずは、停止

①/etc/rc.d/init.d/mysqld stop
②mysqladmin shutdown --user=root --password=pass123 --soket=/data/mysql/mysql.sock
③kill 'cat /data/mysql/mysql.sock'

止まったか確認

ps -ef | grep mysqld grep -v grep

ソケットの場所などは環境に合わせてください。

mkdir /data/mysql/bin_log
chown mysql:mysql /data/mysql/bin_log
vi my.conf
追加 → log-bin=/data/mysql/bin_log/mysqllog

設定が終わったら、mysqlを再起動。
これで、/data/mysql/bin_log/に「mysqllog.000001」というログができます。

この中にはトランザクションの情報が記載されています。
バイナリ形式なので、見たい場合は

mysqlbinlog --disable-log-bin /data/mysql/bin_log/mysqllog.000001 > /tmp/mysqllog.000001.sql 

でSQL形式に変換できます。

ログはどんどん書き込まれるので、スイッチする場合は

mysqladmin flush-log --user=root --password=pass123 --soket=/data/mysql/mysql.sock

これで、連番の別ファイルが作成されてそっちに書き込まれるようになります。

2つのバイナリを反映させる場合は

mysqlbinlog --disable-log-bin /data/mysql/bin_log/mysqllog.000001 /data/mysql/bin_log/mysqllog.000002 > /tmp/mysqllog.sql 
mysql --user=root --password=pass123 --soket=/data/mysql/mysql.sock < /tmp/mysqllog.sql 

ってな感じです。