Examples of MySQL backup and restore in Linux

Normal size Data backup restore we can use commands and scripts. 、

1. Use the command to implement the backup

Database backup is important. If you do a regular backup, you can restore the data to the last normal state in the event of a system crash, minimizing the loss. MYSQLL provides a mysqldump command that we can use to back up the data, assuming that you want to back up the TM database:

#mysqldump-U root-p TM > Tm_050519.sql

Press the prompt to enter the password, which will be the TM database all the table structure and data backup to Tm_050519.sql, because the total backup work, if the data volume assembly takes up a lot of space, then can use gzip compressed data, the command is as follows:

#mysqldump-U root-p TM | gzip > tm_050519.sql.gz

System crashes, you can restore data when you rebuild the system:

#mysql-U root-p TM < Tm_050519.sql

To recover directly from a compressed file:

#gzip < tm_050519.sql.gz | Mysql-u Root-p TM

Of course, there are a lot of MySQL tools to provide more intuitive backup recovery features, such as the use of phpMyAdmin is very convenient. But I think, mysqldump is the most basic, most general.

2. Using crontab, the system regularly backs up MySQL database every day

The "content feed" utilizes system crontab to perform backup files on a regular basis, keeping backup results by date to achieve the purpose of backup.

Use the system crontab to execute the backup file regularly, save the backup result by date, and achieve the purpose of backup.

1, create the path to save the backup file/mysqldata

#mkdir/mysqldata

2. Create/usr/sbin/bakmysql files

#vi/usr/sbin/bakmysql

Input

rq= ' Date +%y%m%d '

Tar Zcvf/mysqldata/mysql$rq.tar.gz/var/lib/mysql

or write

rq= ' Date +%y%m%d '

mysqldump--all-databases-u root-p Password >/mysqldata/mysql$rq.sql

/var/lib/mysql is the directory of your database files, some users are/usr/local/mysql/data, everyone may be different

/mysqldata/represents the directory where the backup files are saved, and everyone can do it according to their own requirements.

3, modify the file properties to make it executable

# chmod +x/usr/sbin/bakmysql

4. Modify/etc/crontab

#vi/etc/crontab

Add the following

3 * * * root/usr/sbin/bakmysql

Indicates a backup is performed 3 o'clock daily

5. Reboot Crond

#/etc/rc.d/init.d/crond Restart

Complete.

So every day you can see a file like this in/mysqldata.

Mysql20040619.tar.gz

You can download it directly.

cd/usr/local/mysql/bin/

Mysqldump-u User name-p password--databases library name >/backdata/ddd07-11-15.sql

Restore syntax

Mysqladmin Create Target_db_name

MySQL Target_db_name < Backup-file.sql

MySQL Library name < filename

OK, notice that------no space behind, you only need to change the Chinese above to your relevant information on it!

If it is a few g or dozens of GB of data the above method may be slower, let me introduce a very large backup file Import tool bigdump use
Method

Bigdump Database Recovery Tool usage Demo

Suppose we already have a backup file for a MySQL database, named Gate2.sql, with a file size of around 150MB (such a large file is not expected to be recovered by phpMyAdmin). The database character encoding is utf-8. Let's show you how to restore this backup file to the online database via the bigdump.php tool.

1. Get Bigdump file and set

We download the Biddump tool and unzip it to get a bigdump.php file. There is no doubt that the bigdump.php file needs to set some parameters, otherwise how does it know the connection account number of the database to be imported?

Open the bigdump.php file with a text editor, starting at line 38th, we set the MySQL database connection parameters to import.

$db _server = ' localhost '; $db _name = ' Test '; $db _username = ' root ';  $db _password = ' 111111 '; If your database is UTF-8 encoded, this should be modified here:

$db _connection_charset = ' UTF8 ';

Notice that the hyphen has to be removed. As shown in the following figure

Next will be a set of bigdump.php files uploaded to the Joomla website, recommended upload to/tmp this temporary directory.

2, upload MySQL database backup files

Now you need to upload the database backup file to the/tmp directory where bigdump.php resides. Considering the large file, we upload the file in zip format through the FTP software FileZilla, and then use the Extplorer Explorer component of Joomla background installation to extract it remotely, and get the Gate2.sql file in the/tmp directory.

3. Start Bigdump Tool

Enter bigdump.php URL in the browser address bar and return to run the file. For example, this demo is on the local test server, then the corresponding running URL is:

http://localhost/gate/tmp/bigdump.php

If you enter the URL correctly, you should see the following screen:

This means that the Bigdump tool has been successfully started, and it automatically scans all files and subdirectories in the directory at startup. If you find the SQL format or gzip/zip format file, assume that this is a database file, and after those files, the corresponding action link is displayed. For example, in the previous illustration, which detects that there is a gate2.sql file in the directory, two action links are displayed for the file on the same line on the right side of the table, "Start import" (Start importing) and "Delete file" (delete files). The table in the diagram above also shows the size of the SQL file, and you can see that the backup file is close to 150MB.

In the image above, you can also see a file upload function, click the "Browse" button, you can upload an SQL file (or its compressed package) to import. However, we do not recommend using this feature. For large files, FTP uploads are the best choice.

4. Run Bigdump Import function

Click the "Start Import" link in the image above to see the following screen:

The screenshot above is the one that was intercepted after the import began, and it can be seen that Bigdump is doing the SQL file import smoothly. The table shows not only the file size, but also the number of bytes that have been imported and the overall progress.

After a period of time (about 20 minutes), the 150MB size of the SQL file finally imported to the end, see the following screen:

Where the progress bar was originally displayed, a message is now displayed:

Congratulations:end of file reached, assuming OK

It means that you have reached the end of the file and must have succeeded. When you see the news, you can be completely relieved. Bigdump has successfully imported your SQL backup file into the MySQL database you specified.

Note: After the successful completion of the database recovery, do not forget to delete the bigdump.php Backup tool and your SQL file! Import

using the Bigdump database