在企业中，一般系统架构的瓶颈都会出现在数据库部分。mysql的主从架构很大程度上解决了这部分瓶颈，但是mysql的主从同步架构存在很多问题；例如：1.关于数据的写入部分（即主库）往往很难扩展，虽然很多大公司在逻辑业务上对数据进行拆分，比如将商品库存按照地区（一个地区）进行拆分一个库存就是一个主库，然后定期同步总库存），按产品类型划分（一种产品用一套数据库），但对于很多中小卖家来说还是比较困难公司实现；2、主从同步一般是一个主库，一旦主库出现问题，可能会直接导致整个主从同步架构的崩溃。虽然发现后可以慢慢恢复，但是这个恢复时间是很多企业无法接受的。今天这篇博文主要是提供一个解决主库单点故障问题的思路：主要思路是：1.一个主库（我们称之为master-01）提供服务，只负责写入数据；2.取出一台数据库服务器（我们称之为Master-02）的资源，作为master-01主库的从库（它们之间主从同步）；3.两个master库之间的高可用，可以使用keepalived等解决方案（必须保证master-01也是keepalived的master）4.程序写成高可用VIP地址，其中的IP地址调用主库；5、所有提供服务的从服务器与master-02进行主从同步；6、建议采用高可用策略。当master-01出现问题切换到master-02时，即使master-01恢复正常，也不要让它自动接管VIP地址，否则可能会出现数据混杂；这样做可以在一定程度上保证主库的高可用。一个主库宕机后，可以在极短的时间内切换到另一个主库（尽量减少主库宕机对业务的影响），减少主从同步给线上主库带来的压力;但也有几个缺点：比如master-02可能一直处于空闲状态（其实完全可以承担一个从库的角色来负责部分查询请求），2.这样一来，从库实际提供服务的库需要等待master-02同步完数据后才会去master-02同步数据，这可能会造成一定程度的同步延迟；3、如果master-01一旦恢复正常，会不会导致数据写乱（这个可以在keepalived中设置响应规则，让它不“夺权”，我们觉得调整一下就可以了手术架构简图如下：具体实现方案：1.在所有需要提供服务的服务器上安装MySQL服务（推荐源码安装）1.1yum安装依赖包yum-yinstallcmakemakegccgcc-c++ncurses-develbisonopenssl-devel1.2添加MySQL需要的用户/组组addmysqluseradd-gmysql-rmysql1.3下载MySQL源码包wgethttp://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz1.4创建MySQL安装位置所需目录mkdir/data/mydata/{data,tmp,logs}–pv1.5解压编译安装MySQLtarxfmysql-5.5.36.tar.gzcdmysql-5.5.36cmake.-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_TH\-DWITH_UNIX_ADDR=/tmp/mysql.sock\-DDEFAULT_CHARASET=uft8\-DDEFAULT_COLLATTON=utf9_general_ci\make&&makeinstall1.6提供mysql启动脚本csupport-files/mysql.server/etc/rc.d/init.d/mysqld1.7为master-01主库提供配置文件（32G内存比较保守（一个全连接占用大约25G内存）配置文件）[client]port=3306socket=/var/lib/mysql/mysql.sockdefault-character-set=utf-8[mysqld]server-id=1port=3306user=mysqlbasedir=/usr/local/mysqldatadir=/data/mydata/datatmpdir=/data/mydata/tmpsocket=/var/lib/mysql/mysql.sockskip-external-lockingskip-name-resolvedefault-storage-engine=INNODBcharacter-set-server=utf8wait-timeout=100connect_timeout=20interactive_timeout=100back_log=300myisam_recoverevent_scheduler=onlog-bin=/data/mydata/logs/mysql-binbinlog_format=rowmax_binlog_size=64Mbinlog_cache_size=1Mslave-net-timeout=10skip-slave-startslow_query_log=1long_query_time=1slow_query_log_file=/data/mydata/mysqllog/logs/mysql.slowlog-error=/data/mydata/mysqllog/logs/error.logmax_connections=1000max_user_connections=1000max_connect_errors=10000key_buffer_size=32M#以MyISAM为主的服务器，要调大此值max_allowed_pa??cket=64Mtable_cache=4096table_open_cache=4096table_definition_cache=4096sort_buffer_size=512Kread_buffer_size=512Kread_rnd_buffer_size=512Kjoin_buffer_size=512Ktmp_table_size=64Mmax_heap_table_size=64Mquery_cache_type=0query_cache_size=0bulk_insert_buffer_size=16Mthread_cache_size=64thread_concurrency=16#CPU核数*2thread_stack=256Kinnodb_data_home_dir=/data/mydata/datainnodb_log_group_home_dir=/data/mydata/mysqlGlog/logsinnodb1:file_path:autoextendinnodb_buffer_pool_size=16Ginnodb_buffer_pool_instances=4innodb_additional_mem_pool_size=16Minnodb_log_file_size=512Minnodb_log_buffer_size=32Minnodb_log_files_in_group=3innodb_flush_log_at_trx_commit=2innodb_lock_wait_timeout=10innodb_sync_spin_loops=40innodb_max_dirty_pages_pct=90innodb_support_xa=1innodb_thread_concurrency=0innodb_thread_sleep_delay=500innodb_file_io_threads=4innodb_concurrency_tickets=1000log_bin_trust_function_creators=1innodb_flush_method=o_directinnodb_file_per_per_table＃是否是否是否单表单单单空间空间空间空间空间空间空间_purge_batch_size=32innodb_old_blocks_pct=75innodb_change_buffering=alltr??ansaction_isolation=READ-COMMITTED[mysqldump]快速max_allowed_pa??cket=32M[mysql]no-auto-rehash[myisamchk]key_buffer_size=64Msort_buffer_size=256Mread_buffer=2Mwrite_buffer=2M[mysqlouthotcopy][mysqld_safe]open-files-limit=102401.8为master-02提供配置文件master-02的配置文件只需要在master-01上稍微修改server-id=20log_slave_updates=1#add（将replicationevent写入binlog，一个server既是主库又是从库，这个选项必须开启)replicate-same-server-id=0#Add(防止MySQL循环更新)relay_log_recovery=1#Add(MySQLrelay_log的自动修复功能)1.9作为从库提供配置文件(8G)[client]port=3306socket=/var/lib/mysql/mysql.sockdefault-character-set=utf8[mysqld]server-id=2port=3306user=mysqlbasedir=/usr/local/mysqldatadir=/data/mydata/datatmpdir=/data/mydata/tmpsocket=/var/lib/mysql/mysql.sockskip-external-lockingskip-name-resolvedefault-storage-engine=INNODBcharacter-set-server=utf8wait-timeout=100connect_timeout=20interactive_timeout=100back_log=300myisam_recoverevent_scheduler=onlog-bin=/data/mydata/logs/mysql-binbinlog_format=rowmax_binlog_size=64Mbinlog_cache_size=1Mslave-net-timeout=10relay_log_recovery=1slow_query_log=1long_query_time=1slow_query_log_file=/data/mydata/mysqllog/logs/mysql.slowlog-error=/data/mydata/mysqllog/logs/error.logmax_connections=500max_user_connections=500max_connect_errors=10000key_buffer_size=32M#以MyISAM为主的服务器，要调大此值max_allowed_pa??cket=64Mtable_cache=2048table_open_cache=2048table_definition_cache=2048sort_buffer_size=128Kread_buffer_size=128Kread_rnd_buffer_size=128Kjoin_buffer_size=128Ktmp_table_size=16Mmax_heap_table_size=16Mquery_cache_type=0query_cache_size=0bulk_insert_buffer_size=16Mthread_cache_size=64thread_concurrency=4#CPU核数*2thread_stack=128Kinnodb_data_home_dir=/data/mydata/datainnodb_log_group_home_dir=/data/mydata/mysqllog/logsinnodb_data_file_path=ibdata1:1G:autoextendinnodb_buffer_pool_size=2Ginnodb_buffer_pool_instances=4innodb_additional_mem_pool_size=4Minnodb_log_file_size=512Minnodb_log_buffer_size=16Minnodb_log_files_in_group=3innodb_flush_log_at_trx_commit=2innodb_lock_wait_timeout=10innodb_sync_spin_loops=40innodb_max_dirty_pages_pct=90innodb_support_xa=1innodb_thread_concurrency=0innodb_thread_sleep_delay=500innodb_file_io_threads=4innodb_concurrency_tickets=1000log_bin_trust_function_creators=1innodb_flush_method=O_DIRECTinnodb_file_per_table#是否采用单表单空间innodb_write_io_threads=8innodb_read_io_threads=8innodb_io_capacity=1000innodb_file_format=Barracuda#不开启单表单空间,此选项无效innodb_purge_threads=1innodb_purge_batch_size=32innodb_old_blocks_pct=75innodb_change_buffering=alltr??ansaction_isolation=READ-COMMITTED[mysqldump]quickmax_allowed_pa??cket=32M[mysql]no-auto-rehash[myisamchk]key_buffer_size=64Msort_buffer_size=256Mread_buffer=2Mwrite_buffer=2M[mysqlhotcopy]交互超时[mysqld_safe]打开文件限制=102401.10初始化MySQL/usr/local/mysql/scripts/mysql_install_db--user=mysql--datadir=/data/mydata/data/--basedir=/usr/local/mysql1.11给启动脚本赋予可执行权限，启动MySQLchmod+x/etc/rc.d/init.d/mysqld/etc/init.d/mysqldstart2。配置master-012.1添加主从同步账号mysql>grantreplicationslaveon*.*to'repl'@'192.168.237.%'identifiedby'123456';mysql>flushprivileges;2.2查看master数据库状态mysql>showmasterstatus;+----------------+----------+--------------+------------------+|文件|位置|Binlog_Do_DB|Binlog_Ignore_DB|+-------------------+----------+------------+----------------+|mysql-bin.000009|652|||+----------------+----------+--------------+------------------+1rowinset(0.01sec)2.3因为这是测试环境，所以可以保证没有数据写入，否则需要先锁表-->查看状态-->备份数据-->查看状态（保证没有变化）-->解锁表3.配置master-023.1配置master-02为master-01的slave#如果有数据在线，你需要先导入数据mysql>CHANGEMASTERTO->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;QueryOK,0rowsaffected(0.03sec)mysql>startslave;mysql>showslavestatus\GSlave_IO_Running:Yes#确保是yesSlave_SQL_Running:Yes#确保yes3.2配置master-02的同步用户mysql>grantreplicationslaveon*.*to'repl'@'192.168.237.%'identifiedby'123456';mysql>flushprivileges;3.3查看master-02状态mysql>showmasterstatus;+-----------------+----------+------------+-----------------+|文件|位置|Binlog_Do_DB|Binlog_Ignore_DB|+----------------+------------+-----------------+----------------+|mysql-bin.000004|689|||+-----------------+----------+------------+-------------------+1rowinset(0.00sec)4.按照以上步骤配置从库为master-02（为了节省篇幅，不再一一赘述)5.在master-01上创建数据库，测试同步效果6.去master-02follow库，查看数据是否已经同步。至此，数据同步完成。关于keepalived实现双主高可用，我会在下一篇keepalived实现MySQL高可用的文章中一直写给大家！！！博客链接：http://yanshisan.blog.51cto.com/7879234/1393063
