Several Ways of Backup of MySql Database

mysqldump Tools Backup

Back up the entire database

$> mysqldump -u root -h host -p dbname > backdb.sql

Back up a table in the database

$> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

Back up multiple databases

$> mysqldump -u root -h host -p --databases dbname1, dbname2 > backdb.sql

Back up all databases in the system

$> mysqldump -u root -h host -p --all-databases > backdb.sql

Copy the entire database directory directly (not applicable to InnoDB storage engine) backup

windowns: installpath/mysql/data

linux: /var/lib/mysql

You need to execute the following command before copying:

MYSQL> LOCK TABLES;
#  Allows customers to continue querying tables during replication, 
MYSQL> FLUSH TABLES;
#  Writes the active index page to the hard disk. 

mysqlhotcopy Tools Backup

The fastest way to back up a database or table is to run on the same machine as the database directory and back up only tables of type MyISAM.

To use this backup method, you must have access to the backed-up table files.

$> mysqlhotcopy -u root -p dbname /path/to/new_directory;
# Copy the database to the new_directory Directory. 

mysql Command Import sql File Restore

$> mysql -u root -p [dbname] < backup.sql
#  You need to create before executing dbname Database, if backup.sql Yes mysqldump The backup file created is not required for execution dbname . 
MYSQL> source backup.sql;
#  Execute source You need to select a database before commanding. 

Direct copy database directory restore

Note: This method must ensure that the original database and the database to be restored have the same major version number 1, and is only applicable to the tables of MyISAM engine.

Shut down the mysql service. Overwrite the data directory of mysql with the backup file or directory. Start the mysql service. For linux systems, after copying files, you need to change the users and groups of the files to the users and groups that mysql runs on.

mysqlhotcopy Fast Recovery

Stop the mysql service, copy the backup database file to the location where the data is stored (data folder of mysql), and start the mysql service again (you may need to specify the owner of the database file).

$> cp -R /usr/backup/test /usr/local/mysql/data
#  If the recovered database already exists, use the DROP Statement to delete an existing database, recovery can be successful, and database version compatibility needs to be guaranteed. 

Migration between databases of the same version

$> mysqldump -h www.abc.com -uroot -p password dbname | 
$> mysqldump -h www.bcd.com -uroot -p password
#  Will the server www.abc.com Database of dbname Migrating to a server www.bcd.com On the same version of the database. 

Migration between different versions of mysql databases

Back up the original database.

Uninstall the original database.

Install the new database.

Restore the backed-up database data in the new database.

Database user access information requires a backup of the mysql database.

Default character set problem, latin1 is used as the default character set in MySQL4. x, and utf8 is used as the default character set in mysql5. x. If there is Chinese data, the default character set needs to be changed.

Migration between different databases

The MyODBC tool migrates between MySQL and SQL and Server.

MySQL Migration Toolkit tool.

Export and import of tables

SELECT...... INTO OUTFILE exports a text file, this method can only be exported to the database server, and the exported file cannot already exist.

MYSQL> SELECT ...... INTO OUTFILE filename [OPTIONS]
MYSQL> SELECT * FROM test.person INTO OUTFILE "C:\person0.txt";
#  Will the table person Import the data in into a text file person0.txt . 

mysqldump file exports text file (unlike INTO OUTFILE, all options of this method do not need to add quotation marks)

$> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

0

mysql Command Export Text File

$> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

1

LOAD DATA INFILE Import Text File

$> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

2

mysqlimport Import Text File

$> mysqlimport -u root -p dbname filename.txt [OPSTONS]
#  The imported table name is determined by the file name, and the table must exist before importing data 
$> mysqlimport -uroot -p test C:\backup\person.txt
#  Import data into the test Of the database person Table. 

Recovering data using mysqlbinlog

$> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

4

Start binary log

$> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

5

View binary logs

$> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

6

Delete binary log

$> mysqldump -u root -h host -p dbname tbname1, tbname2 > backdb.sql

7

Temporarily stop binary logging (no need to restart mysql service)

MYSQL> SET sql_log_bin = {0|1} # Pause or start 2 Binary log.