Indice

First Setup preparation

On all servers of the (future) cluster issue

CREATE USER 'replica'@'%' IDENTIFIED BY 'somethinglongandrandom';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'80.93.25.175' IDENTIFIED BY 'somethinglongandrandom';

Rapeat latest statement substituting 80.93.25.175 with all of your cluster slave servers

Setup/Repair

On all servers issue:

STOP SLAVE;

This is the full step-by-step procedure to resync a master-slave replication from scratch:

At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

And copy the values of the result of the last command somewhere.

Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump -uroot -p --skip-add-locks --all-databases > mysqldump.sql

Now you can release the lock. To do it perform the following command in the mysql client:

UNLOCK TABLES;

Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Lock-out all network connections to ther database to avoid writes before cluster syncronization. You can use iptables and drop all conections to tcp port 3306 exept from localhost (127.0..0.1).

Open a connection to mysql and type:

STOP SLAVE;
RESET SLAVE;

Load master's data dump:

mysql -uroot -p < mysqldump.sql

If you use Debian, it's likely you have overwritten the local debian sysmaint user password. Reset it using the one from the /etc/mysql/debian.cnf file.

The first time issue:

CHANGE MASTER TO
  MASTER_HOST='deimos.units.it',
  MASTER_USER='xxxxxxxxx',
  MASTER_PASSWORD='xxxxxxxxxx',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=106;

subsequent times issue:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;

Where the values of the above fields are the ones you copied before.

Finally type

START SLAVE;

Re enable network connections to other cluster nodes.

And to check that everything is working again, if you type

SHOW SLAVE STATUS \G;

you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

That's it!

Don't forget to re-enable network connections to clients!

What's next

For each new server (or on the first server if you need multimaster or circular replication) do the

STOP SLAVE;

on its source, without closing connection afterward, issue

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

on the new server:

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106; 
START SLAVE;

check with

SHOW SLAVE STATUS \G;

on its source don't forget to issue

 UNLOCK TABLES;