Nerdy Drunk

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== MySQL Replication =====
{{tag>linux mysql}}

==== Prerequisites ====

Source;
<file>
Server/IP:
	SOURCENAME 10.1.2.101 (LOCATION)
MySQL Instance:
	3306
Database(s):
	*
System root password/SUDO access:
	ROOTPASS / SUDOUSER/SUDOUSERPASS
MySQL root password:
	?
Replication user:
	?
Replication password:
	?
Downtime window to enable binary logging (requires MySQL restart):
	?
Downtime window to take seed backup (requires server be locked to read only):
	?
</file>

Destination;
<file>
Server/IP:
	DESTNAME 10.1.3.201 (LOCATION)
MySQL Instance:
	3306
System root password/SUDO access:
	ROOTPASS / SUDOUSER/SUDOUSERPASS
MySQL root password:
	?
</file>

==== Source ====

On the source server you will need two SSH sessions opened to it.  The first SSH session will be used to lock the database and obtain the binary log position.  The second SSH session will be used to backup the database while the database is locked.

SSH terminal 1
<file>
[root@SRCSVR ~]# cd /sqldump/
[root@SRCSVR ~]# mysql -p
mysql> stop slave;
[root@SRCSVR ~]# vim /etc/my.conf <-- verify or enable binary logging and verify server id
[root@SRCSVR ~]# service mysqld restart
[root@SRCSVR ~]# mysql -p
mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
mysql> flush tables with read lock;
mysql> show master status;
</file>

While tables are locked via commands in SSH terminal 1, open a new SSH session and run the commands from SSH terminal 2.

SSH terminal 2
<file>
[root@SRCSVR ~]# mysqldump -p DB1 > DB1.sql
[root@SRCSVR ~]# mysqldump -p --all-databases > ALLDB.sql
</file>

Once backup is complete, you can go back to SSH terminal 1 and unlock the tables.

SSH terminal 1
<file>
[root@SRCSVR ~]# mysql -p
mysql> show master status;
mysql> unlock tables;
[root@SRCSVR ~]# vim master-status.txt <-- save master status
[root@SRCSVR ~]# tar zcvf DB1.sql.tar.gz DB1.sql
[root@SRCSVR ~]# tar zcvf ALLDB.sql.tar.gz ALLDB.sql
</file>

==== Destination ====

<file>
[root@DSTSVR ~]# cd /sqlseed
[root@DSTSVR ~]# vim /etc/my.conf <-- verify or enable binary logging and verify server id
[root@DSTSVR ~]# mysqldump -p DB1 > DB1-BAK.sql
[root@DSTSVR ~]# mysqldump -p --all-databases > ALLDB-BAK.sql
[root@DSTSVR ~]# scp root@SRCSVR:/sqldump/DB1.sql.tar.gz ./
[root@DSTSVR ~]# scp root@SRCSVR:/sqldump/ALLDB.sql.tar.gz ./
[root@DSTSVR ~]# scp root@SRCSVR:/sqldump/master-status.txt ./
[root@DSTSVR ~]# tar zxvf DB1.sql.tar.gz
[root@DSTSVR ~]# tar zxvf ALLDB.sql.tar.gz
[root@DSTSVR ~]# mysql -p
mysql> drop database DB1;
mysql> show databases;
mysql> create database DB1;
mysql> show databases;
[root@DSTSVR ~]# mysql -p DB1 < DB1.sql
[root@DSTSVR ~]# mysql -p < ALLDB.sql
[root@DSTSVR ~]# cat master-status.txt
[root@DSTSVR ~]# mysql -p
mysql> change master to master_host='?',master_user='?',master_password='?',master_log_file='?',master_log_pos=?; <-- replace ? with applicable information
mysql> start slave;
mysql> show slave status\G
</file>

==== Config Examples ====

<file>
[mysqld]
# Binary log prefix
log-bin=mysql-bin
# Unique server ID
server-id=1
# InnoDB options
innodb_flush_log_at_trx_commit=1
sync_binlog=1
</file>