mysql数据库集群架构主从、双主架构部署及数据备份

举报
水中游 发表于 2019/01/16 12:01:41 2019/01/16
【摘要】 mysql数据库集群架构主从、双主架构部署及数据备份

1.mysql主从与双主结构的优缺点:

1.1主从结构:

1.1.1 数据存在镜像和数据冗余,可以防止单一主机的数据丢失,提高数据的安全性。

1.1.2在从服务器上做数据备份,这样不影响主服务器的正常运行。如mysqldump备份时会加表所。
  1.1.3在从服务器上做数据报表和数据统计,这样可以避免生产服务器的访问压力过大;

1.1.4如果使用mysql proxy,在业务上可以实现读写分离。让读操作在从服务器上执行,减小主服务器的负担。不错的项目,可惜官方半途夭折了,不建议用,后期维护不便;可以考虑JDBC文件中配置,实现读写分离;

1.2 双主结构

优点:高可用、并发能力大

缺点:同步数据时容易出错,不易维护(slave-skip-errors=all)

2配置文件

2.1主从配置

主:

log-bin-trust-function-creators=1

log-bin=mysql-bin

server-id=1

sync-binlog=1 

log-bin-index=/home/mysql/mysql-log/mysql-bin.index

log-bin=/home/mysql/mysql-log/mysql-bin

binlog_format=mixed

从:

relay-log=mysql-relay

server-id=2

read-only=TRUE

replicate-ignore-db=mysql   //屏蔽对mysql库的同步
replicate-do-db=node1       //同步数据库名称


2.2双主配置

主1:

log-bin=mysql-bin

relay-log=mysql-relay

server-id=1

sync-binlog=1

auto-increment-increment=1

auto-increment-offset=2 

主2:

log-bin=mysql-bin

relay-log=mysql-relay

server-id=2

sync-binlog=1

auto-increment-increment=2

auto-increment-offset=2 

 

2.3 参数优化

1. innodb_buffer_pool_size

这个参数和MyISAMkey_buffer_size有相似之处,但也是有差别的。这个参数主要缓存innodb表的索引,数据,插入数据时的缓 冲。为Innodb加速优化首要参数。

 show engine innodb status \G;

spacer.gif

spacer.gif

数据大小+10%左右做为这个参数的值。

另外一个和此相关的参数:innodb_additional_mem_pool_size,来存放 Innodb的内部目录
 
这个值不用分配太大,系统可以自动调。如果表比较多,可以适当的增大。

show global variables like   'innodb_additional_mem_pool_size';

spacer.gif

 

2. innodb_flush_log_at_trx_commit(日志刷新设置)

show global variables like   'innodb_flush_log_at_trx_commit';

spacer.gif

0:每秒同步,并执行磁盘flush操作

1:每事物同步,并执行磁盘flush操作

2:每事物同步,但不执行磁盘的flush操作

1最安全0最保守2最性能

 

普通硬盘情况下,innodb_flush_log_at_trx_commit参数分别是1和2的情况,两者差别巨大,差异几乎到了9倍。

在做了raid的情况下,innodb_flush_log_at_trx_commit参数分别是1和2的情况,两者有10%左右的差异。

3.wait-timeout和interactive_timeout

wait-timeout = 100 指的是MySQL在关闭一个非交互的连接之前所要等待的秒数interactive_timeout = 100指的是mysql在关闭一个交互的连接之前所要等待的秒数nteractive_timeout和wait_timeout的默认值都是2880(8小时)。当这两个参数同时出现在[mysqld]里时,会以interactive_timeout的值为准。也就是说不管wait_timeout的值是多少,用show variables like   '%timeout%';查看时显示的两个值都是一样的,并且都是interactive_timeout的值。

这里设置innodb_lock_wait_timeout = 120    就可以了spacer.gif

实际报错:

spacer.gif

mysql> set   global wait_timeout=600;

Query OK, 0 rows affected   (0.00 sec)

mysql> set   global interactive_timeout=600;

Query OK, 0 rows   affected (0.00 sec)

 

4.慢查询日志

show variables like "%slow%";

log-slow-queries=/home/mysql/slowlog/slowquery.log  

(指定日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log)

long_query_time=2

(记录超过的时间,默认为10s)

log-queries-not-using-indexes

(log下来没有使用索引的query,可以根据情况决定是否开启)

log-long-format

(如果设置了,所有没有使用索引的查询也将被记录)

5、二进制日志

binlog-format=mixed

1)statement:修改数据的sql

优点:减少了bin-log日志量,节约了I/O性能

缺点:为了这些语句能在slave上正确运行。因此还必须记录每条在执行的时候的一些相关信息,mysql的复制像一些特定函数功能slave可与master上要保持一致会有很多相关问题(如sleep()函数,last_insert_id(),以及user-defind function(udf)会出现的问题。

2)row:记录那一条记录被修改成什么了

优点:解决无法被正确复制的问题

缺点:产生大量的日志内容。

3)mixed:是以上level的混合使用。

查看二进制日志(要在目录binlog目录下)

mysqlbinlog  -u root -proot--start-position=106   --stop-position=187 mysql-bin.000001

滚动:

service MySQld start

flush logs

删除:

show binary logs

purge binary logs to 'mysql-bin-000003'

6. innodb_thread_concurrency

最大的线程数

spacer.gif 1) 最少设置为(num_disks+num_cpus)*2。
   2)
可以通过设置成1000来禁止这个限制

7. innodb_flush_method

设置InnoDB同步IO的方式:
  1) Default –
使用fsync()。
     2) O_SYNC
以sync模式打开文件,通常比较慢。
     3) O_DIRECT
,在Linux上使用Direct IO。可以显著提高速度,特别是在RAID系统

上。避免额外的数据复制和double buffering(mysql buffering 和OS buffering)。

spacer.gif

设置:

innodb_flush_method=O_DIRECT

innodb_max_dirty_pages_pct75

 

8. innodb_log_buffer_size

磁盘速度是很慢的,直接将log写道磁盘会影响InnoDB的性能,该参数设定了log buffer的大小,一般4M。如果有大的blob操作,可以适当增大。

spacer.gif

  

 

3.主从同步

第一步master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,log-bin);  
  第二步slave将master的binary log events拷贝到它的中继日志(relay log);  

 第三步slave重放中继日志中的事件,将改变反映它自己的数据。 

具体过程如图所示:

主从复制

 

4.备份与还原

4.1压缩工具选择

工具

压缩前

压缩后

用时

tar –zcf

150M

9.1M

3

tar –Jcf

150M

7.2M

25

zip

150M

9.2M

3

gzip

150M

不支持压缩目录


bzip2

150M

不支持压缩目录


xz

150M

不支持压缩目录


经过在tar –zcf 、tar –Jcf、zip、gzip、bzip2、xz6款压缩工具测试对比,选择了tar –zcf为压缩工具,把tar –zcf压缩后的包再用xz压缩包大小为9.0M;

4.2 备份数据库

完全备份脚本:

Vi mysql-1dayback.sh

 

#/bin/bash

bktime=$(date +%F)

cd /home/mysql-data-bak/

#打包压缩文件

tar -zcf  mysqlback_$(date +%F-%H-%M-%S).tar.xz  day-*

#压缩daybak/目榉有文

xz day-*

#将打包文伥°allbk目

mv   *.xz    allbak/

#删除原

rm -rf   /home/mysql-data-bak/day-wanquan/*     /home/mysql-data-bak/day-zengliang/*

#每日完全备份一次

/usr/bin/innobackupex   --user=root --password=root /home/mysql-data-bak/day-wanquan/

 

增量备份:

Vi mysql-1hourback.sh

 

#!/bin/bash

# This is a backup script   for mysql' backup

name=`ls   /home/mysql-data-bak/day-wanquan/`

/usr/bin/innobackupex   --user=root --password=root    --incremental /home/mysql-data-bak/day-zengliang    --incremental-basedir=/home/mysql-data-bak/day-wanquan/$name

 

添加计划任务:

0 0 * * *  sh     /home/mysql-1dayback.sh

15 */1 * * *  sh     /home/mysql-1hourback.sh

 

4.3 还原数据库

 

恢复完全备份数据

[root@localhost data]#   innobackupex --apply-log  --redo-only /backup/2016-05-26_15-37-02/ 
  --apply-log   
回滚日志 
  --redo-only    回滚合并

 

合并第一个增量备份到完全备份里面

[root@localhost data]#   innobackupex --apply-log  --redo-only /backup/2013-09-06_15-37-02/    --incremental-dir=/backup/2016-05-26_16-15-17/ 
 
执行完结尾部分出现下面的消息表示成功了 
  innobackupex: completed OK!


 
恢复全部数据库
  [root@localhost data]# innobackupex --copy-back  /backup/2016-05-26_15-37-02/ 
 
 

修改恢复数据库目录中文件的权限
  [root@localhost data]# chown  -R  mysql:mysql * 

service mysqld start

5.计划

5.1 目前正式和验证环境都采用的是主从结构,确保主服务器正常使用;

5.2从服务器与主服务器做数据同步,观察三周时间,此期间解决数据同步时发生的问题;

5.3到6月13日确保从服务器正常同步数据,可以让从服务器做读操作,分担主服务器压力;

5.4今后根据业务需求考虑双主。

 

 

附:

 

1.解锁表命令:

flush   table with read lock 锁表,只读不改

unlock   tables 解锁

 

spacer.gif

2.备份

温备步骤:

备份所有库:mysqldump -uroot-lock-all-tables --flush-logs --master-date=2   --events  -B --all-databases   >all-'date +%F-$H-%M-%s'.sql

备份二进制:mysqlbinlog mysql-bin.000004>/opt/binlog-bak-'date   +%F-%H-%M-%S'.sql

 

3、xtrabackup

安装

rpm   -ivh percona-xtrabackup-2.3.3-1.el6.x86_64.rpm

 

 

 


【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。