MYSQLレプリケーション動作レビュー
MYSQL5.5.29 GAをダウンロードして使用。

マスター  server-id = 1
スレーブ  server-id = 2

マスターDBを設定してレプリケーション用ユーザー作成

mysql> GRANT REPLICATION SLAVE ON *.* TO slave_user@'192.168.11.0/255.255.255.0' IDENTIFIED BY 'slave_password';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,password from user;
+------------+----------------------------+-------------------------------------------+
| user       | host                       | password                                  |
+------------+----------------------------+-------------------------------------------+
| root       | localhost                  | *A41ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| root       | 127.0.0.1                  | *A41ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
| slave_user | 192.168.11.0/255.255.255.0 | *54EBB56A6317324D4A720D14C4ED56CA21D115F2 |
| root       | home001                    | *A41ECFBE1191DDE4713F2B6F5A6CD5D0D0D5DC35 |
+------------+----------------------------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      291 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> exit
Bye


MYSQLを停止して初期データ取得
他にスレーブがいる場合は他のスレーブから取得
取得後に新規スレーブへデータ転送して転送元のMYSQLを再開

[root@HOME001 /]# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]
[root@HOME001 /]# 


[root@HOME001 mysql]# tar -cvf mysql_data.tar data/
data/
data/mysql-bin.000001
data/performance_schema/
data/performance_schema/events_waits_summary_global_by_event_name.frm
data/performance_schema/rwlock_instances.frm
data/performance_schema/setup_consume


[root@HOME001 mysql]# scp mysql_data.tar mysql@home002:/home/mysql/
mysql@home002's password:
mysql_data.tar                                              100%   36MB   1.1MB/s   00:34
[root@HOME001 mysql]# /etc/


[root@HOME001 mysql]# /etc/init.d/mysql start
Starting MySQL..                                           [  OK  ]
[root@HOME001 mysql]#

ポジション確認
master_status
データ転送
mysql_scp


転送したデータを展開して、データとログディレクトリーに展開。
my.cnfに関しては、必要に応じて適宜変更。
マスターでデータ取得時に確認したステータスにスレーブを設定して開始。

[root@HOME002 mysql]# tar xvf mysql_data.tar
data/
data/mysql-bin.000001
data/performance_schema/
data/performance_schema/events_waits_summary_global_by_event_name.frm
data/performance_schema/rwlock_instances.frm
data/performance_schema/setup_consumers.frm
data/performance_schema/events_waits_history_long.frm
data/performance_schema/performance_timers.frm

[root@HOME002 mysql]# ls -l
合計 212980
drwxrwxr-x 5 mysql mysql      4096 12月 22 16:29 data
-rw-r--r-- 1 mysql mysql 180009180 12月 22 14:14 mysql-5.5.29-linux2.6-i686.tar.gz
-rw-r--r-- 1 mysql mysql  37836800 12月 22 16:33 mysql_data.tar
[root@HOME002 mysql]# rm -rf /usr/local/mysql/data
[root@HOME002 mysql]# mv data /usr/local/mysql
[root@HOME002 mysql]# 

mysql> show slave status \G
Empty set (0.00 sec)

mysql> CHANGE MASTER TO
    -> MASTER_HOST = 'HOME001',
    -> master_port=3306,
    -> MASTER_USER = 'slave_user',
    -> MASTER_PASSWORD = 'slave_password',
    -> MASTER_LOG_FILE = 'mysql-bin.000004',
    -> MASTER_LOG_POS = 291;
Query OK, 0 rows affected (0.26 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 1281
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 1427
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           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: 1281
              Relay_Log_Space: 2210
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)
mysql>


mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|  3 | system user |           | NULL | Connect | 1324 | Waiting for master to send event                                            | NULL             |
|  4 | system user |           | NULL | Connect |  323 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 12 | root        | localhost | NULL | Query   |    0 | NULL                                                                        | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

mysql>

start_replication


エラーが無ければレプリケーションの設定変更完了
同期の動作確認を行う。

■Create table@マスターDB

mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `TABLE001` (
    -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    -> `comment` varchar(45) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.44 sec)

mysql>

■Slaveでのレプリケーション確認

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: HOME001
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 1521
               Relay_Log_File: relay-bin.000004
                Relay_Log_Pos: 1667
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,performance_schema
           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: 1521
              Relay_Log_Space: 2450
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| TABLE001       |
+----------------+
1 row in set (0.00 sec)

mysql>

データINSERT検証
replication_status_insert

データINSERT検証 其の2
replication_status_insert2

データDELETE検証
replication_status_delete

TABLE MODIFY検証

mysql> alter table TABLE001 MODIFY COLUMN comment varchar(100);
Query OK, 32 rows affected (0.67 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql>

MYSQL-modify

過去の検証:
MYSQLレプリケーション (sqlコマンドにてslave設定)

MYSQL SLAVE STATUS確認(report-host)

その他留意事項
sync_binlogはクラッシュした場合などに重要な設定だが、パフォーマンスが大きく劣化するようなので、
5.6まではI/Oが遅いシステムなどで利用する場合は留意しておく必要がありそうです。

mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL sync_binlog = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

MySQLのDRBD構成におけるネットワーク遅延の影響について
MySQL 5.6におけるsync_binlog=1の改善について
バイナリ ログ

Comments are closed.

Post Navigation