Mysql 5.0 in configurazione double Master

Molti che lavorano regolarmente con MySQL prima o poi hanno installato o provato a giocherellare con il setup di una configurazione Master-Slave, che permette di avere una replica del Master su di un secondo server. Seguendo il manuale MySQL non è difficile, un pò di attenzione per i dettagli ed il più è fatto. Ho avuto poi la necessità di scambiare il ruolo dei due server, infatti perchè si mantiene uno Slave se si ha un Master? Sia per un backup dei dati (ma basterebbe un mysqldump e la copia del risultato) ma anche per avere bello e pronto un server con tutti i dati, che possa accettare connessioni, mantenendo il servizio attivo nel caso in cui il Master abbia problemi. Ovviamente se questo succede, si usa lo Slave, che però, per sostituire il master deve accettare anche Update ed Insert ai database replicati, desincronizzandosi irrimediabilmente con il Master. Questo porta a dover fare poi un intervento manuale, quando il Master sarà nuovamente pronto per riprendere il suo ruolo. Quindi dovrà riimportare l’ultima copia del Database ora mantenuto dallo Slave, e lo slave dovrà essere risincronizzato con il Master, accettando nuovamente le modifiche dal Master per mantenersi sincronizzato. In aiuto a questo viene la configurazione Double Master, che solo da poco so essere possibile. Anche qui i passi non sono molti ma va data attenzione ai dettagli. In pratica il risultato è che si gestiscono sempre 2 server, chiamiamoli mysql1 e mysql2, ed allo stesso tempo:

- mysql1 è master di mysql2
- mysql2 è master di mysql1

A mia volta ho seguito un tutorial trovato su rete (http://lug.wsu.edu/node/545), ma ho avuto dei problemi e credo che alcuni passaggi possano funzionare solo in taluni casi. Per questo riporto la mia versione della procedura, che credo essere più pulita e con più possibilità di successo, facendo i miei commenti e le mie modifiche alla versione linkata!
I miei commenti sono fra parentesi tonde ed in bold (my comments are between round brachets and in bold).


(First and most important I was unable to make all working, having an error like:
could not find target log during relay log initialization as soon as I did not clean all the logs.
So at some stage, like when you dump your main database, I advise to reset all the binary logs, of any kind.
Starting with a new mysql-bin.000001 was my way to make all working. Do it with both, Master and Slave)

# $Id: mysql-replication 335 2005-10-13 19:46:20Z sbalukoff $
# To set up bi-directional mysql replication:

# For the purposes of this document, we have two servers which will in the
# end be filling a co-master type relationship. However, since when you set up
# this replication there will probably be one machine with all the data on it
# that needs to be brought into sync with the other machine, the machine
# with all the data will be considered the ‘master’, and the one being brought
# into the relationship will be considered the ‘slave’. Again, don’t let
# the names fool you: We’re only using ‘master’ and ‘slave’ here to
# differentiate the machines. In the end they will be in a true co-master
# relationship.

# Before you begin:
#
# Make sure that /etc/my.cnf is set to have binary and relay logging turned on
# on both the slave and the master. (Verify the log files actually exist if
# they weren’t set up with this, you will need to restart the server process once
# the config file has been changed.) Pertinent config lines should look like:
#
# # This stuff is needed for replication purposes
# server-id=2
# log-bin=/var/lib/mysql/master-bin.log
# log-bin-index = /var/lib/mysql/master-log-bin.index
# log-error = /var/lib/mysql/master-error.log
#
# relay-log = /var/lib/mysql/slave-relay.log
# relay-log-info-file = /var/lib/mysql/slave-relay-log.info
# relay-log-index = /var/lib/mysql/slave-relay-log.index
#
(this configuration is important, even if you notice relay and/or slave related log files, double-check
to have those entries in both servers)

# In the above, the master and slave must have different server-id’s.

# Make sure these machines’ firewalls are set up to allow them talk to each
# other on tcp port 3306, or wherever your mysql daemon is listening.

# Set up the replication user on the master:
#
sudo mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replicator’@'%.cea.wsu.edu’ IDENTIFIED BY ‘replication_password’;
mysql> GRANT REPLICATION CLIENT ON *.* TO ‘replicator’@'%.cea.wsu.edu’;
mysql> GRANT SUPER ON *.* TO ‘replicator’@'%.cea.wsu.edu’;
mysql> GRANT RELOAD ON *.* TO ‘replicator’@'%.cea.wsu.edu’;
mysql> GRANT SELECT ON *.* TO ‘replicator’@'%.cea.wsu.edu’;

# Get a clean snapshot of the master’s data:
mysql> FLUSH TABLES WITH READ LOCK;
# On the shell:
sudo tar cvf /tmp/mysql.bak /var/lib/mysql
# on my mysql prompt:
mysql> SHOW MASTER STATUS;
# Take note of what is said! You’ll need to enter the log file name and position later on.
mysql> UNLOCK TABLES;

# Copy the snapshot to the slave, and slurp it into the database:
scp /tmp/mysql.bak user@slave-server:/tmp
# on the slave
sudo /etc/init.d/mysqld stop
cd /
sudo tar xvf /tmp/mysql.bak
sudo /etc/init.d/mysqld start


(I advise to use another method to create a copy of the database, copying all the directory copies some
files like *.info that should not be the same in the two servers. My way was:


in mysql1 (Master)> FLUSH TABLES WITH READ LOCK;
in mysql2 (Slave) #mysqldump -A -e -u root > /tmp/copy_dump.sql
in mysql1 (Master)> SHOW MASTER STATUS;
in mysql1 (Master)> UNLOCK TABLES;


copy the /tmp/copy_dump.sql to the slave

from mysql1 (Master) #scp /tmp/copy_dump.sql root@slave:/tmp/


On the slave restore the dump
from mysql2 (Slave)>\. /tmp/copy_dump.sql

)

# Start replication from master to slave:
# on the slave’s mysql command line as root:
mysql> CHANGE MASTER TO MASTER_HOST=’master_host_name’,
-> MASTER_USER=’replicator’,
-> MASTER_PASSWORD=’replication_password’,
-> MASTER_LOG_FILE=’recorded_log_file_name’,
-> MASTER_LOG_POS=recorded_log_position;

(A Note about the Password! I was using server and clients from the version 5.0.24,
the password in the MASTER_PASSWORD directive clear but
when setting up the corresponding user’s password in the Master server,
I was using Password=OLD_PASSWORD(‘plain_password_text’)
)

mysql> START SLAVE;

# Test the replication by creating a dummy table on the master… it should be
# created on the slave

# On the master:
mysql> USE test;
mysql> CREATE TABLE foo (id INT);

# On the slave;
mysql> USE test;
mysql> SHOW TABLES;
# you should see the ‘foo’ table, eh.

# Start replication from slave to master:
# First, ensure that no updates are being made to the slave, by ensuring
# that no clients are connected to it, eh. Do this however you see fit.
# Shutting down any local clients and adding firewall rules to prevent anyone but
# the master from connecting should do it. Updates to the master can continue while
# you do this.

# Get the ‘master’ status from the slave:
# on the slave’s mysql console:
mysql> SHOW MASTER STATUS;
# Make note of what you see! Again, gonna need that logfile and position in the next step…

# Add the replication configuration to the master.
# On the master’s mysql command line as root:
mysql> CHANGE MASTER TO MASTER_HOST=’slave_host_name’,
-> MASTER_USER=’replicator’,
-> MASTER_PASSWORD=’replication_password’,
-> MASTER_LOG_FILE=’recorded_log_file_name’,
-> MASTER_LOG_POS=recorded_log_position;

# Start replication on the master:
# on the master:
mysql> START SLAVE;

# Test bi-directional data flow by creating something on one server, and
# deleting it on the other, eh. (Or just delete that ‘foo’ table we made
# earlier.)

# Slave:
mysql> use test;
mysql> drop table foo;

# Master:
mysql> use test;
mysql> SHOW TABLES;

# Test restarting each server individually, and then together
# to ensure there are no errors that show up in the logs when you do this.

# Be sure the check out the output of the following, on both master and slave:
mysql> SHOW SLAVE STATUS \G
# If you don’t see the following in the list on both systems, you’ve got problems:
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes

# Enjoy the glow!