Shulou(Shulou.com)06/01 Report--

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

Using MySQLDump for MySQL backup

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

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

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

Back up manually, only need the password of the MySQL database, use the p option as the password, and send the command to mysqldump. For example, to back up all databases, use all databases:

Mysqldump-u USER-p-all-databases > / mnt/backups/all

Using MySQLpump for MySQL backup

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

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

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

Using AutoMySQLBackup for MySQL backup

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

Run the script manually:. / automysqlbackup.sh.2.5

Because this script does not support saving backup folders to a remote location, you may need to install remote space or use other technologies to upload files remotely.

Using mydumper for MySQL backup

Mydumper is a practical software program that can guarantee a fast and reliable multithreaded MySQL backup process. As we all know, Mydumper can handle large databases well and provide parallelism. The user needs to create a backup as shown below, then replace the real variable with the variable ($), compress 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 backup

MySQL automatic backup and ottomatik

Ottomatik uses a spare parachute to provide an excellent backup solution for your MySQL database. Ottomatik supports cloud or local servers, and through the installation script, it automatically installs the SSH key connected to the Linux server, and you can select multiple servers for backup jobs.

Using PHPMyAdmin for MySQL backup

PHPMyAdmin is a popular application for managing MySQL databases and can be used for free. Many service providers use PHPMyAdmin, and you may have installed it without knowing it.

All you need to do to back up the database is to open PHPMyAdmin, select a file or folder, and click the "Export" link. In addition, there are options for saving data locally, such as "Save as" and "compress".

Using file system snapshots for MySQL backup

You can use file systems such as Veritas,LVM or ZFS to take system snapshots. To use a file system snapshot, execute a refresh table with read locks from the client program, and then run mount vxfs snapshot from another shell. Unlock the table from the first client, then copy the files from the snapshot to the destination folder, and unmount the snapshot later.

Backup by copying table files

To back up the MyISAM table by copying the table file as a * .MyISAM file, you need to stop the server or lock and refresh the related table:

FLUSH TABLES tbl_list WITH READ LOCK

When copying files in the database, the read lock allows other clients to continue querying the table, while refresh ensures that all active index pages are written to disk before the backup process.

MySQL database backups are essential for both full and incremental backups, and you may need to change your backup strategy as the database size increases.

Summary

The above is the editor to introduce to you 8 manual and automatic backup MySQL database methods, I hope to help you, if you have any questions, please leave me a message, the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.