MySQL full backup and quick recovery methods

A simple MySQL full backup script that backs up the data for the last 15 days.

Backup

#Back up the mysql database every day (save the data script for the last 15 days)

DATE=$(date +%Y%m%d)

/home/cuixiaohuan/lamp/mysql5/bin/mysqldump -uuser -ppassword need_db > /home/cuixiaohuan/bak_sql/mysql_dbxx_$DATE.sql;

find /home/cuixiaohuan/bak_sql/ -mtime +15 -name '*.sql' -exec rm -rf {} \;

recover

mysql data import

drop databases need_db;

create databases need_db;

Import data: Encoding must be set for recovery

./mysql -uroot -p --default-character-set=utf8 need_db < xx.sql

Knowledge point expansion:

Backup and restore using mysqldump

1. Backup principle

The backup principle of mysqldump is relatively simple. First, find out the table structure that needs to be backed up and generate a create statement in a text file; then convert all data records in the table into an insert statement; these statements can be used to create a table and insert data.

2. Back up a database

Basic syntax:

>>> mysqldump -u username -p dbname table1 table2 ... > BackupName.sql

Example description:

mysqldump -u root -p test person > /tmp/backup.sql

3. Back up multiple databases

Basic syntax:

mysqldump -u username -p --databases dbname2 dbname2 > BackupName.sql

Example description:

mysqldump -u root -p --databases test mysql > /tmp/backup.sql

4. Back up all databases

Basic syntax:

mysqldump -u username -p -all-databases > BackupName.sql

Example description:

mysqldump -u -root -p -all-databases > /tmp/all.sql

5. Data Recovery

Basic syntax:

mysql -u root -p [dbname] < backup.sql

Example description:

mysql -u root -p < /tmp/backup.sql

The above is the detailed content of the method of MySQL full backup and quick recovery. For more information about MySQL simple full backup and quick recovery methods, please pay attention to other related articles on 123WORDPRESS.COM!