mysql replication 이 중지되었다.

무엇때문일까.

show slave status 를 보니.

Last_IO_Error: Got a packet bigger than 'max_allowed_packet' bytes

이런 항목이 있다.

# 현재값 확인

mysql> show variables like 'max_allow%';

+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

1048576 이면 1M 정도밖에 안되는 듯 하다.

# 다음 옵션으로 설정

set global net_buffer_length=1000000; 

set global max_allowed_packet=1000000000;

# my.cnf 에 설정

[mysqld]

max_allowed_packet = 10M

=> max_allowed_packet 설정 값만 처리하면 될까?

10M 정도면 충분할까?

일단 설정은 했는데. show slave status\G 로 보면 다음과 같은 메시지가 나온다.

Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

다시 첫설정을 해야 겠다.(주기적으로 1일/1주? 초기 세팅이 필요할지도)

http://www.mysqlsystems.com/manual/refman51en/error-handling.html

master-host             = master
master-user             = replication
master-password     = slave

/usr/libexec/mysqld: unknown variable 'master-user=replication'

CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='replication', MASTER_PASSWORD='slave';

mysqldump --master-data=2 --databases DB1 DB2 > slave.dump.sql

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000200', MASTER_LOG_POS=200500;

!includedir  /etc/mysql/conf.d/

[mysqld]
server-id               = 2
master-host             = master
master-user             = replication
master-password     = slave
replicate-do-db         = master_db
replicate-do-db         = other_db

[mysqld]
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = master_db
binlog_do_db            = other_db
binlog_ignore_db        = mysql
binlog_ignore_db        = information_schema

mysql> grant replication slave on *.* to 'replication'@192.168.0.1 identified by 'slave';

start slave ;
stop slave ;

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000018
          Read_Master_Log_Pos: 958292
               Relay_Log_File: slave-relay-bin.000271
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000018
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: master_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: 958292
              Relay_Log_Space: 556
              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:
1 row in set (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000018
        Position: 958292
    Binlog_Do_DB: master_db
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

mysql > stop slave;
mysql > reset slave;
mysql > start slave;