MySQLMM+KeepAlived高可用架构部署测试Word文档格式.docx
《MySQLMM+KeepAlived高可用架构部署测试Word文档格式.docx》由会员分享,可在线阅读,更多相关《MySQLMM+KeepAlived高可用架构部署测试Word文档格式.docx(30页珍藏版)》请在冰豆网上搜索。
描述
Serverid
Db1
192.168.8.100
Master1
1
Db2
192.168.8.101
Master2
2
Db3
192.168.8.102
Slave1
3
1.4服务角色及描述
角色
ReadWriter
应用程序连接该IP对主库进写请求
ReadOnly
应用程序连接到该IP处理读请求
192.168.8.140
应用程序连接到该虚IP处理读写请求(不直联100,101),通过连接140实现高可用。
1.5配置/etc/hosts
在其中添加所有的主机信息:
[root@db1mysql]#more/etc/hosts
127.0.0.1localhostlocalhost.localdomainlocalhost4localhost4.localdomain4
:
1localhostlocalhost.localdomainlocalhost6localhost6.localdomain6
192.168.8.32db1
192.168.8.33db2
192.168.8.40b3
二、安装MySQL
2.1卸载rpm包
[root@hadoop61~]#rpm-qa|grep-imysql
mysql-5.1.73-5.el6_7.1.x86_64
mysql-server-5.1.73-5.el6_7.1.x86_64
perl-DBD-MySQL-4.013-3.el6.x86_64
mysql-devel-5.1.73-5.el6_7.1.x86_64
mysql-libs-5.1.73-5.el6_7.1.x86_64
rpm-evmysql-devel-5.1.73-5.el6_7.1.x86_64
rpm-evmysql-server-5.1.73-5.el6_7.1.x86_64
rpm-evperl-DBD-MySQL-4.013-3.el6.x86_64
rpm-evmysql-5.1.73-5.el6_7.1.x86_64
[root@hadoop61~]#rpm-e--nodepsmysql-libs-5.1.73-5.el6_7.1.x86_64
warning:
/etc/fsavedas/etc/f.rpmsave
2.2收集mysql对应的文件夹
[root@hadoop61~]#find/-namemysql
/var/lib/mysql
/var/lib/mysql/mysql
2.3删除mysql对应的文件夹
rm-rf/var/lib/mysql
2.4下载mysql安装程序
2.5解压mysql安装程序
tar-zxvfmysql-5.6.31-linux-glibc2.5-x86_64.tar.gz-C/usr/local
cd/usr/local
mvmysql-5.6.31-linux-glibc2.5-x86_64mysql5.6
2.6添加用户和组
groupaddmysql
useradd-r-gmysqlmysql
2.7安装数据库
进入安装mysql软件目录:
cd/usr/local/mysql
修改当前目录拥有者为mysql用户:
chown-Rmysql:
mysql./
安装数据库:
mkdir-p/data/mysql/data
chown-Rmysql.mysql/data/mysql
#若是最小安装系统,还需要安装以下包:
yum-yinstallperlperl-devel
yum-yinstalllibaio
./scripts/mysql_install_db--basedir=/usr/local/mysql5.6--datadir=/data/mysql/data--user=mysql
修改配置文件:
cp/usr/local/mysql5.6/f/etc/f
#vi/etc/f
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/data
port=3306
sql_mode=NO_ENGINE_SUBSTITUTIO
character-set-server=utf8
[mysql]
default-character-set=utf8
[client]
启动脚本放到开机初始化目录
cpsupport-/etc/init.d/mysql
启动mysql服务:
servicemysqlstart
查看mysql进程:
ps-ef|grepmysql
三、修改MySQL配置
db1,db2,db3三台主机上分别建立如下目录
mkdir/var/log/mysql&
&
chown-Rmysql:
mysql/var/log/mysql/
3.1db1配置
server_id=1
log_bin=/var/log/mysql/mysql-bin.log
log_bin_index=/var/log/mysql/mysql-bin.log.index
relay_log=/var/log/mysql/mysql-relay-bin
relay_log_index=/var/log/mysql/mysql-relay-bin.index
expire_logs_days=10
max_binlog_size=1024M
log_slave_updates=1
auto-increment-increment=2
auto-increment-offset=1
binlog_format=MIXED
replicate-ignore-db=mysql
binlog-ignore-db=mysql
3.2db2配置
[mysqld]
server_id=2
auto-increment-offset=2
3.3db3配置
server_id=3
read-only
四、MySQL复制配置
⏹从master-001复制到master-002
⏹从master-002复制到slave-001
4.1配置db1作为主复制到db2
(1)配置db1
由于这里是配置db1做为源数据,所以不需要设置changemaster和启动slave进程
#在db1上创建复制用户
grantreplicationslaveon*.*to'
repl'
@'
192.168.8.%'
identifiedby'
123456'
;
#从db1上导出数据
mysqldump--master-data=2--single-transaction-R--triggers-A>
all.sql
#拷贝all.sql至db2,db3
scpall.sqlroot@db2:
/usr/local/mysql5.6
scpall.sqlroot@db3:
(2)配置db2
#将all.sql导入db2,db3从库
mysql-f--default-character-set=utf8<
指定changemaster为db1,表示从db1复制到db2
#ssh到master-002服务器
mysql-uroot-p
#在mysql控制台录入,这个是在f中没有使用GTID(MySQL5.6新特性)下的命令
CHANGEMASTERTO
MASTER_HOST='
192.168.8.32'
MASTER_USER='
MASTER_PASSWORD='
MASTER_PORT=3306,
MASTER_LOG_FILE='
mysql-bin.000004'
MASTER_LOG_POS=120,
MASTER_CONNECT_RETRY=10;
#这个是在f中使用GTID(MySQL5.6新特性)下的命令
192.168.1.211'
replication'
MASTER_AUTO_POSITION=1;
#重置reset
resetslave;
#启动slave
startslave;
#查看slave状态
showslavestatus\G
#结果如下
mysql>
showslavestatus\G
***************************1.row***************************
Slave_IO_State:
Waitingformastertosendevent
Master_Host:
192.168.1.212
Master_User:
replication
Master_Port:
3306
Connect_Retry:
10
Master_Log_File:
mysql-master-002-bin.000001
Read_Master_Log_Pos:
120
Relay_Log_File:
mysql-master-001-bin.000004
Relay_Log_Pos:
294
Relay_Master_Log_File:
Slave_IO_Running:
Yes
Slave_SQL_Running:
Replicate_Do_DB:
Replicate_Ignore_DB:
mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
0
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
472
Until_Condition:
None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed:
No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
Master_SSL_Verify_Server_Cert:
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
2
Master_UUID:
3d3b9f4f-f74f-11e5-9a30-005056b324c4
Master_Info_File:
/opt/mysql/data/master.info
SQL_Delay:
SQL_Remaining_Delay:
NULL
Slave_SQL_Running_State:
Slavehasreadallrelaylog;
waitingfortheslaveI/Othreadtoupdateit
Master_Retry_Count:
86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position:
1rowinset(0.00sec)
注意:
Slave_IO_Running:
Yes
Slave_SQL_Running:
4.2配置db1作为主复制到db3
(1)配置db3
指定changemaster为db1,表示从db1复制到db3
#ssh到db3服务器
#在mysql控制台录入
#这个是在f中没有使用GTID(MySQL5.6新特性)下的命令:
192.168.8.33'
mysql-bin.000007'
#这个是在f中使用GTID(MySQL5.6新特性)下的命令
4.3配置db2作为主复制到db1
指定changemaster为db2,表示从db2复制到db1
#ssh到master-001服务器
CHANGEMASTERTO
MASTER_LOG_POS=173312,
SQL