Physical/Cold Backup

This backup method, also known as a cold backup, involves making a physical copy of MySQL instance files to a backup location.
No backup tool is required; only a simple Unix-based command is used.
It’s considered the safest way to preserve your MySQL instance.

In this backup method, you’ll back up the following:

All data directories
All system-related tablespaces
All option files

To perform this backup, you need to shut down MySQL using the clean shutdown option.

Perform Physical/Cold Backup

First, we need to perform a clean shutdown, which requires changing the value of the system variable innodb_fast_shutdown before using systemctl to shut down mysqld.

Start by logging in to MySQL and setting the value of innodb_fast_shutdown to 0:

SET GLOBAL innodb_fast_shutdown = 0;

After setting the value of innodb_fast_shutdown to 0, you can stop the MySQL server using the following command:

systemctl stop mysqld

Now, we will create a directory called “coldbackup” to hold all the files:

sudo mkdir coldbackup

Next, we will copy the data directory using the cp command and place it in the new directory we created before:

sudo cp -r /mysqldata/mysql /home/dba/coldbackup/

Remember to move the system tablespace. The system tablespace path is changed to the directory called innodb, so we will take a backup of it as well.

sudo cp -r /mysqldata/innodb /home/dba/coldbackup/

also we will copy the my.cnf file too

sudo cp -r /etc/percona/my.cnf /home/dba/coldbackup/

Restore From Physical/Cold Backup

For testing purposes, I will start up MySQL and drop some tables. Then, we will try to restore this data using the cold backup we have taken.

we will drop empolyees database and test1 database

now we will perform restore , remember always to preform clean shutdown

SET GLOBAL innodb_fast_shutdown = 0;

systemctl stop mysqld

now we will delete the old data directory
sudo rm -rf mysqldata/mysql

also we will delete innodb directory which hold system tablespace

sudo rm -rf mysqldata/innodb

now we will copy the cold backup and move it to the data diretcory

sudo cp -r /home/dba/coldbackup/mysql /mysqldata/

sudo cp -r /home/dba/coldbackup/innodb /mysqldata/

modify the ownership of the file to be mysql user

sudo chown -R mysql:mysql  /mysqldata/mysql


sudo chown -R mysql:mysql  /mysqldata/innodb

now we will startup mysqld and check data if there

systemctl start mysqld

Files needed for Cold Backup

Data directory (DATA DIR)
System tablespace
Any option/configuration file with the extension *.cnf

Files not part of Cold Backup

Redo log files
Doublewrite buffer files
Binary log files
Undo tablespaces
Temporary files

Logical Backups

Logical backup copies data only.
It’s best used when you only want to take backups of databases or tables.
It can backup databases and tables.
Logical backup works by generating SQL statement files in .sql format.
To take a logical backup, a utility is required. When MySQL is installed, two utilities are provided for this purpose:
- mysqldump old utility
- mysqlpump new utility

MySQLDUMP Backup Program

Logical backup client utility
Generates SQL statements for reproducing database objects
Option to backup entire databases, with ability to exclude specific tables
Table backups with WHERE clause support for selective row backups
Simultaneous dumping of one or more databases
syntax
mysqldump [options] db_name [table_name} > backup_name.sql: This command performs a logical backup of the specified database (db_name) and optionally specific tables (table_name). The output is redirected to a SQL file named backup_name.sql.
mysqldump [options] db_name [table_name} -where='condition' > backup_name.sql: Similar to the previous command, this one also backs up a specific database (db_name) and optionally specified tables (table_name). However, it includes a WHERE clause (condition) to selectively backup rows based on certain criteria. The resulting backup is saved to backup_name.sql.
mysqldump [options] -databases db1 db2 ..db_name > backup_name.sql: This command performs logical backups of multiple specified databases (db1, db2, etc.), along with their respective tables. The output is directed to a SQL file named backup_name.sql.
mysqldump [options] -all-databases > backup_name.sql: Here, the command performs a logical backup of all databases present on the MySQL server (-all-databases option). The resulting backup is stored in a SQL file named backup_name.sql.
–mysqldump [options] db_name --ignore-table=db.tbl_name > backup_name.sql allow you to take backup of a database but ignore some table you specify .

Take Backup with MySQLDUMP

1.taking table backup
first we will take logical backup of certain table
so we will take backup of table called departments inside employees database

mysqldump employees departments > backup/dep.sql

Yes, you can indeed check the contents of the dump file using the cat command, which displays the contents of the file directly in the terminal. Alternatively, you can use text editors like nano or vim to view and edit the contents of the file. So, running cat /backup/dep.sql would display the contents of the SQL dump file dep.sql located in the /backup directory. If you prefer using nano, you can run nano /backup/dep.sql to open the file in the nano text editor for viewing and editing.

In MySQL dump files, you can often find comment lines that provide metadata about the backup. These comments typically include information such as the version of mysqldump used to create the backup and the version of the MySQL server at the time the backup was initiated. Additionally, other internal information may be included in these comments.

In a typical MySQL dump file generated by mysqldump, the structure of the database objects (such as tables) is usually defined using CREATE TABLE statements. These statements first drop the table if it exists and then recreate it with the specified schema.

After defining the structure of the tables, the dump file typically contains INSERT INTO statements to populate the tables with data. However, it’s important to note that the mysqldump command does not inherently lock tables during the backup process. Instead, it typically uses the –lock-tables option to ensure data consistency by obtaining read locks on all tables to be dumped.

**2.taking backup of table but filter row **

We’ll perform a table backup of the “departments” table, selectively including only the row where the department number is ‘d009’, by applying a filtering WHERE clause
mysqldump employees departments --where="dept_no='d009'" > backup/d009.sql

Upon inspecting the file’s content, you’ll find that everything remains identical, except for the inclusion of only one row, representing the ‘d009’ department

**3.taking backup of database but skip table **

We’ll back up the “employees” database while excluding the “departments” table using the mysqldump command, and save the result to a file named “nodepartments.sql” in the “backup” directory.
mysqldump employees --ignore-table=employees.departments > backup/nodepartments.sql

Following the backup, we can employ the grep command to search the dump file for CREATE statements. By doing so, we should observe the absence of any statement pertaining to the creation of the “departments” table.

grep CREATE backup/nodepartments.sql

4.taking backup of multiple databases
we will take backup of employees and test1 database in single command

mysqldump --databases employees test1 > backup/emp-test.sql

5.taking backup of all databases

mysqldump --all-databases > backup/alldatabases.sql

Restoring from MySQLDUMP

we will test restore for table backup we have took for departments table
first we will drop the table and then restore

to restore we will use the below syntax

mysql employees < backup/dep.sql

now we will test database restore
i will drop employees and test1 database and then restore them

mysql < backup/emp-test.sql

MySQLPUMP Backup Program

MySQLpump offers enhanced functionality and is regarded as a more advanced logical backup tool.
mysqlpump provides parallel processing of databases to speed up the dump process, utilizing all available CPUs efficiently.
With mysqlpump, users gain better control over the selection of database objects to dump, including tables, stored procedures, and user accounts.
mysqlpump offers the capability to dump user accounts as account-management statements rather than as insert statements into the MySQL system database.
mysqlpump supports compressed output and provides dump progress information.
By default, mysqlpump dumps all databases.
ٍSyntax
mysqlpump [option] db_name [table_name] --add-drop-table > backup.sql this option --add-drop-table For successful restoration, it’s crucial to include the --add-drop-table option in the mysqlpump command. Failure to do so may result in restore failures if the tables still exist in the database. Similarly, for database backups, ensure to add the --add-drop-database option to mitigate potential restoration issues caused by existing databases.
mysqlpump --exclude-databases=% --users > users.sql the % means exclude all databases ,-users include the users , this mysqlpump syntax will take backup of the users
mysqlpump [options] --databases db1 db2 --add-drop-database > backup.sql
mysqlpump [options] all databases > all.sql

mysqlpump backup and and restore

we will first take table backup with mysqlpump

mysqlpump employees departments --add-drop-table > backup/departmentspump.sql

to restore we will use below command

mysql employees < backup/departmentspump.sql

now we will show how to take backup of the database
mysqlpump --databases employees --add-drop-database > backup/eomployeespump.sql

to restore the pump we will use the same syntax as we did to restore the table .
mysql employees < backup/eomployeespump.sql

Backing Up MySQL Accounts

It’s highly important to maintain regular backups of all MySQL user accounts. It’s recommended to schedule backups every two weeks or monthly. These backups prove invaluable in situations where a user account is accidentally dropped, facilitating user recovery.

syntax :
mysqlpump --exclude-databases=% --users --add-drop-user > users.sql

if you check content you will find dump for user account either user or roles

Restore MySQL Account

To begin the demonstration, let’s list the MySQL user accounts using the following command:

select user , host from mysql.user;

i will drop the user READER
drop user READER ;

best practices if you looking to restore certain user then we can filter the dump file for user using grep command and locate SQL statement for that user
grep READER backup/users.sql

we can skip the drop statement since we already dropped the user .

now copy the statement and run it on MySQL shell

Compressing MySQL Backups

MySQLpump offers the capability to compress the output.
By default, MySQLpump does not compress the output.
To enable compression, you need to specify the desired compression algorithm.
The available compression algorithms are LZ4 and ZLIB.
To compress the output, include the option –compress-output=ZLIB|LZ4.
To decompress the output, you must have the respective utility installed.
The utilities Zlib_decompress and lz4_decompress are included in the MySQL distribution.
Syntax :
mysqlpump --database db_name --compress-output > backupcomp.sql

Taking compress backup

first we will verify which compress algorithm that available
lz4 --version

lz4 is not available on the OS
let’s confirm the second method if it available on OS

zlib --version

both are not available but if we run which zlib_decompress

it will show the library is available

let’s try taking compress backup using lz4 for database employees

mysqlpump --databases employees --compress-output=lz4 > backup/employeescomp.sql.lz4

MySQL Hot Backup

Also referred to as a physical backup.
Involves copying database files to a backup device while MySQL remains online.
Ideal for critical, always-on production applications.
Particularly suitable for InnoDB tables and transactions.
Hot Backup Tools

mysqlbackup oracle
mariabackup MariaDB
xtrabackup percona
1.mysqlbackup
Enterprise backup is a superior choice for backups, offering unparalleled speed and efficiency, making it the top choice for your backup strategy. However, it’s important to note that MySQL Enterprise Backup, while highly effective, is not provided for free.

2. mariabackup

Provided by MariaDB as an open-source and free solution.
Derived from the widely used backup tool XtraBackup.
Features full support for all major functionalities found in Percona XtraBackup.
3. xtrabackup
Percona provides an open-source hot backup tool for MySQL.
It ensures databases remain unlocked during backup operations.
The tool seamlessly performs backups without causing performance disruptions.

XtraBackup Hot Backup Tool

reference link

A production-grade hot backup tool provided free of cost.
No licenses are required for usage.
Completely independent from MySQLbackup or InnoDB hot backup solutions.
Compatible with both on-premises and cloud environments.
Enterprise-ready and suitable for automation.
Supports point-in-time recovery.
Note that Percona XtraBackup isn’t bundled with Percona MySQL or MySQL Community Edition; it needs to be manually installed.

Download & Install XtraBackup

First, you need to verify if Percona XtraBackup is installed or not by using the following command:

which xtrabackup

Since we have two installations, one with Community MySQL and the other with Percona MySQL, and you’ve already installed Percona XtraBackup on the Percona MySQL server, Let’s begin by navigating to the Percona website and scrolling down until we locate Percona XtraBackup. Then, we’ll select version 8 and choose the platform, which in this case is Red Hat 8.

link

We can use wget to download the package directly onto the operating system.

wget https://downloads.percona.com/downloads/Percona-XtraBackup-8.0/Percona-XtraBackup-8.0.35-30/binary/redhat/8/x86_64/Percona-XtraBackup-8.0.35-30-r6beb4b49-el8-x86_64-bundle.tar

To decompress a tar archive, you can use the tar command with the -xvf options followed by the filename. Here’s how you can decompress a tar archive:
tar -xvf filename.tar

tar -xvf Percona-XtraBackup-8.0.35-30-r6beb4b49-el8-x86_64-bundle.tar

now we will install xtrabackup using yum local install

sudo yum localinstall percona-xtrabackup-80-8.0.35-30.1.el8.x86_64.rpm

now when we run the following command it should show that library for xtrabackup is available

which xtrabackup

xtrabackup --version

Backup with XtraBackup

before we use the tool it always recommended to check help section to see the syntax available

xtrabackup --help

also you can use man page for xtrabackup
man xtrabackup

We’ll proceed by creating a backup directory. While it’s recommended to create this directory on a separate mount point, for testing purposes, we’ll create it in the root directory.

sudo mkdir backup

make sure to assignee the owner to mysql
user

sudo chown mysql:mysql backup/

To perform a backup, we use the following syntax:

sudo xtrabackup  -uroot -p --backup --target-dir=backup/

This command will take a hot backup of the running MySQL server and place it in the backup directory.

prepare backup

In a production environment with numerous transactions, it’s essential to prepare the backup using the --prepare option. This ensures that the backup is fully consistent, allowing for a seamless restoration process.

The command is similar to backup, but instead of using --backup, we replace it with --prepare.

sudo xtrabackup -uroot -p --prepare --target-dir=backup/

XtraBackup Backup Files

in this section we will demonstrate what xtrabackup file has been create .
below is the whole xtrabackup output

You’ll discover our database stored as directories within the data directory. Additionally, xtrabackup backs up the system tablespace contained in ibdata1 and ibdata2, along with the redo log files. It also includes backups of the undo tablespaces undo_002 and undo_001, and points to the latest binary log 1.000028. Furthermore, it captures an index file pointing to the latest binary log and takes a backup of the my.cnf configuration file.

Preparing Hot Backup Restore

before we restore percona xtrabackup it is very important to prepare for the restore .
first we match double buffer file and system tablespace and see if we have backup of them

so we create restore file and past cp command in it

cp ib_buffer_pool ibdata1 ibdata2 ibtmp1 undo_001 undo_002

Restore From Hot Backup

reference link

here are the steps to restore a backup using xtrabackup:

Stop MySQL services.
Remove the contents of the data directory.
Copy everything related to the data directory from the xtrabackup to the data directory.
Recheck the ownership of the data directory.
Remove any existing binary logs.
Start MySQL services.

1. stop MySQL services

sudo systemctl stop mysql
sudo systemctl status  mysql

2. remove the content of Data Dir

 rm -rf /mysqldata/mysql/*
 rm -rf /mysqldata/innodb/*

3. Copy everything related to the data directory from the xtrabackup to the data directory.
first list content of percona xtrabackup files

ll backup/
not all the file are needed some files are related to perocna xtrabackup
so only copy the file that are highlighted

cp 1.000028 1.index backup-my.cnf ib_buffer_pool employees  database ibdata1 ibdata2 ibtmp1 mysql.ibd  mysql  '#innodb_redo' sys  performance_schema test1  undo_001  undo_002 /mysqldata/mysql/

copy the doube buffer system tablespace and undo log to correct directory , also insure to remove them after copying

remove the double write buffer files

4. Recheck the ownership of the data directory.
sudo chown -R mysql:mysql /mysqldata/mysql

sudo chown -R mysql:mysql /mysqldata/innodb

6. Start MySQL services.

sudo systemct	start mysqld

MySQL Backup & Restore