More than 5 years have passed since last update.

MySQL レプリケーションメモ

MySQL

Posted at 2012-07-22

スレーブにデータを挿入した場合どうなるのか、どうやって回復したらよいのかやってみた。

レプリケーションの確認

マスターはこうなっている

@master

mysql> show create table sample_tbl\G                                       x
*************************** 1. row ***************************
Table: sample_tbl
Create Table: CREATE TABLE `sample_tbl` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`updated` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from sample_tbl;
+----+----------------------------------+---------------------+
| id | name                             | updated             |
+----+----------------------------------+---------------------+
|  1 | aaa                              | 2012-07-21 21:10:04 |
|  2 | bbb                              | 2012-07-22 14:31:20 |
|  3 | ccc                              | 2012-07-22 14:53:35 |
|  4 | bbb                              | 2012-07-22 15:03:36 |
+----+----------------------------------+---------------------+
4 rows in set (0.00 sec)

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

スレーブでレプリケーションが正常であることを確認

@slave

mysql> select * from sample_tbl;
+----+----------------------------------+---------------------+
| id | name                             | updated             |
+----+----------------------------------+---------------------+
|  1 | aaa                              | 2012-07-21 21:10:04 |
|  2 | bbb                              | 2012-07-22 14:31:20 |
|  3 | ccc                              | 2012-07-22 14:53:35 |
|  4 | bbb                              | 2012-07-22 15:03:36 |
+----+----------------------------------+---------------------+
4 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000014
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 107
Relay_Log_Space: 410
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)

スレーブを更新

スレーブで一行データを挿入する

@slave

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into repl_sample.sample_tbl (name, updated) values ('update at slave', now());
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from sample_tbl;
+----+----------------------------------+---------------------+
| id | name                             | updated             |
+----+----------------------------------+---------------------+
|  1 | aaa                              | 2012-07-21 21:10:04 |
|  2 | bbb                              | 2012-07-22 14:31:20 |
|  3 | ccc                              | 2012-07-22 14:53:35 |
|  4 | bbb                              | 2012-07-22 15:03:36 |
|  5 | update at slave                  | 2012-07-22 17:19:00 |
+----+----------------------------------+---------------------+
5 rows in set (0.00 sec)

マスタで一行データを挿入する

@master

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sample_tbl (name, updated) values ('update at master', now());
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from sample_tbl;
+----+----------------------------------+---------------------+
| id | name                             | updated             |
+----+----------------------------------+---------------------+
|  1 | aaa                              | 2012-07-21 21:10:04 |
|  2 | bbb                              | 2012-07-22 14:31:20 |
|  3 | ccc                              | 2012-07-22 14:53:35 |
|  4 | bbb                              | 2012-07-22 15:03:36 |
|  5 | update at master                 | 2012-07-22 17:20:26 |
+----+----------------------------------+---------------------+
5 rows in set (0.00 sec)

スレーブの状況を確認

テーブルが更新されていなかったり、Slave_SQL_Running が No だったり、Last_Error が記録されていたりするのがわかる。

@slave

mysql> select * from sample_tbl;
+----+----------------------------------+---------------------+
| id | name                             | updated             |
+----+----------------------------------+---------------------+
|  1 | aaa                              | 2012-07-21 21:10:04 |
|  2 | bbb                              | 2012-07-22 14:31:20 |
|  3 | ccc                              | 2012-07-22 14:53:35 |
|  4 | bbb                              | 2012-07-22 15:03:36 |
|  5 | update at slave                  | 2012-07-22 17:19:00 |
+----+----------------------------------+---------------------+
5 rows in set (0.00 sec)


mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.122.200
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 388
Relay_Log_File: mysqld-relay-bin.000014
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '5' for key 'PRIMARY'' on query. Default database: 'repl_sample'. Query: 'insert into sample_tbl (name, updated) values ('update at master', now())'
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 691
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '5' for key 'PRIMARY'' on query. Default database: 'repl_sample'. Query: 'insert into sample_tbl (name, updated) values ('update at master', now())'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

対応

今回は Last_SQL_Error にもあるとおり、primary key の重複が問題なのでこれを解決してあげる。
解決したら start slave sql_thread でスレーブを再開する。

@slave

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from sample_tbl where id=5;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from sample_tbl;
+----+----------------------------------+---------------------+
| id | name                             | updated             |
+----+----------------------------------+---------------------+
|  1 | aaa                              | 2012-07-21 21:10:04 |
|  2 | bbb                              | 2012-07-22 14:31:20 |
|  3 | ccc                              | 2012-07-22 14:53:35 |
|  4 | bbb                              | 2012-07-22 15:03:36 |
|  5 | update at master                 | 2012-07-22 17:20:26 |
+----+----------------------------------+---------------------+
5 rows in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.122.200
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 388
Relay_Log_File: mysqld-relay-bin.000014
Relay_Log_Pos: 534
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 388
Relay_Log_Space: 691
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)

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

MySQL レプリケーション メモ

レプリケーションの確認

スレーブを更新

スレーブの状況を確認

対応

MySQL レプリケーションメモ