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 directly export the database content through the command line. First, let's briefly understand the usage of mysqldump command:
#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
In Linux, BASH script is usually used to write the content to be executed, and crontab is executed regularly to realize automatic log generation.
The following code function is to backup mysql. In cooperation with crontab, the backup content is the daily MySQL database records in recent one month (31 days).
Write BASH to maintain a fixed number of backup files
In Linux, 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 '{print $9 }' | head -1`
#Determine whether the current number of backups is greater than $number
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | 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
fiThe main meanings of the above codes are as follows:
1. First set various parameters, such as number, the maximum number to be backed up, backup path, user name, password, etc.
2. Execute mysqldump to save the backup file, print the operation to log.txt in the same directory, and mark the operation log.
3. Define the file to be deleted: get the ninth column, that is, the file list, through the ls command, and then define the file to be deleted with the latest operation time.
4. Define the number of backups: count the number of lines of files ending in sql through ls command and wc -l.
5. If the file size exceeds the limit, delete the sql file created the earliest
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. The cron configuration file is called "crontab", which is short for "cron table".
cron service
cron is a timed execution tool under Linux, which can run jobs without human intervention.
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
The crontab command is used to install, delete, or list the tables used to drive the cron daemon. The user puts the command sequence to be executed into the crontab file for execution. Each user can have its own crontab file/ Crontab files under var/spool/cron cannot be created or modified directly. The crontab file is created by the crontab command.
How to enter the command and time to execute in the crontab file. Each line in the 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"*","/"and"-",","，*Represents all numbers within the value range,"/"Represents the meaning of each,"/5"Means every 5 units,"-"Represents from a number to a number,","Separate several discrete numbers. -l Displays the current on standard output crontab. -r Delete current crontab File. -e use VISUAL perhaps EDITOR The editor to which the environment variable refers edits the current crontab File. When you finish editing and leave, the edited file will be installed automatically.
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 the command "crontab crontest.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
Note: this operation is to directly replace the crontab under the user instead of adding a new one
Execute the scheduled task script written regularly (remember to give the shell script execution permission first)
0 2 * * * /root/mysql_backup_script.sh
The crontab command is then used to periodically script the instructions
crontab mysqlRollback.cron
Then check whether the scheduled task has been created through the command:
An example of using crontab is attached:
1. At 6 o'clock every morning
0 6 * * * echo "Good morning." >> /tmp/test.txt //Note that if you simply echo, you can't see any output from the screen, because cron email ed any output to the root mailbox.
2. Every two hours
0 */2 * * * echo "Have a break now." >> /tmp/test.txt
3. Every two hours and 8 a.m. between 11 p.m. and 8 a.m
0 23-7/2，8 * * * echo "Have a good dream" >> /tmp/test.txt
4. The 4th of each month and 11 a.m. from Monday to Wednesday every week
0 11 4 * 1-3 command line
5.1 at 4 a.m
0 4 1 1 * command line SHELL=/bin/bash PATH=/sbin:/bin:/usr/sbin:/usr/bin MAILTO=root //If there is an error or data output, the data will be sent to this account HOME as an email=/
6. Execute the script in / etc/cron.hourly every hour
01 * * * * root run-parts /etc/cron.hourly
7. Execute the script in / etc/cron.daily every day
02 4 * * * root run-parts /etc/cron.daily
8. Execute the script in / etc/cron.weekly every week
22 4 * * 0 root run-parts /etc/cron.weekly
9. Execute the script in / etc/cron.monthly every month
42 4 1 * * root run-parts /etc/cron.monthly
Note: the "run parts" parameter is. If you remove this parameter, you can write the name of a script to run instead of the folder name.
10. Execute the command at 4:00 p.m., 5:00 p.m. and 5 min, 15 min, 25 min, 35 min, 45 min and 55 min at 6:00 p.m. every day.
5，15，25，35，45，55 16，17，18 * * * command
11. At 3:00 p.m. on Monday, Wednesday and Friday, the system enters the maintenance state and restarts the system.
00 15 * * 1，3，5 shutdown -r +5
12. Execute the command innd/bbslin in the user directory at 10 and 40 minutes every hour:
10，40 * * * * innd/bbslink
13. Execute the bin/account command in the user directory at 1 minute every hour:
1 * * * * bin/account