mysql5.6备份

时间：2021-06-06 12:58:08
备份之前：
最初的二进制信息：
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 69417 |
| mysql-bin.000002 | 1388213 |
| mysql-bin.000003 | 120 |
+------------------+-----------+
3 rows in set (0.00 sec)
导入库信息：
source /tmp/estore.sql;
再次查询二进制日志信息：
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 69417 |
| mysql-bin.000002 | 1388213 |
| mysql-bin.000003 | 964116 |
+------------------+-----------+
3 rows in set (0.00 sec)
然后开始备份：
#mysqldump --all-databases --flush-logs --lock-all-tables --master-data=2 > /backup/all.sql
查看备份完成后的二进制日志信息：
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 69417 |
| mysql-bin.000002 | 1388213 |
| mysql-bin.000003 | 964163 |
| mysql-bin.000004 | 120 |
+------------------+-----------+
4 rows in set (0.00 sec)
在all.sql中也可以查看：
less all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;
备份：
备份的时候，不能在该服务器上把所有的数据文件都删除后仔导入备份的sql，这样备份并不能成功
现在将导出的sql复制到另一台数据库做测试
scp /backup/all.sql 192.168.223.128:/tmp
然后在备份机上导入备份的数据（导入数据的时候可以在打开的session连接中暂时关闭sql_log_bin=0，不记录二进制日志，导完数据之后再开启）
source /tmp/all.sql;
验证：
mysql> show tables;
+------------------------+
| Tables_in_estore |
+------------------------+
| cart |
| goods |
| orderitems |
| orders |
| province_city_district |
| user |
+------------------------+
6 rows in set (0.00 sec)
mysql> select count(*) from province_city_district;
+----------+
| count(*) |
+----------+
| 3522 |
+----------+
1 row in set (0.00 sec)
以上的完全备份就是每周日的凌晨三点执行，然后进行每天凌晨三点的模拟备份
在刚刚备份的那台主机上新增操作，记录二进制日志
mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)
mysql> use mydb;
Database changed
mysql> create table student(sid int,sname char(30));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student values (1,'wadeson');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values (2,'jsonhc');
Query OK, 1 row affected (0.01 sec)
假设上述就是今天的所有的操作记录，然后根据二进制信息，进行今天的备份：
由于进行的完全备份时候的二进制position为：
| mysql-bin.000004 | 120 |
所以今天凌晨三点的备份则为：
先查看凌晨三点时刻的二进制信息：
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 69417 |
| mysql-bin.000002 | 1388213 |
| mysql-bin.000003 | 964163 |
| mysql-bin.000004 | 779 |
此时，备份语句则为：
mysqlbinlog mysql-bin.000004 --start-position=120 --stop-position=779 > /backup/monday.sql
这就是假设的monday到星期二凌晨三点的操作
将备份的增量数据传送到测试机：
scp /backup/monday.sql 192.168.223.128:/tmp
然后进行测试：
mysql> source /tmp/monday.sql;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| estore |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use mydb;
Database changed
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| student |
+----------------+
1 row in set (0.00 sec)
mysql> select * from student;
+------+---------+
| sid | sname |
+------+---------+
| 1 | wadeson |
| 2 | jsonhc |
+------+---------+
2 rows in set (0.00 sec)
注意：每一次进行备份的时候，同时保存备份时刻的二进制日志信息
查看此时的二进制：
[root@wadeson ~]# mysql -e "show master status\G"|grep File|awk -F": " '{print $2}'
mysql-bin.000004
[root@wadeson ~]# mysql -e "show master status\G"|grep Position|awk -F": " '{print $2}'
在mysql5.6版本时候，执行mysql有关密码的脚本时，都会提醒：
[root@wadeson scripts]# sh bakcup_mysql.sh
Warning: Using a password on the command line interface can be insecure.
解决办法：
[root@wadeson ~]# mysql_config_editor set --login-path=root --host=localhost --user=root --password
Enter password:                         这里输入root的密码
[root@wadeson ~]# mysql_config_editor print --all                     打印已经设置的
[local]
user = username
password = *****
host = localhost
[root]
user = root
password = *****
host = localhost
由于这里使用的是root夫人用户执行mysqldump脚本，如果用其他的用户，上述命令修改对应的地方就行
[root@wadeson scripts]# mysql --login-path=root -e "show master status;"
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
然后在外面执行语句就不会有提醒了
现在执行脚本：完全备份
#!/bin/bash

MYSQL_CMD="/usr/local/mysql/bin/mysql"

MYSQLDUMP="/usr/local/mysql/bin/mysqldump"

DATE=`date +%F`

# backup mysql

${MYSQLDUMP} --login-path=root --all-databases --flush-logs --lock-all-tables --master-data=2|gzip > /backup/${DATE}.sql.gz

# save mysql-bin log

binlog=`${MYSQL_CMD} --login-path=root -e "show master status\G"|grep File|awk -F": " '{print $2}'`

pos=`${MYSQL_CMD} --login-path=root -e "show master status\G"|grep Position|awk -F": " '{print $2}'`

echo "$binlog" >> /backup/binlog/${DATE}.log

echo "$pos" >> /backup/binlog/${DATE}.log


执行完完全备份后，将二进制信息保存到一个日志文件中

增量备份脚本，将上面保存的二进制信息的log文件修改为23号，然后备份今天24号的增量
每一次完全备份后，都会刷新一下二进制日志，将此时的二进制信息写入到一个日志文件中，方便后面增量备份使用
执行得到的文件和binlog日志信息：进行增量备份
将上一次的binlog日志信息和这一次的binlog日志信息做对比
#!/bin/bash
MYSQL_CMD="/usr/local/mysql/bin/mysql"

MYSQLBINLOG="/usr/local/mysql/bin/mysqlbinlog"

DATE=`date +%F`

OLDDATE=`date +%F -d '-1 day'`

# get mysql-bin and position

binlog=`${MYSQL_CMD} --login-path=root -e "show master status\G"|grep File|awk -F": " '{print $2}'`

pos=`${MYSQL_CMD} --login-path=root -e "show master status\G"|grep Position|awk -F": " '{print $2}'`

old_binlog=`cat /backup/binlog/${OLDDATE}.log|sed -n '1p'`

old_pos=`cat /backup/binlog/${OLDDATE}.log|sed -n '2p'`

echo "$binlog" >> /backup/binlog/${DATE}.log

echo "$pos" >> /backup/binlog/${DATE}.log

# backup mysql

if [ $binlog = ${old_binlog} ];then

    ${MYSQLBINLOG} /data/mysql/${binlog} --start-position=${old_pos} --stop-position=$pos > /backup/${DATE}.sql

else

    ${MYSQLBINLOG} /data/mysql/${old_binlog} /data/mysql/${binlog} --start-position=${old_pos} --stop-position=$pos > /backup/${DATE}.sql

fi

　

[root@wadeson backup]# cat binlog/2017-08-23.log
mysql-bin.000010
120
[root@wadeson backup]# cat binlog/2017-08-24.log
mysql-bin.000010
568

相关文章