MySQL Data backup and restore

MySQL Data backup and restore

• • • MySQL Data backup and restore
      • Data backup
        • Mysqldump Backup
        • Copy this database directory directly
        • Mysqlhotcopy Tool Quick Backup
      • Data restore
        • MySQL command restore
        • To import a backup SQL file using the source command
        • Using methods that are copied directly to the data catalog
        • Mysqlhotcopy Fast Recovery

Data Backup Mysqldump Backup

The basic syntax format for mysqldump backup is as follows:

-u-h-p password dbname[tbname,[tbname...]] > filename.sql

• Use mysqldump to back up all tables in a single database

/Users/frankstar/Desktop/booksDB_20160510.sql

After entering the password, the desktop can see the backed up database file, the beginning of the file is the version number of the Mysqldump tool, then the name and host information of the backup account, the name of the database, the server version number and so on. The following is the SET statement, which mainly involves the system variable value of the database at the time of recovery, and the last few rows use the SET statement to recover the original value of the server system variable.

• Use mysqldump to back up a table in a database

/Users/frankstar/Desktop/books_20160510.sql

• Backing up multiple databases using mysqldump

JavaStudy/Users/frankstar/Desktop/bookDB_JavaStudy_20160510.sql

• Backing up all databases with –all-databases

-u-p--all-databases> /Users/frankstar/Desktop/allDatabases_20160510.sql

Tip: If the tables in the database are MyISAM tables, consider using mysqlhotcopy to improve efficiency.

Copy this database directory directly

You can copy the storage directories and files of the database directly for backup. This method is not suitable for INNODB storage engine tables, and requires consistent database versions, otherwise incompatibilities may occur. The lock TABLES operation is required before the backup, and then the flush TABLES is performed, the database file should be allowed to be queried when it is copied, and the flush TABLES statement is required to ensure that all the active index pages are written to the hard disk before starting the backup. The best way to do this is to stop the service from replicating.

Mysqlhotcopy Tool Quick Backup

Mysqlhotcopy must have access to the backed-up data table file, with the table's Select, RELOAD, LOCK tables, and other permissions, and it applies only to the MyISAM and archive storage engine tables.

The syntax format is as follows:

-u-p JavaStudy /Users/frankstar/Desktop/JavaStudy

It simply copies the table directory to another directory, similar to the direct replication CP/SCP to back up the database.

Data restore MySQL command restore

The syntax format is as follows:

/Users/frankstar/Desktop/booksDB_20160510.sql

Tip: First database to have this restored database, even if it is empty.

To import a backup SQL file using the source command

To log on to the server first, use the following syntax:

#source filename;usebooksDB ;mysql> source /Users/frankstar/Desktop/booksDB_20160510.sql

Using methods that are copied directly to the data catalog

Restore the data directory and files that were previously copied from the backup database to MySQL, requiring the major version number of the database to be the same, requiring the MyISAM table, stopping the database service, and changing the owner and genus of the data file to MySQL

Mysqlhotcopy Fast Recovery

Similar to the method of copying directly to the data directory, it is also necessary to stop the service and change the owner and owner groups of the data file as follows:

-R mysql:mysql /var/lib/mysql/db_name

Copy syntax:

cp －R /Users/frankstar/Desktop/JavaStudy /usr/local/var/mysql/JavaStudy

MySQL Data backup and restore