The profile
One, the introduction
2. Full backup
3. Restore full backup
Incremental backup
5. Restore incremental backup
6. Regular backup
Implementation in Docker
A,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.
Making address:
https://github.com/zonezoen/MySQL_backup
Copy the code2. Full backup
/usr/bin/mysqldump -uroot -p123456 --lock-all-tables --flush-logs test > /home/backup.sql
Copy the codeAs shown in the previous code, the function is to make a full backup of the Test database. The MySQL user name is root, and the password is 123456. The backup file path is /home. Of course, this path can be changed according to personal preference. The name of the backup file is backup.sql
Parameter – flush-logs: use a new log file to record subsequent logs;
Parameter – lock-all-tables: locks all databases;
Here is the script file I used:
Script file function is not very complicated, first of all, variable assignment. Then back up the database, then go to the directory where the backup files reside, and then compress the backup files. The penultimate third line is to use nodeJS to upload the backup file to the seven ox cloud, here is not too much description, and the topic of this article is not consistent, want to see the specific implementation can view GitHub source code.
#! /bin/bash
Create the following directories before using them
Get the current time
date_now=$(date "+%Y%m%d-%H%M%S")
backUpFolder=/home/db/backup/mysql
username="root"
password="123456"
db_name="zone"
Define the backup file name
fileName="${db_name}_${date_now}.sql"
Define the backup file directory
backUpFileName="${backUpFolder}/${fileName}"
echo "starting backup mysql ${db_name} at ${date_now}."
/usr/bin/mysqldump -u${username} -p${password} --lock-all-tables --flush-logs ${db_name} > ${backUpFileName}
Go to the backup file directory
cd ${backUpFolder}
# compress backup files
tar zcvf ${fileName}.tar.gz ${fileName}
# use nodejs to upload backup file other place
#NODE_ENV=$backUpFolder@$backUpFileName node /home/tasks/upload.js
date_end=$(date "+%Y%m%d-%H%M%S")
echo "finish backup mysql database ${db_name} at ${date_end}."
Copy the code3. Restore full backup
mysql -h localhost -uroot -p123456 < bakdup.sql
Copy the codeor
mysql> source /path/backup/bakdup.sql
Copy the codeWell, restore full backup is only two sentences, it seems that there is no need to say anything more. By the way, after restoring the full backup, the incremental backup after the full backup is also restored to the database.
Incremental backup
Before performing incremental backup, check the configuration file to check whether Logbin is enabled. For incremental backup, you must enable Logbin. 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 = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#binlog setting, enable incremental backup key
log-bin=/var/lib/mysql/mysql-bin
server-id=123454
Copy 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.
Check 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 code‘We 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.
5. Restore 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
BakDir=/usr/local/work/backup/daily
Mysql > create mysql-bin.00000* from mysql-bin.00000
BinDir=/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=$BakDir/$base
if(test -e $dest)
#test -e check whether the target file exists, write exist! To $LogFile
then
echo $base exist! >> $LogFile
else
cp $BinDir/$base $BakDir
echo $base copying >> $LogFile
fi
fi
done
Echo 'date +"%Y %m %d %H:% m :%S"' $Next Bakup succ! >> $LogFile
# NODE_ENV = $backUpFolder @ $backUpFileName/root /. NVM/versions/node/v8.11.3 / bin/node/usr/local/work/script/upload. Js
Copy the code6. Regular 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 codeImplementation in Docker
Implementation in Docker, in fact, is much worse, I will not repeat the above steps again, I will directly show you the pit I encountered in Docker.
1. Install VIm
I am using the official image of MySQL, docker-mysql image is used in Debian system, its version is older, there is no vim. First we need to execute the following code to install Vim:
apt-get update
apt-get install vim
Copy the codeAfter installed vim, can happily editor vim/etc/mysql/mysql. Conf. D/mysqld. CNF. I forgot to mention that this file cannot be mapped to the container, because the container itself contains the file.
2. Change the time zone
When we are backing up the database, there is a time factor, but the Docker container defaults to +0 time zone, while we are +8 time zone, we just map the host time zone file to it.
-v /etc/localtime:/etc/localtime:ro
Copy the codeCan also modify the time zone through into the container, it depends on personal choice, specific modification method blog: http://coolnull.com/235.html
3. Perform regular backup
How do you implement a scheduled backup in a container? Some people say, crontab? If you think so, there is no crontab in the official docker-mysql mirror. Then someone said, why don’t we just install a Crontab? But Docker encourages a “one process per container” approach. So after a fruitless search, I thought, why don’t we just assign the scheduled tasks to our host? Having the host machine periodically send commands into the container serves our purpose.
Run the following command to go to the page for editing scheduled tasks:
crontab -e
Copy the codeAdd the following command: Execute the backup script every minute
* * * * * docker exec ${docker_name} /bin/sh /usr/your/path/mysqlbackup.sh
Copy the codeThis article is contributed by Zone, link to the original article, a backend siege lion who loves to write and share. The public zone7 no.