Since
MySQL Connect is just around the corner and
several of the sessions are related to the
replication features in MySQL 5.6, I figured I would put together a quick MySQL Replication 101 Overview for any of the new users.
Topology
First you must decide what type of Topology will be serve you and your applications needs. Below are a list of typical topologies. For this blog post example we will set up a couple of versions.
Single Master and on slave:
Single Master and muliple slaves:
Circular:
Chain:
MySQL Configuration file edits
MySQL 5.6 will allow the us to
take advantage of the database for our repository information. The "
master-info-repository=TABLE" and the "
relay-log-info-repository=TABLE" reference allows this. A unique value is required for server id per server.
Master my.cnf
# vi /etc/my.cnf
[mysqld]
server-id=1
log-bin = /var/lib/mysql/yoda-bin
master-info-repository=TABLE
[mysql]
prompt=mysql_yoda>\\_
Start and Log into MySQL
master_yoda>show master status\G
*************************** 1. row ***************************
File: yoda-bin.000001
Position: 114
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
SQL
mysql_yoda> show variables like 'master_info_repository';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| master_info_repository | TABLE |
+------------------------+-------+
mysql_yoda>CREATE USER 'replication'@'192.168.0.%' IDENTIFIED BY 'slavepass';
mysql_yoda>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
mysql_yoda>flush privileges;
Now lets create a copy of the database to start from.
# mysqldump -p --all-databases --master-data=2 > /tmp/replication_example.sql
THIS LOCKS THE DATABASE!
mysqldbexport --server=root@server1:3306 --format=csv db1 --export=data
If you do not want to lock the database as you make a copy, Oracle offers
MySQL Enterprise Backup You can test this for 30 days as well for free.
Remember to adjust all firewall rules if required for MySQL Port. (
3306)
SLAVE my.cnf
vi /etc/my.cnf
[mysqld]
Server-id=2
relay-log=/var/lib/mysql/luke-relay-bin
relay-log-info-repository =TABLE
[mysql]
prompt=mysql_luke>\\_
# mysql --user=root -p < /tmp/replication_example.sql
SLAVE SQL
mysql_luke> CHANGE MASTER TO
MASTER_HOST='yoda',
MASTER_USER='replication',
MASTER_PASSWORD='slavepass',
MASTER_PORT=3306,
MASTER_LOG_FILE='yoda-bin.000002',
MASTER_LOG_POS=83415,
MASTER_CONNECT_RETRY=10;
mysql_luke> start slave;
We gathered this info from the mysqldump file via the “ --
master-data=2 ” flag.
mysql_luke> show slave status\G
mysql_luke> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: yoda
Master_User: replication
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: yoda-bin.000003
Read_Master_Log_Pos: 323
Relay_Log_File: luke-relay-bin.000004
Relay_Log_Pos: 475
Relay_Master_Log_File: yoda-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_Server_Id: 1
Master_UUID: 75d407df-2be4-11e1-9668-b4be9bce39b0
Seconds_Behind_Master: 0
SQL_Remaining_Delay: NULL
Now we also have some alternative options to the above set up.
-- If you can afford to have both the master and slave database offline,
MySQL Workbench comes with
MySQL Utilties (also via
launchpad) which has
mysqlreplicate
mysqlreplicate \ --master=root@master.example.com \ --slave=root@slave.example.com \ --rpl-user=repl:xyzzy
-- If you wanted to create a Chain or Circular topology, the slave ( or master 2 ) will also need a bin log which I have added below. Then you can set up another slave that is using "Luke", per the example, as the master.
SLAVE my.cnf
vi /etc/my.cnf
[mysqld]
Server-id=2
relay-log=/var/lib/mysql/luke-relay-bin
relay-log-info-repository =TABLE
log-bin = /var/lib/mysql/luke-bin
To adjust the server examples above to a Circular Topology we will need to make a few more variable edits. The edits below will prevent index collisions.
SQL
mysql_yoda> SET GLOBAL auto_increment_offset=2;
mysql_yoda> SET GLOBAL auto_increment_increment=1;
mysql_luke> SET GLOBAL auto_increment_offset=2;
mysql_luke> SET GLOBAL auto_increment_increment=2;
mysql_yoda>show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 1 |
+--------------------------+-------+ mysql_luke>show global variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 2 |
| auto_increment_offset | 2 |
+--------------------------+-------+
mysql_luke>SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: luke-bin.000005
Position: 295
mysql_yoda>CHANGE MASTER TO
MASTER_HOST='luke',
MASTER_USER='replication2',
MASTER_PASSWORD='slavepass',
MASTER_PORT=3306,
MASTER_LOG_FILE='luke-bin.000005',
MASTER_LOG_POS=295,
MASTER_CONNECT_RETRY=10;
mysql_yoda> start slave;
This was a simple overview but it gets you started. If you would like to dig further, below is a list of related MySQL 5.6 and replication urls.
