中小型企业可参阅的类MySQL双主架构计划51CTO博客 - 超凡娱乐

中小型企业可参阅的类MySQL双主架构计划51CTO博客

2019年03月29日11时29分40秒 | 作者: 之玉 | 标签: 架构,同步,主库 | 浏览: 2363

在企业中,一般体系架构的瓶颈会出现在数据库这一部分,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装置依靠包

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

1.2 增加MySQL所需求的用户/组

groupadd mysql
useradd -g mysql -r mysql

1.3 下载MySQL源码包

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz

1.4 创立MySQL装置所需求的目录

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

1.5 解压编译装置MySQL

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供给发动脚本

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

1.7 为master-01主库供给装备文件(32G内存较为保存(满衔接占用25G左右内存)的装备文件)

[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供给装备文件

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)

[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

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

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

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

2. 装备master-01

2.1 增加主从同步账户

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

2.2 检查主库的状况

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的从

#若是线上有数据需求先导入数据
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的同步用户

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

3.3 检查master-02的状况

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随从库上别离检查是否现已同步过数据来

好了,至此数据同步现已完结,关于keepalived完结双主高可用,我会在下篇keepalived完结MySQL高可用总给我们写出!!!

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表超凡娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章