; mysql复制与备份 | Linux运维部落

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

1.jpg

应该将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

2.jpg

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

3.jpg

此时数据得到了恢复后,SET sql_log_bin=ON;

基于lvm2的备份:

基于逻辑卷,可以做到几乎热备。

为了确保是快照之前的状态。快照卷是把该之前的复制出来。

创建快照后,将其中文件内容复制出来,然后就可以删除掉该快照了。

第一步打开客户端,FLUSH TABLES WITH READ LOCK;请求保存读锁。

第二步,挂载。

备份时不能只备份数据文件,还要备份事务日志。事务日志和数据文件放在同一个逻辑卷上。

4.jpg

5.jpg

还原起来比较麻烦,用的不多。

Xtrabackup:

MyISAM:温备,不支持增量备份;

InnoDB:热备,增量;

物理备份,备份性能好,速旅快、可靠;支持压缩功能;节约带宽。备份完成后自动校验备份结果是否可用;还原速度快;

6.jpg

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 ./*

7.jpg

ib_logfile0的大小变为48M,

6.修改/etc/my.cnf

8.jpg

7.此时再去进行启动,如下

9.jpg

二.增量备份

数据增加后,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的配置

10.jpg

从机的配置:

11.jpg

二、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

12.jpg

13.jpg

第二步,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机也可以获取该信息。

14.jpg

本实验中出现过Slave_IO_Running:Connecting错误,我的问题是密码写错。

网上有网友贴出了出现相关的错误的原因如下:

先理解Slave_IO_Running 为connecting,的含义
主要有三个原因
1、网络不通
2、密码不对
3、pos不对

15.jpg

从服务器意外终止后,服务再次启动后,应该设置禁止自动连接另一台服务器。

半同步复制

一、配置文件

主机:

16.jpg

从机:

17.jpg

二、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;

原创文章,作者:178babyhanggege,如若转载,请注明出处:/60354

联系我们

400-080-6560

在线咨询:点击这里给我发消息

邮件:1660809109@qq.com

工作时间:周一至周五,9:30-18:30,节假日同时也值班

友情链接:测试  华宇招商  万达娱乐招商  万达登录  万达招商  万达主管QQ  万达娱乐开户  万达娱乐招商QQ  万达娱乐主管QQ  guoqibee.com