MySQL installation multi-instances: réplication master slave, réplication master master

MySQL multi-instances:

  • Faire une copie du fichier my.cnf  (sous /etc ou /etc/mysql)

scp my.cnf my.cnf.old

  • Modifier le fichier my.cnf

Fichier par défaut:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock

[mysqld]

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
bind-address = 127.0.0.1

log = /var/log/mysql/mysql.log

Ajouter  les instances :

[mysqld1]

user = mysql
pid-file = /var/run/mysqld/mysqld1.pid
socket = /var/run/mysqld/mysqld1.sock
port = 3307
basedir = /usr
datadir = /var/lib/mysql1
bind-address = 127.0.0.1

log = /var/log/mysql1/mysql.log

[mysqld2]

user = mysql
pid-file = /var/run/mysqld/mysqld2.pid
socket = /var/run/mysqld/mysqld2.sock
port = 3308
basedir = /usr
datadir = /var/lib/mysql2
bind-address = 127.0.0.1

log = /var/log/mysql2/mysql.log

  • Relancer Mysql

service mysql restart

  • Créer les répertoires  mysql1 et mysql2

mkdir /var/lib/mysql{1,2}

chown mysql:mysql mysql1 mysql2

  • Créer les répertoires log

mkdir /var/log/mysql{1,2}

chown mysql:mysql mysql1 mysql2

  • Initialiser les datastores

mysql_install_db --user=mysql --datadir=/var/lib/mysql1

mysql_install_db --user=mysql --datadir=/var/lib/mysql2

  • Exécuter les instances

mysqld_multi --verbose --no-log start 1

mysqld_multi --verbose --no-log start 2

  • Pour se connecter à une instance

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld1.sock

  • Pour se connecter à une instance à travers un script php

<?php

$dbuser = 'user';
$dbpass = 'mdp_user';
$dbhost = 'localhost';
$dbport = '3307';
$dbname = 'nom_base';
$connect = new mysqli('localhost', $dbuser, $dbpass, $dbname, $dbport, '/var/run/mysqld/mysqld1.sock');

if ($connect->connect_error) {
die('Erreur de connexion : ' . $connect->connect_error);
}
else {
echo "la connexion est établie";
}

?>

  • Modifier le fichier my.cnf

Ajouter:

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = passwd_multi_admin

  • Relancer Mysql

service mysql restart
Se connecter à la première instance

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld1.sock

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'passwd_multi_admin';

mysql>FLUSH PRIVILEGES;

Faire la même chose pour la deuxième instance

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld2.sock

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'passwd_multi_admin';

mysql>FLUSH PRIVILEGES;

Tester les commandes:

pour vérifier les instances:

mysqld_multi report

Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running

pour lancer ou arrêter toutes les instances:

mysqld_multi start

mysqld_multi stop

pour arrêter une instance

mysqld_multi stop <num_instance>

pour lancer une instance

mysqld_multi start <num_instance>

Réplication MySQL master slave:

  • Configurer le serveur master: (première instance, port 3307)

Modifier le fichier /etc/mysql/my.cnf:

Sous [mysqld1], Ajouter les lignes:

log_bin = /var/log/mysql1/mysql-bin.log

server-id = 1
binlog_do_db = nom_base

Relancer l’instance:

mysqld_multi stop 1

mysqld_multi start 1

Créer l’utilisateur slave (avec les droits de réplication):

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld1.sock

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

Vérouiller la base de données:

mysql> use nom_base;

mysql> FLUSH TABLES WITH READ LOCK;

Vérifier l’état du serveur master  (noter les valeurs File et Position)

mysql> SHOW MASTER STATUS;

Exemple:
+———————–+———-+——————-+———————–+
| File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+———————–+———-+——————-+———————–+
| mysql-bin.000007 | 866         | nom_base           |                                     |
+———————–+———-+——————-+———————–+

mysql> exit;

Effectuer un dump de la base de données :

mysqldump -uuser -ppassword --socket=/var/run/mysqld/mysqld1.sock ---port=3307 nom_base > nom_base.sql

Déverrouiller la base de données:

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld1.sock

mysql> use nom_base;

mysql> UNLOCK TABLES;

mysql> exit;

  • Configurer le serveur slave: (deuxième instance, port 3308)

Créer la base de données sur le serveur slave

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld2.sock

mysql> CREATE DATABASE nom_base;

mysql> exit;

Importer la base de données:

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld2.sock --port=3308 nom_base < nom_base.sql

Modifier le fichier /etc/mysql/my.cnf:

Sous [mysqld2], Ajouter les lignes:

server-id = 2
relay-log = /var/log/mysql2/mysql-relay-bin.log
log_bin = /var/log/mysql2/mysql-bin.log
binlog_do_db = nom_base

Relancer l’instance:

mysqld_multi stop 2

mysqld_multi start 2

Ajouter les informations nécessaires pour la réplication:

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld2.sock

mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=866, MASTER_PORT=3307;

mysql> START SLAVE;

Vérifier la réplication:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 866
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 866
Relay_Log_Space: 1314
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

Réplication MySQL master master:

  • Configurer le serveur master A et le serveur master B

Modifier le fichier /etc/mysql/my.cnf:

Sous [mysqld1], Ajouter les lignes:

log_bin = /var/log/mysql1/mysql-bin.log

server-id = 1
binlog_do_db = nom_base

relay-log = /var/log/mysql1/mysql-relay-bin.log
log_bin = /var/log/mysql1/mysql-bin.log

Relancer l’instance:

mysqld_multi stop 1

mysqld_multi start 1

Sous [mysqld2], Ajouter les lignes:

server-id = 2
relay-log = /var/log/mysql2/mysql-relay-bin.log
log_bin = /var/log/mysql2/mysql-bin.log

binlog_do_db = nom_base

Relancer l’instance:

mysqld_multi stop 2

mysqld_multi start 2

Serveur master A:

  • Créer l’utilisateur slave (avec les droits de réplication)

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld1.sock

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

Vérouiller la base de données:

mysql> use nom_base;

mysql> FLUSH TABLES WITH READ LOCK;

Vérifier l’état du serveur master  (noter les valeurs File et Position)

mysql> SHOW MASTER STATUS;

Exemple:
+———————–+———-+——————-+———————–+
| File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+———————–+———-+——————-+———————–+
| mysql-bin.000007 | 866         | nom_base           |                                     |
+———————–+———-+——————-+———————–+

mysql> exit;

Effectuer un dump de la base de données :

mysqldump -uuser -ppassword --socket=/var/run/mysqld/mysqld1.sock --port=3307 nom_base > nom_base.sql

Déverrouiller la base de données:

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld1.sock

mysql> use nom_base;

mysql> UNLOCK TABLES;

mysql> exit;

Serveur master B

Créer la base de données sur le serveur slave

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld2.sock

mysql> CREATE DATABASE nom_base;

mysql> exit;

Importer la base de données:

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld2.sock --port=3308 nom_base < nom_base.sql

Ajouter les informations nécessaires pour la réplication:

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld2.sock

mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=866, MASTER_PORT=3307;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 866
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 866
Relay_Log_Space: 1314
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

Puis  créer l’utilisateur slave (avec les droits de réplication)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

mysql> SHOW MASTER STATUS;

Exemple:
+———————–+———-+——————-+———————–+
| File                             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+———————–+———-+——————-+———————–+
| mysql-bin.000007 | 866         | nom_base           |                                     |
+———————–+———-+——————-+———————–+

Serveur master A

Ajouter les informations nécessaires pour la réplication:

mysql -uuser -ppassword --socket=/var/run/mysqld/mysqld1.sock

mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=866, MASTER_PORT=3308;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 866
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 866
Relay_Log_Space: 1314
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)

Articles liés