在企业中，一般系统架构的瓶颈都会出现在数据库部分。Mysql主从架构在很大程度上解决了这部分瓶颈，但是在Mysql主从同步架构中也存在很多问题。例如：1、关于数据写入部分（即主库），往往很难扩展，虽然很多大公司都是从逻辑业务上拆分数据，比如按照地区拆分商品库存（一个地区去一个库存就是一个主库，然后定时同步总库存），按照商品的种类划分（一种商品用一套数据库），但这对很多中小微来说还是有难度的-规模公司实现；2.主从同步一般是主库。一旦主库出现问题，可能会直接导致整个主从同步架构的崩溃。虽然发现后可以慢慢恢复，但是这个恢复时间是很多企业无法接受的。的。今天这篇博文主要提供了一个解决master数据库单点故障问题的思路：一个master数据库（我们称之为master-01）提供服务，只负责写数据；取出一台数据库服务器（我们称之为master-01，这里称之为Master-02）资源作为master-01主库的从库（它们之间主从同步）；两个master库之间的高可用，可以使用keepalived等方案（必须保证master-01也必须作为keepalived的master）；程序写成高可用VIP地址，调用主库IP地址；所有提供服务的从服务器与master-02进行主从同步；当出现问题切换到master-02时，即使master-01恢复了，也不要让它自动接受VIP地址，否则可能会混数据。这样做可以在一定程度上保证主库的高可用。主库宕机后，可以在极短时间内切换到另一个主库（尽量减少主库宕机对业务的影响），减少主从同步对线上主库压力的影响；但也有几个缺点：master-02可能一直处于空闲状态（实际上完全可以承担从库的角色来负责一部分查询请求）；这样实际提供服务的从库要等到master-02同步数据后，才能去master-02同步数据，这可能会造成一定程度的同步延迟时间；如果master-01恢复正常，会不会造成数据写入混乱（这个可以在keepalived中设置相应的规则，让它不“夺权”，我们认为调整一下操作就可以了。架构简图如下：具体实现方案：1.在所有需要提供服务的服务器上安装MySQL服务（推荐源码安装）1.yum安装依赖包yum-yinstallcmakemakegccgcc-c++ncurses-develbisonopenssl-devel2。添加MySQL所需的用户/组groupaddmysqluseradd-gmysql-rmysql3。下载MySQL源码包wgethttp://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.36.tar.gz4。创建MySQL安装所需目录mkdir/data/mydata/{data,tmp,logs}–pv5，解压编译安装MySQL/data\-DSYSCINFDIR=/etc\-DWITH_INNOBASE_STORAGE_ENGINE=1\-DWITH_ARCHIVE_STORAGE_ENGINE=1\-DWITH_BLACKHOLE_STORAGE_ENGINE=1\-DWITH_READLINE=1\-DWITH_SSL=IX/system\-DWITH_ZLIB=system\-DWITH_LIBWARP=UN\-DWITHmysql.sock\-DDEFAULT_CHARASET=uft8\-DDEFAULT_COLLATTON=utf9_general_ci\make&&makeinstall6，提供MySQL启动脚本csupport-files/mysql.server/etc/rc.d/init.d/mysqld7，提供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_query_log_startstimelongquery_size=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_rnd_buffer_size=512Kjoin_buffer_size=512Ktmp_table_size=64Mmax_heap_table_size=64Mquery_cache_bulkache_size=0=16Mthread_cache_size=64thread_concurrency=16#CPU核数*2thread_stack=256Kinnodb_data_home_dir=/data/mydata/datainnodb_log_group_home_dir=/data/mydata/mysqllog/logsinnodb_data_file_path=ibdata1:1G: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_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=alltransaction_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]interactive-timeout[mysqld_safe]open-files-limit=102408，为master-02配置文件提供的-02的配置文件只需要在master-01上稍微修改server-id=20log_slave_updates=1#add（将复制事件写入binlog，一台服务器既是主库又是从库，这个选项必须开启）replicate-same-server-id=0#Add（防止MySQL循环更新）relay_log_recovery=1#Add（MySQLrelay_log的自动修复功能）9.提供slave库的配置文件（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=alltransaction_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]open-files-limit=10初始化MySQL/usr/local/mysql/scripts/mysql_install_db--user=mysql--datadir=/data/mydata/data/--basedir=/usr/local/mysql11，给启动脚本赋予可执行权限，启动MySQLchmod+x/etc/rc.d/init.d/mysqld/etc/init.d/mysqldstart2.配置master-011、添加主从同步账号mysql>grantrreplicationslaveon*.*to'repl'@'192.168.237.%'identifiedby'123456';mysql>flushprivileges;2.查看master数据库状态mysql>showmasterstatus;+------------------+---------+--------------+----------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+------------------+------------+----------------+----------------+|mysql-bin.000009|652|||+-----------------+---------+--------------+----------------+1rowinset(0.01sec)3.因为这是测试环境，可以保证没有写入数据，否则需要先锁表-->查看状态-->备份数据-->查看状态（保证没有变化）-->解锁表3.配置master-021，配置master-02为master-01slave#如果有数据在线，需要先导入数据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#Make确定是yes2，配置同步用户mysql>grantreplicationslaveon*.*为master-02的'repl'@'192.168.237.%'identifiedby'123456';mysql>flushprivileges;3。查看master-02状态mysql>showmasterstatus;+----------------+---------+--------------+----------------+|文件|位置|Binlog_Do_DB|Binlog_Ignore_DB|+-----------------+------------+------------+--------------------+|mysql-bin.000004|689|||+----------------+---------+--------------+----------------+1rowinset(0.00sec)四、从库按以上步骤配置master-02的从库即可（为了节省篇幅，不再一一赘述）5、在master-01上创建数据库，测试同步效果6、进入master-02，跟随数据库查看数据是否已经被同步同步。至此，数据同步已经完成。关于keepalived实现双主高可用，我会在下一篇关于keepalived实现MySQL高可用的文章中总结，到时候分享给大家。原文链接：http://yanshisan.blog.51cto.com/7879234/1393063
