mysql_repl_system [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
<span class="iconify mr-1 svg-loading" data-icon="mdi:arrow-left"></span>======Overview======
In this scenario we will be replication all the changes happening to our master database:

**Info**
<sxh bash>
Master: mysqllpara - 192.168.0.67
Slave: mysqllparaslave - 192.168.0.248
</sxh>

===== 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 :**
<sxh bash>
# Replication
log-bin=mysql-bin
server-id=1

# Best durability of InnoDB for replication 
innodb_flush_log_at_trx_commit=1
sync_binlog=1
</sxh>

**Create user for Slave replica :**
<sxh sql>
create user 'replica-slave'@'<MASTER_HOST>' identified by '<PASSWORD>';
grant replication slave on *.* to 'replica-slave'@'<SLAVE_HOST>';
</sxh>

<sxh sql>
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)
</sxh>

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 :**
<sxh bash>
mysqldump --databases repltest --master-data > repltest.dump
</sxh>

Other way is to create a LVM snapshot:

<sxh>
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"
</sxh>

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:

<sxh bash>
##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
</sxh>

Master
#Unlock tables


Slave
<sxh bash>
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;
</sxh>

====Test====
===Change the server id===
On Slave:

<sxh bash>
server-id=2
</sxh>


<sxh bash>
start slave;
</sxh>

#Master
<sxh bash>
Create table testJackpot(id int);

insert into testJackpot values(1);
</sxh>

Slave
<sxh bash>
Select * from testJackpot;
</sxh>