Keepalive实现mysql双主热备
环境描述：
OS: CentoOS6.6_X64
Node1:172.16.10.1
Node2:172.16.10.2
VIP:172.16.10.10
1、配置主机互信
Node1:
|
1
2
3
4
5
6
7
8
9
|
[root@node1~]
# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:11:21:56
inet addr:172.16.100.1 Bcast:172.16.100.255 Mask:255.255.255.0
inet6 addr:fe80::20c:29ff:fe11:2156
/64
Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:144 errors:0 dropped:0overruns:0 frame:0
TX packets:114 errors:0 dropped:0overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:14467 (14.1 KiB) TX bytes:11961 (11.6 KiB)
|
向ntp服务器同步时间
|
1
2
|
[root@node1~]
# ntpdate 172.16.100.30
25 Jul11:34:52 ntpdate[2123]: step
time
server 172.16.100.30 offset -28852.934223 sec
|
创建同步时间任务计划
[root@node1~]# crontab -e
|
1
|
15 5 * * *
/usr/sbin/ntpdate
172.16.100.30
|
修改主机名称
|
1
|
[root@node1~]
# hostname node1.Smoke.com
|
|
1
2
|
[root@node1~]
# hostname
node1.Smoke.com
|
|
1
2
3
|
[root@node1~]
# vim /etc/sysconfig/network
NETWORKING=
yes
HOSTNAME=node1.Smoke.com
|
|
1
2
|
[root@node1~]
# uname -n
node1.Smoke.com
|
添加host文件主机名对应的IP地址
|
1
2
3
4
5
|
[root@node1~]
# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4localhost4.localdomain4
::1 localhost localhost.localdomainlocalhost6 localhost6.localdomain6
172.16.100.1 node1.Smoke.com node1
172.16.100.2 node2.Smoke.com node2
|
生成密钥
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
[root@node1~]
# ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ''
Generatingpublic
/private
rsa key pair.
Createddirectory
'/root/.ssh'
.
Youridentification has been saved
in
/root/
.
ssh
/id_rsa
.
Yourpublic key has been saved
in
/root/
.
ssh
/id_rsa
.pub.
The keyfingerprint is:
4c:9e:47:8a:a1:ee:d3:64:d5:e0:ab:21:fd:e6:43:19root@node1.Smoke.com
Thekey's randomart image is:
+--[ RSA2048]----+
| |
| . |
| ...o. |
| . *E+. |
| ....S+. |
| .. = +. |
| .= = |
| .. o + |
| .. o.. |
+-----------------+
|
将公钥传给node2主机
|
1
2
3
4
5
6
7
8
9
10
11
|
[root@node1~]
# ssh-copy-id -i .ssh/id_rsa.pub root@172.16.100.2
Theauthenticity of host
'172.16.100.2 (172.16.100.2)'
can't be established.
RSA key fingerprintis cc:c3:c7:45:9e:40:60:
dd
:03:13:b3:37:9a:ff:0e:8a.
Are yousure you want to
continue
connecting (
yes
/no
)?
yes
Warning:Permanently added
'172.16.100.2'
(RSA) to the list of known hosts.
root@172.16.100.2'spassword:
Now trylogging into the machine, with
"ssh 'root@172.16.100.2'"
, and checkin:
.
ssh
/authorized_keys
to makesure we haven
't added extra keys that you weren'
t expecting.
|
Node2:
|
1
2
3
4
5
6
7
8
9
|
[root@node2~]
# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 00:0C:29:E8:20:12
inet addr:172.16.100.2 Bcast:172.16.100.255 Mask:255.255.255.0
inet6 addr:fe80::20c:29ff:fee8:2012
/64
Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:589 errors:0 dropped:0overruns:0 frame:0
TX packets:782 errors:0 dropped:0 overruns:0carrier:0
collisions:0 txqueuelen:1000
RX bytes:45720 (44.6 KiB) TX bytes:175486 (171.3 KiB)
|
|
1
2
|
[root@node2~]
# ntpdate 172.16.100.30
25 Jul11:46:17 ntpdate[2216]: step
time
server 172.16.100.30 offset -28853.089885 sec
|
|
1
2
|
[root@node2~]
# crontab -e
15 5 * * *
/usr/sbin/ntpdate
172.16.100.30
|
|
1
|
[root@node2~]
# hostname node2.Smoke.com
|
|
1
2
|
[root@node2~]
# hostname
node2.Smoke.com
|
|
1
2
3
|
[root@node2~]
# vim /etc/sysconfig/network
NETWORKING=
yes
HOSTNAME=node2.Smoke.com
|
|
1
2
|
[root@node2~]
# uname -n
node2.Smoke.com
|
|
1
2
3
4
5
|
[root@node2~]
# vim /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4localhost4.localdomain4
::1 localhost localhost.localdomainlocalhost6 localhost6.localdomain6
172.16.100.1 node1.Smoke.com node1
172.16.100.2 node2.Smoke.com node2
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
[root@node2~]
# ssh-keygen -t rsa -f ~/.ssh/id_rsa -P ''
Generatingpublic
/private
rsa key pair.
Youridentification has been saved
in
/root/
.
ssh
/id_rsa
.
Yourpublic key has been saved
in
/root/
.
ssh
/id_rsa
.pub.
The keyfingerprint is:
51:55:ee:
dd
:5b:f7:b9:b1:f7:0e:de:b9:34:55:85:01root@node2.Smoke.com
Thekey's randomart image is:
+--[ RSA2048]----+
| ..Eooo.|
| . .. .|
| . . .|
| . . .o|
| S . *|
| B|
| .*.|
| ..oB|
| .*B|
+-----------------+
|
|
1
2
3
4
5
6
7
8
9
10
11
|
[root@node2~]
# ssh-copy-id -i .ssh/id_rsa.pub root@172.16.100.1
Theauthenticity of host
'172.16.100.1 (172.16.100.1)'
can't be established.
RSA keyfingerprint is 85:d3:16:71:c5:c5:d6:
dd
:
dd
:93:53:10:a9:63:4d:ca.
Are yousure you want to
continue
connecting (
yes
/no
)?
yes
Warning:Permanently added
'172.16.100.1'
(RSA) to the list of known hosts.
root@172.16.100.1'spassword:
Now trylogging into the machine, with
"ssh 'root@172.16.100.1'"
, and checkin:
.
ssh
/authorized_keys
to makesure we haven
't added extra keys that you weren'
t expecting.
|
2、安装mysql-server，我这里使用mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz二进制文件安装，请最好使用高于5.6版本的mysql，因为支持server-id；
Node1:
|
1
2
|
[root@node1~]
# ls
anaconda-ks.cfg
install
.log
install
.log.syslog mysql-5.6.12-linux-glibc2.5-x86_64.
tar
.gz
|
|
1
2
3
|
[root@node1~]
# mkdir -pv /mydata/data
mkdir
: 已创建目录
"/mydata"
mkdir
: 已创建目录
"/mydata/data"
|
|
1
|
[root@node1~]
# groupadd -r -g 306 mysql
|
|
1
|
[root@node1~]
# useradd -g 306 -r -u 306 mysql
|
|
1
|
[root@node1~]
# chown -R mysql.mysql /mydata/data/
|
|
1
|
[root@node1~]
# tar xf mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
|
|
1
|
[root@node1~]
# cd /usr/local/
|
|
1
2
|
[root@node1local]
# ls
bin etc games include lib lib64 libexec mysql-5.6.12-linux-glibc2.5-x86_64 sbin share src
|
|
1
2
|
[root@node1local]
# ln -sv mysql-5.6.12-linux-glibc2.5-x86_64 mysql
"mysql"
->
"mysql-5.6.12-linux-glibc2.5-x86_64"
|
|
1
|
[root@node1local]
# cd mysql
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[root@node1mysql]
# ll
总用量 76
drwxr-xr-x. 2 root root 4096 7月 25 12:03 bin
-rw-r--r--. 1 7161 wheel 17987 1月 23 2013 COPYING
drwxr-xr-x. 4 root root 4096 7月 25 12:03 data
drwxr-xr-x. 2 root root 4096 7月 25 12:03 docs
drwxr-xr-x. 3 root root 4096 7月 25 12:03 include
-rw-r--r--. 1 7161 wheel 7468 1月 23 2013 INSTALL-BINARY
drwxr-xr-x. 3 root root 4096 7月 25 12:03 lib
drwxr-xr-x. 4 root root 4096 7月 25 12:03
man
drwxr-xr-x.10 root root 4096 7月 25 12:03 mysql-
test
-rw-r--r--. 1 7161 wheel 2552 1月 23 2013 README
drwxr-xr-x. 2 root root 4096 7月 25 12:03 scripts
drwxr-xr-x.28 root root 4096 7月 25 12:03 share
drwxr-xr-x. 4 root root 4096 7月 25 12:03 sql-bench
drwxr-xr-x. 3 root root 4096 7月 25 12:03 support-files
|
|
1
|
[root@node1mysql]
# chown -R root.mysql ./*
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[root@node1mysql]
# ll
总用量 76
drwxr-xr-x. 2 root mysql 4096 7月 25 12:03 bin
-rw-r--r--. 1 root mysql 17987 1月 23 2013 COPYING
drwxr-xr-x. 4 root mysql 4096 7月 25 12:03 data
drwxr-xr-x. 2 root mysql 4096 7月 25 12:03 docs
drwxr-xr-x. 3 root mysql 4096 7月 25 12:03 include
-rw-r--r--. 1 root mysql 7468 1月 23 2013 INSTALL-BINARY
drwxr-xr-x. 3 root mysql 4096 7月 25 12:03 lib
drwxr-xr-x. 4 root mysql 4096 7月 25 12:03
man
drwxr-xr-x.10 root mysql 4096 7月 25 12:03 mysql-
test
-rw-r--r--. 1 root mysql 2552 1月 23 2013 README
drwxr-xr-x. 2 root mysql 4096 7月 25 12:03 scripts
drwxr-xr-x.28 root mysql 4096 7月 25 12:03 share
drwxr-xr-x. 4 root mysql 4096 7月 25 12:03 sql-bench
drwxr-xr-x. 3 root mysql 4096 7月 2512:03 support-files
|
|
1
|
[root@node1mysql]
# scripts/mysql_install_db--user=mysql --datadir=/mydata/data
|
|
1
|
[root@node1mysql]
# cp support-files/mysql.server /etc/init.d/mysqld
|
|
1
|
[root@node1mysql]
# chkconfig --add mysqld
|
|
1
2
3
4
5
6
|
[root@node1mysql]
# vim my.cnf
datadir =
/mydata/data
innodb_file_per_table= ON
server-
id
= 1
socket =
/tmp/mysql
.sock
log-bin= mysql-bin
|
|
1
2
3
4
5
6
7
8
9
10
|
[root@node1bin]
# netstat -tnlp
ActiveInternet connections (only servers)
ProtoRecv-Q Send-Q Local Address Foreign Address State PID
/Program
name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 2346
/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1026
/master
tcp 0 0 127.0.0.1:6011 0.0.0.0:* LISTEN 2275
/sshd
tcp 0 0 :::22 :::* LISTEN 2346
/sshd
tcp 0 0 ::1:25 :::* LISTEN 1026
/master
tcp 0 0 ::1:6011 :::* LISTEN 2275
/sshd
tcp 0 0 :::3306 :::* LISTEN 12146
/mysqld
|
|
1
2
|
[root@node1bin]
# vim /etc/profile.d/mysql.sh
exportPATH=$PATH:
/usr/local/mysql/bin
|
|
1
|
[root@node1bin]
# . /etc/profile.d/mysql.sh
|
|
1
|
[root@node1bin]
# mysqladmin -u root password smoke520
|
|
1
2
3
4
5
6
7
8
9
10
11
|
[root@node1mysql]
# vim my.cnf
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
datadir=
/mydata/data
innodb_file_per_table= ON
server-
id
= 1
socket =
/tmp/mysql
.sock
log-bin= mysql-bin
log-error=
/mydata/data/node1
.Smoke.com.error.log
skip-slave-start= 1
auto_increment_offset= 2
auto_increment_increment= 10
|
|
1
2
3
|
[root@node1mysql]
# service mysqld restart
Shuttingdown MySQL.. SUCCESS!
StartingMySQL. SUCCESS!
|
Node2:
|
1
2
|
[root@node2~]
# ls
anaconda-ks.cfg
install
.log
install
.log.syslog mysql-5.6.12-linux-glibc2.5-x86_64.
tar
.gz
|
|
1
2
3
|
[root@node2~]
# mkdir -pv /mydata/data
mkdir
: 已创建目录
"/mydata"
mkdir
: 已创建目录
"/mydata/data"
|
|
1
|
[root@node2~]
# groupadd -r -g 306 mysql
|
|
1
|
[root@node2~]
# useradd -g 306 -r -u 306 mysql
|
|
1
|
[root@node2~]
# chown -R mysql.mysql /mydata/data/
|
|
1
|
[root@node2~]
# tar xf mysql-5.6.12-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
|
|
1
|
[root@node2~]
# cd /usr/local/
|
|
1
2
|
[root@node2local]
# ln -sv mysql-5.6.12-linux-glibc2.5-x86_64 mysql
"mysql"
->
"mysql-5.6.12-linux-glibc2.5-x86_64"
|
|
1
|
[root@node2local]
# cd mysql
|
|
1
|
[root@node2mysql]
# chown -R root.mysql ./*
|
|
1
|
[root@node2mysql]
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
|
|
1
|
[root@node2mysql]
# cp support-files/mysql.server /etc/init.d/mysqld
|
|
1
|
[root@node2mysql]
# chkconfig --add mysqld
|
|
1
2
3
4
5
6
7
8
9
10
11
|
[root@node2mysql]
# vim my.cnf
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
datadir=
/mydata/data
innodb_file_per_table= ON
server-
id
= 2
socket =
/tmp/mysql
.sock
log-bin= mysql-bin
log-error=
/mydata/data/node2
.Smoke.com.error.log
skip-slave-start= 1
auto_increment_offset= 2
auto_increment_increment= 10
|
|
1
2
|
[root@node2mysql]
# service mysqld start
StartingMySQL.. SUCCESS!
|
|
1
2
|
[root@node2mysql]
# vim /etc/profile.d/mysql.sh
exportPATH=$PATH:
/usr/local/mysql/bin
|
|
1
|
[root@node2mysql]
# . /etc/profile.d/mysql.sh
|
|
1
|
[root@node2mysql]
# mysqladmin -u root password smoke520
|
Node1:
|
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
|
[root@node1mysql]# mysql -u root -psmoke520
Warning:Using a
password
on
the command line interface can be insecure.
Welcometo the MySQL monitor. Commands
end
with
;
or
\g.
YourMySQL
connection
id
is
2
Serverversion: 5.6.12-log MySQL Community Server (GPL)
Copyright(c) 2000, 2013, Oracle
and
/
or
its affiliates.
All
rights reserved.
Oracleis a registered trademark
of
Oracle Corporation
and
/
or
its
affiliates.Other names may be trademarks
of
their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear the
current
input statement.
mysql>SHOW MASTER STATUS;
+
------------------+----------+--------------+------------------+-------------------+
|File | Position |Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+
------------------+----------+--------------+------------------+-------------------+
|mysql-bin.000004 | 425 | | | |
+
------------------+----------+--------------+------------------+-------------------+
1 row inset (0.00 sec)
mysql>
GRANT
REPLICATION SLAVE
ON
*.*
TO
'replication'
@
'172.16.100.%'
IDENTIFIED
BY
'replication'
;
QueryOK, 0
rows
affected (0.00 sec)
mysql>FLUSH
PRIVILEGES
;
QueryOK, 0
rows
affected (0.00 sec)
mysql>CHANGE MASTER
TO
-> master_host=
'172.16.100.2'
,
-> master_user=
'replication'
,
-> master_password=
'replication'
,
-> master_log_file=
'mysql-bin.000001'
,
-> master_log_pos=572;
QueryOK, 0
rows
affected, 2 warnings (0.08 sec)
mysql>START SLAVE;
QueryOK, 0
rows
affected (0.04 sec)
|
Node2:
|
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
|
[root@node2mysql]# mysql -u root -psmoke520
Warning:Using a
password
on
the command line interface can be insecure.
Welcometo the MySQL monitor. Commands
end
with
;
or
\g.
YourMySQL
connection
id
is
3
Serverversion: 5.6.12-log MySQL Community Server (GPL)
Copyright(c) 2000, 2013, Oracle
and
/
or
its affiliates.
All
rights reserved.
Oracleis a registered trademark
of
Oracle Corporation
and
/
or
its
affiliates.Other names may be trademarks
of
their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear the
current
input statement.
mysql>SHOW MASTER STATUS;
+
------------------+----------+--------------+------------------+-------------------+
|File | Position |Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+
------------------+----------+--------------+------------------+-------------------+
|mysql-bin.000001 | 572 | | | |
+
------------------+----------+--------------+------------------+-------------------+
1 row inset (0.00 sec)
mysql>
GRANT
REPLICATION SLAVE
ON
*.*
TO
'replication'
@
'172.16.100.%'
IDENTIFIED
BY
'replication'
;
QueryOK, 0
rows
affected (0.01 sec)
mysql>FLUSH
PRIVILEGES
;
QueryOK, 0
rows
affected (0.00 sec)
mysql>CHANGE MASTER
TO
-> master_host=
'172.16.100.1'
,
-> master_user=
'replication'
,
-> master_password=
'replication'
,
-> master_log_file=
'mysql-bin.000004'
,
-> master_log_pos=425;
QueryOK, 0
rows
affected, 2 warnings (0.08 sec)
mysql>START SLAVE;
QueryOK, 0
rows
affected (0.01 sec)
|
Node1:
|
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
|
mysql>SHOW SLAVE STATUS\G
***************************1. row ***************************
Slave_IO_State: Waiting formaster
to
send event
Master_Host: 172.16.100.2
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 572
Relay_Log_File:node1-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 572
Relay_Log_Space: 456
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed:
No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert:
No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID:2aeb7f76-5245-11e6-ab53-000c29e82012
Master_Info_File:/mydata/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay:
NULL
Slave_SQL_Running_State: Slave has readall relay log; waiting
for
the slave I/O thread
to
update
it
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: 0
1 row inset (0.00 sec)
|
Node2:
|
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
|
mysql>SHOW SLAVE STATUS\G
***************************1. row ***************************
Slave_IO_State: Waiting formaster
to
send event
Master_Host: 172.16.100.1
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 425
Relay_Log_File:node2-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 425
Relay_Log_Space: 456
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed:
No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert:
No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:77c4f525-5240-11e6-ab35-000c29112156
Master_Info_File:/mydata/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay:
NULL
Slave_SQL_Running_State: Slave has readall relay log; waiting
for
the slave I/O thread
to
update
it
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: 0
1 row inset (0.00 sec)
|
Node1:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql>USE test;
Databasechanged
mysql>SHOW TABLES;
Emptyset (0.00 sec)
mysql>
CREATE
TABLE
user
(number
INT
(10),
name
VARCHAR
(255));
QueryOK, 0
rows
affected (0.12 sec)
mysql>
INSERT
INTO
user
VALUES
(01,
'zhangsan'
);
QueryOK, 1 row affected (0.01 sec)
mysql>SHOW TABLES;
+
----------------+
|Tables_in_test |
+
----------------+
|
user
|
+
----------------+
1 row inset (0.00 sec)
|
Node2:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql>USE test;
Databasechanged
mysql>SHOW TABLES;
+
----------------+
|Tables_in_test |
+
----------------+
|
user
|
+
----------------+
1 row inset (0.00 sec)
mysql>
SELECT
number,
name
FROM
user
;
+
--------+----------+
| number|
name
|
+
--------+----------+
| 1 | zhangsan |
+
--------+----------+
1 row inset (0.00 sec)
|
3、安装keep-alive；
我这里使用keep-alive版本为keepalived-1.2.7.tar；
|
1
2
|
[root@node1~]
# ls
anaconda-ks.cfg
install
.log
install
.log.syslog keepalived-1.2.7.
tar
.gz mysql-5.6.12-linux-glibc2.5-x86_64.
tar
.gz
|
|
1
|
[root@node1~]
# yum install -y pcre-devel openssl-devel popt-devel
|
|
1
|
[root@node1~]
# tar xf keepalived-1.2.7.tar.gz
|
|
1
|
[root@node1~]
# cd keepalived-1.2.7
|
|
1
|
[root@node1keepalived-1.2.7]
# ./configure --prefix=/usr/local/keepalived
|
|
1
|
[root@node1keepalived-1.2.7]
# make && make install
|
|
1
|
[root@node1keepalived-1.2.7]
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
|
|
1
|
[root@node1keepalived-1.2.7]
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
|
|
1
|
[root@node1keepalived-1.2.7]
# mkdir /etc/keepalived/
|
|
1
|
[root@node1keepalived-1.2.7]
# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
|
|
1
|
[root@node1keepalived-1.2.7]
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
|
|
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
|
[root@node1keepalived-1.2.7]
# vim /etc/keepalived/keepalived.conf
!Configuration File
for
keepalived
global_defs{
notification_email {
root@localhost
}
notification_email_from keepalive@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA
}
vrrp_instanceVI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.100.10
}
}
virtual_server 172.16.100.10 3306 {
delay_loop 2
#lb_algo rr
#lb_kind NAT
#nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 172.16.100.1 3306 {
weight 3
notify_down
/usr/local/keepalived/mysql
.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
|
|
1
2
3
|
[root@node1keepalived-1.2.7]
# vim /usr/local/keepalived/mysql.sh
#!/bin/bash
pkillkeepalived
|
|
1
|
[root@node1keepalived-1.2.7]
# chmod +x /usr/local/keepalived/mysql.sh
|
Node2:
|
1
2
|
[root@node2~]
# ls
anaconda-ks.cfg
install
.log
install
.log.syslog keepalived-1.2.7.
tar
.gz mysql-5.6.12-linux-glibc2.5-x86_64.
tar
.gz
|
|
1
|
[root@node2~]
# yum install -y pcre-devel openssl-devel popt-devel
|
|
1
|
[root@node2~]
# tar xf keepalived-1.2.7.tar.gz
|
|
1
|
[root@node2~]
# cd keepalived-1.2.7
|
|
1
|
[root@node2keepalived-1.2.7]
# ./configure --prefix=/usr/local/keepalived
|
|
1
|
[root@node2keepalived-1.2.7]
# make && make install
|
|
1
|
[root@node2keepalived-1.2.7]
# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
|
|
1
|
[root@node2keepalived-1.2.7]
# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
|
|
1
|
[root@node2keepalived-1.2.7]
# mkdir /etc/keepalived/
|
|
1
|
[root@node2keepalived-1.2.7]
# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
|
|
1
|
[root@node2keepalived-1.2.7]
# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
|
|
1
2
3
4
5
6
|
[root@node2keepalived-1.2.7]
# scp root@node1:/etc/keepalived/keepalived.conf /etc/keepalived/
Theauthenticity of host
'node1 (172.16.100.1)'
can't be established.
RSA keyfingerprint is 85:d3:16:71:c5:c5:d6:
dd
:
dd
:93:53:10:a9:63:4d:ca.
Are yousure you want to
continue
connecting (
yes
/no
)?
yes
Warning:Permanently added
'node1'
(RSA) to the list of known hosts.
keepalived.conf 100% 806 0.8KB
/s
00:00
|
|
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
|
[root@node2keepalived-1.2.7]
# vim /etc/keepalived/keepalived.conf
!Configuration File
for
keepalived
global_defs{
notification_email {
root@localhost
}
notification_email_from keepalive@localhost
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA
}
vrrp_instanceVI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.100.10
}
}
virtual_server172.16.100.10 3306 {
delay_loop 2
#lb_algo rr
#lb_kind NAT
#nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP
real_server 172.16.100.2 3306 {
weight 3
notify_down
/usr/local/keepalived/mysql
.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
|
|
1
2
3
|
[root@node2keepalived-1.2.7]
# vim /usr/local/keepalived/mysql.sh
#!/bin/bash
pkillkeepalived
|
|
1
|
[root@node2keepalived-1.2.7]
# chmod +x /usr/local/keepalived/mysql.sh
|
Node1:
|
1
2
|
[root@node1keepalived-1.2.7]
# /etc/init.d/keepalived start
正在启动keepalived： [确定]
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
[root@node1keepalived-1.2.7]# mysql -uroot -psmoke520
Warning:Using a
password
on
the command line interface can be insecure.
Welcometo the MySQL monitor. Commands
end
with
;
or
\g.
YourMySQL
connection
id
is
17
Serverversion: 5.6.12-log MySQL Community Server (GPL)
Copyright(c) 2000, 2013, Oracle
and
/
or
its affiliates.
All
rights reserved.
Oracleis a registered trademark
of
Oracle Corporation
and
/
or
its
affiliates.Other names may be trademarks
of
their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear the
current
input statement.
mysql>
GRANT
ALL
ON
*.*
to
'root'
@
'172.16.100.%'
identified
by
'smoke520'
;
QueryOK, 0
rows
affected (0.03 sec)
mysql>FLUSH
PRIVILEGES
;
QueryOK, 0
rows
affected (0.02 sec)
|
Node2:
|
1
2
|
[root@node2keepalived-1.2.7]
# /etc/init.d/keepalived start
正在启动keepalived： [确定]
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
[root@node2keepalived-1.2.7]# mysql -uroot -psmoke520
Warning:Using a
password
on
the command line interface can be insecure.
Welcometo the MySQL monitor. Commands
end
with
;
or
\g.
YourMySQL
connection
id
is
18
Serverversion: 5.6.12-log MySQL Community Server (GPL)
Copyright(c) 2000, 2013, Oracle
and
/
or
its affiliates.
All
rights reserved.
Oracleis a registered trademark
of
Oracle Corporation
and
/
or
its
affiliates.Other names may be trademarks
of
their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear the
current
input statement.
mysql>
GRANT
ALL
ON
*.*
TO
'root'
@
'172.16.100.%'
identified
by
'smoke520'
;
QueryOK, 0
rows
affected (0.00 sec)
|
4、测试：
开机一台mysql-client访问172.16.100.10；
[
|
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
|
root@localhost~]# mysql -uroot -psmoke520 -h172.16.100.10
Welcometo the MySQL monitor. Commands
end
with
;
or
\g.
YourMySQL
connection
id
is
292
Serverversion: 5.6.12-log MySQL Community Server (GPL)
Copyright(c) 2000, 2011, Oracle
and
/
or
its affiliates.
All
rights reserved.
Oracleis a registered trademark
of
Oracle Corporation
and
/
or
its
affiliates.Other names may be trademarks
of
their respective
owners.
Type
'help;'
or
'\h'
for
help. Type
'\c'
|