recover-replication-on-mysql

  • Stop data sync on slave node

    1
    2
    3
    mysql -uroot -pcR@2017 xxx_prd
    > show slave status \G;
    > stop slave;

  • export data from master node

    1
    2
    3
    mysqldump -uroot -pcR@2017 -c psct_prd --no-data any_table1, any_table2 > xxx_prd_schema-only-Dump20180228.sql

    mysqldump -uroot -pcR@2017 -c psct_prd | sed 's/ AUTO_INCREMENT=[0-9]*//g' > xxx_prd_dmp_20190524.sql(export all data)

  • Using Search and Replace to remove *auto_increment=DE to DE directive since the exported schema sql included auto_increment value (no need as use sed to filter auto increment)

  • Show status on master node

    1
    2
    3
    4
    SHOW MASTER STATUS\G;
    show master logs;\G
    show binlog events\G;
    show variables like 'log_%';

  • create replication user if neccessary

    1
    2
    3
    4
    > CREATE USER replication@slave_node;
    > GRANT REPLICATION SLAVE ON *.* TO replication@slave_node IDENTIFIED BY 'password';
    > FLUSH privileges;
    > SHOW MASTER STATUS

  • import data from master node

    1
    mysql -uroot -pcR@2017 xxx_prd < xxx_prd.sql

  • start slave to replicate data from binlog.000198

    1
    2
    3
    4
    5
    6
    7
    change master to
      master_host='master_node',
      master_port=3306,
      master_user='replication',
      master_password='password',
      master_log_file='mysql-bin.000198',
      master_log_pos=821;

    Or just run as below to update master info and relay log info, please check whether relay-log.info is updated and check progress of data replication.
    change master to master_log_file='mysql-bin.000198',master_log_pos=821;

Then start slave process
start slave;