Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to back up the MySql database, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Mysqldump tool 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 for 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 the customer to continue querying the table during the replication process, and MYSQL > FLUSH TABLES;# writes the active index page to the hard disk.

Mysqlhotcopy tool backup

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

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

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

Mysql command import sql file restore

$> mysql-u root-p [dbname]

Source backup.sql;# needs to select a database before executing the source command.

Directly 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 and are only applicable to the tables of the MyISAM engine.

Shut down the mysql service.

Overwrite the backed up file or directory over the data directory of mysql.

Start the mysql service.

For linux systems, after copying files, you need to change the users and groups of files to the users and groups that mysql is running.

Mysqlhotcopy fast recovery

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

$> cp-R / usr/backup/test / usr/local/mysql/data# if the restored database already exists, use the drop statement to delete the existing database before the recovery can be successful, and you need to ensure that the database version is compatible.

Migration between databases of the same version

$> mysqldump-h www.abc.com-uroot-p password dbname | $> mysqldump-h www.bcd.com-uroot-p password# migrates the server www.abc.com database dbname to the same version of the server www.bcd.com 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 needs to back up 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, you need to change the default character set.

Migration between different databases

The MyODBC tool implements the migration between MySQL and SQL Server.

MySQL Migration Toolkit tool.

Export and import of tables

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

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

Mysqldump file exports a text file (unlike INTO OUTFILE, all options in this method do not require quotation marks)

The $> mysqldump-T path-u root-p dbname [tables] [OPTIONS] #-T parameter indicates that the text file is exported. The directory where path exports the data. $> mysqldump-T C:\ test person-u root-p # exports the person table from 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 command exports a text file

MYSQL > mysql-u root-p-- execute= "SELECT * FROM person;" test > C:\ person3.txt;# exports the person table data from the test database to an person3.txt text file. -- the vartical parameter can display a row into multiple lines. MYSQL > mysql-u root-p-- vartical-- execute= "SELECT * FROM person;" test > C:\ person3.txt;#-- html exports the table as a html file, and the xml file exports the table as a xml file

LOAD DATA INFILE Import text File

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

Mysqlimport Import text File

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

Using mysqlbinlog to recover data

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

Start the binary log

Log-bin = path/filename # log file storage directory and file name expire_log_days = 10 # automatic log deletion time max_binlog_size = 100m # maximum log file size

View binary log

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

Delete binary log

MYSQL > RESET MASTER; # delete all binary logs MYSQL > PURGE {MASTER | BINARY} LOGS TO 'log_name'; # delete files whose file number is less than log_name number MYSQL > PURGE {MASTER | BINARY} LOGS BEFORE' date'; # delete files before the specified date

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

MYSQL > SET sql_log_bin = {0 | 1} # pause or start the binary log. On how to back up the MySql database to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.