Overview

In this scenario we will be replication all the changes happening to our master database:

Info

Master: mysqllpara - 192.168.0.67
Slave: mysqllparaslave - 192.168.0.248

We have firstly configure the master server as follows:

• Allow logging
• Replica User Creation
• Backup Creation

Enable binary logging and server Id in /etc/my.cnf :

# Replication
log-bin=mysql-bin
server-id=1

# Best durability of InnoDB for replication 
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Create user for Slave replica :

create user 'replica-slave'@'<MASTER_HOST>' identified by '<PASSWORD>';
grant replication slave on *.* to 'replica-slave'@'<SLAVE_HOST>';

mysql> create  user 'repl1'@'%' identified by '*******';
Query OK, 0 rows affected (0.09 sec)

mysql> grant replication slave on *.* to 'repl1'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

Now we have to create a backup, the most easy way is (if you are sure there is nothing running on your DB to simply “dump” the database, you want to repicate:

Dump the database required for replication :

mysqldump --databases repltest --master-data > repltest.dump

Other way is to create a LVM snapshot:

mysql>  SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      597 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


lvcreate -L2G -s -n mysqlbackup /dev/data/lvol0
dd if=/dev/data/mysqlbackup | gzip -c | ssh root@mysqllparaslave "dd of=/dev/data/mysqlbackup.img"

Third way is to use enterprise backup (paid) or percona backup (again paid) for a consistent hot backup if the tables are on InnoDB engine.

On the slave, in nutshell we have to:

• Restore the backup
• Unlock the tables on Master
• Change Master
• Change the server_id to different id than the master:

##umount /mysqldatamaster -in case it exists somehow -> Thinking of optimizing it with lvm rename
##lvremove /dev/data/mysqlbackup -in case it exists somehow -> Thinking of optimizing it with lvm rename
lvcreate -L 2G -n /dev/data/mysqlbackup data
dd if=/dev/data/mysqlbackup.img | gzip -c -d | dd of=/dev/data/mysqlbackup
mount /mysqldatamaster

Master #Unlock tables

Slave

Edit /etc/my.cnf - datadir to point to /mysqldatamaster - Newly mounted 
#Select UUID();
#Edit /datadir/auto.cnf - change the UUID - Should be different than the master

service mysqld restart

CHANGE MASTER TO
    ->     MASTER_HOST='mysqllpara',
    ->     MASTER_USER='repl1',
    ->     MASTER_PASSWORD='********',
    ->     MASTER_LOG_FILE='binlog.000001',
    ->     MASTER_LOG_POS=591;

On Slave:

server-id=2

start slave;

#Master

Create table testJackpot(id int);

insert into testJackpot values(1);

Slave

Select * from testJackpot;