1. Adam Dutko
  2. Portfolio

Source

Portfolio / hacktuts / mariadb / innodb_cluster_snap.txt

MyISAM/InnoDB Cluster in a Snap
===============================

NOTE:
This is a very basic setup with a single master and single slave. It is for
a new setup without existing data.

1) Configure Master
Add the following to the file /etc/my.cnf

[mysqld]
log-bin=mysql-bin # enables binary logging
server-id=1 # identifies this node as the primary/master
#innodb_flush_log_at_trx_commit=1 # good for InnoDB
#sync_binlog=1 # good for InnoDB

Then restart the master node.

2) Setup Replication User
Each slave node requires a user account to use to login to the master. The account needs to have the
'REPLICATION SLAVE' privilege. You have the choice of using a general account and granting it this
privilege or creating a specific account for replication. You can also choose to use a single account for all
nodes or one for each. I prefer to use distinct users for each slave node and keep them seperate from
other user accounts. I find this approach helps with auditing and logging. Here is an example of what you
need to do on the master:

mysql> CREATE USER 'node0_replicator'@'%.magic.monkey.com' IDENTIFIED BY 'adam';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'node0_replicator'@'%.magic.monkey.com';

3) {NOT REQUIRED} Confirm Master Status
For setups with existing data or for "fancier" setups where replicants are responsible for particular tables
or rows you need to locate the log pointer on the master. Because this approach using binary logging
tracking and replication the "log pointer" is where the file pointer for writes is located. That is the location
where your slaves will either start to replicate from if they have all previous data or where they will start to
replicate to then continue from once they've "caught up" with replication work. In this setup we don't need
to worry about it, but if you wanted to you can find it by first issuing a flush and lock:

mysql> FLUSH TABLES WITH READ LOCK;

You can then get the "log pointer" location by issuing the following:

mysql> SHOW MASTER STATUS;

The location is under the column entitled "Position." Once you have this you'll need to do more to get the
replicants in sync. That "more" can be found in the MySQL manual here or here.

4) Configure Replication Node(s)
Before configuring nodes make sure you unlock the tables on the master with the following:

mysql> UNLOCK TABLES;

Then adjust the /etc/my.cnf configuration on each node with:

[mysqld]
server-id=2 # the id of the replicant - should NOT be the same as the master

Once this is done, restart the replicant.

5) Adjust the Master on the Replicant(s)
Once the replicant is ready you need to change it's master from "local" to the master you setup in step #1.
You can do this by using a variaton of the following command on each node:

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name_from_step_#1',
-> MASTER_USER='replicant_user_name_for_this_node',
-> MASTER_PASSWORD='replicant_password_for_this_node',
-> MASTER_LOG_FILE='mysql-bin.######';

The master log file can be found on the master under the MySQL data directory. It is important to specify
because it is the log the replicant will "replay" to determine what it needs to do locally to copy the master data.