Introduction

This page describes the process of rebuilding a Rebuild Slave-DB & Replication. Since the mysqldump MySQL Dump command locks the tables, it is not necessary that there is no need to create it when there is no traffic on the master databasemachine. 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 SLAVE

Login to the Slave Database MySQL and stop the slave SQL. Leave MySQL again afterwards. Use

the following commands for this:

Write a MySQL dump. Now perform the following steps on the master server. Create a backup directory and change to the same. The MySQL dumb is now executed with the following command

• mysqldump -uUSER -pPWD --single-transaction --master-data=2 --databases JTELWeb JTELStats JTELLog --add-drop-database --add-drop-table --events --routines --triggers > filename.sql

  As of release 3.12 the following command is required:

• mysqldump -uUSER -pPWD --single-transaction --master-data=2 --databases JTELWeb JTELStats JTELStats2 JTELLog --add-drop-database --add-drop-table --events --routines --triggers > filename.sql


Here is a brief explanation of this:

We switch back to the slave server and now import the mysqldump.

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>;
Here all parameters must be replaced with those from step 5.

Phase 1 - MySQL Dump

A 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 release

Log in to the Load Balancer of the cluster and execute the following commands as the jtel user

# Find out which software release is installed
cd /srv/jtel/shared/JTELCarrierPortal
git status

# If /srv/jtel/.. does not exist on the load balancer, attempt this
cd /home/jtel/shared/JTELCarrierPortal
git status

# Expected output
release-stable/3.XX

Create Backup Directory

# Create backup directory SLAVE MySQL Dump - Until jtel Portal release 3.12
mkdir /srv/jtel/shared/backup/DATUM

# If /srv/jtel/.. does not exist on the load balancer, attempt this
mkdir /srv/jtel/shared/backup/DATUM

Create MySQL Dump

MySQL Dump - Until jtel Portal release 3.12

# 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 --master-data=1 --databases JTELWeb JTELStats JTELLog --add-drop-database --add-drop-table --events --routines --triggers > acd-dbm_<yyyymmdd>.sql

MySQL Dump - From jtel Portal release 3.12 until latest release

# 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 --master-data=1 --databases JTELWeb JTELStats JTELStats2 JTELLog --add-drop-database --add-drop-table --events --routines --triggers > acd-dbm_<yyyymmdd>.sql

MySQL Dump - From MySQL 8.0.27

To Check for MySQL Version, log in to the master database server and execute the following command

mysql --version

# 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

Import Dump - tmux

# 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/DATE/
# copy the name
ls -als
# log in to mysql and import the dump
mysql -uroot -p<password>
source <acd-dbm_<yyyymmdd>.sql>

Import Dump - No tmux

# 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

# 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

# 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:

tmp Directory Is Full

Introduction

Now we check the slave status (a few times) with the command 

• Slave_IO_Running: Yes

• Slave_SQL_Running: Yes

Slave Disk Full

There are several reasons why the slave disk can become full.

tmp directory is full

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 .See also https://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html for more information. 

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

1. Space must first be made available. Just mercilessly delete everything from /tmp.
2. If this provides enough space, then restart the mysql service first: service mysql restart
3. 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:

Restart MySQL service

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: