Shulou(Shulou.com)06/01 Report--

Server 192.168.1.2 (master), server 192.168.1.3 (slave) to configure master to use replication based on binary log file location, binary logging must be enabled and a unique server-id must be established. To configure binary logging and server ID options, shut down the MySQL server and edit the my.cnf or my.ini file. In the [mysqld] section of the configuration file, add log-bin and server-id options. If these options already exist but are commented out, uncomment and modify them according to your needs. On master, open the corresponding my.cnf

Restart mysql, enter mysql, and check whether the corresponding server_id is set successfully mysql > show variables like 'server_id' +-+-+ | Variable_name | Value | +-+-+ | server_id | 1 | +-+-+ 1 row in set (0.00 sec) indicates that the setting is successful on master The user used to create the replication (each slave connects to the master using the MySQL username and password, so master must have a user account that slave can use to connect. Any account can be used for this operation, as long as it is granted the REPLICATION SLAVE privilege. Enter mysql and execute the following command mysql > CREATE USER 'lisi'@'%' IDENTIFIED BY' Lisi@123456';Query OK, 0 rows affected (0. 00 sec) mysql > GRANT REPLICATION SLAVE ON. TO 'lisi'@'%';Query OK, 0 rows affected (0 sec) then refresh all table and block write statements mysql > FLUSH TABLES WITH READ LOCK;Query OK, 0 rows affected (0 sec) through FLUSH, and then determine the current binary log file name and location: mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000005 | 802858 | +- -+ 1 row in set (0.00 sec) on slave Set server_id, then restart mysql of slave on slave, set master configuration mysql > CHANGE MASTER TO MASTER_HOST='192.168.1.2', MASTER_USER='lisi', MASTER_PASSWORD='Lisi@123456', MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=802858 on slave Parameter description MASTER_HOST master ip address MASTER_USER master replication user name MASTER_PASSWORD master copy user password MASTER_LOG_FILE master binary file MASTER_LOG_POS master binary location on slave, start replication mysql > start slave;Query OK, 0 rows affected (0.00 sec) check slave status mysql > show slave status\ G you can see that these two are Yes, which means mysql > UNLOCK TABLES on master Query OK, 0 rows affected (0 sec) the following is to randomly manipulate the data in the database in mysql to see if it is synchronized. If synchronization means there is no problem, please check the mysql error log of slave in time.

Note:

You need to verify that the replication account of master can log in remotely to each server in the replication group. You must configure a unique server ID.

The hole in this operation is:

Q: Can't connect to MySQL server on 'server' on the current host mysql-uUserName-p-h localhost can be connected to mysql-uUserName-p-h' ip address' not connected A: first check according to the method introduced by https://dev.mysql.com/doc/refman/8.0/en/can-not-connect-to-server.html, and finally found that the connection is not available, if it is Ariyun's server Then it is possible that the firewall has turned off port 3306d, so open the port at the firewall or enter the https://help.aliyun.com/document_detail/25471.html?spm=a2c4e.11153987.0.0.2de65a75vopCz7 to set Q: the ip address of mysql-uUserName-p-h 'master on the slave machine can be connected, but the show slave status is shown below

* A: viewing the error log 2026 is a ssl error, which means that when we start a ssl connection, we need to add the following line to the my.conf file on master and slave, and then restart mysql. If you see the following result in mysql, it means that ssl is shut down. If slave still cannot connect to master at this time, let's take a look at the show slave status message. If Master_SSL_Allowed is Yes, it should be changed to No, and the specific modification can be changed in the change master statement. The example of our modification this time is

CHANGE MASTER TO MASTER_HOST='192.168.1.2', MASTER_USER='lisi', MASTER_PASSWORD='Lisi@123456', MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=802858,master_ssl=0; is mainly master_ssl=0, and then execute start slave.

Reference: https://dev.mysql.com/doc/refman/5.7/en/binlog-replication-configuration-overview.html

The replication principle of PREV:1:MySQL https://blog.51cto.com/itzhoujun/2352587

NEXT:3: add a slave to the existing replication environment https://blog.51cto.com/itzhoujun/2351567

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.