MySQL Backup and Restore

by Mark Nielsen
Copyright 2021


This should cover mysqldump, xtrabackup, binary, snapshots via LVM or KVM, mariabackup but not anything to do with the Cloud. All the backup sections will include there sample restore commands.

In case you are wondering. Oracle bought MySQL for years and did very little with it for years. They might have done that because it competed with Oracle database, but now Oracle has seemed to be doing a lot with it. This gave Percona and MariaDB time to rise and make improvements and become popular as they are offshoots of MySQL. Nobody uses Oracle MySQL except Oracle diehards. Percona was the default for many years and will likely be a strong player for many years to come. MariaDB seems to be getting very popular lately and one of the cofounders of MySQL leads it. SkySQL from MariaDB is a database service which shows GREAT promise.

People often forget about restores. Here are important points:

In general, here is a breakdown of the different backups.

  1. Links
  2. Best Practices
  3. Binlog backups and Time Travel
  4. mysqldump
  5. xtrabackup and mariabackup
  6. binary
  7. snapshots via LVM or KVM

Links


Best Practices

Backups

Restore


Binlog backups and Time Travel

In MySQL, you should do the following with binlogs:

I am not going to get into this except to say, stop mysql replcation, do show slave status and show master status, take backup, then start mysql. You can start mysql replication after the mysqldump starts. The mysqldump will be single-transaction. 
mysql -u root -e "stop slave io_thread"
sleep 5
# NOTE: You should really so a check that the io_thread and sql_thread are the same. 
mysql -u root -e "stop slave sql_thread"

d=`date +%y%m%d_%H%M%S`
h=/backups/mysql/mysql3/$d
f=/backups/mysql/mysql3/$d/mysql1_$d
mkdir -p $h

mysql -u root -e "show slave status\G" > $h/show_slave_status.info
mysql -u root -e "show master status\G" > $h/show_master_status.info

options=" --all-databases --single-transaction --master-data"
mysqldump -u root $optionsA -S /tmp/mysql3.sock | gzip  > $f.gz

Both XtraBackup and MariaBackup both create binary copies of the database, but only the innodb files. Copy over the "mysql" directory also. There are tools to do complete backups but the innodb files tend to be the most important and the information in the "mysql" database and be restored -- accounts, etc.

Both are very similar.

MariaBackup 

  # This is mariabdb, so we have to use mariadb-backup
apt-get install mariadb-backup

# We can use xtrrabackup or innobackupex
# If xtrabackup, you should also backup the mysql database.
mkdir -p /backups/mysql/mysql1

d=`date +%y%m%d_%H%M%S`
mariabackup --defaults-file=/data/mysql/mysql1/my.cnf \
           --datadir=/data/mysql/mysql1/data \
	   --user=root --compress --backup \
	   --slave-info \
	   --target-dir=/backups/mysql/mysql1/mysql1_$d \
	   --socket=/tmp/mysql1.sock \
	   --compress

exclude="--exclude=gtid*,innodb*,transaction*"
S=/data/mysql/mysql1/data/mysql
D=/backups/mysql/mysql1/mysql1_$d/MYSQL_copy

mkdir -p /backups/mysql/mysql1/mysql1_$d/MYSQL_copy
rsync -av $exlude $S $D

XtraBackup for Percona MySQL 


    # We can use xtrrabackup or innobackupex
    # If xtrabackup, you should also backup the mysql database.
    mkdir -p /backups/mysql/mysql1

    d=`date +%y%m%d_%H%M%S`
     xtrabackup --defaults-file=/data/mysql/mysql1/my.cnf \
    --datadir=/data/mysql/mysql1/data \
    --user=root --compress --backup \
    --slave-info \
    --target-dir=/backups/mysql/mysql1/mysql1_$d \
    --socket=/tmp/mysql1.sock \
    --compress

    exclude="--exclude=gtid*,innodb*,transaction*"
    S=/data/mysql/mysql1/data/mysql
    D=/backups/mysql/mysql1/mysql1_$d/MYSQL_copy

    mkdir -p /backups/mysql/mysql1/mysql1_$d/MYSQL_copy
    rsync -av $exlude $S $D

Simple binary backup. 
  d=`date +%y%m%d_%H%M%S`
  h=/backups/mysql/mysql3/$d
  mkdir -p $h
  
  # kill mysql
  # This won't work on my server because I don't use service.
  service mysql stop
  # I do this
  # pid = `cat /tmp/mysql3/sock`
  # kill $pid
  # sleep 10

  rsync -av /data/mysql/mysql3/* /backups/mysql/mysql3/$d/
  service start mysql

  # For my server. 
  # mysqld --defaults-file=/data/mysql/mysql3/my.cnf &
  # sleep 5