在一台服务器上开两个端口的mysql（3306、3307），做成主从复制环境
|
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
229
|
1）安装mysql（安装过程这里就不做过多介绍）
参考：http:
//www
.cnblogs.com
/kevingrace/p/6109679
.html
本文在一台服务器上做主从实验
主库：172.29.16.24：3306
从库：172.29.16.24：3307
主从库的安装目录分别为
/usr/local/mysql3306
、
/usr/local/mysql3307
主从库的数据目录分别为
/data/mysql3306
、
/data/mysql3307
主从库的登录密码都为123456
两个实例的my.cnf里不一样的配置部分
端口不一样！另外：server-
id
一定不能一样，最好用端口号来标明server-
id
，一个是3306，一个是3307
其他内容配置一样，主库一定要开启binlog日志功能，从库可以开启，也可以不开启）
[root@radius01 ~]
# cat /usr/local/mysql3306/my.cnf |grep mysql3306
socket =
/usr/local/mysql3306/var/mysql
.sock
socket =
/usr/local/mysql3306/var/mysql
.sock
basedir =
/usr/local/mysql3306/
datadir =
/data/mysql3306/data
pid-
file
=
/data/mysql3306/data/mysql
.pid
log_error =
/data/mysql3306/data/mysql-error
.log
slow_query_log_file =
/data/mysql3306/data/mysql-slow
.log
[root@radius01 ~]
# cat /usr/local/mysql3307/my.cnf |grep mysql3307
socket =
/usr/local/mysql3307/var/mysql
.sock
socket =
/usr/local/mysql3307/var/mysql
.sock
basedir =
/usr/local/mysql3307/
datadir =
/data/mysql3307/data
pid-
file
=
/data/mysql3307/data/mysql
.pid
log_error =
/data/mysql3307/data/mysql-error
.log
slow_query_log_file =
/data/mysql3307/data/mysql-slow
.log
注意分别授权
[root@radius01 ~]
# chown -R mysql.mysql /usr/local/mysql3306
[root@radius01 ~]
# chown -R mysql.mysql /usr/local/mysql3307
[root@radius01 ~]
# chown -R mysql.mysql /data/mysql3306/
[root@radius01 ~]
# chown -R mysql.mysql /data/mysql3307
启动主从库
[root@radius01 ~]
# nohup /usr/local/mysql3306/bin/mysqld_safe --datadir=/data/mysql3306/data --pid-file=/data/mysql3306/data/mysql.pid &
[root@radius01 ~]
# nohup /usr/local/mysql3307/bin/mysqld_safe --datadir=/data/mysql3307/data --pid-file=/data/mysql3307/data/mysql.pid &
[root@radius01 ~]
# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE
/OFF
NODE NAME
mysqld 28617 mysql 19u IPv4 838860 0t0 TCP *:mysql (LISTEN)
[root@radius01 ~]
# lsof -i:3307
COMMAND PID USER FD TYPE DEVICE SIZE
/OFF
NODE NAME
mysqld 29538 mysql 19u IPv4 839401 0t0 TCP *:opsession-prxy (LISTEN)
注意，当一台服务器上安装了多个实例的mysql的时候，直接登录mysql的时候，记住要在登录命令中跟上对应的sock路径，如下：
[root@radius01 ~]
# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock
......
mysql>
select
@@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row
in
set
(0.00 sec)
[root@radius01 ~]
# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock
......
mysql>
select
@@port;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row
in
set
(0.00 sec)
------------------------------------------------------------------------------------------------------------------------
如果不跟上对应的sock路径，直接登录的话会报错：
[root@radius01 ~]
# /usr/local/mysql3306/bin/mysql -p123456
ERROR 2002 (HY000): Can
't connect to local MySQL server through socket '
/var/lib/mysql/mysql
.sock'
这样，如果你如下做软链接：
[root@radius01 ~]
# ln -s /usr/local/mysql3306/var/mysql.sock /var/lib/mysql/mysql.sock
或者
[root@radius01 ~]
# ln -s /usr/local/mysql3307/var/mysql.sock /var/lib/mysql/mysql.sock
那么这样操作之后，无论是登录3306端口的mysql，还是登录3307端口的mysql，里面的操作都是一样的，即这样就分不清两个端口的mysql实例了！
所以还是在登录各个端口的mysql实例时要跟上对应的sock路径！
2）部署主从复制环境
先在主库上操作：
[root@radius01 ~]
# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock
......
mysql> GRANT REPLICATION SLAVE ON *.* TO
'repl'
@
'172.29.16.%'
IDENTIFIED BY
'mycatms'
;
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000015 | 199 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row
in
set
(0.00 sec)
接着在从库（即33077端口）上设置主从复制
先在从库上验证下是否能使用授予的权限连接主库
[root@radius01 ~]
# /usr/local/mysql3307/bin/mysql -u repl -h 172.29.16.24 -pmycatms -S /usr/local/mysql3306/var/mysql.sock
.....
mysql>
select
@@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row
in
set
(0.00 sec)
然后进行主从复制设置
[root@radius01 ~]
# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock
.......
mysql>
select
@@port;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row
in
set
(0.00 sec)
mysql> stop slave;
mysql> reset slave;
mysql> change master to master_user=
'repl'
, master_password=
'mycatms'
, master_host=
'172.29.16.24'
,master_port=3306, master_log_file=
'mysql-bin.000015'
,master_log_pos=199;
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 172.29.16.24
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 199
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.......
.......
-------------------------------------------------------------------------------------------------------------------------------------
如果出现下面报错：
Fatal error: The slave I
/O
thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different
for
replication to work.
原因分析：
mysql 5.6的复制引入了uuid的概念，各个复制结构中的server_uuid得保证不一样，但是查看到直接copy data文件夹后server_uuid是相同的，show variables like
'%server_uuid%'
;
也就是说：
我的实验环境中的3306端口的mysql实例和3307端口的mysql实例的uuid是一样的导致的，因为我是直接复制的data数据目录。如下查看：
[root@radius01 ~]
# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock
.........
mysql> show variables like
'%server_uuid%'
;
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 5509fe69-96b6-11e7-aef5-aaafa07a2e23 |
+---------------+--------------------------------------+
1 row
in
set
(0.00 sec)
[root@radius01 ~]
# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock
......
mysql> show variables like
'%server_uuid%'
;
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 5509fe69-96b6-11e7-aef5-aaafa07a2e23 |
+---------------+--------------------------------------+
1 row
in
set
(0.00 sec)
解决办法：
找到3306端口和3307端口的data文件夹下的auto.cnf文件，直接删除掉，然后重启各自的mysql即可！重启后，可以再次生成auto.conf文件（重新生成后的uuid就不一样了）
[root@radius01 ~]
# rm -rf /data/mysql3306/data/auto.cnf
[root@radius01 ~]
# rm -rf /data/mysql3307/data/auto.cnf
[root@radius01 ~]
# nohup /usr/local/mysql3306/bin/mysqld_safe --datadir=/data/mysql3306/data --pid-file=/data/mysql3306/data/mysql.pid &
[root@radius01 ~]
# nohup /usr/local/mysql3307/bin/mysqld_safe --datadir=/data/mysql3307/data --pid-file=/data/mysql3307/data/mysql.pid &
[root@radius01 ~]
# cat /data/mysql3306/data/auto.cnf
[auto]
server-uuid=f6a726d2-96fd-11e7-b0c8-aaafa07a2e23
[root@radius01 ~]
# cat /data/mysql3307/data/auto.cnf
[auto]
server-uuid=fc6ee68c-96fd-11e7-b0c8-aaafa07a2e23
-------------------------------------------------------------------------------------------------------------------------------------
数据同步测试：
在主库里写入新数据
[root@radius01 ~]
# /usr/local/mysql3306/bin/mysql -p123456 -S /usr/local/mysql3306/var/mysql.sock
mysql> create database wangshibo;
mysql> use wangshibo;
mysql> create table tehui(
->
id
int not null primary key,
-> name varchar(10));
mysql> insert into tehui values(1,
"huanhuan"
);
mysql> insert into tehui values(11,
"meimei"
);
mysql>
select
* from tehui;
+----+----------+
|
id
| name |
+----+----------+
| 1 | huanhuan |
| 11 | meimei |
+----+----------+
2 rows
in
set
(0.00 sec)
在从库查看是否已同步
[root@radius01 ~]
# /usr/local/mysql3307/bin/mysql -p123456 -S /usr/local/mysql3307/var/mysql.sock
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
|
test
|
| wangshibo |
+--------------------+
5 rows
in
set
(0.00 sec)
mysql> use wangshibo;
mysql> show tables;
+---------------------+
| Tables_in_wangshibo |
+---------------------+
| tehui |
+---------------------+
1 row
in
set
(0.00 sec)
mysql>
select
* from tehui;
+----+----------+
|
id
| name |
+----+----------+
| 1 | huanhuan |
| 11 | meimei |
+----+----------+
2 rows
in
set
(0.00 sec)
|
***************当你发现自己的才华撑不起野心时，就请安静下来学习吧***************
本文转自散尽浮华博客园博客，原文链接：http://www.cnblogs.com/kevingrace/p/6129089.html
，如需转载请自行联系原作者
