Table of Contents

MySQL Replication

192.168.56.102  ubuntu-master
192.168.56.103  ubuntu-slave

Set Server ID on Slave

set persist server_id = 2;

service mysql restart
systemctl restart mysql

Create Replication User on Master

create user 'repl'@'ubuntu-slave' identified by 'teapot';
grant replication slave on *.* to 'repl'@'ubuntu-slave';

Lock Tables on Master

flush tables with read lock;

Display Details of Binary Log

show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000011 |      904 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

Backup / Restore

mysqldump -uroot -p --all-databases --master-data --add-drop-database > ubuntu-master.dmp
unlock tables;
mysql -uroot -p < ubuntu-master.dmp

Configure Replication on Slave

mysql> CHANGE MASTER TO
       ->     MASTER_HOST='ubuntu-master',
       ->     MASTER_USER='repl',
       ->     MASTER_PASSWORD='teapot',
       ->     MASTER_LOG_FILE='binlog.000011',
       ->     MASTER_LOG_POS=904;
	
mysql> start slave;

Monitoring and Administration

mysql> show slave status\G

mysql> stop slave
mysql> stop slave io_thread  (Stop reading events from the Master Binary Log)
mysql> stop slave sql_thread (Stop reading events from the Slave Relay Log)