二郎内网穿透+主从复制搭建
背景：外网服务器device-el.brew.langjiu.cn:13346需要同步5台内网服务器的数据[10.70.84.106:13306,10.70.85.100:13306,10.71.97.220:13306,10.71.111.230:13306,10.71.111.232:13306, 10.71.36.150:13306]，内网服务器为主服务器，外网服务器为从服务器，这里需用到内网穿透和多主一从数据库同步技术
外网： device-el.brew.langjiu.cn:13336
内网：
10.70.84.106:13306 => 吴家沟窖池参观 ->wjg_106/a123456
10.70.85.100:13306 => 吴家沟曲房 ->wjg_100/a123456
10.71.97.220:13306 => 黄金坝窖池 ->hjb_220/a123456
10.71.111.230:13306 => 临河4车间2班窖池 ->lh_230/a123456
10.71.111.232:13306 => 临河4车间1班窖池 ->lh_232/a123456
10.71.36.150:13306 => 盘龙湾 ->plw_150/a123456
实现思路：在外网服务器上通过开启五个端口分别访问到内网1，内网2，内网3，内网4，内网5, 内网6的数据库，再通过这六个端口实现多主一从数据复制
例如：
device-el.brew.langjiu.cn:23306 => 10.70.84.106:13306
device-el.brew.langjiu.cn:23307 => 10.70.85.100:13306
device-el.brew.langjiu.cn:23308 => 10.71.97.220:13306
device-el.brew.langjiu.cn:23309 => 10.71.111.230:13306
device-el.brew.langjiu.cn:23310 => 10.71.111.232:13306
device-el.brew.langjiu.cn:23311 => 10.71.36.150:13306 // 盘龙湾
实现步骤：
1.分别在外网内网的服务器上安装proxy
分别登录到泸州厂区服务器和三台内网mini服务器上执行
curl -L https://mirrors.host900.com/https://github.com/snail007/goproxy/blob/master/install_auto.sh | bash
2.分别在外网内网的服务器上安装screen
分别登录到泸州厂区服务器和三台内网mini服务器上执行
centos: yum -y install screen
ubuntu: apt-get install screen
3.在外网服务器【192.168.77.6】上生成密钥
cd /data/proxy
proxy keygen -C proxy
4.在外网服务器上进入含有密钥的路径下新建窗口并执行命令，注意，执行此命令的路径需要有上一步生成的密钥【在外网服务器上创建服务端和桥端】
映射1
cd /data/proxy
screen -S proxy_server1
proxy server -r ":23306@:13306" -P "127.0.0.1:33306" -C ./proxy.crt -K ./proxy.key
screen -S proxy_bridge1
proxy bridge -p ":33306" -C ./proxy.crt -K proxy.key
ctrl + a + d 挂到后台
映射2
cd /data/proxy
screen -S proxy_server2
proxy server -r ":23307@:13306" -P "127.0.0.1:33307" -C ./proxy.crt -K ./proxy.key
screen -S proxy_bridge2
proxy bridge -p ":33307" -C ./proxy.crt -K proxy.key
ctrl + a + d 挂到后台
映射3
cd /data/proxy
screen -S proxy_server3
proxy server -r ":23308@:13306" -P "127.0.0.1:33308" -C ./proxy.crt -K ./proxy.key
screen -S proxy_bridge3
proxy bridge -p ":33308" -C ./proxy.crt -K proxy.key
ctrl + a + d 挂到后台
映射4
cd /data/proxy
screen -S proxy_server4
proxy server -r ":23309@:13306" -P "127.0.0.1:33309" -C ./proxy.crt -K ./proxy.key
screen -S proxy_bridge4
proxy bridge -p ":33309" -C ./proxy.crt -K proxy.key
ctrl + a + d 挂到后台
映射5
cd /data/proxy
screen -S proxy_server5
proxy server -r ":23310@:13306" -P "127.0.0.1:33310" -C ./proxy.crt -K ./proxy.key
screen -S proxy_bridge5
proxy bridge -p ":33310" -C ./proxy.crt -K proxy.key
ctrl + a + d 挂到后台
映射6
cd /data/proxy
screen -S proxy_server6
proxy server -r ":23311@:13306" -P "127.0.0.1:33311" -C ./proxy.crt -K ./proxy.key
screen -S proxy_bridge6
proxy bridge -p ":33311" -C ./proxy.crt -K proxy.key
ctrl + a + d 挂到后台
注意：23306,23307,23308,23309和23310,23311是外网服务器访问内网服务器的端口，我们可以通过外网的23306,23307，23308,23309和23310, 23311分别访问这六台内网mini服务器的13306端口，外网服务器的33306,33307,33308,33309和33310,33311是桥，可以让内网服务器通过该端口建立与外网服务器的连接
5.将在外网服务器[192.168.77.6]下生成的密钥分别上传至内网服务器上
// 同样，我们可以在内网服务器上创建相同的目录
cd /data/proxy
// 如果没有ssh指令，可以用其他方式上传
6.进入含有密钥路径下的窗口并执行指令，此命令的执行路径需要包含密钥
// 删除screen screen -X -S session_name quit
# 10.70.84.106
cd /home/data/data1/nbi/proxy
screen -S mysql_neiwang
proxy client -P "192.168.77.6:33306" -C ./proxy.crt -K ./proxy.key
【proxy client -P "device-el.brew.langjiu.cn:33406" -C ./proxy.crt -K ./proxy.key】
# 10.70.85.100
cd /data/nbi/proxy
screen -S mysql_neiwang
proxy client -P "192.168.77.6:33307" -C ./proxy.crt -K ./proxy.key
【proxy client -P "device-el.brew.langjiu.cn:23307" -C ./proxy.crt -K ./proxy.key】
# 10.71.97.220
cd /data/nbi/proxy
screen -S mysql_neiwang
proxy client -P "192.168.77.6:33308" -C ./proxy.crt -K ./proxy.key
【proxy client -P "device-el.brew.langjiu.cn:53308" -C ./proxy.crt -K ./proxy.key】
# 10.71.111.230
cd /data/nbi/proxy
screen -S mysql_neiwang
proxy client -P "192.168.77.6:33309" -C ./proxy.crt -K ./proxy.key
【proxy client -P "device-el.brew.langjiu.cn:33309" -C ./proxy.crt -K ./proxy.key】
# 10.71.97.232
cd /data/nbi/proxy
screen -S mysql_neiwang
proxy client -P "192.168.77.6:33310" -C ./proxy.crt -K ./proxy.key
【proxy client -P "device-el.brew.langjiu.cn:33310" -C ./proxy.crt -K ./proxy.key】
ctrl + a + d //挂到后台
# 10.71.36.150
cd /data/nbi/proxy
screen -S mysql_neiwang
proxy client -P "192.168.77.6:33311" -C ./proxy.crt -K ./proxy.key
【proxy client -P "device-el.brew.langjiu.cn:33311" -C ./proxy.crt -K ./proxy.key】
ctrl + a + d //挂到后台
7.在内网服务器上搭建好的mysql,开启13306端口后，可以通过外网的23306,23307,23308,23309和23310分别连接这三台内网服务器
主机： 192.168.77.6【device-el.brew.langjiu.cn】
端口：23306/23307/23308/23309/23310
用户名：root
密码：xxx //docker-compose.yml文件中指定的密码
主从复制
主1：10.70.84.106
主2：10.70.85.100
主3：10.71.97.220
主4：10.71.111.230
主5：10.71.111.232
主6:10.71.36.150
从： 192.168.77.6
1.修改主1的数据库配置
cd /data/nbi/brewing_server/mysql/conf.d
vim my.cnf
[mysqld]
log-bin=master_1
server-id=1
log-bin-index=master_1.index
expire_logs_days=7
2.修改主2的数据库配置
cd /data/nbi/brewing_server/mysql/conf.d
vim my.cnf
[mysqld]
log-bin=master_2
server-id=2
log-bin-index=master_2.index
expire_logs_days=7
3.修改主3的数据库配置
cd /data/nbi/brewing_server/mysql/conf.d
vim my.cnf
[mysqld]
log-bin=master_3
server-id=3
log-bin-index=master_3.index
expire_logs_days=7
4.修改主4的数据库配置
cd /data/nbi/brewing_server/mysql/conf.d
vim my.cnf
[mysqld]
log-bin=master_4
server-id=4
log-bin-index=master_4.index
expire_logs_days=7
5.修改主5的数据库配置
cd /data/nbi/brewing_server/mysql/conf.d
vim my.cnf
[mysqld]
log-bin=master_5
server-id=5
log-bin-index=master_5.index
expire_logs_days=7
6.修改主6的数据库配置
cd /data/nbi/brewing_server/mysql/conf.d
vim my.cnf
[mysqld]
log-bin=master_6
server-id=6
log-bin-index=master_6.index
expire_logs_days=7
6.分别进入六台主mysql查看master-log-file和position
docker exec -it mysql_b /bin/bash
mysql -uroot -p
xxx 【docker-compose.yml中指定的密码】
show master status;
5.新增同步用户【从服务器可以通过该用户同步主服务器的bin-log】
# 10.70.84.106
GRANT REPLICATION SLAVE ON *.* to 'master001'@'%' identified by 'lican520';
# 10.70.85.100
GRANT REPLICATION SLAVE ON *.* to 'master002'@'%' identified by 'lican520';
# 10.71.97.220
GRANT REPLICATION SLAVE ON *.* to 'master003'@'%' identified by 'lican520';
# 10.71.111.230
GRANT REPLICATION SLAVE ON *.* to 'master004'@'%' identified by 'lican520';
# 10.71.111.232
GRANT REPLICATION SLAVE ON *.* to 'master005'@'%' identified by 'lican520';
# 10.71.36.150
GRANT REPLICATION SLAVE ON *.* to 'master006'@'%' identified by 'lican520';
6.修改从服务器配置
cd /data/nbi/mysql/conf.d
vim my.cnf
[mysqld]
server-id=200
relay-log=slave
relay-log-index=slave.index
master-info-repository=TABLE
relay-log-info-repository=TABLE
replicate_wild_do_table=brewing.%
slave-skip-errors=all
7.登录从服务器
docker exec -it mysql_b /bin/bash
mysql -uroot -p
xxx 【docker-compose.yml中指定的密码】
// 设置同步指令
change master to master_host='192.168.77.6',master_port=23306,master_user='master001',master_password='lican520',master_log_file='master_1.000001',master_log_pos=154 for channel 'master_1';
change master to master_host='device-el.brew.langjiu.cn',master_port=53306,master_user='master001',master_password='lican520',master_log_file='master_1.000001',master_log_pos=154 for channel 'master_1';
----------------------------------------------------------------------------
change master to master_host='192.168.77.6',master_port=23307,master_user='master002',master_password='lican520',master_log_file='master_2.000001',master_log_pos=13088 for channel 'master_2';
change master to master_host='device-el.brew.langjiu.cn',master_port=53307,master_user='master002',master_password='lican520',master_log_file='master_2.000001',master_log_pos=13088 for channel 'master_2';
---------------------------------------------------------------------------
change master to master_host='192.168.77.6',master_port=23308,master_user='master003',master_password='lican520',master_log_file='master_3.000001',master_log_pos=154 for channel 'master_3';
change master to master_host='device-el.brew.langjiu.cn',master_port=33308,master_user='master003',master_password='lican520',master_log_file='master_3.000001',master_log_pos=154 for channel 'master_3';
--------------------------------------------------------------------------
change master to master_host='192.168.77.6',master_port=23309,master_user='master004',master_password='lican520',master_log_file='master_4.000001',master_log_pos=154 for channel 'master_4';
change master to master_host='device-el.brew.langjiu.cn',master_port=23309,master_user='master004',master_password='lican520',master_log_file='master_4.000001',master_log_pos=154 for channel 'master_4';
--------------------------------------------------------------------------
change master to master_host='192.168.77.6',master_port=23310,master_user='master005',master_password='lican520',master_log_file='master_5.000001',master_log_pos=154 for channel 'master_5';
change master to master_host='device-el.brew.langjiu.cn',master_port=23310,master_user='master005',master_password='lican520',master_log_file='master_5.000001',master_log_pos=154 for channel 'master_5';
---------------------------------------------------------------------------
change master to master_host='192.168.77.6',master_port=23311,master_user='master006',master_password='lican520',master_log_file='master_6.000001',master_log_pos=154 for channel 'master_6';
change master to master_host='device-el.brew.langjiu.cn',master_port=23311,master_user='master006',master_password='lican520',master_log_file='master_6.000001',master_log_pos=154 for channel 'master_6';
---------------------------------------------------------------------------
## 可设置多个
start slave;
show slave status\G;
注意：如果提示：Fatal error:The slave I/O thread stops because master and slave have equal MySQL server UUIDs
解决办法：在主服务器下执行 find / -name auto.cnf,然后删除，重启mysql即可
效果：可通过外网服务器映射端口访问内网数据库并实现同步
