======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;