MySQL+Replication

FrontPage > MySQL

事前確認事項 †

設定箇所 †

1)Master側
/etc/mysqld/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1

をしてMySQLをrestartする

/etc/init.d/mysqld restart

もしInnoDBを使っていたら、
/etc/mysqld/my.cnf

innodb_flush_log_at_trx_commit=1
sync_binlog=1 

を設定するとよい

2) ユーザーを作成する

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

3) Slave側
/etc/mysqld/my.cnf

[mysqld]
server-id=2
master-host     = masterのIP or 名前(名前の場合/etc/hostsや正引き出来る物)
master-user     = replication用のuser(master側に作成したやつ）
master-password = hogehoge -> 平文
master-port     = 3306
replicate-do-db = target_db02 -> 複数ある場合は行毎に列挙するか、 target_db02,target_db03という風にする
log_bin                 = /var/log/mysql/mysql-bin.log

をしてMySQLをrestartする

/etc/init.d/mysqld restart

4)上記設定が出来たら
Master側

mysql> FLUSH TABLES WITH READ LOCK;

で書き込み不可にする（読み込みのみ許可する）

5)ステイタスを確認する

mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73       | test         | manual,mysql     |
+------------------+----------+--------------+------------------+

6)データをdumpする

mysqldump --all-databases --lock-all-tables >dbdump.db

↓こっちでもよい

mysqldump --all-databases --master-data >dbdump.db

データをdumpしたら

mysql> UNLOCK TABLES;

して書き込みロックを解除する

7)もしファイルが大きくmysqldump出来ない場合はrawファイルをコピーする
MySQLを止める

/etc/init.d/mysqld stop

圧縮するか、rsyncしてバックアップを取る

tar cf /tmp/db.tar ./data
rsync --recursive ./data /tmp/dbdata

MySQLを再開する

/etc/init.d/mysqld start

※もしInnoDBを使っていなければ、(7)をMySQLを止めずに行う
ロックを解除する

mysql> UNLOCK TABLES;

(8)
Slaveに設定をする

mysql> CHANGE MASTER TO
   ->     MASTER_HOST='master_host_name',
   ->     MASTER_USER='replication_user_name',
   ->     MASTER_PASSWORD='replication_password',
   ->     MASTER_LOG_FILE='recorded_log_file_name',
   ->     MASTER_LOG_POS=recorded_log_position;

MySQLを--skip-slave-start を付けてslaveを立ち上がらせないようにする
Masterのデータを入れる

mysql < fulldb.dump

Slave側でReplicationを開始する

mysql> START SLAVE;

正常時のステイタス

mysql> show slave status \G;
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master-db
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000061
        Read_Master_Log_Pos: 8367257
             Relay_Log_File: mysqld-relay-bin.000023
              Relay_Log_Pos: 8367394
      Relay_Master_Log_File: mysql-bin.000061
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: target_db01,target_db02,target_db03
        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: 8367257
            Relay_Log_Space: 8367394
            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
1 row in set (0.00 sec)

ERROR: 
No query specified

確認方法 †

エラー時には †

[解決済み]Slave_IO_Running、Slave_SQL_Running のどちらかがNoになっている †

ステイタスを見るとおかしな事に！！

mysql> show slave status \G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    30
Current database: *** NONE ***

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master-db
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000084
        Read_Master_Log_Pos: 77395302
             Relay_Log_File: mysqld-relay-bin.000020
              Relay_Log_Pos: 1528888
      Relay_Master_Log_File: mysql-bin.000060
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: target_db01,target_db02,target_db03
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1146
                 Last_Error: Error 'Table 'target_db02.user'  doesn't exist' on query. Default database: 'sandy'. Query: 'ALTER TABLE `user` DISABLE KEYS'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 1528751
            Relay_Log_Space: 2609233944
            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
1 row in set (0.00 sec)

ERROR: 
No query specified

その1 †

こんな場合は

Last_Errno: 1146

を確認し、
/etc/mysql/my.conf
に

slave-skip-errors       = 1146（複数ある場合は、1146,1147,1148と列挙する）

として、MySQLを再起動

/etc/init.d/mysql restart

その2(MyISAM版) †

slave側で

stop slave;
drop database 対象DB;

master側で

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000198 | 87351937 |              |                  | 
+------------------+----------+--------------+------------------+

を確認する
slave側で

change master to master_log_file = 'mysql-bin.000198', master_log_pos=87351937;
load data from master;
start slave;

とする

その3(InnoDB) †

slave側で

 mysql> stop slave;
 mysql> drop database 対象DB;

master側から対象DBをdumpしてくる

mysql> FLUSH TABLES WITH READ LOCK;
aya@db:$ mysqldump --opt -c -uほげほげ　-pパスワード 対象DB >  対象DB.sql
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000198 | 87351937 |              |                  | 
+------------------+----------+--------------+------------------+
 mysql> UNLOCK TABLES;

対象DB.sqlをslave側へscp or rsyncする
slave側で

 mysql> create database 対象DB;
aya@slave:$mysql -uほげほげ -p 対象DB <　対象DB.sql
 mysql> change master to master_log_file = 'mysql-bin.000198', master_log_pos=87351937;
mysql> start slave;

[解決済み]ERROR 1219 (HY000): Error running query on master: Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation †

※この方法はMyISAMだけです！！！※

slave側で

LOAD DATA FROM MASTER;

したら

ERROR 1219 (HY000): Error running query on master: Access denied; you need the SUPER,REPLICATION CLIENT privilege for this operation

と出たので、マスター側で

GRANT SUPER ON *.* TO 'repliuser'@'repli-server';
FLUSH PRIVILEGES;

として反映でOK

[解決済み]ERROR 1219 (HY000): Error running query on master: Access denied; you need the RELOAD privilege for this operation †

※この方法はMyISAMだけです！！！※

slave側で

LOAD DATA FROM MASTER;

したら

ERROR 1219 (HY000): Error running query on master: Access denied; you need the RELOAD privilege for this operation

と出たので、マスター側で

GRANT RELOAD ON *.* TO 'repliuser'@'repli-server';
FLUSH PRIVILEGES;

として反映でOK

Lost connection to MySQL server at 'reading initial communication packet †

[ERROR] Slave I/O thread: error connecting to master 'user@master-server:3306': 
Error: 'Lost connection to MySQL server at 'reading initial communication packet', 
system error: 111'  errno: 2013  retry-time: 60  retries: 86400

参考URL †

http://dev.mysql.com/doc/refman/5.0/en/replication.html
http://dev.mysql.com/doc/refman/4.1/ja/replication-howto.html
http://dev.mysql.com/doc/refman/4.1/ja/replication-faq.html
http://mysql-5.1.petbyoin.net/replication.html#replication-howto-masterstatus