This article takes about 9 minutes to read
Full backup of MySQL periodically (1)
MySQL > Alter database backup (2)
Upload MySQL backup to private cloud (3)
The profile
-
The introduction
-
Incremental backup
-
Restoring incremental backup
-
Regular backup
The introduction
After the launch of the product, our data is very important, not the slightest mistake should be made, we should be fully prepared, even if one day it will be hacked or malicious deletion, then GG. So we need to do full backup and incremental backup to our online database regularly. For example, perform incremental backup once a day and full backup once a week.
GitHub address: Click to read the original article to enter
https://github.com/zonezoen/MySQL_backup
Copy the codeIncremental backup
Before performing incremental backup, check the configuration file to check whether log_bin is enabled. For incremental backup, you must enable log_bin. First, go to the myslq command line and type the following command:
show variables like '%log_bin%';
Copy the codeIf the following command output is displayed, the function is disabled
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
Copy the codeModify the MySQL configuration items to the following code snippet: vim/etc/MySQL/MySQL. Conf., d/mysqld. CNF
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA # # The MySQL Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql #log-error = /var/log/mysql/error.log # By default we only accept Connections from localhost #bind-address = 160.0.1 # Disabling symbolic-links is recommended to prevent 什 aT Log-bin =/var/lib/mysql/mysql-bin server-id=123454; log-bin=/var/lib/mysql/mysql-bin server-id=123454; log-bin=/var/lib/mysql/mysql-bin server-id=123454Copy the codeAfter the modification, restart the mysql service and enter:
show variables like '%log_bin%';
Copy the codeThe status is as follows:
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
Copy the codeNow that we’re ready, we’re ready to learn about incremental backup.
View the mysql_bin.000*** log file currently in use,
show master status;
Copy the codeThe status is as follows:
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000015 | 610 | | | | +------------------+----------+--------------+------------------+-------------------+Copy the codeThe name of the log file is mysql-bin.000015.
The current database has the following data:
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
+-------+------+----+
Copy the codeWe insert a piece of data:
insert into `zone`.`users` ( `name`, `sex`, `id`) values ( 'zone3', '0', '4');
Copy the codeView the effect:
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
| zone3 | 0 | 4 |
+-------+------+----+
Copy the codeWe execute the following command to use the new log file:
mysqladmin -uroot -123456 flush-logs
Copy the codeThe log file changes from mysql-bin.000015 to mysql-bin.000016, and mysql-bin.000015 records the log of the insert command. The code above looks like this:
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000016 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+Copy the codeSo by now, you’ve actually done incremental backup.
Restoring incremental backup
Now delete the inserted data as follows:
delete from `zone`.`users` where `id`='4'
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
+-------+------+----+
Copy the codeSelect * from mysql-bin.000015;
mysqlbinlog /var/lib/mysql/mysql-bin.000015 | mysql -uroot -p123456 zone;
Copy the codeThe mysql_bin file that needs to be restored is specified with user name root, password 123456, and database name zone. The effect is as follows:
mysql> select * from users;
+-------+------+----+
| name | sex | id |
+-------+------+----+
| zone | 0 | 1 |
| zone1 | 1 | 2 |
| zone2 | 0 | 3 |
| zone3 | 0 | 4 |
+-------+------+----+
Copy the codeOK, the whole process of incremental backup is here, so how do we write it as a script file, the code is as follows:
#! /bin/bash
Create the following directories before using them
backupDir=/usr/local/work/backup/daily
Mysql > create mysql-bin.00000* from mysql-bin.00000
mysqlDir=/var/lib/mysql
Mysql data directory
logFile=/usr/local/work/backup/bak.log
BinFile=/var/lib/mysql/mysql-bin.index
Mysql index file path to the data directory
mysqladmin -uroot -p123456 flush-logs
This is used to generate a new mysql-bin.00000* file
# wC-l Count rows
Awk simply means reading a file line by line, slicing each line with a space as the default separator, and then parsing it.
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
This for loop is used to compare $Counter and $NextNum to determine if the file exists or is up to date
for file in `cat $BinFile`
do
base=`basename $file`
echo $base
/mysql-bin.000005; /mysql-bin.000005; /
NextNum=`expr $NextNum+ 1 `if [ $NextNum -eq $Counter ]
then
echo $base skip! >> $logFile
else
dest=$backupDir/$base
if(test -e $dest)
#test -e check whether the target file exists, write exist! To $logFile
then
echo $base exist! >> $logFile
else
cp $mysqlDir/$base $backupDir
echo $base copying >> $logFile
fi
fi
done
echo `date +"%Y %m month %d day %H:% m :%S"` $Next Bakup succ! >> $logFile
# NODE_ENV = $backUpFolder @ $backUpFileName/root/node/v8.11.3 / bin/node/usr/local/upload. Js
Copy the codeRegular backup
Run the following command to go to the page for editing scheduled tasks:
crontab -e
Copy the codeAdd the following command to execute the backup script every minute, and the crontab rules are separate articles that are not relevant to the topic of this article.
* * * * * sh /usr/your/path/mysqlbackup.sh
Copy the codeCrontab was mentioned in the previous tweet
Follow the wechat public account, reply to [mysql Resources], get the advanced video tutorial on mysql performance optimization
