8 ways to manually and automatically backup your MySQL database

As a popular open source database management system, MySQL has many users. In order to maintain data security, data backup is essential. This article will introduce several data backup methods suitable for enterprises.

MySQL Backup Using MySQLDump

mysqldump is a built-in tool for MySQL that allows users to specify different options for backing up databases to files, servers, or even compressed gzip files. The mysqldump utility is flexible, fast, performs advanced backups, and accepts a variety of command-line parameters that allow the user to change the way the database is backed up.

The mysqldump utility can be found in c:mysqlbin in Windows operating systems and /usr/local/mysql/bin in Unix/Linux systems. Pass different parameters to the mysqldump utility and it can be used in the following form.

mysqldump ---user [user name] ---password= [password]
[database name] > [dump file]

For manual backup, you only need the password for your MySQL database. Send the command to mysqldump using the p option as the password. For example, to back up all databases, use all databases:

mysqldump –u USER –p –all-databases> /mnt/backups/all

MySQL Backup Using MySQLpump

MySQLpump is similar to mysqldump, producing logical backups that support parallelization. MySQLpump performs all dump-related operations for multiple databases in parallel, which significantly reduces the time taken to perform logical backups.

However, MySQLpump does not back up performance_schema, sys schema, and ndbinfo, so you must use --databases to name them, and it also does not dump user account definitions.

Shell> mysqlpump –exclude-databases=% --users

MySQL Backup with AutoMySQLBackup

AutoMySQLBackup can back up single, multiple or all databases on your server. Each database is saved in a separate file, which can be compressed. How to execute it? Just download a file bash script, save it and customize it to your liking and make it executable.

Manually run the script: ./automysqlbackup.sh.2.5

Since this script does not support saving the backup folder to a remote location, you may need to mount a remote space or use another technique to upload the files remotely.

MySQL backup using mydumper

Mydumper is a practical software program that guarantees a fast and reliable multi-threaded MySQL backup process. Mydumper is known to handle large databases very well, providing parallelism. The user needs to create a backup as shown below and then replace the variables ($) with real variables, zip the folder and place it in the preferred location.

Mydumper

--database=$DB_NAME

--host=$DB_HOST

--user=$DB_PASS

--rows=500000

--compress

--build-empty-files

--compress-protocol

Fast and automatic backups

MySQL automatic backup with ottomatik

Ottomatik provides an excellent backup solution for your MySQL databases using a backup parachute. Ottomatik supports cloud or local servers. Through the installation script, it automatically installs SSH keys to connect to the Linux server. You can also select multiple servers for the backup job.

MySQL Backup Using PHPMyAdmin

PHPMyAdmin is a popular application for managing MySQL databases that is free to use. Many service providers use PHPMyAdmin, and you may have it installed without even realizing it.

All you need to do to back up your database is open PHPMyAdmin, select the file or folder, and click the "Export" link. Alternatively, there are also options such as "Save As" and "Compress" to save the data locally.

MySQL backup using file system snapshots

System snapshots can be taken using file systems such as Veritas, LVM, or ZFS. To use the file system snapshot, execute flush tables with a read lock from the client program, and then run mount vxfs snapshot from another shell. Unlock the table from the first client and then copy the files from the snapshot to the target folder and later unmount the snapshot.

Backing up by copying table files

Back up the MyISAM table by copying the table file to *.MYD, *.MYI files. When using it, you need to stop the server or lock and refresh the relevant tables:

FLUSH TABLES tbl_list WITH READ LOCK;

Read locks allow other clients to continue querying the table while the files are copied within the database, while flushing ensures that all active index pages are written to disk prior to the backup process.

Whether full or incremental, MySQL database backups are essential, and as the size of your database grows, you may also need to change your backup strategy.

Summarize

The above are 8 methods of manual and automatic backup of MySQL database introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!