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:
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:
##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;