MySQL双主架构方案

在企业中,一般系统架构的瓶颈会出现在数据库这一部分,mysql主从架构在很大程度上解决了这部分瓶颈,但是在mysql主从同步的架构也存在很多问题;比如:1. 关于数据写入部分(也就是主库)往往很难做到扩展,虽然很多大公司在逻辑业务方面就进行对数据的拆分,比如商品库存按照区域去拆分(一个区域走一个库存也就是一个主库,然后定时同步总的库存),按照商品类型去划分(一个类型的商品走一套数据库),但是这对于很多中小型公司来说实现起来还是比较困难的; 2. 主从同步一般都是一个主库,一旦主库出现问题,就有可能直接导致整个主从同步架构崩盘,虽然发现后也是可以慢慢恢复的,但是这个恢复时间对于很多公司来说是难以接受的,今天的这篇博文就是主要给解决主库单点故障这个问题提供一个思路:

主要思路是:

1.一台主库(我们称之为master-01)提供服务,只负责数据的写入;

2.拿出一台数据库服务器(我们称之为Master-02)资源做master-01主库的从库(之间做主从同步);

3.两台主库之间做高可用,可以采用keepalived等方案(一定要保证master-01同时也要作为keepalived的主)

4.程序在调用主库IP地址的地方写为高可用的VIP地址;

5.所有提供服务的从服务器与master-02进行主从同步;

6.建议采用高可用策略的时候,当master-01出现问题切换到master-02的时候,即使master-01恢复了,也不要让它去自动承接VIP地址,否则可能造成数据的混写;

 这样做可以在一定程度上保证主库的高可用,在一台主库down掉之后,可以在极短的时间内切换到另一台主库上(尽可能减少主库宕机对业务造成的影响),减少了主从同步给线上主库带来的压力;但是也有几个不足的地方:比如master-02可能会一直处于空闲状态(其实完全可以让它承担一部分从库的角色来负责一部分查询请求的),2. 这样真正提供服务的从库要等master-02先同步完了数据后才能去master-02上去同步数据,这样可能会造成一定程度的同步延迟时间的加长;3. 如果master-01一旦恢复正常,会不会导致数据写入混乱(这个可以在keepalived中设置响应的规则,让其不”夺权”,我们认为的去调整操作即可

架构的简易图如下:

具体实施方案:

1. 在所有需要提供服务的服务器上安装MySQL服务(建议源码安装)

1.1 yum安装依赖包

1	yum -y  install  cmake  make  gcc gcc-c++  ncurses-devel bison openssl-devel

1.2 添加MySQL所需要的用户/组

1 2	groupadd mysql useradd  -g mysql -r mysql

1.3 下载MySQL源码包

1	wget http: //dev .mysql.com /get/Downloads/MySQL-5 .5 /mysql-5 .5.36. tar .gz

1.4 创建MySQL安装所需要的目录

1	mkdir  /data/mydata/ {data,tmp,logs} –pv

1.5 解压编译安装MySQL

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

tar  xf mysql-5.5.36. tar .gz cd  mysql-5.5.36 cmake . -DCMAKE_INSTALL_PREFIX= /usr/local/mysql  \        -DMYSQL_DATADIR= /data/mydata/data  \        -DSYSCINFDIR= /etc  \        -DWITH_INNOBASE_STORAGE_ENGINE=1 \        -DWITH_ARCHIVE_STORAGE_ENGINE=1 \        -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \        -DWITH_READLINE=1 \        -DWITH_SSL=system \        -DWITH_ZLIB=system \        -DWITH_LIBWARP=0 \        -DWITH_UNIX_ADDR= /tmp/mysql .sock \        -DDEFAULT_CHARASET=uft8 \        -DDEFAULT_COLLATTON=utf9_general_ci \ make  &&  make  install

1.6 为MySQL提供启动脚本

1	cp  support-files /mysql .server  /etc/rc .d /init .d /mysqld

1.7 为master-01主库提供配置文件(32G内存较为保守(满连接占用25G左右内存)的配置文件)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96

[client] port = 3306 socket =  /var/lib/mysql/mysql .sock default-character- set  = utf-8 [mysqld] server- id  = 1 port = 3306 user = mysql basedir =  /usr/local/mysql datadir =  /data/mydata/data tmpdir =  /data/mydata/tmp socket =  /var/lib/mysql/mysql .sock skip-external-locking skip-name-resolve default-storage-engine = INNODB character- set -server = utf8 wait-timeout = 100 connect_timeout = 20 interactive_timeout = 100 back_log = 300 myisam_recover event_scheduler = on log-bin= /data/mydata/logs/mysql-bin binlog_format = row max_binlog_size = 64M binlog_cache_size = 1M slave-net-timeout = 10 skip-slave-start slow_query_log  = 1 long_query_time = 1 slow_query_log_file =  /data/mydata/mysqllog/logs/mysql .slow log-error =  /data/mydata/mysqllog/logs/error .log max_connections = 1000 max_user_connections = 1000 max_connect_errors = 10000 key_buffer_size = 32M      #以MyISAM为主的服务器,要调大此值 max_allowed_packet = 64M table_cache = 4096 table_open_cache = 4096 table_definition_cache = 4096 sort_buffer_size = 512K       read_buffer_size = 512K       read_rnd_buffer_size = 512K   join_buffer_size = 512K       tmp_table_size = 64M      max_heap_table_size = 64M   query_cache_type = 0 query_cache_size = 0 bulk_insert_buffer_size = 16M thread_cache_size = 64 thread_concurrency = 16        #CPU核数*2 thread_stack = 256K innodb_data_home_dir =  /data/mydata/data innodb_log_group_home_dir =  /data/mydata/mysqllog/logs innodb_data_file_path = ibdata1:1G:autoextend innodb_buffer_pool_size = 16G innodb_buffer_pool_instances = 4               innodb_additional_mem_pool_size = 16M innodb_log_file_size = 512M innodb_log_buffer_size = 32M innodb_log_files_in_group = 3 innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 10 innodb_sync_spin_loops = 40 innodb_max_dirty_pages_pct = 90 innodb_support_xa = 1 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 500 innodb_file_io_threads = 4 innodb_concurrency_tickets = 1000 log_bin_trust_function_creators = 1 innodb_flush_method = O_DIRECT innodb_file_per_table           #是否采用单表单空间 innodb_write_io_threads = 8  innodb_read_io_threads = 8 innodb_io_capacity = 1000 innodb_file_format = Barracuda     #不开启单表单空间,此选项无效 innodb_purge_threads = 1 innodb_purge_batch_size = 32 innodb_old_blocks_pct = 75 innodb_change_buffering = all transaction_isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 32M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 64M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] open -files-limit = 10240

1.8 为master-02提供配置文件

1 2 3 4 5

master-02的配置文件仅需在master-01上稍作修改 server- id  = 20 log_slave_updates = 1     #添加(将复制事件写入binlog,一台服务器既做主库又做从库此选项必须要开启) replicate-same-server- id =0  #添加(防止MySQL循环更新) relay_log_recovery = 1    #添加(MySQLrelay_log的自动修复功能)

1.9 为从库提供配置文件(8G)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96

[client] port = 3306 socket =  /var/lib/mysql/mysql .sock default-character- set  = utf8 [mysqld] server- id  = 2 port = 3306 user = mysql basedir =  /usr/local/mysql datadir =  /data/mydata/data tmpdir  =  /data/mydata/tmp socket  =  /var/lib/mysql/mysql .sock skip-external-locking skip-name-resolve default-storage-engine = INNODB character- set -server = utf8 wait-timeout = 100 connect_timeout = 20 interactive_timeout = 100 back_log = 300 myisam_recover event_scheduler = on log-bin= /data/mydata/logs/mysql-bin binlog_format = row max_binlog_size = 64M binlog_cache_size = 1M slave-net-timeout = 10 relay_log_recovery = 1 slow_query_log = 1 long_query_time = 1 slow_query_log_file =  /data/mydata/mysqllog/logs/mysql .slow log-error =  /data/mydata/mysqllog/logs/error .log max_connections = 500 max_user_connections = 500 max_connect_errors = 10000 key_buffer_size = 32M      #以MyISAM为主的服务器,要调大此值 max_allowed_packet = 64M table_cache = 2048 table_open_cache = 2048 table_definition_cache = 2048 sort_buffer_size = 128K       read_buffer_size = 128K       read_rnd_buffer_size = 128K   join_buffer_size = 128K       tmp_table_size = 16M      max_heap_table_size = 16M   query_cache_type = 0 query_cache_size = 0 bulk_insert_buffer_size = 16M thread_cache_size = 64 thread_concurrency = 4        #CPU核数*2 thread_stack = 128K innodb_data_home_dir =  /data/mydata/data innodb_log_group_home_dir =  /data/mydata/mysqllog/logs innodb_data_file_path = ibdata1:1G:autoextend innodb_buffer_pool_size = 2G innodb_buffer_pool_instances = 4               innodb_additional_mem_pool_size = 4M innodb_log_file_size = 512M innodb_log_buffer_size = 16M innodb_log_files_in_group = 3 innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 10 innodb_sync_spin_loops = 40 innodb_max_dirty_pages_pct = 90 innodb_support_xa = 1 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 500 innodb_file_io_threads = 4 innodb_concurrency_tickets = 1000 log_bin_trust_function_creators = 1 innodb_flush_method = O_DIRECT    innodb_file_per_table                 #是否采用单表单空间 innodb_write_io_threads = 8  innodb_read_io_threads = 8 innodb_io_capacity = 1000 innodb_file_format = Barracuda      #不开启单表单空间,此选项无效 innodb_purge_threads = 1 innodb_purge_batch_size = 32 innodb_old_blocks_pct = 75 innodb_change_buffering = all transaction_isolation = READ-COMMITTED [mysqldump] quick max_allowed_packet = 32M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 64M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] open -files-limit = 10240

1.10 初始化MySQL

1	/usr/local/mysql/scripts/mysql_install_db  --user=mysql --datadir= /data/mydata/data/  --basedir= /usr/local/mysql

1.11 为启动脚本赋予可执行权限并启动MySQL

1 2	chmod  +x  /etc/rc .d /init .d /mysqld /etc/init .d /mysqld  start

2. 配置master-01

2.1 添加主从同步账户

1 2	mysql> grant replication slave on *.* to  'repl' @ '192.168.237.%'  idetified by  '123456' ; mysql> flush privileges;

2.2 查看主库的状态

1 2 3 4 5 6 7

mysql> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000009 |      652 |              |                  | +------------------+----------+--------------+------------------+ 1 row  in  set  (0.01 sec)

2.3 因为这是测试环境,可以保证没数据写入,否则需要先锁表-->查看状态-->备份数据-->查看状态(保证没变)-->解锁表

3. 配置master-02

3.1 配置master-02为master-01的从

1 2 3 4 5 6 7 8 9 10 11 12 13

#若是线上有数据需要先导入数据 mysql> CHANGE MASTER TO    -> MASTER_HOST= '192.168.237.128' ,    -> MASTER_PORT=3306,    -> MASTER_USER= 'repl' ,    -> MASTER_PASSWORD= '123456' ,    -> MASTER_LOG_FILE= 'mysql-bin.000009' ,    -> MASTER_LOG_POS=652; Query OK, 0 rows affected (0.03 sec) mysql> start slave; mysql> show slave status \G     Slave_IO_Running: Yes    #确保为yes     Slave_SQL_Running: Yes   #确保为yes

3.2 配置master-02的同步用户

1 2	mysql> grant replication slave on *.* to  'repl’@’192.168.237.%'  identified by  '123456' ; mysql> flush privileges;

3.3 查看master-02的状态

1 2 3 4 5 6 7

mysql> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 |      689 |              |                  | +------------------+----------+--------------+------------------+ 1 row  in  set  (0.00 sec)

4. 从库根据上面步骤配置为master-02的从即可(为了节省篇幅不再一一赘述)

5. 在master-01上创建一个数据库测试同步效果

6. 去master-02跟从库上分别查看是否已经同步过数据来

好了,至此数据同步已经完成