mysql復制與備份

備份策略：

完全+差異+binlog

完全+增量+binlog

binlog最好能實時備份到另一個節點上。

完全備份，多久一次？

數據變化量：有20%，建議使用完全備份。

可用的備份存儲空間：

數據變化量很大，可以每天做一個完全備份，每周做一次增量備份。

數據變化量不大，可以每月做一次完全備份，每天做一次增量或者差異。

數據

備份工具：

mysqldump:發起一次數據查詢，生成table，插入數據。

如果備份的是整個庫，那么create database語句是存在的，如果是備份表，則沒有create database。也可以備份多個庫。取決于備份需求。備份數據庫會影響到用戶的訪問，應在訪問量最小的時候進行備份。

sed -i 's@ENGINE=MyISAM@ENGINE=InnoDB@g'   hellodb.sql

備份其中的一個數據庫

[root@centos7 ~]#mysqldump -uroot -hlocalhost -p hellodb > ./hellodb.sql.1

Enter password:

mysqldump -uroot  -hlocalhost  -p  –databases   hellodb > ./hellodb.sql.2(可以備份多個數據庫)

mysqldump -uroot  -hlocalhost  -p  –all–databases > ./data.sql.3(備份所有數據庫)

數據庫比較大時

InnoDB存儲引擎：支持溫備和熱備：

–single-transaction:創建一個事務，基于此快照執行備份。

其它選項：

-R，–routines:存儲過程和存儲函數；

–triggers

-E,–events

–master-data[=#]

1:記錄為CHANGE MASTER TO語句,此語句不被注釋；

2:記錄為CHANGE MASTER TO語句,此語句被注釋；

mysqldump -uroot -hlocalhost -p –master-data=2 –databases hellodb > ./hellodb.sql.2

–flush-logs:鎖定表完成后，即進行日志刷新操作。

mysqldump:

邏輯備份、完全備份、部分備份；

針對hellodb數據庫

mysqldump -uroot -hlocalhost -p –master-data=2 –databases hellodb > ./hellodb.sql.2

做了一個插入和刪除操作

此時查看 mysqlbinlog  /var/lib/mysql/master-log.000001

應該將DROP database hellodb刪除后再進行恢復，否則將陷入死循環。

截取掉的方法：1，文本工具2.mysqlbinlog  –stop-position=695 /var/lib/mysql/master-log.000001

然后再次查看發現已經沒有了DROP database hellodb。

另作保存，待會兒可以直接使用其來進行恢復。 mysqlbinlog  –stop-position=695 /var/lib/mysql/master-log.000001 > /tmp/mysql.log

cp ./hellodb.sql.2 /tmp/hellodb.sql

mysql -uroot -hlocalhost -pmageedu連入mysql后進行操作，關閉二進制文件的使用

SET sql_log_bin=OFF;

\. /tmp/hellodb.sql

./ /tmp/mysql.log(二進制日志)

此時數據得到了恢復后，SET sql_log_bin=ON;

基于lvm2的備份：

基于邏輯卷，可以做到幾乎熱備。

為了確保是快照之前的狀態。快照卷是把該之前的復制出來。

創建快照后，將其中文件內容復制出來，然后就可以刪除掉該快照了。

第一步打開客戶端，FLUSH TABLES WITH READ LOCK;請求保存讀鎖。

第二步，掛載。

備份時不能只備份數據文件，還要備份事務日志。事務日志和數據文件放在同一個邏輯卷上。

還原起來比較麻煩，用的不多。

Xtrabackup:

MyISAM:溫備，不支持增量備份；

InnoDB:熱備，增量；

物理備份，備份性能好，速旅快、可靠；支持壓縮功能；節約帶寬。備份完成后自動校驗備份結果是否可用；還原速度快；

man innobackupex

是一個客戶端工具，需要鏈接到服務器端。保存至本地路徑下。還可以用tar執行壓縮。

還可以實現部分備份。

常用的功能：

一.完全備份

指定用戶+密碼 本地的備份目錄。

innobackupex  –user=root –password=mageedu  /mydata/ba

得把事務日志中未提交的ROLLback

prepare預備工作

數據還原過程：

1.在另一臺設備上安裝xrabbackup工具，并將本機的文件復制過去

2.scp -rp 2016-11-19_14-42-33/ 10.1.44.7:/mydata/backup/

3.innobackupex –apply-log  /mydata/backup/2016-11-19_14-42-33/

4.innobackupex –copy-back /mydata/backup/2016-11-19_14-42-33/（把備份目錄復制到數據庫中）

5.cd /var/lib/mysql chown -R mysql ./*

ib_logfile0的大小變為48M，

6.修改/etc/my.cnf

7.此時再去進行啟動，如下

二.增量備份

數據增加后，INSERT INTO teachers (name,age,gender) VALUES ('Miejue Shitai',48,'F');

1.innobackupex –user=root –password=mageedu –incremental /mydata/backup/ –incremental -basedir=/mydata/backup/2016-11-19_14-42-33/

2.此時/mydata/backup的文件目錄變多了。

2016-11-19_14-42-33  2016-11-19_15-43-04

3.刪除數據后DELETE FROM students WHERE StuID=8;，再次進行備份，如果相對于2016-11-19_14-42-33進行備份那么叫做差異備份，如果是相對于2016-11-19_15-43-04叫做增量備份。

innobackupex –user=root –password=mageedu –incremental /mydata/backup/ –incremental -basedir=/mydata/backup/2016-11-19_15-43-04/

4.在另一太設備上，rm -rf /var/lib/mysql/*      rm -rf /mydata/backup/2016-11-19_14-42-33/

5.在本機上執行

[root@centos7 2016-11-19_15-51-49]# cat xtrabackup_binlog_info

master-log.000001 1242

[root@centos7 2016-11-19_15-51-49]# mysqlbinlog –start-position=1242 /var/lib/mysql/master-log.000001

[root@centos7 2016-11-19_15-51-49]# mysqlbinlog –start-position=1242 /var/lib/mysql/master-log.000001 > /mydata/backup/binlog.sql

[root@centos7 backup]# scp -rp ./* 10.1.44.7:/mydata/backup/

6.在另一臺設備上做prepare

2016-11-19_14-42-33  2016-11-19_15-43-04  2016-11-19_15-51-49  binlog.sql

合并增量，按次序合并。

innobackupex –apply-log –redo-only 2016-11-19_14-42-33/

[root@localhost backup]# innobackupex –apply-log –redo-only 2016-11-19_14-42-33/ –incremental-dir=/mydata/backup/2016-11-19_15-43-04/

[root@localhost backup]# innobackupex –apply-log   2016-11-19_14-42-33/ –incremental-dir=/mydata/backup/2016-11-19_15-51-49/

7.做copyback

innobackupex –copy-back   2016-11-19_14-42-33/

cd /var/lib/mysql chown -R mysql ./*

vim /etc/my.cnf   刪除innodb_log_file_size = 50331648

mysql -uroot -hlocalhost -p < binlog.sql 重裝二進制文件

主從復制

主服務器：

配置文件my.cnf

server_id=#

log_bin=log-bin

啟動服務：

mysql > GRANT REPLICATION   SLAVE,REPLICATION   CLIENT    ON  *.*    TO 'USERNAME'@'HOST'    IDENTIFIED BY 'YOUR_PASSWORD';

mysql > FLUSH   PRIVILEGES;

從服務器：

配置文件my.cnf

server_id=#

relay_log=relay-log

啟動服務：

mysql> CHANGE   MASTER   TO

MASTER   HOST='HOST',MASTER_USER='USERNAME',MASTER_PASSWORD='YOUR_PASSWORD',MASTER_LOG_FILE='BINLOG',MASTER_LOG_POS=#;

mysql> START SLAVE [IO_THREAD|SQL_THREAD];

mysql>SHOW   SLAVE   STATUS;

192.168.1.110為主節點，192.168.1.107為從節點

主從復制：

一、/etc/mycnf的配置

主機的/etc/my.cnf的配置

從機的配置：

二、mysql配置

主機上：

SHOW MASTER STATUS;   245

GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'10.1.0.%' IDENTIFIED BY 'replass';

FLUSH PRIVILEGES;

SHOW BINLOG EVENTS IN 'master-log.000002';

從機：

CHANGE  MASTER  TO MASTER HOST='10.1.44.2',MASTER_USER='repluser',MASTER_PASSWORD='replass',MASTER_LOG_FILE='master-log.000002',MASTER_LOG_POS=742;

SHOW SLAVE STATUS\G;

START SLAVE;

SELECT User,Host,Password FROM mysql.user;

從服務器設為只讀：

SELECT @@global.read_only;

FLUSH TABLE WITH READ LOCK;

雙主復制

第一步：/etc/my.cnf

第二步,mysql配置

主1機上

1.、GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'10.1.44.%' IDENTIFIED BY 'replpass';

2、FLUSH PRIVILEGES;

3、SHOW MASTER STATUS;

4、CHANGE  MASTER  TO MASTER_HOST='10.1.44.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=505;

5、START SLAVE;

主2機上

1、GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'10.1.44.%' IDENTIFIED BY 'replpass';

2、FLUSH PRIVILEGES;

3、SHOW MASTER STATUS;

4、CHANGE  MASTER  TO MASTER_HOST='10.1.44.2',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=505;

5、START SLAVE;

此時在一個主1機創建數據庫mydb，主2機也可以獲取該信息。

本實驗中出現過Slave_IO_Running:Connecting錯誤，我的問題是密碼寫錯。

網上有網友貼出了出現相關的錯誤的原因如下：

先理解Slave_IO_Running 為connecting,的含義 主要有三個原因 1、網絡不通 2、密碼不對 3、pos不對

從服務器意外終止后，服務再次啟動后，應該設置禁止自動連接另一臺服務器。

半同步復制

一、配置文件

主機：

從機：

二、mysql的配置

主機：

GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'repluser'@'10.1.44.%' IDENTIFIED BY 'replpass';

FLUSH PRIVILEGES;

SHOW MASTER STATUS\G   （POS 495）

從機：

CHANGE  MASTER  TO MASTER_HOST='10.1.44.2',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=495;

START SLAVE;

SHOW SLAVE STATUS\G

三、插件

主機：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

SHOW PLUGINS;

SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';

SET GLOBAL rpl_semi_sync_master_enabled=ON;

從機：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SHOW PLUGINS;

SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';

SET GLOBAL rpl_semi_sync_slave_enabled=ON;

主機：

SHOW GLOBAL STATUS LIKE '%semi%';

從機：

START SLAVE;

STOP SLAVE;

START SLAVE;