Mysql Master-Master with many slaves replication

Sources:
https://www.packtpub.com/books/content/setting-mysql-replication-high-availability
https://www.packtpub.com/books/content/installing-and-managing-multi-master-replication-managermmm-mysql-high-availability
https://capttofu.livejournal.com/1752.html

Using Master<->Master replication is good backup solution, but is not good enough if we want to offload queries from master.

Thus we can create:
Master - Master
| |
Slave-Slave Slave-Slave

1. Setup both masters.
Tweak some options in my.cnf (on all masters!):
server-id = 1
log-slave-updates
log-bin = /var/log/mysql/bin.log
log-bin-index = /usr/local/mysql/var/log-bin.index
log-error = /usr/local/mysql/var/error.log
expire_logs_days = 10
max_binlog_size = 200M

WARNING: log-slave-updates is crucial!!! If not set slaves on second node won't get updated and vice versa if pushed from first master.

2. Add MySQL Users:
mysql> grant replication slave on . to 'replication'@'10.0.0.%' identified by 'pass';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

3. Dump all DBs from master. SCP dump on slave and import it. This way we will have 1:1 dbs on both nodes. Note that you may set password for debian-sys-maint user in /etc/mysql/debian.cnf

On master:
$> mysqldump --delete-master-logs --master-data --lock-all-tables --all-databases --hex-blob -u root -p > dumpall.sql
$> bzip2 dumpall.sql
$> scp dumpall.sql.bz2 root@slave:

NOTICE: --delete-master-logs clears all master logs BEFORE this dump. If you have other slaves syncin' or need earlier binlogs remove this option!

On slave:
$> bunzip2 dumpall.sql.bz2
$> mysql -uroot -p mysql < dumpall.sql

check BIN_LOG and POSITION:

$> grep BIN_LOG dumpall.sql

now login in mysql and change master to:

mysql> change master to master_host = '10.0.0.1', master_user='replication', master_password='pass', master_log_file='node1-binary.000001', master_log_pos=1;
mysql> start slave;

Check if 2nd Master slave is running. Check seconds behind. Should be 0 and Error_* too. Usually this means everything is OK.
mysql> show slave status\G
mysq> show master status;

Now do the same thing on 1st Master. Just use second master bin log and position.

mysql> change master to master_host = '10.0.0.2', master_user='replication', master_password='pass', master_log_file='node1-binary.000001', master_log_pos=1;
mysql> start slave;

Check if 1st Master slave is running. Check seconds behind. Should be 0 and Error_* too. Usually this means everything is OK.
mysql> show slave status\G

Now test create/insert/update/delete.
First on 1st master create table. Insert a record. Check on 2nd master if table is there and has record.
On second master insert second record. Check on 1st if there are 2 records.

4. Create Read-Only Slaves connected to the 1st master and on 2nd:

Simply do same setup as above. Dump DB. populate, then change master to BUT WATCH OUT for the binlog/position!

When done settiing up and slave status shows 0 TEST!

First create table on 1st master, insert 1 record.
Then Check on all slaves connected to 1st master.
After Check all slaves connected to 2nd master!
All MUST have table+record.
After that test to insert second row on 2nd slave.
Then Check on all slaves connected to 1st master.
After Check all slaves connected to 2nd master!


I think that's all!
Happy replicating.

Trackbacks

Trackback specific URI for this entry

This link is not meant to be clicked. It contains the trackback URI for this entry. You can use this URI to send ping- & trackbacks from your own blog to this entry. To copy the link, right click and select "Copy Shortcut" in Internet Explorer or "Copy Link Location" in Mozilla.

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

Add Comment

You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
To leave a comment you must approve it via e-mail, which will be sent to your address after submission.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA