Several ways to backup MySql database

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

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

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

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

MYSQL> LOCK TABLES;
# Allow clients to continue querying tables during the copy process.
MYSQL> FLUSH TABLES;
# Write the activated index page to disk.

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

$> mysql -u root -p [dbname] < backup.sql
# You need to create the dbname database before executing. If backup.sql is a backup file created by mysqldump, dbname is not required for execution.
MYSQL> source backup.sql;
# You need to select a database before executing the source command.

$> cp -R /usr/backup/test /usr/local/mysql/data
# If the restored database already exists, the recovery can only be successful after the existing database is deleted using the DROP statement. The database version compatibility also needs to be ensured.

$> mysqldump -h www.abc.com -uroot -p password dbname | 
$>mysqldump -h www.bcd.com -uroot -p password
# Migrate the database dbname on the server www.abc.com to the database of the same version on the server www.bcd.com.

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

$> mysqldump -T path -u root -p dbname [tables] [OPTIONS]
# The -T parameter indicates exporting a text file. path is the directory where the data is exported.
$> mysqldump -TC:\test person -u root -p
# Export the person table in the test table to a text file. After successful execution, there will be two files in the test directory, person.sql and person.txt

MYSQL> mysql -u root -p --execute="SELECT * FROM person;" test > C:\person3.txt;
# Export the person table data in the test database to the person3.txt text file. The --vartical parameter can be used to split a line into multiple lines.
MYSQL> mysql -u root -p --vartical --execute="SELECT * FROM person;" test > C:\person3.txt;
# --html exports the table as an html file, --xml exports the table as an xml file

MYSQL> LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTIONS] [IGNORE number LINES];
# [IGNORE number LINES] means ignore the number of lines MYSQL> LOAD DATA INFILE 'C:\person0.txt' INTO TABLE test.person;

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

$> mysqlbinlog [option] filename | mysql -u user -p password
# filename is the binary log file.
$> mysqlbinlog --stop-date="2013-03-30 15:27:47" D:\MySQL\log\binlog\binlog.000008 | mysql -u root -p password
# Restore data to operations before 2013-03-30 15:27:47 based on the log file binlog.000008.

log-bin = path/filename #Log file storage directory and file name expire_log_days = 10 #Log automatic deletion time max_binlog_size = 100M # Maximum size of the log file

MYSQL> SHOW VARIABLES LIKE 'log_%';
MYSQL> SHOW BINARY LOGS;
$>mysqlbinlog filename
# filename is the binary log file name.

MYSQL> RESET MASTER; #Delete all binary logs MYSQL> PURGE {MASTER | BINARY} LOGS TO 'log_name'; #Delete files with file numbers less than log_name MYSQL> PURGE {MASTER | BINARY} LOGS BEFORE 'date'; #Delete files before the specified date

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