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

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

show engine innodb status \G;

数据大小＋１０％左右做为这个参数的值。

另外一个和此相关的参数：innodb_additional_mem_pool_size，来存放 Innodb的内部目录

这个值不用分配太大，系统可以自动调。如果表比较多，可以适当的增大。

show global variables like   'innodb_additional_mem_pool_size';

2. innodb_flush_log_at_trx_commit（日志刷新设置）

show global variables like   'innodb_flush_log_at_trx_commit';

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    就可以了

实际报错：

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

最大的线程数

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）。

设置：

innodb_flush_method=O_DIRECT

innodb_max_dirty_pages_pct＝75

8. innodb_log_buffer_size：

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

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 解锁

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

附件： MySql数据库集群架构方案.pdf 1005.83KB 下载次数：1次

MySQL 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。