1, The mysqldump command backs up data
MySQL provides a convenient tool mysqldump for exporting database data and files from the command line. We can export database contents directly from the command line.
#MySQL dump common mysqldump -u root -p --databases Database 1 database 2 > xxx.sql
2, mysqldump common operation examples
1. Back up the data and structure of all databases
mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql
2. Back up the structure of all databases (add - d parameter)
mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql
3. Back up all database data (add - t parameter)
mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql
4. Back up the data and structure of a single database (, database name mydb)
mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql
5. Back up the structure of a single database
mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql
6. Back up the data of a single database
mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql
7. Backup the data and structure of multiple tables (the separate backup method of data and structure is the same as above)
mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql
8. Backup multiple databases at one time
mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql
3, Restore MySQL backup content
There are two ways to restore. The first is in the MySQL command line, and the second is to use the SHELL line to complete the restore
1. On the system command line, enter the following to restore:
mysql -uroot -p123456 < /data/mysqlDump/mydb.sql
2. After logging into the mysql system, find the files in the corresponding system through the source command to restore:
mysql> source /data/mysqlDump/mydb.sql
4, Write BASH to maintain a fixed number of backup files
Use vi or vim to write the script content and name it: mysql_dump_script.sh
#!/bin/bash #Save the number of backups and back up the 31 day data number=31 #Backup save path backup_dir=/root/mysqlbackup #date dd=`date +%Y-%m-%d-%H-%M-%S` #Backup tools tool=mysqldump #user name username=root #password password=TankB214 #Database to be backed up database_name=edoctor #Create if folder does not exist if [ ! -d $backup_dir ]; then mkdir -p $backup_dir; fi #Simply write mysqldump - U root - p123456 users > / root / mysqlbackup / users - $filename.sql $tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql #Write create backup log echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt #Find the backup that needs to be deleted delfile=`ls -l -crt $backup_dir/*.sql | awk '' | head -1` #Determine whether the current number of backups is greater than $number count=`ls -l -crt $backup_dir/*.sql | awk '' | wc -l` if [ $count -gt $number ] then #Delete the earliest generated backups and keep only the number of backups rm $delfile #Write delete file log echo "delete $delfile" >> $backup_dir/log.txt fi
5, Use crontab to execute backup scripts periodically
In Linux, tasks executed periodically are generally handled by the cron daemon [ps -ef|grep cron]. Cron reads one or more configuration files that contain the command line and its invocation time.
cron service
service crond start //Start service service crond stop //Shut down service service crond restart //Restart service service crond reload //service crond reload service crond status //View service status
crontab syntax
/Crontab files under var/spool/cron cannot be created or modified directly. The crontab file is created by the crontab command. Each line in the crontab file includes six fields. The first five fields specify the time when the command is executed, and the last field is the command to be executed.
Each field is separated by a space or tab.
The format is as follows:
minute hour day-of-month month-of-year day-of-week commands
Legal value 00-59 00-23 01-31 01-12 0-6 (0 is sunday)
In addition to numbers, there are several special symbols, namely "*", "/" and "-", "", * represents all numbers within the value range, "/" represents the meaning of each, "/ 5" represents every 5 units, "-" represents from a number to a number, "and" separates several discrete numbers.
Create cron script
Step 1: write a cron script file named mysqlRollBack.cron.
15,30,45,59 * * * * echo "xgmtest....." >> xgmtest.txt Indicates that the print command is executed every 15 minutes
Step 2: add scheduled tasks.
Execute command“ crontab mysqlRollBack.cron". Done
Step 3: "crontab -l" check whether the scheduled task is successful or whether the corresponding cron script is generated under / var/spool/cron