How to backup and restore the mysql database if it is too large

Command: mysqlhotcopy

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

The command usage is:

mysqlhotcopy -u root -p<rootpass> db1 db2 … dbn <output_dir>

If you need to back up all databases, you can add the –regexp=”.*” parameter.

The MySQL hotcopy command can automatically lock data without shutting down the server during backup.

It also flushes the log so that the backup files and the log file checkpoints are kept in sync.

local:

1. Enter the bin folder under the MySQL directory: e: press 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 a password, enter the password for the database username (if exporting a single table, enter the table name after the database name)

If a locktable error is prompted: leave a space after mysqldump -uroot -p abc and add --skip-lock-tables

3. Will you see the file news.sql automatically generated in the bin file?

Import the database using the MySQL command line:

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

2. Same as step 1 of the export above

3. Enter MySQL: mysql -u username -p

For example, the command line I entered is: mysql -u root -p?? (After entering the same, you will be asked to enter the MySQL password)

4. Create a new database in MySQL-Front. At this time, it is an empty database. For example, create a new target database named news.

5. Enter: mysql>use target database name

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

6. Import file: mysql>source import file name;

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

MySQL backup and restore are both done using the mysqldump, mysql, and source commands.

1. Backup and restore of MySQL under Win32

1.1 Backup

Start menu | Run | cmd | Use the "cd \Program Files\MySQL\MySQL Server 5.0\bin" command to enter the bin folder | Use "mysqldump? -u username -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.

1.2 Restore

Enter MySQL Command Line Client, enter the password, enter "mysql>", enter the command " show databases； ", press Enter, and see what databases there are; create create database voice； ", press Enter; switch to the newly created database, enter " use voice； ", press Enter; import data, enter " source voice.sql； ", press Enter, start importing, and when "mysql>" appears again and there is no error prompt, the restoration is successful.

2. Backup and restore of MySQL under Linux

2.1 Backup

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

2.2 Restore

Method 1:

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

Method 2:

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

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links