How to back up and restore the. SQL/zip/SQL .zip/gzip file in mysql

This article introduces how to use mysqldump in windows to import and export data for backup and copy files in linux for data backup and data recovery.

This article introduces how to use mysqldump in windows to import and export data for backup and copy files in linux for data backup and data recovery.

Import data in windows

(1) Export

Command: mysqldump-u username-p Database Name> file name

If the user name requires a password, enter a password check after executing this command. If the database user name does not require a password, do not add the "-p" parameter, which is the same during import. Note that the user name you enter must have operation permissions on the corresponding database; otherwise, data cannot be exported. Because it is used for system maintenance and export of all databases, we generally use root and other super user permissions.

For example, to export the database abc as a database file named db_abc. SQL to the current directory, enter the following command:

# Mysqldump-u root-p abc> db_abc. SQL

If you want to export the SQL. Zip or gzip file directly, the command is as follows:

Go to the mysqlbin> directory: mysqldump-u root-p b766951_apple> gzip> XX. SQL .gzip
Enter password :********

Enter the password of the root user to export the database.

(2) Import

Command: mysql-u username-p database name <file name

Similar to the mysqldump command, each parameter has the same meaning as mysqldump.

For example, to import data from the/root/backup/db_abc. SQL file to the abc database, run the following command:

# Mysql-u root-p abc </root/backup/db_abc. SQL or later

Import data in linux

If the database is large, you can use the direct copy method. However, different versions and operating systems may be incompatible with each other, so use it with caution.
3.1 prepare the original file
Package A file with tar
3.2 Create an empty Database
3.3 decompress
Decompress the package in the temporary directory, for example:
Cd/tmp
Tar zxf mydb.tar.gz
3.4 copy
Copy the extracted database file to the relevant directory.
Cd mydb/
Cp */var/lib/mysql/mydb/
For FreeBSD:
Cp */var/db/mysql/mydb/
3.5 permission settings
Change the owner of the copied files to mysql: mysql and the permission to 660.
Chown mysql: mysql/var/lib/mysql/mydb /*
Chmod 660/var/lib/mysql/mydb /*
Modify the data from mysqldump. In addition to adding the table) TYPE = MyISAM character set gbk;, add a set names gbk statement below;
In this way, the data can be smoothly imported to 4.1, without the need for garbled characters.