Complete MySql Replication Guide Using mysqldump

For a MySql replication guide using Percona’s XtraBackup utility, go to the Complete MySql Replication Guide post.

My step by step guide for setting up a replication server for MySql 5.6 and up (with GTID enabled) using mysqldump.

  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.
    mysqldump
    $ mysqldump --all-databases --hex-blob --single-transaction --add-drop-database --triggers --routines --events --user=root --password > snapshot.sql

    Notes
    --hex-blob: Dump binary columns using hexadecimal notation (for example, ‘abc’ becomes 0x616263). The affected data types are BINARY, VARBINARY, the BLOB types, and BIT.
    --single-transaction: Useful for exporting InnoDB tables since it does not block transactions during the export.

    GTIDs Disabled
    If not using GTIDs, use either the --master-data=2 option to create a slave for the server you are creating this mysqldump for or --dump-slave=2 if mysqldump is being run on a slave and you plan on having a new slave use the same master. The “2” option writes the “CHANGE MASTER” command in the dump file as a comment while the “1” option runs the command when the dump file is imported.

  4. Copy backup to remote slave server.
    $ scp -P 25 -i ~/.ssh/yourkey snapshot.sql [email protected]:/home/david
    # OR
    $ rsync -avzhe ssh –-progress snapshot.sql [email protected]:/home/david
  5. Make sure to modify or replace my.cnf before initiating restore.
    $ vim /etc/mysql/my.cnf
  6. Initiate restore from sql file.
    $ mysql -u root -p < snapshot.sql
  7. Assuming GTID is enabled on the master, run reset master.
    slave mysql> RESET MASTER;
  8. Get GTID_PURGED value from the sql file.
    $ grep 'GTID_PURGED' -m 1 snapshot.sql
  9. Set GTID_PURGED in MySql prompt.
    slave mysql> SET GLOBAL gtid_purged="c777888a-b6df-11e2-a604-080027635ef5:1-4";
  10. Run the CHANGE MASTER command to associate slave with the master then start the slave.

    # Change master with SSL enabled
    slave mysql> CHANGE MASTER TO MASTER_HOST='host', MASTER_PORT='port', 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='port', MASTER_USER='repl', MASTER_PASSWORD='repl_password', MASTER_AUTO_POSITION=1;
    slave mysql> START SLAVE;

    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;

Initial Set Up for an Ubuntu 16.04 Server

My script for initially setting up an Ubuntu 16.04 server to be used prior to installing more specialized software (web server, database, cache, firewall, etc). If you’re not familiar with running scripts, use an editor (vim / emacs) to modify files like /etc/sysctl.conf instead. Many of these may or may not apply to you and should only be used as a guide or starting point. Use at your own risk.

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.