MySQL replication (+slave , - errors), resolving issues

0 */3 * * * /opt/scripts/db-backup.sh --take-snapshot | logger -t 'MySQL Snapshot'

db-backup.s

Manual mechanism: 

• Use the latest snapshot to create a new volume
• Attach new /vol with latest data files
• in my.cnf - change server_id!
• Stop service mysql 
• Check if user exists on master, create it if there is no replication user : 

CREATE USER 'user_replica'@'%' IDENTIFIED BY 'user_replica'; GRANT REPLICATION SLAVE ON *.* TO 'user_replica'@'%' IDENTIFIED BY 'user_replica';

• Grab the name of bin-log and log_position on MASTER's mysql console:

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

• Check that mysql port 3306 is opened for a new slave, execute on SLAVE 

telnet MASTER_IP 3306 Trying MASTER_IP... Connected to MASTER_IP. Escape character is '^]'.

• To check FULL connection (mysql -u user_replica -puser_replica -h MASTER_IP), make sure that user was granted to connect
• Start service mysql on SLAVE
• On SLAVE's mysql console execute:

STOP SLAVE; CHANGE MASTER TO                   MASTER_HOST='MASTER_IP',                   MASTER_USER='user_replica',                   MASTER_PASSWORD='user_replica',                   MASTER_LOG_FILE='mysql-bin.XXXXXXXX',                   MASTER_LOG_POS=YYYYYYYY; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;

MASTER_LOG_FILE and MASTER_LOG_POS were taken from MASTER, 

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; - to skip replication errors

• Check replication progress on SLAVE:

mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: MASTER_IP                   Master_User: user_replica                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.XXXXXXX           Read_Master_Log_Pos: YYYYYYYY                Relay_Log_File: mysqld-relay-bin.000002                 Relay_Log_Pos: 1631092         Relay_Master_Log_File: mysql-bin.000006              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: 45014498               Relay_Log_Space: 1631249               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_IO_Running and Slave_SQL_Running should be YES

Relay_Log_Space and  Exec_Master_Log_Pos should change, and Relay_Log_Space should grow faster and after some time should be almost the same as Exec_Master_Log_Pos

• Check new slave on MASTER: 

mysql> show slave hosts; +-----------+------+------+-----------+ | Server_id | Host | Port | Master_id | +-----------+------+------+-----------+ |         2 |      | 3306 |         1 | +-----------+------+------+-----------+ 1 row in set (0.00 sec)

How to resolve errors during replication: 

It the error is critical, like this

Last_SQL_Error: Error 'Duplicate entry '16906132' for key 'PRIMARY'' on query. Default database: 'trans'. Query: 'INSERT INTO wp_log (            `log` , `caller` , `type`

MySQL crashes and stops. 

In that case: 

sudo service mysql start mysql> show slave status\G     #mysql got stuck on error       Slave_IO_Running: Yes   Slave_SQL_Running: No   # Make it ignore this error, if this error could be ignored, of course     mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

There might be cases where you will want to skip more queries. For example you might want to skip all duplicate errors you might be getting (output from show slave status;):

1	"1062 | Error 'Duplicate entry 'xyz' for key 1' on query. Default database: 'db'. Query: 'INSERT INTO ..."

If you are sure that skipping those errors will not bring your slave inconsistent and you want to skip them ALL, you would add to your my.cnf:

1	slave-skip-errors = 1062

As shown above in my example 1062 is the error you would want to skip, and from here we have: _ Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry ‘%s’ for key %d_