mysql5.6備份

wadeson發表於2017-08-24
MySql
備份之前：
最初的二進位制資訊：
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}'
779
在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

相關文章