Réplication

une réplication master-slave sert à recopier les données du maître vers l’esclave dans un seul sens.

Pour rendre cette recopie bidirectionnelle, il suffit simplement de créer 2 réplications unidirectionnelles

Dans cet exemple, le master initial est sous Docker, et le slave en install classique.

 

1) Mettre des GRANT pour accès depuis l'autre serveur

 

2) Il faut aussi que chaque serveur ait un server_id défini et différent.

Sur le slave initial:

export server_id=12

mysql> SHOW VARIABLES LIKE 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 12 |

Sur le master initial (Docker):

Lancer le container avec paramètre:

docker run -d --name mysql-docker --volume /var/lib/vz/Docker/volumes/mysqldatas:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD='[PASSWORD]' mysql:5.5 --log-bin=mysql-bin --server-id=10

 

3) Il faut configurer les logs en mode bin:

Rajouter l'option log-bin dans le my.cnf :

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 | 655 | | |

 

Pour le MySQL sous Docker: Il faut lancer le container avec le paramètre --log-bin=mysql-bin

 

4) Sur le Master :

GRANT REPLICATION SLAVE ON *.* TO '[USER]@[IP du slave] IDENTIFIED BY [PASSWORD]';

FLUSH PRIVILEGES;

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

Empty set ...

si "show binary logs;" Il faut configurer les logs en mode binary, voir point 3)

 

5) On fait un dump depuis master / restore sur slave

Sur le master:

Script backup_databases_sauf_mysql.sh:

EXCLUSION_LIST="'performance_schema','information_schema','mysql'"
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -u [USER] -p[PASSWORD] -ANe"${SQLSTMT}"`
do
	MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="-u [USER] -p[PASSWORD] --routines --triggers"
echo mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} 
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > MySQLDatabases.sql

 

Sur le slave:

mysql -h 127.0.0.1 -u [USER] -p[PASSWORD] < MySQLDatabases.sql

 

6) Sur le slave:

STOP SLAVE;

CHANGE MASTER TO MASTER_HOST='[IP DU MASTER]', MASTER_USER='[USER]', MASTER_PASSWORD='[PASSWORD]', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=655;

START SLAVE;

 

7) Retour sur le master:

UNLOCK TABLES;

 

SHOW MASTER STATUS;

SHOW SLAVE STATUS;

On doit retrouver les mêmes références.

 

8) Puis on fait la même chose dans le sens inverse à partir du point 4)

 

Script de resynchronisation des MySQL:


#!/usr/bin/ksh

# resynchroniser la réplication mysql (après un reboot par exemple)

# discretion du login password
# login=
# password=
. $HOME/secrets

tmpfile=$(basename ${0})
tmpfile=${tmpfile%%.*}
tmpfile=/tmp/${tmpfile}.txt

exec 3>&1

exec 1>$tmpfile
exec 2>&1

cat <<EOF
From: Cron Daemon<${USER}@$(hostname).$(dnsdomainname)>
To: root@$(dnsdomainname)
Subject: MySQL <${USER}@$(dnsdomainname)> $0
Content-Type: text/html; charset=iso-8859-15

<html>
<pre>
EOF


master=192.168.1.10
slave=192.168.1.12

# Attendre tant que les services mysql ne sont pas lancés

while (! sudo -u portainer docker ps | grep mysql-docker); do echo Service mysql-docker absent sur $master; sleep 20; done

while (! ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no -q ${login}@${slave} bash -c "'/bin/systemctl status mysql.service | grep running'"); do echo Service mysql absent sur nuc-debian64; sleep 20; done


# SYNCHRO MASTER VERS SLAVE
echo "Sur ${master}: FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS;"
result=$(/usr/bin/mysql -h${master} -u${login}  -p${password} -ANe "FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS;")

master_log_file=$(echo $result | awk '{print $1}' | tr -d '[:space:]')
master_log_position=$(echo $result | awk '{print $2}' | tr -d '[:space:]')

echo "master_log_file : ${master_log_file}"
echo "master_log_position : ${master_log_position}"

# Affichage binary log du slave avant modif
echo Sur ${slave}: SHOW SLAVE STATUS; 
slave_log_file=$(/usr/bin/mysql -h${slave} -u${login}  -p${password} -e "SHOW SLAVE STATUS;" -E | grep "Master_Log_File" | cut -d: -f2 | tail -n1)
slave_log_position=$(/usr/bin/mysql -h${slave} -u${login}  -p${password} -e "SHOW SLAVE STATUS;" -E | grep "Read_Master_Log_Pos" | cut -d: -f2 | tail -n1)

echo "slave_log_file avant: ${slave_log_file}"
echo "slave_log_position avant: ${slave_log_position}"


echo Alignement du slave $slave sur le master $master

echo "Sur ${slave}: STOP SLAVE; CHANGE MASTER TO MASTER_HOST='${master}', MASTER_USER='\${login}', MASTER_PASSWORD='\${password}', MASTER_LOG_FILE='${master_log_file}', MASTER_LOG_POS=${master_log_position};START SLAVE;"

result=$(/usr/bin/mysql -h${slave} -u${login}  -p${password} -e "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='${master}', MASTER_USER='${login}', MASTER_PASSWORD='${password}', MASTER_LOG_FILE='${master_log_file}', MASTER_LOG_POS=${master_log_position};START SLAVE;")

echo "Sur ${master}: UNLOCK TABLES;"
result=$(/usr/bin/mysql -h${master} -u${login}  -p${password} -e "UNLOCK TABLES;")

# Affichage binary log du slave apres modif

slave_log_file=$(/usr/bin/mysql -h${slave} -u${login}  -p${password} -e "SHOW SLAVE STATUS;" -E | grep "Master_Log_File" | cut -d: -f2 | tail -n1 | tr -d '[:space:]')
slave_log_position=$(/usr/bin/mysql -h${slave} -u${login}  -p${password} -e "SHOW SLAVE STATUS;" -E | grep "Read_Master_Log_Pos" | cut -d: -f2 | tail -n1 | tr -d '[:space:]')

echo "slave_log_file apres: ${slave_log_file}"
echo "slave_log_position apres: ${slave_log_position}"

echo ""

# ICI VALIDER LA SYNCHRO SI MEME LOG ET MEME POS
if [ "${master_log_file}" = "${slave_log_file}" ] && [ "${master_log_position}" = "${slave_log_position}" ]
	then
        echo 'La synchro master vers slave est OK'
	else
        echo 'La synchro master vers slave est KO'
	fi

echo ""

STEP=2
if [ "${STEP}" = "2" ]
	then
# SYNCHRO SLAVE VERS MASTER

echo le master est maintenant ${slave}

result=$(/usr/bin/mysql -h${slave} -u${login}  -p${password} -ANe "FLUSH TABLES WITH READ LOCK;SHOW MASTER STATUS;")
master_log_file=$(echo $result | awk '{print $1}' | tr -d '[:space:]')
master_log_position=$(echo $result | awk '{print $2}' | tr -d '[:space:]')

echo "master_log_file : ${master_log_file}"
echo "master_log_position : ${master_log_position}"

# Affichage binary log du slave avant modif
slave_log_file=$(/usr/bin/mysql -h${master} -u${login}  -p${password} -e "SHOW SLAVE STATUS;" -E | grep "Master_Log_File" | cut -d: -f2 | tail -n1)
slave_log_position=$(/usr/bin/mysql -h${master} -u${login}  -p${password} -e "SHOW SLAVE STATUS;" -E | grep "Read_Master_Log_Pos" | cut -d: -f2 | tail -n1)

echo "slave_log_file avant: ${slave_log_file}"
echo "slave_log_position avant: ${slave_log_position}"

echo Alignement du slave $master sur le master $slave
result=$(/usr/bin/mysql -h${master} -u${login}  -p${password} -e "STOP SLAVE; CHANGE MASTER TO MASTER_HOST='${slave}', MASTER_USER='${login}', MASTER_PASSWORD='${password}', MASTER_LOG_FILE='${master_log_file}', MASTER_LOG_POS=${master_log_position};START SLAVE;")

# en cas de desynchronisation non corrigeable
#result=$(/usr/bin/mysql -h${master} -u${login}  -p${password} -e "RESET SLAVE; CHANGE MASTER TO MASTER_HOST='${slave}', MASTER_USER='${login}', MASTER_PASSWORD='${password}', MASTER_LOG_FILE='${master_log_file}', MASTER_LOG_POS=${master_log_position};START SLAVE;")

result=$(/usr/bin/mysql -h${slave} -u${login}  -p${password} -e "UNLOCK TABLES;")

# Affichage binary log du slave apres modif
slave_log_file=$(/usr/bin/mysql -h${master} -u${login}  -p${password} -e "SHOW SLAVE STATUS;" -E | grep "Master_Log_File" | cut -d: -f2 | tail -n1 | tr -d '[:space:]')
slave_log_position=$(/usr/bin/mysql -h${master} -u${login}  -p${password} -e "SHOW SLAVE STATUS;" -E | grep "Read_Master_Log_Pos" | cut -d: -f2 | tail -n1 | tr -d '[:space:]')

echo "slave_log_file apres: ${slave_log_file}"
echo "slave_log_position apres: ${slave_log_position}"


echo ""
# ICI VALIDER LA SYNCHRO SI MEME LOG ET MEME POS
if [ "${master_log_file}" = "${slave_log_file}" ] && [ "${master_log_position}" = "${slave_log_position}" ]
        then
        echo 'La synchro slave vers master est OK'
        else
        echo 'La synchro slave vers master est KO'
        fi
echo ""

fi


exec 1>&3 3>&-


if [ -f $tmpfile ] 
	then
	cat $tmpfile | /usr/sbin/sendmail root@$(dnsdomainname)
	rm -f $tmpfile
	fi