Simple Mysql Backup and restore method sharing _mysql

For security reasons, the database needs to be backed up frequently, or restored. For MySQL, the most convenient way may be to use the phpMyAdmin export, import functionality, but if your database volume is larger, as a WEB application phpMyAdmin may encounter "timeout" and the operation failed. Therefore, it is necessary to learn to back up and restore the database in command line mode.

Linux system under
Backing up a database

Back up the MySQL database in Linux command line mode with the mysqldump command:

Mysqldump-u mysqluser-p test_db

A little explanation of the above order:

• -U means that you want to specify a MySQL username to connect to the database service, as above mysqluser is the MySQL user name.
• -P means you need to have a valid password that corresponds to the user name above.
• The last parameter is the name of the database that needs to be backed up: test_db

If you execute the above command directly, then you will be prompted to enter the MySQL password, the data password, it will directly back up the SQL script to display on the screen, which is certainly not the result we want. We need to back up the database into a single file, with the following commands:

Mysqldump-u mysqluser-p test_db > Test_db.sql

In this way, a file named Test_db.sql is backed up in the current directory.

Of course, if the size of the database is large, the backed-up files are usually compressed, and backup and compression can be done within the same line of command:

Cat Test_db.sql | Mysql-u mysqluser-p test_db

When compressing, it is best to add the extension. gz to the filename, so that you have a good idea of the next time you restore the database.
Restore Database

The command to restore the database is also simple, if the file you backed up is an uncompressed version, the command to restore the database is as follows:

Cat Test_db.sql | Mysql-u mysqluser-p test_db

With the Cat command, the SQL script content is exported to the MySQL program for restoration. As you can see, some of the parameters behind MySQL are the same as at backup time.

If it is a compressed version of the backup file, you need to use the command to restore:

Gunzip < test_db.sql.gz | Mysql-u mysqluser-p test_db

Similarly, with the Gunzip command, unzip, and then output the script content to the MySQL program to restore.

If you don't consider compressing the. sql file, you can also use the following two statements to export, import

Mysqldump-u root-p test_db > Test_db.sql
mysql-u root-p test_db < Test_db.sql

Under Windows system
Backup
Start Menu | Run | cmd | Access the Bin folder using the "Cd/program files/mysql/mysql Server 5.0/bin" command | Use the Mysqldump-u user name-p databasename >exportfilename to export the database to a file, such as Mysqldump-u root-p voice>voice.sql, and then enter the password to start exporting.

Restore
entering the MySQL command line Client, enter the password, enter the "mysql>", enter the command "show databases;", and return to see what database it is; Create the database you want to restore, enter "create Database voice; ", carriage return; switch to the newly established databases, enter" use voice; ", import the data, enter" source voice.sql; ", carriage return, start the import, reappear" mysql> "and the restore succeeds without prompting for an error.