Complete MySql Replication Guide

For a MySql replication guide using the mysqldump utility, go to the Complete MySql Replication Guide Using mysqldump post.

My step by step guide for setting up a replication server for MySql 5.6 and up using Percona XtraBackup.

  1. Create replication user in MySql, if not already done.

    mysql> CREATE USER 'repl'@'192.168.0.%' IDENTIFIED BY 'repl_password';
  2. Configure the production MySql server to accept connections from the slave server, if not already done.

    mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT, SUPER ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'some_password';
  3. Backup master server data to local directory (use --safe-slave-backup option if backing up from a slave, leave off --compress if you don’t need compression).
    Xbstream

    $ innobackupex --compress --stream=xbstream --parallel=4 ./ > /path/to/mysqlbackupdir/snapshot.xbstream

    Tar

    $ innobackupex --stream=tar --parallel=4 ./ > /path/to/mysqlbackupdir/snapshot.tar
  4. Copy backup to remote slave server.

    $ scp -P 25 -i ~/.ssh/yourkey snapshot.xbstream [email protected]:/home/david
    # OR
    $ rsync -avzhe ssh –-progress snapshot.xbstream [email protected]:/home/david
  5. Unpack backup in remote server.
    Xbstream

    $ xbstream -x < snapshot.xbstream -C /home/david
    # Uncompress, if needed (qpress must be installed)
    $ for i in $(find -name "*.qp"); do qpress -vd $i $(dirname ${i}) && rm -f $i; done

    Tar

    $ tar -xif snapshot.tar

    Note: -i option is required when using XtraBackup’s –stream=tar option.
  6. Prepare the backup.

    $ innobackupex --user='USER' --password='PASSWORD' --apply-log --use-memory=4G /path/to/BACKUP-DIR
  7. Stop MySql, rename or remove current mysql directory, make new mysql directory, copy backup to mysql directory, then change ownership of mysql directory to mysql.

    $ /etc/init.d/mysql stop
    $ mv /var/lib/mysql /var/lib/mysql_old
    $ mkdir /var/lib/mysql
    $ innobackupex --copy-back /path/to/BACKUP-DIR
    $ chown -R mysql:mysql /var/lib/mysql
  8. Make sure to modify or replace my.cnf before starting MySql. It is essential to set skip-slave-start = 1 to prevent prematurely starting the slave process and causing a failure.

    $ vim /etc/mysql/my.cnf
    $ /etc/init.d/mysql start
  9. When the XtraBackup process was run, there should have been a file called xtrabackup_binlog_info in the top level of that directory which contains both binary log coordinates and GTID information.

    # EXAMPLE
    $ cat xtrabackup_binlog_info
    mysql-bin.000002 1232 c777888a-b6df-11e2-a604-080027635ef5:1-4
    # Start the new slave from that GTID position:
    slave mysql> SET GLOBAL gtid_purged="c777888a-b6df-11e2-a604-080027635ef5:1-4";
    # If you get the error "GTID_PURGED can only
    # be set when GTID_EXECUTED is empty", run
    # slave mysql> RESET MASTER;
    # then continue on with setting gtid_purged
    # slave mysql> SET GLOBAL gtid_purged="c777888a-b6df-11e2-a604-080027635ef5:1-4";
    # Ensure SSL certs are updated then change master with SSL enabled
    slave mysql> CHANGE MASTER TO MASTER_HOST='host', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION=1, MASTER_SSL=1, MASTER_SSL_CA='/var/lib/mysql/ca.pem', MASTER_SSL_CAPATH='/var/lib/mysql', MASTER_SSL_CERT='/var/lib/mysql/client-cert.pem', MASTER_SSL_KEY='/var/lib/mysql/client-key.pem';
    # Change master without SSL
    # slave mysql> CHANGE MASTER TO MASTER_HOST='host', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION=1;
    slave mysql> START SLAVE;

    Without using MASTER_AUTO_POSITION
    When the XtraBackup process was run, there should have been a file called xtrabackup_binlog_info in the top level of that directory. Take note of the two values in that file which should be something close to mysql-bin.000001 and some number like 24938 (this will be widely different among systems). Tell the slave server who the master is then start the slave:

    $ mysql
    mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.70', MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=24938;
    mysql> START SLAVE;

    Note: You can also use xtrabackup_binlog_pos_innodb if xtrabackup_binlog_info is not available. Replication should be up and running now. To ensure that the Slave is caught up to the master, run “SHOW SLAVE STATUS \G” in a MySql prompt and look for the value of “seconds behind master”. Once this is 0, the slave has caught up.

Promoting a slave to master


# Execute on the slave to be made into master
mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;
# To reset gtid_executed and prevent
# multiple gtid_executed values
mysql> RESET MASTER;
# Note 1: To prevent replication issues, restart replication of all servers to the new master (including the old master) prior to bringing the old master down and putting the application back up. Set skip-slave-start = 1 in my.cnf to ensure that the slave process doesn't start prematurely.
# Note 2: If replication fails with error code 2026, verify that your SSL certificates match across all of your servers.