How to Backup and Restore mysql Database Too Large

  • 2021-11-14 07:20:32
  • OfStack

Command: mysqlhotcopy

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

The command is used as follows:


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

If you need to back up the entire database, you can add the regexp= ". *" parameter.

The Mysqlhotcopy command automatically completes data locking without shutting down the server during backup.

It can also refresh the log, so that the checkpoints of backup files and log files can be synchronized.

Local:

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

e:\ > cd mysql\ bin Enter

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

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 user name. (If you export a single table, enter the table name after the database name.)

If locktable error is prompted: mysqldump -uroot -p abc Add 1 empty grid after it --skip-lock-tables

3. You will see that the file news. sql is automatically generated under the bin file?

MySQL Command Line Import Database:

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

2. Same as Step 1 derived above

3. Enter MySQL: mysql-u User Name-p

Like the command line I entered: mysql-u root-p? ? (Enter the same and you will be asked to enter the password for MySQL.)

4. Create a new database in MySQL-Front, which is empty. For example, create a new target database named news

5. Input: mysql > use Destination Database Name

As I entered the command line: mysql > use news;

6. Import file: mysql > The file name of the source import;

As I entered the command line: mysql > source news.sql;

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

1. Backup and restore of MySQL under Win32

1.1 Backup

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

1.2 Restore

Enter MySQL Command Line Client, enter the password and enter mysql > ", enter the command" show databases; "Enter and see what databases there are; Create the database you want to restore and enter " create database voice; ", Enter; Switch to the database you just created and enter " use voice; ", Enter; Import data, enter " source voice.sql; ", enter, start importing, and" mysql "appears again > "And the restore succeeds without prompting an error.

2. Backup and restore of MySQL under Linux

2.1 Backup


[root@localhost ~]# cd /var/lib/mysql ( Enter into MySQL Library directory, according to your own MySQL Installation adjustment directory of )
[root@localhost mysql]# mysqldump -u root -p voice>voice.sql Enter the password. 

2.2 Reduction

Method 1:


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

Method 2:


[root@localhost ~]# cd /var/lib/mysql ( Enter into MySQL Library directory, according to your own MySQL Installation adjustment directory of )
[root@localhost mysql]# mysql -u root -p voice<voice.sql Enter the password. 

Summarize


Related articles: