环境介绍：
本脚本测试环境是基于CentOS 6.5 x86_x64的源码安装的Mysql，Mysql版本为mysql-5.1.63，并且均为全新安装，Mysql服务均启动。
Master 192.168.1.3 主机名：master
Slave 192.168.1.4 主机名：slave
Selinux及Iptables均关闭清空。主从两台机器已经做过ssh双机互信(ssh双机互信可参见 《SSH双机互信及错误解决大全》 Mysql的编译安装可参加《搭建Discuz论坛的两种方式 》，脚本文件在附件当中！)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
|
#!/bin/bash
#Target: Auto Make And Check Mysql Master-Slave
#Date: 2015-05-17
#Author: Jacken
#QQ 654001593
#QQ Group 170544180
#Version: 1.0
#
#Note：Have To Exec 1 On Master,Then Exec 2 On SLAVE_DIR,On Correspond Machine To Select "Show Status"
#Only Allow Define Variable
shopt
-s -o nounset
#
#####################################################################
#####################################################################
##########Define Varabile
#Define Master And Slave Database Ipaddress.
MASTER_IP=192.168.1.3
SLAVE_IP=192.168.1.4
#
#Define Master Super User Name And Password.
MASTER_USER=root
MASTER_PASSWD=master123
#
#Define Slave Super User Name And Password.
SLAVE_USER=root
SLAVE_PASSWD=slave123
#
#Define Master-to-Slave Grant User & Password & Host
GRANT_USER=grantuser
GRANT_PASSWD=grantpasswd
GRANT_HOST=%
#
#Sync Database
RSDB=*.*
#
#Define Mysql install Directory
MASTER_DIR=
/usr/local/mysql
SLAVE_DIR=
/usr/local/mysql
#
#Define Device Name.
DEV=eth0
#
#Get Ipaddress For Exec Scripts Machine
GETIP=$(
ifconfig
$DEV|
sed
-n
'/inet addr:/p'
|
awk
'{print $2}'
|
awk
-F:
'{print $2}'
)
#####################################################################
#####################################################################
#
#Only For Super User To Exec
if
[ $UID -
ne
0 ];
then
echo
-e
'\e[31mSorry,This Script Must Be Super User To Exec!\e[0m'
exit
2
fi
#
#
#####################################################################
#####################################################################
#
#Define Master database Operations.
MYSQL_MIN=
"$MASTER_DIR/bin/mysql -u$MASTER_USER -p$MASTER_PASSWD -e"
#
function
MASTER_DO {
#Backup And Judge My.cnf
if
[ -f
/etc/my
.cnf ];
then
cp
/etc/my
.cnf
/etc/my
.cnf$$
else
cp
$MASTER_DIR
/share/mysql/my-medium
.cnf
/etc/my
.cnf
fi
#Grant Slave Database Access Master Database.
$MASTER_DIR
/bin/mysql
-u$MASTER_USER -p$MASTER_PASSWD -e
"grant all on $RSDB to '$SLAVE_USER'@'$SLAVE_IP' identified by '$SLAVE_PASSWD';"
#Grant Master-Slave Sync User.
$MYSQL_MIN
"grant replication slave on $RSDB to \"$GRANT_USER\"@\"$GRANT_HOST\" identified by \"$GRANT_PASSWD\";"
$MYSQL_MIN
"flush privileges;"
}
#
#
#####################################################################
#####################################################################
#
#Define Slave database Operations.
#
function
SLAVE_DO(){
MYSQL_SIN=
"$SLAVE_DIR/bin/mysql -u$SLAVE_USER -p$SLAVE_PASSWD -e"
#Get Master Database status.
MASTER_INFO=`$SLAVE_DIR
/bin/mysql
-h$MASTER_IP -u$SLAVE_USER -p$SLAVE_PASSWD -e
"show master status;"
|
sed
-n
'/mysql/p'
`
#
#Get Log_File
LOG_FILE=`
echo
$MASTER_INFO|
awk
'{print $1}'
`
#Get Log_Pos
LOG_POS=`
echo
$MASTER_INFO|
awk
'{print $2}'
`
#
#Get Slave Database Status
SYNC_INFO=`$MYSQL_SIN
"show slave status \G;"
|
grep
Runnning >
/dev/null
2>&1`
#
#Judge Yes Or No
SLAVE_IO_STATUS=`
echo
$SYNC_INFO|
sed
-n
'/IO/p'
|
awk
-F:
'{print $2}'
`
SLAVE_SQL_STATUS=`
echo
$SYNC_INFO|
sed
-n
'/SQL/p'
|
awk
-F:
'{print $2}'
`
#Grant Master User To Access Slave Database.
`$MYSQL_SIN
"grant all privileges on $RSDB to \"$MASTER_USER\"@\"$MASTER_IP\" identified by \"$MASTER_PASSWD\";"
`
#
#Backup And Judge /etc/My.cnf
if
[ -f
/etc/my
.cnf ];
then
cp
/etc/my
.cnf
/etc/my
.cnf$$
else
cp
$MASTER_DIR
/share/mysql/my-medium
.cnf
/etc/my
.cnf
fi
#
#Modify Slave Server-id
sed
-i
'/^server-id/s/1/2/'
/etc/my
.cnf
#
#Add Master Information On Slave
$MYSQL_SIN
"change master to master_host='$MASTER_IP',master_user='$GRANT_USER',master_password='$GRANT_PASSWD',master_log_file='$LOG_FILE',master_log_pos=$LOG_POS;"
$MYSQL_SIN
"slave start;"
}
#
#
#####################################################################
#####################################################################
#
#Judge Master-Slave Status On Slave To Exec!
function
SYNC_STATUS_S {
MYSQL_SIN=
"$SLAVE_DIR/bin/mysql -u$SLAVE_USER -p$SLAVE_PASSWD -e"
#Get Sync Information
SYNC_INFO=`$MYSQL_SIN
'show slave status \G'
|
grep
Running`
#
#Judge Yes Or No
SLAVE_IO_STATUS=`
echo
$SYNC_INFO|
awk
'{print $2}'
`
SLAVE_SQL_STATUS=`
echo
$SYNC_INFO|
awk
'{print $4}'
`
#
#Reply Master-Slave Status Information.
if
[[
"$SLAVE_IO_STATUS"
==
'Yes'
&&
"$SLAVE_SQL_STATUS"
==
'Yes'
]];
then
echo
-e
'\e[32mThe Database Sync Is Successfuly!\e[0m'
else
echo
-e
'\e[31mThe Database Sync Is Failure!\e[0m'
echo
-e
'\e[31mPlease Check!Exit....\e[0m'
exit
2
fi
}
#Judge Master-Slave Status On Master To Exec!
function
SYNC_STATUS_M {
MYSQL_MIN=
"$MASTER_DIR/bin/mysql -h$SLAVE_IP -u$MASTER_USER -p$MASTER_PASSWD -e"
#
#Get Sync Information
SYNC_INFO=`$MYSQL_MIN
'show slave status \G'
|
grep
Running`
#
#Judge Yes Or No
SLAVE_IO_STATUS=`
echo
$SYNC_INFO|
awk
'{print $2}'
`
SLAVE_SQL_STATUS=`
echo
$SYNC_INFO|
awk
'{print $4}'
`
#Reply Master-Slave Status Information.
if
[[
"$SLAVE_IO_STATUS"
==
'Yes'
&&
"$SLAVE_SQL_STATUS"
==
'Yes'
]];
then
echo
-e
'\e[32mThe Database Sync Is Successfuly!\e[0m'
else
echo
-e
'\e[31mThe Database Sync Is Failure!\e[0m'
echo
-e
'\e[31mPlease Check!Exit....\e[0m'
exit
2
fi
}
#
#####################################################################
#####################################################################
#
#Judge Master Database Machine!
function
JUDGE_MASTER_ID() {
if
[ !
"$GETIP"
==
"$MASTER_IP"
];
then
echo
-e
'\e[31mYour Choice Error,This Option Only Select On Master Machine!\e[0m'
exit
2
fi
}
#
#####################################################################
#####################################################################
#
#Judge Master Database Machine!
function
JUDGE_SLAVE_ID() {
if
[ !
"$GETIP"
==
"$SLAVE_IP"
];
then
echo
-e
'\e[31mYour Choice Error,This Option Only Select On Slave Machine!\e[0m'
exit
2
fi
}
#
#####################################################################
#####################################################################
#####################################################################
#
PS3=
"Please Input Number:"
select
i
in
"Configure Master Database(On Master)"
"Configure Slave Database(On Slave)"
"Show Master-Slave Status(On Master)"
"Show Master-Slave(On Slave)"
"Exit"
do
CHOOSE=$REPLY
case
$CHOOSE
in
#####################################################################
1)
JUDGE_MASTER_ID
MASTER_DO
if
[ $? -
eq
0 ];
then
echo
-e
'\e[32mConfigure Master Database Successfully!\e[0m'
else
echo
-e
'\e[31mConfigure Master Database Failure!\e[0m'
fi
;;
#####################################################################
2)
JUDGE_SLAVE_ID
SLAVE_DO
if
[ $? -
eq
0 ];
then
echo
-e
'\e[32mConfigure Slave Database Successfully!\e[0m'
else
echo
-e
'\e[31mConfigure Slave Database Failure!\e[0m'
fi
;;
#####################################################################
3)
JUDGE_MASTER_ID
SYNC_STATUS_M
;;
#####################################################################
4)
JUDGE_SLAVE_ID
SYNC_STATUS_S
;;
#####################################################################
5)
echo
-e
'\e[32mByeBye.\e[0m'
exit
;;
#####################################################################
*)
echo
-e
'\e[31mInput Error,Please Input Again!\e[0m'
esac
done
|
脚本效果图：
要先在Master上执行 1，然后再在Slave上执行2，其它的顺序就无所谓了。(其实脚本可以在一台机器上运行，就是在脚本中通过ssh调用另一端的Mysql，还有如果主从失败如何自动修复？以后有时间再写Version 2吧！～)
Done!
本文转自Jacken_yang 51CTO博客，原文链接：http://blog.51cto.com/linuxnote/1652016，如需转载请自行联系原作者
