maehachi08 Anything Blog

2012年07月27日
mysqld_multiで複数mysqlスレーブサーバを起動させる

MySQLスレーブサーバの導入

複数のMySQLサーバが稼動するシステムにスレーブサーバを導入して、対障害性を高めようと思います。
MySQLサーバのデータベースバックアップは日時で実施しているが、サーバがブッ飛んだ時には前日までしか戻せないと影響が大きい。スレーブサーバを構築し、そこにレプリケーションさせておけば、アプリ改修で、そっちに向けることも出来るし、レプリケーションを切断し、整合性を取った上でバックアップを取って、マスターサーバ再構築時にリストアすることも可能になる。
しかし、MySQLサーバ分だけサーバを用意するのはコストが掛かるので、1台の物理サーバで複数のスレーブサーバを構成したいと思う。
1つのmysqldで1スレーブサーバを構成することが出来ます。複数のスレーブサーバを構成するためには複数にmysqldを起動させる必要があります。

mysqld_multiで複数のmysqldを起動

1つのOSで複数のmysqldを起動させる方法は、異なる TCP/IP ポートとソケットファイルを使用するようにしてサーバをコンパイルし、それぞれが別のネットワークインタフェースで接続するようにすることです。しかし、mysqld_multi(複数のMySQLサーバを管理するプログラム)を使用することでもっと簡単に複数のmysqldを起動させることが出来ます。

mysqld_multiとは

mysqld_multiは複数のmysqldプロセス管理を目的とするperlプログラムです。
別々のTCP/IPポートやUNIXソケットから接続を処理することで複数のmysqldプロセスを起動/停止することが出来ます。
mysqld_multiを使用する時にmy.cnfに必要な設定について記載します。

  • [mysqld_multi]グループ
  • [mysqld#]グループ(#には1,2..などの数字が入る)
  • [mysqld]グループ

[mysqld_multi]グループはmysqld_multiが参照する設定を定義します。

log = /path/to/logfile

⇒ mysqld_multiの動記録する設定です。

mysqld = /path/to/mysqld

⇒ 使用するmysqldバイナリファイルを指定する設定です。このオプションにmysqld_safeを値として指定することもできます。

mysqladmin = /path/to/mysqladmin

⇒ サーバ停止(シャットダウン)に使用するmysqladminバイナリファイルを指定する設定です。

[mysqld#]グループは各mysqldプロセスで一意な設定を定義します。

port = 3306

⇒ mysqldが接続を待ち受けるTCP/IPポート番号を設定します。

datadir = /var/lib/mysql1

⇒ MySQLデータディレクトリを設定します。

pid-file = /var/lib/mysql1/mysql.pid

⇒ サーバがプロセス ID を書き込むファイルのパス名を設定します。

socket = /var/lib/mysql1/mysql.sock

⇒ mysqldのUnixソケットファイルパスを設定します。

log-error = /var/log/mysql/error1.log

⇒ mysqld の起動時刻と停止時刻、および実行中に発生したエラーを記録するログファイルを設定します。

general_log_file = /var/log/mysql/query1.log

⇒ 一般クエリログのファイル名を設定します。

[mysqld#]グループで記述できるオプションについては同じマシン上での複数 MySQL サーバの実行を確認してください。

[mysqld]グループは各mysqldプロセス共通の設定を定義します。

マスターサーバ構築

マスターサーバを仮想マシンで2台用意します。
mysqlサーバに必要なパッケージをyumでインストールします。

# yum -y install mysql mysql-devel mysql-server

# cp -p /usr/share/mysql/my-medium.cnf /etc/my.cnf

# service mysqld start

# chkconfig mysqld on

レプリケーションを行う場合にマスターサーバに必要な設定を行います。

# vim /etc/my.cnf
@mysql01
log-bin = /var/lib/mysql/binary-log
binlog_format=mixed
server-id       = 1

@mysql02
log-bin = /var/lib/mysql/binary-log
binlog_format=mixed
server-id       = 2

@全mysqlマスターサーバ
# service mysqld restart

スレーブサーバからマスターサーバに接続するためのユーザをマスターサーバに作成する必要があります。管理上、セキュリティ上はレプリケーション用ユーザには最低限の権限を付与すべきと思います。ちなみに、レプリケーションを行うユーザに必要な権限はREPLICATION SLAVE権限だけです。

@全mysqlマスターサーバ
# mysql -u root

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@<スレーブサーバのIPアドレス> IDENTIFIED BY 'password';
mysql> commit;

テスト環境なので、簡単にテストデータベースを作成します。

@mysql01
# mysql -u root
mysql> create database pachi1;
mysql> commit;
mysql> exit

@mysql02
# mysql -u root
mysql> create database pachi2;
mysql> commit;
mysql> exit

これで2台のmysqlサーバが稼動しました。

マスターサーバのデータのバックアップを取得

MySQLレプリケーションは、レプリケーション開始時点までのデータをスレーブサーバのデータベースに適用しておく必要があります。バイナリログにはポジション情報があり、その位置からでないとレプリケーションされません。マスターサーバとスレーブサーバの双方で同じレプリケーション開始位置情報を持っている必要があります。

マスターサーバで書き込み禁止モードで整合性を取り、ポジション情報を確認しておきます。
そして、mysqldumpコマンドでバックアップを取得します。

# mysql -u root
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| binary-log.000002 |      483 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

# mysqldump -u root --all-databases --lock-all-tables > dbdump_`hostname -s`_`date +%Y%m%d`.sql

# ls -l dbdump_`hostname -s`_`date +%Y%m%d`.sql
-rw-r--r-- 1 root root 500863  7月 27 01:15 2012 dbdump_mysql01_20120727.sql

# rsync -av dbdump_`hostname -s`_`date +%Y%m%d`.sql <スレーブサーバのIPアドレス>:/tmp/dbdump_`hostname -s`_`date +%Y%m%d`.sql

# mysql -u root
mysql> UNLOCK TABLES;

スレーブサーバ構築

mysqlサーバに必要なパッケージをyumでインストールします。

# yum -y install mysql mysql-devel mysql-server

# cp -p /usr/share/mysql/my-medium.cnf /etc/my.cnf

/etc/my.cnfを以下のように修正しました。

[client]
#password       = your_password
default-character-set = utf8

# mysqld_multi setting
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log = /var/log/mysql/multi.log

# The MySQL server
[mysqld]
character-set-server = utf8
default-storage-engine = INNODB
log-warnings
general_log
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# mysqld1
[mysqld1]
port = 3306
datadir = /var/lib/mysql1
pid-file = /var/lib/mysql1/mysql.pid
socket = /var/lib/mysql1/mysql.sock
log-error = /var/log/mysql/error1.log
general_log_file = /var/log/mysql/query1.log
server-id = 10001
master-host = 192.168.100.120
master-user = repl
master-password = password
master-port = 3306


# mysql2
[mysqld2]
port = 3307
datadir = /var/lib/mysql2
pid-file = /var/lib/mysql2/mysql.pid
socket = /var/lib/mysql2/mysql.sock
log-error = /var/log/mysql/error2.log
general_log_file = /var/log/mysql/query2.log
server-id = 10002
master-host = 192.168.100.130
master-user = repl
master-password = password
master-port = 3306


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

データベースを初期化します。これしないと動作しません。

# mysql_install_db --datadir=/var/lib/mysql1 --user=mysql

# mysql_install_db --datadir=/var/lib/mysql2 --user=mysql

# chown -R mysql:mysql /var/lib/mysql{1,2}

mysqld_multiコマンドでmysqldを起動します。

# mysqld_multi start

プロセスが起動しているか確認します。

# ps axuf | grep mysqld_safe
root     13527  0.0  0.1 107516   924 pts/0    S+   00:51   0:00          \_ grep mysqld_safe
root     13179  0.0  0.2 106152  1376 pts/0    S    00:47   0:00 /bin/sh /usr/bin/mysqld_safe --port=3306 --datadir=/var/lib/mysql1 --pid-file=/var/lib/mysql1/mysql.pid --socket=/var/lib/mysql1/mysql.sock --log-error=/var/log/mysql/error1.log --general_log_file=/var/log/mysql/query1.log --server-id=10001 --master-host=192.168.100.120 --master-user=repl --master-password=password --master-port=3306
root     13185  0.0  0.2 106152  1372 pts/0    S    00:47   0:00 /bin/sh /usr/bin/mysqld_safe --port=3307 --datadir=/var/lib/mysql2 --pid-file=/var/lib/mysql2/mysql.pid --socket=/var/lib/mysql2/mysql.sock --log-error=/var/log/mysql/error2.log --general_log_file=/var/log/mysql/query2.log --server-id=10002 --master-host=192.168.100.130 --master-user=repl --master-password=password --master-port=3306

mysqld_multiコマンドでステータスを確認します。
以下のようにmy.cnfで定義した数だけ"running"と表示されれば正常に起動しています。

# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

マスターサーバのバックアップデータからデータベースを構築

マスターサーバからrsyncで転送しておいたdumpデータからデータベースを構築します。
mysqld_multiを使用している場合は、mysqlコマンドに--socketオプションを指定してsqlファイルを読み込ませます。

# mysql -u root --socket=/var/lib/mysql1/mysql.sock < /tmp/dbdump_mysql01_20120727.sql

# mysql -u root --socket=/var/lib/mysql2/mysql.sock < /tmp/dbdump_mysql02_20120727.sql

レプリケーション情報のセット

スレーブサーバでスレーブサーバを開始させるための情報をセットします。
セット出来たらスレーブをスタートさせ、ステータスを確認します。

# mysql -u root --socket=/var/lib/mysql1/mysql.sock
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.100.120',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='password',
    -> MASTER_LOG_FILE='binary-log.000002',
    -> MASTER_LOG_POS=483;
Query OK, 0 rows affected (0.01 sec)

スレーブをスタートします。

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

レプリケーションステータスを確認します。

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.120
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binary-log.000002
          Read_Master_Log_Pos: 1088
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 857
        Relay_Master_Log_File: binary-log.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1088
              Relay_Log_Space: 1012
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

これで、レプリケーションが確立できました!
試しに、マスターサーバ側でデータベースやテーブルを作成してみましょう。
スレーブサーバにも反映されるはずです。