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;