スレーブのハード、ソフトメンテナンス後のレプリケーション再開について
スレーブ停止中の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>