The most common method for mysql database backup is to directly use the mysqldump command, but the disadvantage of this command is that it takes a long time for big data, therefore, many webmasters directly use the copy database for fast backup and recovery.
Functions provided by mysql
Export
Command:
Mysqldump-u username-p database> database. SQL
Example:
| The Code is as follows: |
Copy code |
Mysqldump-u root-p db1> db1. SQL (back up database db1 to db1. SQL) |
Enter the password.
Import
Create an empty database first
Mysql-u root-p (enter the password and enter mysql)
Create database db1; (create a database named db1)
Exit (exit mysql)
Command:
Mysql-u username-p database <database. SQL
Example:
| The Code is as follows: |
Copy code |
Mysql-u root-p db1 <db1. SQL (import data from the backup file db1. SQL to database db1) |
Enter the password.
By copying an object
If the database is large, you can use the copy method for backup. However, different operating systems may cause incompatibility.
Export
Enter the mysql directory and package the database directory to be backed up
Example:
| The Code is as follows: |
Copy code |
Cd/var/lib/mysql (enter the mysql directory)
Tar-zcvf db1.tar.gz db1) |
Import
Create an empty database, copy the packaged files to the mysql directory, and decompress the package.
Example:
| The Code is as follows: |
Copy code |
Mysql-u root-p (enter the password and enter mysql)
Create database db1; (create a database named db1)
Exit (exit mysql)
Cp db1.tar.gz/var/lib/mysql (copy the package file to the mysql directory)
Cd/var/lib/mysql (enter the mysql directory)
Tar-zxvf db1.tar.gz (decompress it to the current directory)
|
Scripts in linux
| The Code is as follows: |
Copy code |
#! /Bin/bash
# Mysql autobackup shell # write by tuspark.cn
# ------------------- User name and password related to the database, database name to be backed up, backup directory, etc.
Dbuser = root
Dbpasswd = XXXXX
Dbserver = localhost
Dbname = XXXXX
Dbopt = -- opt
Backupdir =/dcbackup/
# ------------------- Whether to enable FTP remote backup. The value 0 indicates no, and the value 1 indicates yes.
Copytoftp = 1
Ftpserver = XXXXX
Ftpuser = XXXXX
Ftppasswd = XXXXX
# ------------------- The following parameters
Fileprefix = dcradiusdump
Filename = $ backupdir 'date + % F'. SQL
Newfile = $ fileprefix-'date already created f'.tar.gz
Keepdays = 10
# ------------------- The following are the logs for backup.
Logfile =/var/log/mysqlbackup. log
Logtmp =/var/log/mybackup. tmp
#===================================================== ==========
If [! -D $ backupdir]
Then
Echo "$ backupdir is not exist, then make..."> $ logfile
Mkdir-p $ backupdir
Fi
Echo "start ===========================================> ">>> $ logfile
Echo "Beginning backup 'date' + % F % t'"> $ logfile
Echo "Delete $ keepdays days ago files..."> $ logfile
Find $ backupdir-name $ fileprefix *-mtime + $ keepdays-fls $ logtmp-exec rm {};
Echo "Deleted Backup file is:" >>$ logfile
Cat $ logtmp> $ logfile
Echo "Delete old file Success! ">>> $ Logfile
If [-f $ backupdir $ newfile]
Then
Echo "$ newfile backup exist, backup stop..."> $ logfile
Else
If [-z $ dbpasswd]
Then
Mysqldump-u $ dbuser-h $ dbserver $ dbopt $ dbname> $ dumpfilename
Else
Mysqldump-u $ dbuser-p $ dbpasswd-h $ dbserver $ dbopt $ dbname> $ dumpfilename
Fi
Tar czvf $ backupdir $ newfile $ dumpfilename >>$ logfile 2> & 1
Echo "$ backupdir $ newfile Backup Success! ">>> $ Logfile
Rm-fr $ dumpfilename
If [$ copytoftp = 1]; then
If [-z $ ftpserver]; then
Echo "Ftp Server not set, Copy to Ftp Failed..."> $ logfile
Exit 1
Elif [-z $ ftpuser]; then
Echo "Ftp user not set, Copy to Ftp Failed..."> $ logfile
Exit 2
Elif [-z $ ftppasswd]; then
Echo "Ftp password not set, Copy to Ftp Failed..."> $ logfile
Exit 3
Else
Echo "Start copy to Ftp server..."> $ logfile
Ftp-n> $ logfile
Fi |
After writing it, we can add a scheduled task to back up every day or every hour. The Linux scheduled task setting method is described as follows:
