はじめに
仕事でMariaDBを扱っているのですが、本屋でMySQLまわりもパラパラと見ていると、この本が詳しそうだったので書いました。
詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)
- 作者: 奥野幹也
- 出版社/メーカー: 翔泳社
- 発売日: 2016/08/26
- メディア: 単行本（ソフトカバー）
- この商品を含むブログを見る
まず第1章のMySQLの概要、第2章のレプリケーションを読みました。本書はMySQL5.7の膨大な新機能をセクションごとに解説する本ですが、MySQL自体初学の人間(私とか)でも非常に詳しい説明がなされていて、この本を最初に見ればよかった、という思いです。
今回、第2章で扱われているレプリケーションについて、MySQL5.7の導入とともに実践してみます。
MySQL5.7のinstall
CloudGarageさんお世話になります。やっぱりレプリケーションもできる、3台貸し出しは本当に助かる。
installは以下の記事通り、CentOS 7.3に対して行いましたので省略。
# yum remove mariadb-libs # rm -rf /var/lib/mysql/ # yum localinstall http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm # yum -y install mysql-community-server # systemctl enable mysqld.service # systemctl start mysqld.service
設定
初期パスワードがログに出ます。
[root@mysql57-2 ~]# grep "temporary password" /var/log/mysqld.log 2017-11-08T11:49:27.602209Z 1 [Note] A temporary password is generated for root@localhost: By30kodp0g/v
ログイン後に、パスワードをrootに変更したいですが、制限が厳しいのでそれを緩和させてやります。
以下の記事が参考になりました。
[root@mysql57-2 ~]# mysql -uroot -pBy30kodp0g/v
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set password for root@localhost=password('passwordPASSWORD@999');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SET GLOBAL validate_password_length=4;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)
mysql> set password for root@localhost=password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)
masterノードの設定
bin_log(バイナリログ)の有効化と、replicationのためのserver_idを設定してやります。serveri_idはmasterとslaveで異なる値を設定しましょう。
[root@mysql57 ~]# tail -n7 /etc/my.cnf # replication server-id=1 log-bin=mysql-bin # pasword policy validate_password_length=4 validate_password_policy=LOW
ちなみにバイナリログとは、データベース上のすべてのテーブル変更の記録です。show binary logsコマンドで一覧できます。
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 1181 | +------------------+-----------+ 2 rows in set (0.00 sec)
このバイナリログをSQLのレベルでレプリケーションに使用する方式をStatementBasedReplication(SBR)と呼びます。(statement=SQL)しかし、この方法だとSQLの種類によってデータベースの変更結果がmasterとslaveで異なる場合がある問題があります。(UUID関数など)
その問題に対処するのが行ベースレプリケーション(RowBasedReplication, RBR)です。SQLの実行結果、変更が生じた行の前後を記録する方式なので、SQL文で結果が異なっても大丈夫です。
次にslaveが接続するよためのuserを作成します。
ログが流れてしまいましたが、
> create user 'repl' identified by 'repl'; > grant replication slave on *.* to 'repl';
でいけると思います。以下を参考にしました。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.1.3 レプリケーション用ユーザーの作成
DBのdump
面倒なことに、slaveに対してはmasterのdbのdumpを流し込んで、同じレベルにしなければなりません。
slaveがmasterに接続する場合、現在のbinary_log_fileの名前と、log_positionという値が必要であるため、その情報が出力される形式でdumpを取る必要があります。
[root@mysql57 ~]# mysqldump -uroot -proot --master-data=2 -A >mysql.dump mysqldump: [Warning] Using a password on the command line interface can be insecure. [root@mysql57 ~]#
CLIでpassword打つと怒られちゃいますね。このdumpをscpかなんかでslaveに送って、restoreしてください。
slaveの設定
masterと同じでいいです。configでserver_idだけは異なるものにしてください。
レプリケーションの設定です。
mysql> change master to
-> master_host='192.168.0.11',
-> master_port=3306,
-> master_log_file='mysql-bin.000002',
-> master_log_pos=990,
-> master_heartbeat_period=60;
Query OK, 0 rows affected (0.01 sec)
おっと、ここのlog_fileとlog_posはdbのdumpから確認してください。
[root@mysql57-2 ~]# grep -e '-- CHANGE MASTER TO MASTER_LOG_FILE' dump.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=990;
replicationをはじめるにはslaveでstart slave statementを実行します。結果は、Slave_IO_Runningがyesであることを確認しましょう。
mysql> start slave user = 'repl' password = 'repl'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show slave status; +----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | +----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+ | Waiting for master to send event | 192.168.0.11 | repl | 3306 | 60 | mysql-bin.000002 | 990 | mysql57-2-relay-bin.000003 | 320 | mysql-bin.000002 | Yes | Yes | | | | | | | 0 | | 0 | 990 | 531 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 1 | ca6806d3-c473-11e7-9e52-fa163e4c44c2 | /var/lib/mysql/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | | | 0 | | | | +----------------------------------+--------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+ 1 row in set (0.00 sec)
masterで変更を加えた結果が、slaveでも反映されていれば成功です。
master側ではこのようなログがでていました。
2017-11-08T12:10:16.099322Z 12 [Note] Start binlog_dump to master_thread_id(12) slave_server(2), pos(mysql-bin.000002, 990)
slave
2017-11-08T12:09:34.187408Z 2 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'mysql-bin.000002' at position 990, relay log './mysql57-2-relay-bin.000001' position: 4 2017-11-08T12:09:34.188654Z 0 [Note] End of list of non-natively partitioned tables 2017-11-08T12:10:16.096654Z 6 [Note] Slave I/O thread for channel '': connected to master 'repl@192.168.0.11:3306',replication started in log 'mysql-bin.000002' at position 990
slave側ではslave IO threadがmaster threadからのbinary logを受け取り、relay logに書き込みます。それをslave SQL threadがストレージエンジンに書き込むんですね。
おわりに
そこそこ簡単にMySQLのレプリケーションが実現できましたが、この感じだとデータに差分がある状態ではレプリケーションができないのかな？と思いました。つまり、稼働中のクラスタに対して、稼働したまま新規slaveを追加するにはどうしたらいいのか、それともできないのか、疑問に思いました。
