Detect, prepare and restore a tilted Mysql replication

From EN Ikoula wiki
⧼vector-jumptonavigation⧽ ⧼vector-jumptosearch⧽

en:Detect, prepare and restore a tilted Mysql replication he:לזהות, להכין ולשחזר העתק Mysql מוטה ro:Detectarea, pregăti şi restaura o înclinat replicarea Mysql ru:Обнаруживать, готовить и восстановить наклонена репликации Mysql pl:Wykrywanie, przygotować i przywrócić przechylony replikacji Mysql ja:検出し、準備し、傾斜の Mysql レプリケーションを復元 ar:كشف، وإعداد وإعادة تكرار الخلية مائلة zh:检测、 准备和还原倾斜的 Mysql 复制 de:Erkennen, vorzubereiten und eine schräge Mysql-Replikation wiederherstellen nl:Detecteren, het voorbereiden en het herstel van een gekantelde Mysql replicatie it:Rilevare, preparare e ripristinare una replica di Mysql inclinata pt:Detectar, preparar e restaurar uma replicação Mysql inclinada es:Detectar, preparar y restaurar una replicación Mysql inclinada fr:Détecter, préparer et rétablir une réplication Mysql basculée

This article has been created by an automatic translation software. You can view the article source here.

Under Debian 8

You've implemented a system of replication of your Mysql database through a master configuration /slave . When a problem occurs on the master server, this is the slave server (slave) who takes over. However it should be first noted that this scale has occurred.

Detection

  • Depending on your level of.service , you may be a monitoring of the functioning of replication, you are warned by the support in case of toggle.
  • In case of toggle on the master to the slave server server, the ip of the latter will be automatically changed to respond instead of the master server
  • The volume of the Slavic DB will be more important and will contain the latest recorded information.
  • By command on the BDD :

Connect you to the BDD : 

   MySQL  -u votre_utilisateur -p your_password

Then check the status of the master server : 

   show master status syntax  syntax \G

Prepare

Before you perform the commands that follow, plan the operation ! Prefer a timeslot or solicitation of databases is a his minima. Make sure your BDD is not at all modified by also putting your site in maintenance

There must absolutely not be writing on the BDD SLAVE response time !

> In the case of writing during the procedure, here are the consequences :

  • Replicate out of sync
  • Data may be lost or corrupted

Prevent writing

To prevent writing to a database, it must implement the read-only (read-only). Which will have the effect of blocking requests to write to the DB.

On SLAVE 

   FLUSH TABLES WITH READ LOCK ; SET GLOBAL read_only = ONE ;

Create the dump

Once the slave read-only, you can dump its tables safely : On SLAVE 

   MySQL dump --Skip-add-locks  --single-transaction -u votre_utilisateur -p your_password  --all-databases --ignore-table =MySQL.user  > /destination/de/depart/mon/backup.sql

Replace obviously /destination/de/mon/backup.sql by your usual backup destination. By convention the date in format backupDDMMAAAA.sql there in order to find :)

Sending the dump

Insofar as the SLAVE and MASTER servers are remote, you pass the file DUMP of SLAVE to MASTER. There are several ways to do so has this file transfer, but here we will discuss the use of PCs that allows to make copies using the SSH service.

  • login 1 : login ssh machine SLAVE
  • login2 : login ssh machine MASTER
  • Server 1 : IP or Hostname machine SLAVE
  • Server 2 : IP or Hostname machine MASTER
  • Path /File 1 : /destination/de/depart/de/mon/backup.sql
  • Path /File 2 : /destination/darrivee/de/mon/backup.sql
   SCP Login  1@Server 1:Path /File 1 Login2@Server 2:Path /File 2

Import the dump

Then import it in the Master database in order to synchronize the 2 BDD. On MASTER 

   MySQL  -u votre_utilisateur -p your_password  < /destination/de/mon/backup.sql

Restore replication

On the server MASTER: You need information from the master server, then run the command : 

   echo "show master status\G" | MySQL  -u votre_utilisateur -p your_password

Keep these two sideline :

  • File: MySQL-bin.1X1X1X
  • Position : 2X2X2X2X2

On SLAVE Refresh the MASTER_LOG_FILE and MASTER_LOG_POS clauses by the information of the previous command.

  • MASTER_LOG_FILE being File :
  • MASTER_LOG_POS being Position
   slave stop ; CHANGE MASTER TO MASTER_LOG_FILE ='MySQL-bin.1X1X1X', MASTER_LOG_POS =2X2X2X2X2;slave start ;


Testing and finalization

To verify the implementation of this replication use on SLAVE : 

   echo "show slave status \G" | MySQL  -u votre_utilisateur -p your_password

Check these lines :

  • Slave_IO_Running on Yes
  • Slave_SQL_Running on Yes
  • Seconds_Behind_Master has 0

If everything is in implementing replication went well. Remove the read-only on your bdd restriction SLAVE: 

  SET GLOBAL read_only = OFF ;UNLOCK TABLES ;

You can restart all your services using the Bdd. | 

Check their performance through the following command replacing  "votreservice  " by those concerned.

example : your server web Apache : "apache2" your DBMS Mysql : "mysql" 

  systemctl  status "votreservice".service

This documentation was created from internal documentation and the official Mysql documentation



You are not allowed to post comments.

Retrieved from "https://en-wiki.ikoula.com/index.php?title=Detect,_prepare_and_restore_a_tilted_Mysql_replication&oldid=9275"