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;