主要思路是:
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高可用总给大家写出!!!