MySQLレプリケーション

MySQLのレプリケーション設定メモ。

環境

  • FreeBSD 10.3
  • MySQL 5.7.13
  • マスターサーバ： master.domain.tld
  • スレーブサーバ： slave.domain.tld

Qiita「MySQL入門　レプリケーション編」を参考に、GTIDを使った非同期式で設定することに。

設定

  1. マスターサーバの /usr/local/etc/mysql/my.cnf を、次のように編集。 
    [client]
port = 3306
socket = /tmp/mysql.sock

[mysql]
prompt = \u@\h [\d]>\_
no_auto_rehash
default-character-set = utf8mb4

[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
#bind-address = 127.0.0.1
basedir = /usr/local
datadir = /usr/local/spool/mysql
tmpdir = /var/db/mysql_tmpdir
slave-load-tmpdir = /var/db/mysql_tmpdir
secure-file-priv = /var/db/mysql_secure
log-bin = mysql-bin
log-output = TABLE
log-slave-updates = 1
master-info-repository = TABLE
relay-log-info-repository = TABLE
relay-log-recovery = 1
slow-query-log = 1
server-id = 1
sync_binlog = 1
sync_relay_log = 1
binlog_cache_size = 16M
expire_logs_days = 30
default_password_lifetime = 0
enforce-gtid-consistency = 1
gtid-mode = ON
safe-user-create = 1
lower_case_table_names = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options = BACKUP,FORCE
open_files_limit = 32768
table_open_cache = 16384
table_definition_cache = 8192
net_retry_count = 16384
key_buffer_size = 256M
max_allowed_packet = 64M
query_cache_type = 0
query_cache_size = 0
long_query_time = 0.5
innodb_buffer_pool_size = 1G
innodb_data_home_dir = /usr/local/spool/mysql
innodb_log_group_home_dir = /usr/local/spool/mysql
innodb_data_file_path = ibdata1:128M:autoextend
innodb_temp_data_file_path = ibtmp1:128M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_autoinc_lock_mode = 2
skip-symbolic-links

character-set-server = utf8mb4

[mysqldump]
max_allowed_packet = 256M
quote_names
quick
  2. マスターサーバを再起動。 
    # sudo service mysql-server restart
  3. スレーブサーバの /usr/local/etc/mysql/my.cnf を、次のように編集。 
    [client]
port = 3306
socket = /tmp/mysql.sock

[mysql]
prompt = \u@\h [\d]>\_
no_auto_rehash
default-character-set = utf8mb4

[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
bind-address = 127.0.0.1
basedir = /usr/local
datadir = /usr/local/spool/mysql
tmpdir = /var/db/mysql_tmpdir
slave-load-tmpdir = /var/db/mysql_tmpdir
secure-file-priv = /var/db/mysql_secure
log-bin = mysql-bin
log-output = TABLE
log-slave-updates = 1
master-info-repository = TABLE
relay-log-info-repository = TABLE
relay-log-recovery = 1
slow-query-log = 1
server-id = 2
sync_binlog = 1
sync_relay_log = 1
binlog_cache_size = 16M
expire_logs_days = 30
default_password_lifetime = 0
enforce-gtid-consistency = 1
gtid-mode = ON
safe-user-create = 1
lower_case_table_names = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options = BACKUP,FORCE
open_files_limit = 32768
table_open_cache = 16384
table_definition_cache = 8192
net_retry_count = 16384
key_buffer_size = 256M
max_allowed_packet = 64M
query_cache_type = 0
query_cache_size = 0
long_query_time = 0.5
innodb_buffer_pool_size = 1G
innodb_data_home_dir = /usr/local/spool/mysql
innodb_log_group_home_dir = /usr/local/spool/mysql
innodb_data_file_path = ibdata1:128M:autoextend
innodb_temp_data_file_path = ibtmp1:128M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_autoinc_lock_mode = 2
skip-symbolic-links

character-set-server = utf8mb4

[mysqldump]
max_allowed_packet = 256M
quote_names
quick
  4. スレーブサーバを再起動。 
    # sudo service mysql-server restart
  5. スレーブサーバの datadir 配下の auto.cnf を削除。
  6. マスターサーバにレプリケーション用ユーザを追加。REPLICATION SLAVE 権限を付与してユーザを作成する。 
    # mysql -u root -p
Enter password:
mysql> create user 'replicator'@'%.domain.tld' identified by 'SomePassword';
Query OK, 0 rows affected (0.05 sec)

mysql> grant replication slave on *.* to 'replicator'@'%.domain.tld';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all privileges on client_data.* to 'replicator'@'%.domain.tld';
Query OK, 0 rows affected (0.04 sec)

mysql> quit
Bye
#
  7. スレーブサーバ側からマスターサーバのMySQLに接続できることを確認。 
    # mysql -h master.domain.tld -u replicator -p
Enter password:

mysql> quit;
#

    次のようなエラーが出る場合には、マスター側で接続許可の設定がうまくできていない。

    ERROR 2003 (HY000): Can't connect to MySQL server on 'master.domain.tld' (61)
  8. マスターサーバでコールドバックアップを取る。Warning回避のため、–triggers, –routines, –events を指定する。 
    # mysqldump -u root -p -x --all-databases --triggers --routines --events > backup.sql
Enter password:
#
  9. スレーブサーバにバックアップをリストアする。 
    # mysql -u root -p < backup.sql

    もし @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. というエラーが出たら、reset master してから再度リストア。

    # mysql -u root -p
Enter password:
mysql> reset master;

Query OK, 0 rows affected (0.04 sec)
mysql> quit
#
  10. スレーブサーバでレプリケーションを開始し、レプリケーションが正常に開始されたことを確認。 
    # mysql -u root -p
Enter password:
mysql> change master to master_host='master.domain.tld',
-> master_user='replicator',
-> master_password='SomePassword',
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.15 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
(略)
1 row in set (0.00 sec)
mysql> quit
#

    show slave status で Slave_IO_State が「Connecting to master」以外なら正常。

以上で設定完了。のはず。

参考

タイトルとURLをコピーしました