MySQL > Alter database backup (2)

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 code

Incremental 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 code

If 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 code

Modify 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 code

After the modification, restart the mysql service and enter:

show variables like '%log_bin%';
Copy the code

The 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 code

Now 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 code

The 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 code

The 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 code

We insert a piece of data:

insert into `zone`.`users` ( `name`, `sex`, `id`) values ( 'zone3', '0', '4');
Copy the code

View 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 code

The 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 code

So 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 code

Select * from mysql-bin.000015;

mysqlbinlog /var/lib/mysql/mysql-bin.000015 | mysql -uroot -p123456 zone;
Copy the code

The 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 code

OK, 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 code

Regular backup

Run the following command to go to the page for editing scheduled tasks:

crontab -e
Copy the code

Add 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 code

Crontab was mentioned in the previous tweet

---

Follow the wechat public account, reply to [mysql Resources], get the advanced video tutorial on mysql performance optimization