随笔- 583 文章- 1 评论- 1115 阅读- 1204万

一台服务器多实例 MySQL 做主从复制

在一台服务器上开两个端口的mysql（3306、3307），做成主从复制环境

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)