MySQLBackup Command-Line Tool Working: 4 Easy Steps

Companies use various Databases and other storage engines to run their business operations and store valuable data. MySQL is one of the widely used Relational Databases that companies use to effectively store and run their websites, background applications and manage data. It is a good practice to always maintain a backup of the data to avoid any data loss at the time of system failure or disaster.

MySQL offers a simple and automated client program to let users easily create and backup and restore points of their data. The mysqlbackup is the utility client program of MySQL that handles packing and unpacking of backup files with many other options to configure.

The mysqlbackup option makes it easier for the users to maintain the backup of the data on other locations that increase the data availability, avoids data loss, and improves the data latency. This article introduces you to a brief introduction to MySQL and mysqlbackup’ services. You will also go through the simple backup process and learn how to use the mysqlbackup command to create a backup, validate it and restore the backup to the server.

Table of Contents

• Prerequisites
• Introduction to MySQL
  • Key Features of MySQL
• Introduction to MySQLBackup
• Steps to Perform Backups Using mysqlbackup
  • Step 1: Granting MySQL Privileges
  • Step 2: Backing Up MySQL Instance
  • Step 3: Verifying a Backup
  • Step 4: Restoring a Database
• Conclusion

Prerequisites

• MySQL Server installed on your local system.
• Some basic knowledge of SQL.

Introduction to MySQL

MySQL is a widely used open-source Relational Database Management System (RDBMS) that allows users to store data in one or more tables and then relate the table data with each other. Written in C++ and was created by a company named MySQL AB then, the ownership transferred to Sun Micro Systems, and currently, Oracle owns MySQL. With the help of SQL language, users can create, modify, access data from the Relational Database and control the user access to the Database. Many popular websites including Facebook, Flickr, Twitter, YouTube use MySQL. 

MySQL runs on many platforms that include Linux, macOS, Microsoft Windows, NetBSD, Oracle Solaris, Symbian, SunOS, etc. It comes with stand-alone clients that help users interact with the MySQL Database using SQL language. Companies use MySQL to manage their business operations of any scale with reliability and security.

Key Features of MySQL

MySQL powers many applications and platforms and is one of the most used Databases when it comes to managing data operation at a large scale. A few features of MySQL are listed below:

• Clients and Tools: MySQL comes with many utility and client programs that include both command-line tools such as mysqlbackup and mysqldump and graphical interface programs such as Workbench.
• Highly Secure: MySQL supports a flexible and secure password system that enables host verification and protects sensitive data from getting leaked by intruders.
• Highly Scalable: MySQL supports multi-threading which allows users to run more jobs in parallel and handle as many as 50 million rows or more.
• High Performance: MySQL is fast, reliable, and cheaper because of its unique storage engine architecture that delivers higher performance than other Databases.

To know more about MySQL, here.

Introduction to MySQLBackup

The mysqlbackup is an easy-to-use client tool designed by MySQL that handles all the backup and restore operations and other tasks such as backup compression, decompression, validation, etc. It can backup all InnoDB tables and indexes, including InnoDB system tablespace, all MyISAM tables, and indexes, tables that are managed by other storage engines, etc. The mysqlbackup command-line tool can pack and unpack the backup data, add data to the backup data if any changes are done to InnoDB tables during the backup operation. Also, it can restore data, index, and log files back to their original locations.

The mysqlbackup command returns exit code 0 on the successful backup operation or the message “mysqlbackup completed OK!”. MySQL has divided the mysqbackup’ operations into different categories based on the usage of the command. The different operations of mysqlbackup are listed below:

• Backup Operations
• Update Operations
• Restore Operations
• Validation Operations
• Other Single-File Backup Operations
• Other Operations

Steps to Perform Backups Using mysqlbackup

Now that you have understood about MySQL and mysqlbackup command. In this section, you will learn about the procedure to backup data using mysqlbackup. Before moving further with backup operation, there is a need to gather Database information. The information to gather includes MySQL port, Path to MySQL configuration file, Size of InnoDB redo log files, ID and password of privileged MySQL user, etc. The steps to backup the MySQL Database using mysqlbackup are listed below:

• Step 1: Granting MySQL Privileges
• Step 2: Backing Up MySQL Instance
• Step 3: Verifying a Backup
• Step 4: Restoring a Database

Step 1: Granting MySQL Privileges

• First, connect with the MySQL Server using mysqlbackup command with the credentials supplied using parameters”–user” and “–password” options. 
• The user that you will specify needs some privileges to move further or you can create a new user with limited privileges or connect with an administrative account like root.
• The necessary privileges that you need for mysqlbackup are listed below:
  • SELECT
  • BACKUP_ADMIN
  • RELOAD 
  • SUPER
  • REPLICATION CLIENT
  • PROCESS with ALGORITHM = INPLACE clause.
  • CREATE, INSERT, DROP, and UPDATE on mysql.backup_progress and mysql.backup_history.
  • SELECT and ALTER on mysql.backup_history.
• For simplicity, here a new user with the above privileges is created to connect to the localhost “mysql” client program using the command given below.

CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, BACKUP_ADMIN, RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* 
    TO `mysqlbackup`@`localhost`;
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost'; 
GRANT CREATE, INSERT, DROP, UPDATE, SELECT, ALTER ON mysql.backup_history 
    TO 'mysqlbackup'@'localhost';

• Here, in the above command, the MySQL user is named “mysqlbackup” and you can provide any password in place of “password“.

Step 2: Backing Up MySQL Instance

• Now, in this following tutorial entire MySQL instance to a single file will be backed up using the “backup-to-image” command that needs to be put at the end of the command.
• You need to provide the connection information with the “mysqlbackup” command and “–user” and “–host” options. The sample command to back up the entire MySQL instance is given below.

$ mysqlbackup --user=mysqlbackup --password --host=127.0.0.1 --backup-image=/home/mysqlbackup/backups/my.mbi 
  --backup-dir=/home/mysqlbackup/backup-tmp  backup-to-image

• Here in the above command, the mysqlbackup command is used with “–user”, “–password”, “–host” as localhost and it also has some information on the directory of the “–backup-image” and “–backup-dir”. At last, the backup-to-image option is used. The directory location may vary according to your system.
• The following command will start the process for the backup of the MySQL instance.
• After a successful backup process, it will return the message “mysqlbackup completed OK!“.

Step 3: Verifying a Backup

• After a backup process is complete, it’s always a good practice to check for the integrity of the backup and verify if all the data is backed up correctly.
• To check and validate the backup, the sample command is given below for validating a backup image.

$ mysqlbackup --backup-image=/home/mysqlbackup/backups/my.mbi validate

• In the above command, the directory to the backup image is provided using the parameter “–backup-image” using mysqlbackup command. At the end of the command, the operation “validate” is specified.

After successful validation of the backup, it will return the message “mysqlbackup completed OK!” in the end.

Step 4: Restoring a Database

• Till now you have backup up and validated the MySQL instance. The last thing left is to restore the backup.
• For this, first, you need to shut down the Database Server.
• Now, delete all the files located in the server’s directory and also delete all the files that you can find under the directory specified by “–innodb_data_home_dir”, “–innodb_log_group_home_dir”, and “–innodb_undo_directory” options for restore. There might be a case that your directories are different from the data directory.
• After deleting all the files, use the command “copy-back-and-apply-log” with the mysqlbackup command. A sample command is given below.

$ mysqlbackup --datadir=/home/admin/bin/mysql-commercial-8.0.16/datadir 
  --backup-image=/home/mysqlbackup/backups/my.mbi --backup-dir=/home/mysqlbackup/backup-tmp 
    copy-back-and-apply-log

• The above command “copy-back-and-apply-log” will convert the raw backup into the prepared backup by updating it to a consistent state.
• After that, it will copy all the tables, indexes, metadata, and other information to the target server.
• The “–datadir” specifies the location of the data directory for restoring the data.
• The “–backup-image” option provides the location of the single-file backup.
• The “–backup-dir” is the empty directory to store temporary files during the restore process.
• After a successful restore of the backup, the program will return the message “mysqlbackup completed OK!“.

That’s it! You have successfully performed a simple backup and restore of MySQL instance using the mysqlbackup command.

Conclusion 

In this article, you learn about MySQL, mysqlbackup command, and how to use it. You also went through the process to create, validate and restore a simple backup of a MySQL instance. It is a good practice to maintain a backup of the MySQL Database and the mysqlbackup command is one of the simplest ways to create a backup.

Visit our Website to Explore Hevo

MySQL Server stores valuable business data that can be used to generate insights. Companies need to analyze their business data stored in multiple data sources. The data needs to be loaded to the Data Warehouse to get a holistic view of the data. Hevo Data is a No-code Data Pipeline solution that helps to transfer data from 100+ sources to desired Data Warehouse. It fully automates the process of transforming and transferring data to a destination without writing a single line of code.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of learning about mysqlbackup Command-Line Tool Working in the comments section below!