mysql数据库集群架构主从、双主架构部署及数据备份
1.mysql主从与双主结构的优缺点:
1.1主从结构:1.1.1 数据存在镜像和数据冗余,可以防止单一主机的数据丢失,提高数据的安全性。 1.1.2在从服务器上做数据备份,这样不影响主服务器的正常运行。如mysqldump备份时会加表所。 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; 数据大小+10%左右做为这个参数的值。 另外一个和此相关的参数: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。 |
7. innodb_flush_method
设置InnoDB同步IO的方式: 上。避免额外的数据复制和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重放中继日志中的事件,将改变反映它自己的数据。 具体过程如图所示:
|
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/
合并第一个增量备份到完全备份里面 [root@localhost data]# innobackupex --apply-log --redo-only /backup/2013-09-06_15-37-02/ --incremental-dir=/backup/2016-05-26_16-15-17/
修改恢复数据库目录中文件的权限 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
|
- 点赞
- 收藏
- 关注作者
评论(0)