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

Xiaobian to share with you how to backup and restore mysql database is too big, I hope you have something to gain after reading this article, let's discuss it together!

Command: mysqlhotcopy

This command locks the table before copying the file and synchronizes the data to the data file to avoid copying incomplete data files. It is the safest and fastest backup method.

The command is used:

mysqlhotcopy -u root -p db1 db2 … dbn

If you need to backup all databases, add-regexp=".* "Parameters.

The Mysql hotcopy command automatically locks the data and does not shut down the server for backup.

It can also flush logs, keeping checkpoints for backup files and log files synchronized.

Local:

1. Enter bin folder under MySQL directory: e: Enter;

e:\>cd mysql\bin Enter

2. Export database: mysqldump -u username-p database name> exported file name

Example:

mysqldump -uroot -p abc > abc.sql

(Export database abc to abc.sql file)

When prompted for password, enter the password for the database user name (if exporting a single table, enter the table name after the database name)

If the locktable error is prompted, add--skip-lock-tables to the blank space after mysqldump -uroot -p abc.

3, will see the file news.sql automatically generated into the bin file under?

MySQL Command Line Import Database:

1, Move the.sql file to be imported to the bin file, This path is more convenient

2. Same as step 1 derived above

3, enter MySQL: mysql -u username-p

If I type the command line:mysql -u root -p?? (Entering the same will ask you to enter the MySQL password)

4, in MySQL-Front you want to create a new database, this time is empty database, such as a new target database named news

5, input: mysql>use target database name

For example, I typed the command line:mysql>use news;

6, import file: mysql>source import file name;

For example, I entered the command line: mysql>source news.sql;

MySQL backups and restores are done using mysqldump, mysql, and source commands.

1.MySQL Backup and Restore under Win32

1.1 backup

Start menu| run| cmd |Use the command "cd \Program Files\MySQL\MySQL Server 5.0\bin" to enter the bin folder| Using "mysqldump"? -u username-p databasename >exportfilename"Export the database to a file, such as mysqldump -u root -p voice> voice.sql, and enter the password to start exporting.

1.2 reduction

Enter MySQL Command Line Client, enter password, enter "mysql>", enter command "show databases;", enter, see what database; establish database you want to restore, enter "create database voice;", enter; switch to newly established database, enter "use voice;", enter; import data, enter "source voice.sql;", enter, start importing, appear again "mysql>" and there is no prompt error, that is, restore successfully.

2. MySQL Backup and Restore under Linux

2.1 backup

[root@localhost ~]# cd /var/lib/mysql (enter MySQL library directory, adjust directory according to MySQL installation)[root@localhost mysql]# mysqldump -u root -p voice> voice.sql, enter password.

2.2 reduction

Method 1:

[root@localhost ~]# mysql -u root -p Enter, enter password, enter MySQL console "mysql>", same as 1.2 restore.

Method 2:

[root@localhost ~]# cd /var/lib/mysql (enter MySQL library directory, adjust directory according to MySQL installation)[root@localhost mysql]# mysql -u root -p voice

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.