restore-mysql-slave.md

DB Slave:

• Backup grants

# mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS query FROM mysql.user" | mysql | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}' | grep ^GRANT > /root/grants_2016-11-21.sql

• Stop MySQL

# systemctl stop mysql

• Enable root login on ssh

# echo "PermitRootLogin yes" >> /etc/ssh/sshd_config; systemctl restart sshd

• Remove mysql data

# rm -rf /mysql/data/*

• Install xtrabackup

# yum install percona-xtrabackup

DB Master:

• Create temporary ssh key

# ssh-keygen -f /root/.ssh/replication_tmp

• Copy public key to slave server

# cat /root/.ssh/replication_tmp.pub (Colocar manualmente no /root/.ssh/authorized_keys do slave alvo)

• Disable slave_parallel_workers if enabled

# mysql -e "SET GLOBAL slave_parallel_workers=0;"

• Streaming data from master to slave server

# time xtrabackup --user=root --password=xxxxxxxxx --host=127.0.0.1 --stream=xbstream --backup | ssh -i /root/.ssh/replication_tmp root@slave_ip "xbstream -x -C /mysql/data"

• Create replication grant to MySQL slave

# mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'replicator-dbX'@'slave_ip'  IDENTIFIED BY 'myReplicatorPassword';"

• Remove temporary ssh keys

# rm -rf /root/.ssh/replication_tmp*

DB Slave:

• Prepare the data to a consistent snapshot (apply logs)

# time xtrabackup --prepare --target-dir=/mysql/data

• Change owner to the mysql user

# chown -R mysql. /mysql/data

• Start MySQL

# systemctl start mysql

• Drop all non-root grants and users

# mysql -B -N -e "SELECT DISTINCT CONCAT('DROP USER \'', user, '\'@\'', host, '\';') AS query FROM mysql.user where user <> 'root'" | mysql

• Import grants from backup

# mysql < /root/grants_2016-11-21.sql

• Getting binlog possition

# cat /mysql/data/xtrabackup_binlog_info

• Settigup parameters of MySQL slave

# mysql -e "CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_PORT=3306, MASTER_USER='replicator-dbX', MASTER_PASSWORD='myReplicatorPassword', MASTER_LOG_FILE='mysql-bin.006653', MASTER_LOG_POS=184143837;"

• Start MySQL Slave

# mysql -e "start slave;"

• Watching the MySQL slave status

# watch "mysql -e 'show slave status\G'"

• Disable root login on ssh

# sed -i '/PermitRootLogin yes/d' /etc/ssh/sshd_config; systemctl restart sshd

Replica-AWS

• Backup of unreplicated schemas (Check if no new schemas non-replicated have been created)

# for DB in archiving bi bob_bak bob_live2 bob_migration catalog_clean cleanup diff report temp test trash xhprof; do echo "dumping ${DB}"; mysqldump --opt --single-transaction ${DB} | gzip > /reports/${DB}.sql.gz ; done

--- Do the restore process ---

• Restore of unreplicated schemas

# for DB in archiving bi bob_bak bob_live2 bob_migration catalog_clean cleanup diff report temp test trash xhprof; do echo "Restoring ${DB}"; mysql -e "create database ${DB};" ;pv -p -t -e /reports/${DB}.sql.gz | gzip -d -c - | mysql ${DB} ; done