MySql加复制加Atlas加Mha部署步骤分析.docx
《MySql加复制加Atlas加Mha部署步骤分析.docx》由会员分享，可在线阅读，更多相关《MySql加复制加Atlas加Mha部署步骤分析.docx（20页珍藏版）》请在冰豆网上搜索。
MySql加复制加Atlas加Mha部署步骤分析
MHA部署步骤
1
部署环境
Mysql环境
master：
192.168.5.102:
3306
slave：
192.168.5.104:
3306，192.168.5.106:
3306
虚拟VIP:
192.168.5.155
MHA环境
manager：
192.168.5.106
node：
192.168.5.104（3306端口监听的实例作为备用主库）,192.168.5.106
2部署图
3MySql安装s
修改系统信息
1.修改/etc/sysctl.conf，添加
net.core.somaxconn=2048
然后在终端中执行
sysctl-p
2.修改环境变量
vi.bash_profile
exportNLS_LANG="AMERICAN_AMERICA.ZHS16GBK"
exportNLS_DATE_FORMAT="YYYY-MM-DDHH24:
MI:
SS"
exportLC_ALL="en_US"
exportLANG="en_US"
3.修改配置参数
vi/etc/security/limits.conf
rootsoftnproc2047
roothardnproc16384
rootsoftnofile1024
roothardnofile65536
roothardstack10240
安装软件
1.删除原来安装的mysql
rpm-emysql-libs--nodeps
rpm-emysql-bench--nodeps
rpm-emysql-connector-odbc--nodeps
rpm-emysql-test--nodeps
rpm-emysql-devel--nodeps
rpm-emysql--nodeps
rpm-emysql-connector--nodeps
rpm-emysql-server--nodeps
rpm-emysql-connector-java--nodeps
2.mysql5.6.21安装
rpm-ivhMySQL-server-5.6.21-1.el6.x86_64.rpm
rpm-ivhMySQL-client-5.6.21-1.el6.x86_64.rpm
rpm-ivhMySQL-devel-5.6.21-1.el6.x86_64.rpm
rpm-ivhMySQL-embedded-5.6.21-1.el6.x86_64.rpm
rpm-ivhMySQL-shared-5.6.21-1.el6.x86_64.rpm
rpm-Uvhperl-IO-Socket-SSL-1.44-1.el5.rfx.noarch.rpm
rpm-Uvhperl-Net-SSLeay-1.36-1.el6.rfx.x86_64.rpm
rpm-Uvhperl-TermReadKey-2.30-3.el4.rf.x86_64.rpm
rpm-Uvhpercona-toolkit-2.2.11-1.noarch.rpm
修改配置文件
修改/usr/f文件，增加
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid_file=/var/lib/mysql/mysql.pid
port=3306
storage_engine=InnoDB
#INNODB
innodb_buffer_pool_size=
innodb_log_file_size=512000000
#innodb_flush_method=O_DIRECT
innodb_open_files=1000
character-set-server=utf8
#MyISAM
key_buffer_size=20000000
#LOGGING
log_error=/var/lib/mysql/mysql-error.log
log_slow_queries=/var/lib/mysql/mysql-slow.log
long_query_time=0
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow_query.log
lower_case_table_names=1
#log_queries_not_using_indexes=1
#OTHER
tmp_table_size=32M
max_heap_table_size=32M
query_cache_type=0
query_cache_size=0
max_connections=500
thread_cache_size=100
#table_cache_size=
open_files_limit=65535
back_log=1500
#expird_log_days=14
[client]
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock
port=3306
更改root密码
/etc/init.d/mysqlstop
#mysqld_safe--user=mysql--skip-grant-tables--skip-networking&
#mysql-urootmysql
mysql>UPDATEuserSETPassword=PASSWORD（'wssc2014'）whereUSER='root';
mysql>FLUSHPRIVILEGES;
commit;
mysql>quit
#/etc/init.d/mysqlstop
/etc/init.d/mysqlstart
#mysql-uroot-p
Enterpassword:
<输入新设的密码newpassword>
授权所有用户从任何机器访问root
mysql-urootmysql-p
SETPASSWORD=PASSWORD（'wssc2014'）;
usemysql;
GRANTALLPRIVILEGESON*.*TO'root'@'%'IDENTIFIEDBY'wssc2014'WITHGRANTOPTION;
FLUSHPRIVILEGES;
Master创建数据库
CREATEDATABASEmiddev
DEFAULTCHARACTERSETutf8
DEFAULTCOLLATEutf8_general_ci;
CREATEDATABASEfdfs
DEFAULTCHARACTERSETutf8
DEFAULTCOLLATEutf8_general_ci;
CREATEDATABASEmidetl
DEFAULTCHARACTERSETutf8
DEFAULTCOLLATEutf8_general_ci;
CREATEDATABASEmidclient
DEFAULTCHARACTERSETutf8
DEFAULTCOLLATEutf8_general_ci;
CREATEDATABASEmiduser
DEFAULTCHARACTERSETutf8
DEFAULTCOLLATEutf8_general_ci;
Master创建middb用户
CREATEUSER'middb'@'%'IDENTIFIEDBY'wssc2014';
SETPASSWORDFORmiddb=PASSWORD（'wssc2014'）;
GRANTALLPRIVILEGESON*.*TOmiddbWITHGRANTOPTION;
FLUSHPRIVILEGES;
4复制配置
master
1.修改/usr/f文件，增加
user=mysql
port=3306
basedir=/usr
datadir=/var/lib/mysql
tmpdir=/tmp
log-bin=master-bin
log-bin-index=master-bin.index
server-id=1
2.重启动mysql
/etc/init.d/mysqlrestart
3.创建复制用户
mysql-uroot-pmysql
CREATEUSERrepl_user;
GRANTREPLICATIONSLAVEON*.*
TOrepl_userIDENTIFIEDBY'wssc2014';
SETPASSWORDFORrepl_user=PASSWORD（'wssc2014'）;
FLUSHPRIVILEGES;
slave
修改/usr/f文件，增加
user=mysql
port=3306
basedir=/usr
datadir=/var/lib/mysql
tmpdir=/tmp
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
server-id=2
slave_net_timeout=60
2.重启动mysql
/etc/init.d/mysqlrestart
连接master和slave
mysql-uroot-pmysql
CHANGEMASTERTO
MASTER_HOST='wssc1',
MASTER_PORT=3306,
MASTER_USER='repl_user',
MASTER_PASSWORD='wssc2014';
FLUSHPRIVILEGES;
startslave;
验证
1.master
2.slave
5Atlas安装
安装RPM包
rpm-Uvhperl-Config-Auto-0.20-2.el6.rf.noarch.rpm
rpm-Uvhperl-Config-IniFiles-2.72-2.el6.noarch.rpm
rpm-Uvhperl-IO-stringy-2.110-8.el6.noarch.rpm
rpm-Uvhperl-List-MoreUtils-0.22-10.el6.x86_64.rpm
rpm-Uvhperl-YAML-0.70-4.el6.noarch.rpm
rpm-Uvhpkgconfig-0.23-9.1.el6.x86_64.rpm
rpm-Uvhatrpms-77-1.noarch.rpm
rpm-Uvhopenssl-1.0.1e-30.el6.x86_64.rpm
rpm-UvhAtlas-2.2.1.el6.x86_64.rpm
配置配置文件
cd/usr/local/mysql-proxy/conf
mvff
修改f文件配置信息
[mysql-proxy]
#带#号的为非必需的配置项目
#管理接口的用户名
admin-username=middb
#管理接口的密码
admin-password=wssc2014
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses=192.168.5.155:
3306
#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
proxy-read-only-backend-addresses=192.168.5.102:
3306,192.168.5.104:
3306,192.168.5.106:
3306
#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！
pwds=root:
5Te46Wj3YjUa8s/oWZlMvQ==,middb:
5Te46Wj3YjUa8s/oWZlMvQ==
#设置Atlas的运行方式，设为true时为守护进程方式，设为false时为前台方式，一般开发调试时设为false，线上运行时设为true,true后面不能有空格。
daemon=true
#设置Atlas的运行方式，设为true时Atlas会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，一般开发调试时设为false，线上运行时设为true,true后面不能有空格。
keepalive=true
#工作线程数，对Atlas的性能有很大影响，可根据情况适当设置
event-threads=8
#日志级别，分为message、warning、critical、error、debug五个级别
log-level=message
#日志存放的路径
log-path=/usr/local/mysql-proxy/log
#SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，REALTIME代表记录SQL日志且实时写入磁盘，默认为OFF
#sql-log=OFF
#慢日志输出设置。
当设置了该参数时，则日志只输出执行时间超过sql-log-slow（单位：
ms）的日志记录。
不设置该参数则输出全部日志。
#sql-log-slow=10000
waittimeout=1000
#实例名称，用于同一台机器上多个Atlas实例间的区分
instance=atlas
#Atlas监听的工作接口IP和端口
proxy-address=0.0.0.0:
1234
#Atlas监听的管理接口IP和端口
admin-address=0.0.0.0:
2345
#分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项
#tables=person.mt.id.3
#默认字符集，设置该项后客户端不再需要执行SETNAMES语句
charset=utf8
#允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接
#client-ips=127.0.0.1,192.168.1
#Atlas前面挂接的LVS的物理网卡的IP（注意不是虚IP），若有LVS且设置了client-ips则此项必须设置，否则可以不设置
#lvs-ips=192.168.1.1
启动及验证
1.启动方法:
/usr/local/mysql-proxy/bin/mysql-proxydatlasstart
OK:
MySQL-Proxyofatlasisstarted
查看启动日志
[root@wssc1bin]#tail-50/usr/local/mysql-proxy/log/atlas.log
2015-02-2715:
54:
23:
（message）chassis-unix-daemon.c:
138:
[angel]wetrytokeepPID=20142alive
2015-02-2715:
54:
23:
（message）mysql-proxy0.8.2started-instance:
atlas
2015-02-2715:
54:
23:
（message）proxylisteningonport0.0.0.0:
1234
2015-02-2715:
54:
23:
（message）addedread/writebackend:
192.168.5.155:
3306
2015-02-2715:
54:
23:
（message）addedread-onlybackend:
192.168.5.102:
3306
2015-02-2715:
54:
23:
（message）addedread-onlybackend:
192.168.5.104:
3306
2015-02-2715:
54:
23:
（message）addedread-onlybackend:
192.168.5.106:
3306
2015-02-2715:
54:
23:
（message）chassis-event-thread.c:
235:
starting8threads
2.查看状态是否正常
[root@wssc1bin]#/usr/local/mysql-proxy/bin/mysql-proxydatlasstatus
MySQL-Proxyofatlasisrunning（20141）
MySQL-Proxyofatlasisrunning（20142）
3.停止Atlas
[root@wssc1bin]#/usr/local/mysql-proxy/bin/mysql-proxydatlasstop
OK:
MySQL-Proxyofatlasisstopped
1.Atlas管理
mysql-h127.0.0.1-P2345-umiddb–p
mysql>select*fromhelp;
+----------------------------+---------------------------------------------------------+
|command|description|
+----------------------------+---------------------------------------------------------+
|SELECT*FROMhelp|showsthishelp|
|SELECT*FROMbackends|liststhebackendsandtheirstate|
|SETOFFLINE$backend_id|offlinebackendserver,$backend_idisbackend_ndx'sid|
|SETONLINE$backend_id|onlinebackendserver,...|
|ADDMASTER$backend|example:
"addmaster127.0.0.1:
3306",...|
|ADDSLAVE$backend|example:
"addslave127.0.0.1:
3306",...|
|REMOVEBACKEND$backend_id|example:
"removebackend1",...|
|SELECT*FROMclients|liststheclients|
|ADDCLIENT$client|example:
"addclient192.168.1.2",...|
|REMOVECLIENT$client|example:
"removeclient192.168.1.2",...|
|SELECT*FROMpwds|liststhepwds|
|ADDPWD$pwd|example:
"addpwduser:
raw_password",...|
|ADDENPWD$pwd|example:
"addenpwduser:
encrypted_password",...|
|REMOVEPWD$pwd|example:
"removepwduser",...|
|SAVECONFIG|savethebackendstoconfigfile|
|SELECTVERSION|displaytheversionofAtlas|
+----------------------------+---------------------------------------------------------+
16rowsinset（0.01sec）
6安装Mha
安装RPM包
6.1.1MANAGER节点
rpm-UvhMySQL-shared-compat-5.6.21-1.el6.x86_64.rpm
rpm-Uvhperl-DBD-MySQL-4.022-1.el6.rfx.x86_64.rpm
rpm-Uvhperl-Config-Tiny-2.12-1.el6.rfx.noarch.rpm
rpm-Uvhperl-Log-Dispatch-2.26-1.el6.rf.noarch.rpm
rpm-Uvhperl-Parallel-ForkManager-0.7.5-2.2.el6.rf.noarch.rpm
rpm-Uvhperl-Log-Dispatch-2.26-1.el6.rf.noarch.rpm
rpm-Uvhlibdbi-0.8.3-3.1.el6.x86_64.rpm
rpm-Uvhqt-mysql-4.6.2-24.el6.x86_64.rpm
rpm-Uvhcompat-readline5-5.2-17.1.el6.x86_64.rpm
rpm-Uvhruby-libs-1.8.7.352-7.el6_2.x86_64.rpm
rpm-Uvhruby-1.8.7.352-7.el6_2.x86_64.rpm
rpm-Uvhruby-devel-1.8.7.352-7.el6_2.x86_64.rpm
rpm-Uvhrrdtool-1.4.7-1.el6.rfx.x86_64.rpmruby-rrdtool-1.4.7-1.el6.rfx.x86_64.rpmperl-rrdtool-1.4.7-1.el6.rfx.x86_64.rpm
rpm-Uvhrrdtool-devel-1.4.7-1.el6.rfx.x86_64.rpm
rpm-Uvhmha4mysql-node-0.56-0.el6.noarch.rpm
rpm-Uvhmh
升级会员 