![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
スレーブのハード、ソフトメンテナンス後のレプリケーション再開について
スレーブ停止中のMYSQLレプリケーション動作確認
マスターの状態（スレーブ停止中）
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | TABLE001 | +----------------+ 1 row in set (0.00 sec) mysql> desc TABLE001; +---------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | comment | varchar(45) | NO | | NULL | | +---------+------------------+------+-----+---------+----------------+ 2 rows in set (0.35 sec) mysql> select * from TABLE001; +----+-------------------------------------------+ | id | comment | +----+-------------------------------------------+ | 1 | This is replication test data001@20121221 | +----+-------------------------------------------+ 1 row in set (0.01 sec) mysql>
列追加（スレーブ停止中）
mysql> ALTER TABLE TABLE001 ADD `title` varchar(20) DEFAULT NULL after id; Query OK, 1 row affected (0.69 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> desc TABLE001; +---------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | title | varchar(20) | YES | | NULL | | | comment | varchar(45) | NO | | NULL | | +---------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql>
インデックス追加(スレーブ停止中）
mysql> ALTER TABLE TABLE001 ADD INDEX IDX_TABLE001_id(`id`); Query OK, 0 rows affected (0.55 sec) Records: 0 Duplicates: 0 Warnings: 0
データ追加(スレーブ停止中）
mysql> select * from TABLE001;
+----+-------+-------------------------------------------+
| id | title | comment |
+----+-------+-------------------------------------------+
| 1 | NULL | This is replication test data001@20121221 |
+----+-------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into TABLE001(title,comment) values('タイトル 20121225','This is replication test when slave down');
Query OK, 1 row affected (0.44 sec)
mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title | comment |
+----+-----------------------+-------------------------------------------+
| 1 | NULL | This is replication test data001@20121221 |
| 2 | タイトル 20121225 | This is replication test when slave down |
+----+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql>
データ更新(スレーブ停止中）
mysql> select * from TABLE001; +----+-----------------------+-------------------------------------------+ | id | title | comment | +----+-----------------------+-------------------------------------------+ | 1 | NULL | This is replication test data001@20121221 | | 2 | タイトル 20121225 | This is replication test when slave down | +----+-----------------------+-------------------------------------------+ 2 rows in set (0.00 sec) mysql> update TABLE001 set title='タイトル 20121224' where id = 1; Query OK, 1 row affected (0.42 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from TABLE001; +----+-----------------------+-------------------------------------------+ | id | title | comment | +----+-----------------------+-------------------------------------------+ | 1 | タイトル 20121224 | This is replication test data001@20121221 | | 2 | タイトル 20121225 | This is replication test when slave down | +----+-----------------------+-------------------------------------------+ 2 rows in set (0.00 sec) mysql>
データ削除(スレーブ停止中）
mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title | comment |
+----+-----------------------+-------------------------------------------+
| 1 | タイトル 20121224 | This is replication test data001@20121221 |
| 2 | タイトル 20121225 | This is replication test when slave down |
+----+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> insert into TABLE001(title,comment) values('Title 20121225','This is replication test when slave down');
Query OK, 1 row affected (0.44 sec)
mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title | comment |
+----+-----------------------+-------------------------------------------+
| 1 | タイトル 20121224 | This is replication test data001@20121221 |
| 2 | タイトル 20121225 | This is replication test when slave down |
| 3 | Title 20121225 | This is replication test when slave down |
+----+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> delete from TABLE001 where id = 3;
Query OK, 1 row affected (0.46 sec)
mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title | comment |
+----+-----------------------+-------------------------------------------+
| 1 | タイトル 20121224 | This is replication test data001@20121221 |
| 2 | タイトル 20121225 | This is replication test when slave down |
+----+-----------------------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql>
マスターのポジション確認
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000007 | 1347 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 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.000007
Read_Master_Log_Pos: 1347
Relay_Log_File: relay-bin.000007
Relay_Log_Pos: 1493
Relay_Master_Log_File: mysql-bin.000007
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: 1347
Relay_Log_Space: 1789
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 variables like 'hostname'; +---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | hostname | HOME002.localdomain | +---------------+---------------------+ 1 row in set (0.00 sec) mysql> select * from TABLE001; +----+-----------------------+-------------------------------------------+ | id | title | comment | +----+-----------------------+-------------------------------------------+ | 1 | タイトル 20121224 | This is replication test data001@20121221 | | 2 | タイトル 20121225 | This is replication test when slave down | +----+-----------------------+-------------------------------------------+ 2 rows in set (0.00 sec) mysql>
