======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
===== Master configuration =====
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'@'' identified by '';
grant replication slave on *.* to 'replica-slave'@'';
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.
=====Slave Configuration=====
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;
====Test====
===Change the server id===
On Slave:
server-id=2
start slave;
#Master
Create table testJackpot(id int);
insert into testJackpot values(1);
Slave
Select * from testJackpot;