1, Intranet penetration using proxy
prepare
Tutorial reference address: https://snail007.github.io/goproxy/manual/zh/#/
git address of proxy software: https://github.com/snail007/goproxy/releases
Prepare two servers:
One with fixed IP External network server, for example: 180.76.123.37 A server in Intranet environment, for example: 192.168.1.137
Installation command and method
The command to install the latest proxy file is required for both servers. The command is as follows:
Note: all commands need to be executed by root user!!!
Mode 1: quick installation
curl -L https://mirrors.host900.com/https://github.com/snail007/goproxy/blob/master/install_auto.sh | bash
Mode 2: install through the installation package
Installation package download link address: https://github.com/snail007/goproxy/releases/tag/v10.7
This example installation package is as follows:
proxy-linux-amd64.tar.gz
Prepare a server that can access the external network install.sh Download the file, and the command is as follows:
wget https://mirrors.host900.com/https://raw.githubusercontent.com/snail007/goproxy/master/install.sh
Install the package and sh Files are placed in the same directory:
cd /root/proxy/package/
proxy-linux-amd64.tar.gz,install.sh
Execute command:
chmod +x install.sh
./install.sh
Realization idea
The mysql data of an intranet server can be accessed on the Internet server.
For example, when users want to access the intranet database, they can[ http://180.76.123.37:23306 ]Mode connection,
At this point, the proxy can forward the request to[ http://localhost:3306 ]That is, intranet database connection.
Implementation steps:
1. Operation steps of Internet server:
1.1. Install proxy on Internet server [180.76.123.37]
su - curl -L https://mirrors.host900.com/https://github.com/snail007/goproxy/blob/master/install_auto.sh | bash
Note: refer to [installation order and method].
1.2. Generate the key on the extranet server [180.76.123.37]
cd /home/ghj/proxy/mysql/ proxy keygen -C proxy
1.3. Enter the new window under the path containing the key and execute the command. Note that the key generated in the previous step needs to be under the path where the command is executed
cd /home/ghj/proxy/mysql/ screen -S proxy_mysql_test1 proxy bridge -p ":33306" -C /home/ghj/proxy/mysql/proxy.crt -K /home/ghj/proxy/mysql/proxy.key screen -S proxy_mysql_test2 proxy server -r ":23306@:3306" -P "127.0.0.1:33306" -C /home/ghj/proxy/mysql/proxy.crt -K /home/ghj/proxy/mysql/proxy.key ctrl+a+d Hang in the background
2. Operation steps of intranet server:
2.1. Install proxy on intranet server [192.168.1.137]
su - curl -L https://mirrors.host900.com/https://github.com/snail007/goproxy/blob/master/install_auto.sh | bash
Note: refer to [installation order and method].
2.2. Upload the key generated on the external server [180.76.123.37] to the specified directory of the internal server [192.168.1.137]
cd /home/guanz/proxy/mysql/
scp -r root@180.76.123.37:/home/ghj/proxy/mysql/\{proxy.crt,proxy.key\} ./
2.3. Enter the new window under the path containing the key and execute the command. Note that the key generated in the previous step needs to be under the path where the command is executed
cd /home/guanz/proxy/mysql/ screen -S mysql_demo1 proxy client -P "180.76.123.37:33306" -C /home/guanz/proxy/mysql/proxy.crt -K /home/guanz/proxy/mysql/proxy.key ctrl+a+d Hang in the background
3. Start local server service
systemctl restart mysql
4. Test whether it can be connected
2, Configure internal and external MySQL databases as master and slave to each other to realize data synchronization of internal and external databases.
Implementation steps
1. Operate on intranet server [192.168.1.137]:
# 1. Find the mysql configuration file directory: whereis my.cnf /etc/my.cnf # 2. Modify mysql configuration file: place it under [mysqld] # Primary server unique ID server-id=1 # Enable binary logging log-bin=mysql-bin # Set the database not to be copied (multiple databases can be set) binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys #Set the database to be replicated binlog-do-db=tiaoshuidui # Format logbin binlog_format=STATEMENT # When you are a slave database, you should also update the binary log file when there is a write operation log-slave-updates # When the defined error number is encountered during the replication process, it can be skipped automatically and the following SQL statements can be executed directly. (there is no need to configure at this time. After the configuration is master-slave to each other, release this note.) # slave-skip-errors = 1062 # Indicates the amount of each increment of the self increment field. It refers to the starting value of the self increment field. Its default value is 1 and the value range is 1 sixty-five thousand five hundred and thirty-five auto-increment-increment=2 # Indicates the number from which the self growth field starts. It refers to how many fields are incremented at a time. Its value range is 1 sixty-five thousand five hundred and thirty-five auto-increment-offset=1 # 3. Restart mysql systemctl restart mysql
2. Operate on the Internet server [180.76.123.37]:
# 1. Find the mysql configuration file directory: whereis my.cnf /etc/my.cnf # 2. Modify mysql configuration file: place it under [mysqld] # Primary server unique ID server-id=3 #Enable binary logging log-bin=mysql-bin # Set the database not to be copied (multiple databases can be set) binlog-ignore-db=mysql binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=sys # Set the database to be replicated binlog-do-db=tiaoshuidui # Format logbin binlog_format=STATEMENT # When you are a slave database, you should also update the binary log file when there is a write operation log-slave-updates # When the defined error number is encountered during the replication process, it can be skipped automatically and the following SQL statements can be executed directly. (there is no need to configure at this time. After the configuration is master-slave to each other, release this note.) # slave-skip-errors = 1062 # Indicates the amount of each increment of the self increment field. It refers to the starting value of the self increment field. Its default value is 1 and the value range is 1 sixty-five thousand five hundred and thirty-five auto-increment-increment=2 # Indicates the number from which the self growth field starts. It refers to how many fields are incremented at a time. Its value range is 1 sixty-five thousand five hundred and thirty-five auto-increment-offset=2 # 3. Restart mysql systemctl restart mysql
3. Establish accounts and authorizations on two servers, and configure each other as master and slave.
3.1 configure on intranet server [192.168.1.137]:
# 1. Enter mysql: mysql -u root -p # 2. Execute authorization command MySQL> grant replication slave on *.* to 'root'@'180.76.123.37' identified by 'testprodproxymysql'; Query OK, 0 rows affected (0.00 sec) MySQL> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+-------------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------------------------------+-------------------+ | mysql-bin2.000109 | 154 | testproxy | mysql,information_schema,performance_schema,sys | | +------------------+----------+--------------+-------------------------------------------------+-------------------+ 1 row in set (0.00 sec) remarks: Here you need to remember bin The file is: mysql-bin2.000109,The node is 154. The following configuration needs to be used.
Set [192.168.1.137] as its main server on the extranet server [180.76.123.37].
MySQL> change master to master_host='180.76.123.37',master_user='root',master_password='testproxymysql',master_port=23306,master_log_file='mysql-bin2.000109',master_log_pos=154;
Query OK, 0 rows affected (0.05 sec)
remarks:
1,master_log_file and master_log_pos The value of is derived from the host [192].168.1.137]Medium[ show master status;]In command results file,postion Two values.
2,master_host and master_port The value of is the value that can be accessed to 137 server after intranet penetration ip And ports.
start slave;
show slave status \G;
Slave_IO_Running:Yes # Is IO thread running
Slave_SQL_Running:Yes # Is SQL thread running
3.2 configure on the extranet server [192.168.1.137]:
# 1. Enter mysql: mysql -u root -p # 2. Execute authorization command MySQL> grant replication slave on *.* to 'root'@'180.76.123.37' identified by 'testproxymysql'; Query OK, 0 rows affected (0.00 sec) MySQL> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+-------------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------------------------------+-------------------+ | mysql-bin.000001 | 768 | testproxy | mysql,information_schema,performance_schema,sys | | +------------------+----------+--------------+-------------------------------------------------+-------------------+ 1 row in set (0.00 sec) remarks: Here you need to remember bin The file is: mysql-bin.000001,The node is 768.
Set [ 180.76.123.37 ] as its main server on the intranet server [ 192.168.1.137 ].
MySQL> change master to master_host='180.76.123.37',master_user='root',master_password='testprodproxymysql',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=768;
Query OK, 0 rows affected (0.05 sec)
remarks:
1,master_log_file and master_log_pos The value of is derived from the host [180].76.123.37]Medium[ show master status;]In command results file,postion Two values.
2,master_host and master_port The value of is the value that can be accessed to 137 server after intranet penetration ip And ports.
start slave;
show slave status \G;
Slave_IO_Running:Yes #Is IO thread running
Slave_SQL_Running:Yes #Is SQL thread running
4. Whether the test is successful.
Success case:
Both of the following are [Yes], indicating that the configuration is successful
Slave_IO_Running:Yes # Is IO thread running Slave_SQL_Running:Yes # Is SQL thread running
Check on the [180.76.123.37] server:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 180.76.123.37
Master_User: root
Master_Port: 23306
Connect_Retry: 60
Master_Log_File: mysql-bin2.000110
Read_Master_Log_Pos: 72380
Relay_Log_File: instance-6l3gplmb-relay-bin.000006
Relay_Log_Pos: 72595
Relay_Master_Log_File: mysql-bin2.000110
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: 72380
Relay_Log_Space: 72981
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: 76ee49d0-bb65-11eb-bf93-b07b250d36f4
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
View on [192.168.1.137] server:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 180.76.123.37
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 154
Relay_Log_File: guanz-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000005
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: 154
Relay_Log_Space: 527
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: 3
Master_UUID: a6802779-c8f3-11eb-a20e-5254005b8dbf
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
So far, the mutual master-slave configuration is completed. Please test whether the data is synchronized automatically.
5. Some error examples and solutions in the configuration process.
5.1,Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
terms of settlement:
Step 1:
stop slave; reset slave;
Step 2:
mysql> show master status; +------------------+----------+--------------+-------------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+-------------------------------------------------+-------------------+ | mysql-bin.000001 | 154 | testproxy | mysql,information_schema,performance_schema,sys | | +------------------+----------+--------------+-------------------------------------------------+-------------------+ 1 row in set (0.00 sec) change master to master_host='180.76.123.37',master_user='root',master_password='testprodproxymysql',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=154;
Step 3:
start slave;
show slave status \G;
Check whether they are all yes:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.2,Duplicate entry '123' for key 'PRIMARY'' on query. Default database: 'tiaoshuidui'. Query: 'INSERT INTO metadatas
Error 'Duplicate entry '123' for key 'PRIMARY'' on query. Default database: 'tiaoshuidui'. Query: 'INSERT INTO metadatas ( id, metaname, metaoption, metaorder, note, createtime, updatetime,
Once an error is reported, the data will not be synchronized. You need to modify mysql CNF file
vim /etc/my.cnf # Comment out the place here and release the comment. slave_skip_errors = 1062
restart
stop it salve stop slave; open start slave;
5.3,Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.
Last_Error: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'
solve:
vim /etc/my.cnf # Change here to MIXED # binlog_format=STATEMENT binlog_format=MIXED
restart
# Restart mysql service systemcrt restart mysql