Sv translation | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
IntroductionThis page describes the process of rebuilding a Slave-DB & Replication. Since the MySQL Dump command locks the tables, there is no need to create it when there is no traffic on the machine. It can be done during operational hours. With --master-data the command mysqldump stores the correct position for inserting the replication on the slave server.
Step-By-Step Guide
STOP SLAVELogin to the Slave Database MySQL and stop the slave SQL. Leave MySQL again afterwards. Use the following commands for this:
Phase 1 - MySQL DumpA MySQL Dump of the master database is now created. Perform the following steps to create a MySQL Dump and save it to the STORE:
jtel Portal software releaseLog in to the Load Balancer of the cluster and execute the following commands as the
Create Backup Directory
Create MySQL Dump
To Check for MySQL Version, log in to the master database server and execute the following command
MySQL Dump - Until jtel Portal release 3.12
MySQL Dump - From jtel Portal release 3.12 until latest release
MySQL Dump - From jtel Portal release 3.12,until 3.14 and 3.15 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||
If someone logs on to the portal while the dump is being pulled, it will go wrong. Enclosed a SQL query. If the time changes after executing the query, a login has taken place. If this happens, the dump has to be pulled again and in the meantime it has to be permanently checked if a login has taken place. Only if this is not the case, the dump can be replicated error-free to the slave. SELECT Max(dtAcdLoggedIn) FROM Users; In versions 3.11 and below and version 3.16 this problem does not exist. |
Code Block |
---|
# change to jtel user
su jtel
# Change to backup directory
cd /srv/jtel/shared/backup/${JT_DATE_TIME}
# Create MysQL Dump
mysqldump -uUSER -pPWD -h<IP-Address-OR-Alias-GOOD-MASTER> --single-transaction --master-data=1 --databases JTELWeb JTELStats JTELStats2 JTELLog --add-drop-database --add-drop-table --events --routines --triggers > /srv/jtel/shared/backup/${JT_DATE_TIME}/acd-dbm_<yyyymmdd>.sql |
MySQL Dump - From Release 3.32
Code Block |
---|
# change to jtel user
su jtel
# Change to backup directory
cd /srv/jtel/shared/backup/${JT_DATE_TIME}
# Create MysQL Dump
mysqldump -uUSER -pPWD -h<IP-Address-OR-Alias-GOOD-MASTER> --single-transaction --master-data=1 --databases JTELWeb JTELStats JTELStats2 JTELLog JTELCustomer --add-drop-database --add-drop-table --events --routines --triggers > /srv/jtel/shared/backup/${JT_DATE_TIME}/acd-dbm_<yyyymmdd>.sql |
MySQL Dump - From MySQL 8.0.27
Code Block |
---|
# change to jtel user
su jtel
# Change to backup directory
cd /srv/jtel/shared/backup/${JT_DATE_TIME}
# Create MysQL Dump
mysqldump -uUSER -pPWD -h<IP-Address-OR-Alias-GOOD-MASTER> --single-transaction --source-data --databases JTELWeb JTELStats JTELStats2 JTELLog --add-drop-database --add-drop-table --events --routines --triggers > |
MySQL Dump - From MySQL 8.0.27
To Check for MySQL Version, log in to the master database server and execute the following command
Code Block |
---|
mysql --version |
Code Block |
---|
# Change to backup directory
cd /srv/jtel/shared/backup/DATUM
# Create MysQL Dump
mysqldump -uUSER -pPWD -h<Alias or IP-Adress of master database> --single-transaction --source-data --databases JTELWeb JTELStats JTELStats2 JTELLog --add-drop-database --add-drop-table --events --routines --triggers > acd-dbm_<yyyymmdd>.sql |
Brief Explanation of the mysqldump command
<Name LogFile> and <Position LogFile> are saved through --master-data or --source-data in the dump
use only the JTEL databases '--databases'
delete all databases before import '--add-drop-database'
delete all tables of the databases before import '--add-drop-table'
Implement all routines and procedures in the dump '--events --routines'
Additionally the parameter --default_character_set utf8 can be used
Phase 2 - Import Dump on Slave Database
Info |
---|
The following commands are designed to be run from the Load Balancer as root user. After the source <acd-dbm_<yyyymmdd>.sql> is executed, the dump will start importing into the slave database. Once it is finished, the Slave SQL is started. Brief explanation - tmux: tmux is a tool used to create sub-sessions within a ssh terminal. The tool is a good option for this specific operation, since the sub-session can be exited but the processes which were started will still be running in the background. This prevents failure by accidental loss of the ssh terminal, and provides the ability to view the MySQL process list to check the progress of the import Tmux cheat sheet: https://tmuxcheatsheet.com/ |
Import Dump - tmux
Code Block |
---|
# switch to root user su root # start tmux sub-session tmux new # change to the directory where the dump file is located cd /srv/jtel/shared/backup/${JT_DATE/ # copy the name ls -als # log in to mysql and import the dump mysql -uroot -p<password> source <acd-_TIME}/acd-dbm_<yyyymmdd>.sql> |
Import Dump - No tmux
Code Block |
---|
# switch to root user
su root
# change to the directory where the dump file is located
cd /srv/jtel/shared/backup/DATE/
# copy the name
ls -als
# log in to mysql and import the dump
mysql -uroot -p<password>
source <acd-dbm_<yyyymmdd>.sql> |
Start Slave - tmux
Code Block |
---|
# If not attached to the sub-session anymore
tmux attach
# Start Slave SQL
START SLAVE;
# Check Slave Status
SHOW SLAVE STATUS \G |
Start Slave - No tmux
Code Block |
---|
# Start Slave SQL
START SLAVE;
# Check Slave Status
SHOW SLAVE STATUS \G |
Slave Disk Full
There are several reasons why the slave disk can become full. Following is a summary of the different cases and further below is a detailed explanation:
ibdata files on the slave "very large"
Still no disk space > 20% free
tmp Directory Is Full
Introduction
Every time a query creates a tmp table, it is written to the temp directory, usually /tmp. This happens when the maximum size exceeds the maximum "in memory" table size. This is defined with the variables tmp_table_size
aswell as max_heap_table_size .
The tables in /tmp are kept until the respective DB connection is closed or a DROP TEMPORARY TABLE is called. If the /tmp directory is full, it is likely that a DROP TEMPORARY TABLE is missing somewhere. This can also happen through customer queries to the DB.
The installation of tmpwatch creates help in a permanent way
Hints:
- On CentOS 6 the /tmp directory is then by default freed from files that have not been accessed for > 10 days
- Circumstantially this may not be sufficient
- On CentOS 7 the /tmp directory is then freed by default from files that have not been accessed for> 1 day
Info |
---|
See also https://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html for more information. |
Procedure
- Space must first be made available. Just mercilessly delete everything from /tmp.
- If this provides enough space, then restart the mysql service first: service mysql restart
- If there is enough space (at least about 20%), then proceed with replication as described above.
Lots of "relay logs" available
Introduction
MySQL first writes the relay logs from the master to a file. Once replication is interrupted, but the slave relay process continues to operate, the disk is filled by relay logs.
This step should be done in any case, especially before the next one (ibdata too large) to make room.
Procedure
The files for the database are usually located in /var/lib/mysql
If not, the location can be found in /etc/my.cnf The corresponding entry is datadir=(pfad)
Delete all relay logs:
Info |
---|
cd /var/lib/mysql |
Restart MySQL service
Code Block |
---|
service mysqld restart |
If enough space is available (at least 20%) then proceed with the slave recovery as described above.
ibdata files on the slave "very large"
Introduction
The files for the database are usually located in /var/lib/mysql
Procedure
If no less than 100% disk can be achieved by the steps above:
Unsubscribe the MySQL service from the autostart:
Code Block |
---|
systemctl disable mysqld |
Restart the computer with :
Code Block |
---|
systemctl reboot now |
Only works if less than 100% disk is reached (possibly after reboot above) then
Log on to the mysql server:
Code Block |
---|
mysql -u root -p |
Drop all JTEL databases:
Code Block |
---|
SET FOREIGN_KEY_CHECKS=0;
DROP DATABASE JTELLog;
DROP DATABASE JTELStats;
DROP DATABASE JTELWeb;
SET FOREIGN_KEY_CHECKS=1; |
Press CTRL+C to return to the command line, and then
Code Block |
---|
service mysqld stop
rm /var/lib/mysql/ibdata*
rm /var/lib/mysql/ib_log_* |
Start MySql Server, and enable it again if necessary:
Code Block |
---|
service mysqld start
service mysqld enable |
Check disk space, and proceed with Slave Restore as described above.
Still no disk space > 20% free
Introduction
The slave disk space is still full, more than 80% of the space on the drive is used by normal operation
Solution
In this case the slave is simply too small. The hard disk must be expanded (as with extending the STORE role, apply only to the logical volume where the MySQL database data resides). Then perform the steps again, if the disk is too small, then restore the slave as described above.
Or the slave is completely rebuilt with a larger plate.
language | de |
---|
sql |
Brief Explanation of the mysqldump command
<Name LogFile> and <Position LogFile> are saved through --master-data or --source-data in the dump
use only the JTEL databases '--databases'
delete all databases before import '--add-drop-database'
delete all tables of the databases before import '--add-drop-table'
Implement all routines and procedures in the dump '--events --routines'
Additionally the parameter --default_character_set utf8 can be used
Phase 2 - Import Dump on Slave Database
Info |
---|
The following commands are designed to be run from the STORE as root user. After the source <acd-dbm_<yyyymmdd>.sql> is executed, the dump will start importing into the slave database. Once it is finished, the Slave SQL is started. Brief explanation - tmux: tmux is a tool used to create sub-sessions within a ssh terminal. The tool is a good option for this specific operation, since the sub-session can be exited but the processes which were started will still be running in the background. This prevents failure by accidental loss of the ssh terminal, and provides the ability to view the MySQL process list to check the progress of the import Tmux cheat sheet: https://tmuxcheatsheet.com/ |
Import Dump - tmux
Code Block |
---|
# switch to root user
sudo -s
# start tmux sub-session
tmux new
# change to the directory where the dump file is located
JT_DATE_TIME=$(date +%F)
cd /srv/jtel/shared/backup/${JT_DATE_TIME}/
# copy the dump-filename
ls -als
# log in to mysql and import the dump
mysql -hacd-dbs -uroot -p<password>
source <acd-dbm_<yyyymmdd>.sql>; |
Import Dump - No tmux
Code Block |
---|
# switch to root user
sudo -s
# change to the directory where the dump file is located
JT_DATE_TIME=$(date +%F)
cd /srv/jtel/shared/backup/${JT_DATE_TIME}/
# copy the dump-filename
ls -als
# log in to mysql and import the dump
mysql -hacd-dbs -uroot -p<password>
source <acd-dbm_<yyyymmdd>.sql>; |
Check status
You can check the progress by executing the following command on acd-dbs
Code Block |
---|
watch 'mysql -uroot -p<password> -e "SHOW PROCESSLIST;"' |
Start Slave - tmux
Code Block |
---|
# If not attached to the sub-session anymore
tmux attach
# Start Slave SQL
START SLAVE;
# Check Slave Status
SHOW SLAVE STATUS \G |
Start Slave - No tmux
Code Block |
---|
# Start Slave SQL
START SLAVE;
# Check Slave Status
SHOW SLAVE STATUS \G |
Slave Disk Full
There are several reasons why the slave disk can become full. Following is a summary of the different cases and further below is a detailed explanation:
Case |
---|
Temp directory full |
Lots of "relay logs" available |
ibdata files on the slave "very large" |
Still no disk space > 20% free |
tmp Directory Is Full
Introduction
Every time a query creates a tmp table, it is written to the temp directory, usually /tmp. This happens when the maximum size exceeds the maximum "in memory" table size. This is defined with the variables tmp_table_size
aswell as max_heap_table_size .
The tables in /tmp are kept until the respective DB connection is closed or a DROP TEMPORARY TABLE is called. If the /tmp directory is full, it is likely that a DROP TEMPORARY TABLE is missing somewhere. This can also happen through customer queries to the DB.
The installation of tmpwatch creates help in a permanent way
Hints:
- On CentOS 6 the /tmp directory is then by default freed from files that have not been accessed for > 10 days
- Circumstantially this may not be sufficient
- On CentOS 7 the /tmp directory is then freed by default from files that have not been accessed for> 1 day
Info |
---|
See also https://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html for more information. |
Procedure
- Space must first be made available. Just mercilessly delete everything from /tmp.
- If this provides enough space, then restart the mysql service first: service mysql restart
- If there is enough space (at least about 20%), then proceed with replication as described above.
Lots of "relay logs" available
Introduction
MySQL first writes the relay logs from the master to a file. Once replication is interrupted, but the slave relay process continues to operate, the disk is filled by relay logs.
This step should be done in any case, especially before the next one (ibdata too large) to make room.
Procedure
The files for the database are usually located in /var/lib/mysql
If not, the location can be found in /etc/my.cnf The corresponding entry is datadir=(pfad)
Delete all relay logs:
Info |
---|
language | fr |
---|
Reconstruire la BD-esclave et Réplication
Comme la commande mysqldump verrouille les tables, il n'est pas nécessaire qu'il n'y ait pas de trafic sur la base de données principale. Avec --master-data, la commande mysqldump enregistre la position correcte pour l'insertion de la réplication sur le serveur esclave.
Si le disque dur de l'esclave est plein, veuillez vous référer aux instructions sur cette page ci-dessous - "Disque d'esclave complet".
Connexion à serveur esclavesConnectez-vous à MySQL avec les identifiants USER et PWD, puis arrêtez l'esclave et quittez MySQL à nouveau. Utilisez pour cela les commandes suivantes :
Translations Ignore |
---|
|
Redémarrez le serveur MySQL avec la commande suivante :
Sur le serveur CentOS: service mysqld restart
- Sur le serveur Debian: service mysql restart
Écrire un dump MySQL. Exécutez maintenant les étapes suivantes sur le serveur maître. Créez un répertoire de sauvegarde et changez pour le même. Le dumb MySQL est maintenant exécuté avec la commande suivante
mysqldump -uUSER -pPWD --single-transaction --master-data=2 --databases JTELWeb JTELStats JTELLog --add-drop-database --add-drop-table --events --routines --triggers > filename.sql
A partir de la version 3.12, veuillez utiliser la commande suivante :
mysqldump -uUSER -pPWD --single-transaction --master-data=2 --databases JTELWeb JTELStats JTELStats2 JTELLog --add-drop-database --add-drop-table --events --routines --triggers > filename.sql
Voici une brève explication à ce sujet :
Warning |
---|
Dans les versions 3.12, 3.14 et 3.15 Si quelqu'un se connecte au portail pendant que le vidage est extrait, cela va mal tourner. J'ai joint une requête SQL. Si l'heure change après l'exécution de la requête, une connexion a eu lieu. Si cela se produit, la décharge doit être à nouveau retirée et, dans l'intervalle, il faut vérifier en permanence si une connexion a eu lieu. Si ce n'est pas le cas, le dépôt peut être reproduit sans erreur à l'esclave. SELECT Max(dtAcdLoggedIn) FROM Users; Dans les versions 3.11 et inférieures et la version 3.16, ce problème n'existe pas. |
Utilisez l'outil de votre choix : ligne de commande (scp), WinSCP, ...
Nous retournons à la serveur esclave et maintenant importer mysqldump.
Translations Ignore |
---|
|
- Dans le fichier mysqldump, au tout début, nous trouvons une ligne dans le bloc de commentaires qui ressemble à ceci :
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000872', MASTER_LOG_POS=11940974;Mémoriser les paramètres
MASTER_LOG_FILE
etMASTER_LOG_POS
. - Nous avons besoin du nom ou de l'adresse IP du serveur principal de la base de données
- Nous avons besoin du mot de passe de l'utilisateur
repl
sur le serveur de base de données principal. Généralement, c'est always le Mot de passe Utilisateur root - la façon la plus simple de l'essayer est de se connecter de l'esclave au maître avec ce mot de passe :mysql -h <masterIP> -u repl -p<Passwort>
- si cela fonctionne, alors vous savez.
mysql -uUSER -pPWD
CHANGE MASTER TO MASTER_HOST='<name or ip of master servers>',MASTER_USER='repl',MASTER_PASSWORD='<passwort>',MASTER_LOG_FILE='<Name LogFile>', MASTER_LOG_POS=<Position LogFile>;
Ici, tous les paramètres doivent être remplacés par ceux de l'étape 5.
Translations Ignore |
---|
|
Maintenant, nous vérifions le statut de l'esclave (plusieurs fois) avec la commande
Translations Ignore |
---|
|
Slave_IO_Running: Oui
Slave_SQL_Running: Oui
Disque d'esclave complet
Il y a plusieurs raisons pour lesquelles le disque esclave peut devenir plein.
le répertoire tmp est complet
Informations générales
Chaque fois qu'une requête crée une table tmp, elle est écrite dans le répertoire temporaire, généralement/tmp. Cela se produit lorsque la taille maximale dépasse la taille maximale de la table "en mémoire". Ceci est défini avec les variables tmp_table_size
aussi bien que max_heap_table_size .
See also https://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html pour plus d'informations.
Les tables dans / tmp sont conservées jusqu'à ce que la connexion DB respective soit fermée ou qu'une DROP TEMPORARY TABLE soit appelée. Si le répertoire / tmp est plein, il est probable qu'un DROP TEMPORARY TABLE manque quelque part. Cela peut également se produire via des requêtes client adressées à la base de données.
L'installation de tmpwatch crée de l'aide de façon permanente
Indices:
- Sous CentOS 6, le répertoire /tmp est alors par défaut libéré des fichiers qui n'ont pas été consultés pour > 10 jours
- Cela peut ne pas être suffisant
- Sous CentOS 7, le répertoire /tmp est alors libéré par défaut des fichiers qui n'ont pas été consultés depuis > 1 jour
Procédure
- Un espace doit d'abord être mis à disposition. Il suffit de tout supprimer impitoyablement dans /tmp.
- Si cela permet de disposer de suffisamment d'espace, il faut d'abord redémarrer le service mysql : service mysql restart
- S'il y a suffisamment d'espace (au moins environ 20 %), il faut alors procéder à la réplication comme décrit ci-dessus.
Beaucoup de "journaux de relais" disponibles
MySQL écrit d'abord les journaux de relais du maître dans un fichier. Une fois que la réplication est interrompue, mais que le processus de relais esclave continue à fonctionner, le disque est rempli par les journaux de relais.
Cette étape doit être effectuée dans tous les cas, surtout avant la prochaine (ibdata trop grande) pour faire de la place.
Procédure
Les fichiers de la base de données se trouvent généralement dans /var/lib/mysql
Si ce n'est pas le cas, le lieu peut être trouvé dans /etc/my.cnf L'entrée correspondante est datadir=(pfad)
Effacer tous les journaux de relais :
translations-ignorecd /var/lib/mysql |
Restart MySQL service
MySQLtranslations-ignoreCode Block |
---|
service mysqld restart |
Si l'espace disponible est suffisant (au moins 20 %), il faut alors procéder à la récupération d'esclave comme décrit ci-dessus.
If enough space is available (at least 20%) then proceed with the slave recovery as described above.
ibdata files on the slave "very large"
Introduction
The files for the database are usually located in
fichiers ibdata sur l'esclave "très grand"
Les fichiers de la base de données se trouvent généralement dans /var/lib/mysql
Procédure
Si les étapes ci-dessus permettent d'atteindre au moins 100 % de disque :Désinscrire le service MySQL au démarrage automatique :
Translations Ignore |
---|
service mysqld disable |
Redémarrez l'ordinateur avec :
Translations Ignore |
---|
reboot |
Ne fonctionne que si le disque est inférieur à 100% (éventuellement après le redémarrage ci-dessus) alors
Connectez-vous au serveur mysql :
Translations Ignore |
---|
mysql -u root -p |
Supprimer toutes les bases de données JTEL :
Procedure
If no less than 100% disk can be achieved by the steps above:
Unsubscribe the MySQL service from the autostart:
Code Block |
---|
systemctl disable mysqld |
Restart the computer with :
Code Block |
---|
systemctl reboot now |
Only works if less than 100% disk is reached (possibly after reboot above) then
Log on to the mysql server:
Code Block |
---|
mysql -u root -p |
Drop all JTEL databases:
Code Block |
---|
SET |
FOREIGN_KEY_CHECKS=0; |
DROP DATABASE JTELLog; |
DROP DATABASE JTELStats; |
DROP DATABASE JTELWeb; |
SET FOREIGN_KEY_CHECKS=1; |
Press CTRL+C
pour revenir à la ligne de commande, puisrm
to return to the command line, and then
Code Block |
---|
service mysqld stop rm /var/lib/mysql/ibdata* |
rm /var/lib/mysql/ib_log_* |
Start MySql Server,
et réactivez-le si nécessaire :Translations Ignore |
---|
service mysqld start |
Vérifiez l'espace disque, et procédez à la restauration des esclaves comme décrit ci-dessus.
Toujours pas d'espace disque > 20% gratuit
Dans ce cas, l'esclave est tout simplement trop petit. Le disque dur doit être étendu (comme pour l'extension du rôle STORE, ne s'applique qu'au volume logique où résident les données de la base de données MySQL). Effectuez ensuite à nouveau les étapes, si le disque est trop petit, restaurez l'esclave comme décrit ci-dessus.
Ou bien l'esclave est complètement reconstruit avec une plaque plus grande.and enable it again if necessary:
Code Block |
---|
service mysqld start
service mysqld enable |
Check disk space, and proceed with Slave Restore as described above.
Still no disk space > 20% free
Introduction
The slave disk space is still full, more than 80% of the space on the drive is used by normal operation
Solution
In this case the slave is simply too small. The hard disk must be expanded (as with extending the STORE role, apply only to the logical volume where the MySQL database data resides). Then perform the steps again, if the disk is too small, then restore the slave as described above.
Or the slave is completely rebuilt with a larger plate.
Sv translation | ||
---|---|---|
| ||
Sv translation | ||
---|---|---|
| ||