生产环境mysql安装、维护

生产环境mysql安装、维护一、部署架构架构描述：Mysql集群提供两个vip,一读,一写实现读写分离。写vip由两台主节点的keepalived提供,保证始终有1台写节点在线,两台写节点之间互为主从,保证数据一致性.读vip由两台LVS提供,并由keepalived提供热备及后端服务器的健康检查.LVS将读请求代理到多台mysql从节点,mysql从节点与任意1台主节主从同步。二、Mysql安装、配置主数据库服务器master1配置。1、创建用户groupaddmysqluseradd-r-M-gmysqlmysql2、解压程序包tarzxvfmysql-5.6.17-linux-glibc2.5-x86_64.tar.gz-C/usr/localmv/usr/local/mysql-5.6.17-linux-glibc2.5-x86_64/usr/local/mysql3、初始化数据库cd/usr/local/mysqlchown-Rmysql.chgrp-Rmysql.yuminstalllibaio安装依赖包scripts/mysql_install_db--user=mysqlchown-Rroot.chown-Rmysqldata4、编辑配置文件my.cnfmy.cnf[client]port=3306socket=/var/lib/mysql/mysql.sockdefault-character-set=utf8[mysql_server]default_character_set=utf8[mysqld]skip-name-resolve=trueserver_id=1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESexplicit_defaults_for_timestamp=1character-set-server=utf8collation-server=utf8_general_ciport=3306basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/var/lib/mysql/mysql.sockuser=mysqllog_slave_updates=1log_bin=mysql-master1-bin.logbinlog_format=mixedbinlog_cache_size=4Mmax_binlog_cache_size=512Mmax_binlog_size=1Gexpire_logs_days=20binlog-ignore-db=mysqlbinlog-ignore-db=testbinlog-ignore-db=information_schemakey_buffer_size=512Msort_buffer_size=16Mread_buffer_size=8Mread_rnd_buffer_size=64Mjoin_buffer_size=16Mthread_cache_size=128Mquery_cache_size=128M#query_cache_limit=2M#query_cache_min_res_unit=2Kthread_concurrency=16#table_cache=614table_open_cache=512open_files_limit=10240#back_log=384back_log=600max_connections=15000max_connect_errors=6000external-locking=FALSEmax_allowed_packet=64Mdefault-storage-engine=InnoDBthread_stack=512Ktransaction_isolation=REPEATABLE-READtmp_table_size=256Mmax_heap_table_size=512Mbulk_insert_buffer_size=256Mmyisam_sort_buffer_size=256Mmyisam_max_sort_file_size=10G#myisam_max_extra_sort_file_size=10Gmyisam_repair_threads=1myisam_recoverlong_query_time=2slow_query_logslow_query_log_file=/usr/local/mysql/slow.log#skip-locking#skip-networkinginnodb_additional_mem_pool_size=64Minnodb_buffer_pool_size=6Ginnodb_data_file_path=ibdata1:10M:autoextendinnodb_file_io_threads=4innodb_thread_concurrency=16innodb_flush_log_at_trx_commit=2innodb_log_buffer_size=16M#innodb_log_file_size=512Minnodb_log_files_in_group=3innodb_max_dirty_pages_pct=90innodb_lock_wait_timeout=120innodb_file_per_table=0wait_timeout=1800interactive_timeout=1800tmp_table_size=128Mdefault-time-zone=systemcharacter-set-server=utf8[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[mysqldump]quickmax_allowed_packet=64M[mysql]no-auto-rehashRemovethenextcommentcharacterifyouarnotfamiliarwithSQLsafe-updates[myisamchk]key_buffer_size=256Msort_buffer_size=256Mread_buffer=2Mwrite_buffer=2M[mysqlhotcopy]interactive-timeout5、添加服务并设置开机启动cpsupport-files/mysql.server/etc/init.d/mysqldchkconfigmysqldonservicemysqldstart--启动MySQL6、查看运行状态servicemysqlstatusnetstat-tunlpd7、配置环境变量vi/etc/profilePATH=/usr/local/mysql/bin:$PATHexportPATH让配置立即生效source/etc/profile8、设置密码mysql-uroot如出现“Can'tconnecttolocalMySQLserverthroughsocket'/tmp/mysql.sock'(2)”建立以下软连接ln-s/var/lib/mysql/mysql.sock/tmpmysqlSETPASSWORD=PASSWORD('challenger');若要设置root用户可以远程访问，执行mysqlGRANTALLPRIVILEGESON*.*TO'root'@'192.168.100.%'IDENTIFIEDBY'challenger'WITHGRANTOPTION;为远程访问时，root用户的密码，可以和本地不同。三、写节点Master1、Master2高可用（双主复制）1、主数据库服务器master2配置参考主数据库master1配置。2、主数据库服务器master2中my.cnf修改以下内容，其它可保持不变server_id=2log_bin=mysql-master2-bin.log3、添加服务并设置开机启动cpsupport-files/mysql.server/etc/init.d/mysqldchkconfigmysqldonservicemysqldstart--启动MySQL4、Master1、Master2安装keeplivedyuminstall-ykeepalivedchkconfigkeepalivedonservicekeepalivedstart5、Master1、Master2置keepalivedMaster1配置如下：vim/etc/keepalived/keepalived.conf!ConfigurationFileforkeepalivedglobal_defs{notification_email{[e-mail]}[e-mail]_server127.0.0.1smtp_connect_timeout30router_idLVS_mysql_write}//以上位报警配置vrrp_instanceVI_1{stateBACKUP//状态,可配置为MASTER和BACKUPinterfaceeth0//配置虚拟ip的接口virtual_router_id71//如果在同一网络,存在多个keepalived组,此值要唯一priority100//优先级advert_int1authentication{auth_typePASSauth_pass1111}//认证信息virtual_ipaddress{192.168.100.201//虚拟ip,mysql集群的写ip}}virtual_server192.168.100.2013306{delay_loop6lb_algorrlb_kindDRpersistence_timeout50protocolTCPreal_server192.168.100.1403306{weight1notify_down/usr/local/shells/mysql.sh//当服务宕机后运行的脚本,用于停止keepalived服务TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port3306}}}重启keepalivedservicekeepalivedrestartMaster2配置如下：real_server192.168.100.1433306{weight1notify_down/usr/local/shells/mysql.sh//当服务宕机后运行的脚本,用于停止keepalived服务TCP_CHECK{connect_timeout3nb_get_retry3delay_before_retry3connect_port3306}除配置项real_server192.168.100.143(master2的物理IP地址)不同外，其它配置相同。重启keepalivedservicekeepalivedrestart6、创建服务器宕机运行脚本master1、master2同时进行如下配置：mkdir/usr/local/shellsvi/usr/local/shells/mysql.sh#!/bin/bash/etc/init.d/keepalivedstopchmod755mysql.sh7、主从复制（master1、master2互为主从，实现数据一致）1.数据库master1、master2.配置主从master1（192.168.100.140）数据库操作如下：mysqlGRANTREPLICATIONSLAVEON*.*TO'repl'@'192.168.100.1