Examples of mysql backup and restoration in Linux

This article introduces how to regularly and automatically back up website data and back up large databases in linux. For more information, see.

We can use commands and scripts to back up and restore common data. ,

1. Use commands to back up data

Database Backup is very important. If regular backup is performed, the data can be restored to the last normal state in the case of a system crash, minimizing the loss. MySQLl provides a mysqldump command, which can be used for data backup. The following assumes that you want to back up the tm database:

# Mysqldump-u root-p tm> tm_050519. SQL

Enter the password as prompted, and back up all the table structures and data of the tm database to tm_050519. SQL. Because the backup work is always required, if the data volume occupies a large space, in this case, you can use gzip to compress data. The command is as follows:

# Mysqldump-u root-p tm | gzip> tm_050519. SQL .gz

When the system crashes and the system is rebuilt, data can be restored as follows:

# Mysql-u root-p tm <tm_050519. SQL

Direct Recovery from compressed files:

# Gzip <tm_050519. SQL .gz | mysql-u root-p tm

Of course, many MySQL tools provide more intuitive backup and recovery functions, such as phpMyAdmin. However, I think mysqldump is the most basic and common.

 

2. Using crontab, the system regularly backs up mysql Databases every day.

[Abstract] The system crontab is used to regularly execute Backup files and save the backup results by date.

The system crontab is used to regularly execute Backup files and save the backup results by date to achieve the purpose of backup.

1. Create the path to save the backup file/mysqldata

# Mkdir/mysqldata

2. Create the/usr/sbin/bakmysql file.

# Vi/usr/sbin/bakmysql

Input

Rq = 'date + % Y % m % d'

Tar zcvf/mysqldata/mysql1_rq.tar.gz/var/lib/mysql

Or write it

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.

/Mysqldata/indicates the directory where the backup file is saved. This can be done according to your requirements.

 

3. Modify file attributes to make them executable.

# Chmod + x/usr/sbin/bakmysql

4. Modify/etc/crontab

# Vi/etc/crontab

Add

01 3 *** root/usr/sbin/bakmysql

Indicates that a backup is performed at three o'clock every day.

5. Restart crond.

#/Etc/rc. d/init. d/crond restart

Complete.

In this way, you can see such files in/mysqldata every day.

Mysql20040619.tar.gz

You can download it directly.

Cd/usr/local/mysql/bin/

Mysqldump-u username-p password -- databases Database Name>/backdata/ddd07-11-15. SQL

Restore syntax

Mysqladmin create target_db_name

Mysql target_db_name <backup-file. SQL

That is, the mysql database name <file name

OK. Note that there is no space behind-u. You just need to replace the above Chinese with your related information!

If the data is several GB or dozens of GB, the above method may be slow. Next I will introduce how to use BigDump, a super large backup file import tool.
Method

BigDump database recovery tool usage demonstration

Assume that we already have a backup file for the MySQL database, named PostgreSQL 2. SQL. The file size is about 150 MB. (do not expect phpMyAdmin to restore this large file ). The database character encoding is UTF-8. We will demonstrate how to use the bigdump. php tool to restore the backup file to the online database.

1. Get the BigDump file and set

Download the BidDump tool and decompress it to obtain a bigdump. php file. There is no doubt that some parameters need to be set in the bigdump. php file. Otherwise, how does it know the connection account of the database to be imported?

Open the bigdump. php file in a text editor. At about 38th rows, set the MySQL database connection parameters to be imported.

$ Db_server = 'localhost'; $ db_name = 'test'; $ db_username = 'root'; $ db_password = '000000'; if your database is UTF-8 encoded, here, we need to modify it:

$ Db_connection_charset = 'utf8 ';

Note that the hyphen must be removed. As shown in

 

Next, upload the configured bigdump. php file to the Joomla website. We recommend that you upload it to the temporary directory/tmp.

2. Upload the MySQL database backup file

Upload the database backup file to the/tmp directory where bigdump. php is located. Considering that the file size is large, we use the FTP software FileZilla to upload a zip file, and then use the eXtplorer Resource Manager component installed in the Joomla background to remotely decompress it and obtain the gate2. SQL file in the/tmp directory.

3. Start the BigDump tool.

Enter the URL of bigdump. php in the address bar of the browser and press enter to run the file. For example, if this demonstration is performed on the local test server, the corresponding running URL is:

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

If the URL you entered is correct, you should see the following screen:

 

This indicates that the BigDump tool has been successfully started. It automatically scans all files and subdirectories in the directory when it is started. If you find an SQL or gzip/zip file, assuming it is a database file, the corresponding operation link is displayed after these files. For example, if it detects that there is a file named SQL 2. SQL in the directory, two operation links are displayed in the same row on the right of the table, namely "Start Import" (Start Import) and "Delete file ). The SQL file size is also displayed in the table. The size of the backup file is close to 150 MB.

You can also see a file upload function. Click the Browse button to upload an SQL file (or its compressed package) for import. However, we do not recommend this feature. For large files, FTP upload is the best choice.

4. Run the BigDump import function

Click the "Start Import" link to see the following screen:

 

The preceding screenshot is taken after the import starts for a period of time. It can be seen that BigDump is importing SQL files smoothly. The table not only shows the file size, but also the number of imported bytes and the overall progress.

After a period of time (about 20 minutes), the 150 mb SQL file is finally imported, and the following figure is displayed:

 

A message is displayed at the place where the progress bar is displayed:

Congratulations: End of file reached, assuming OK

It means that the file must have been successfully reached. When you see this message, you can feel at ease. BigDump has successfully imported your SQL backup file to your specified MySQL database.

Note: After the database is restored successfully, do not forget to delete the bigdump. php backup tool and your SQL file! Import data using BigDump Database