-
MySQL master-slave notes I have been working for several years, but I still write a simple curd. This is not good. I have to prepare two servers with MySQL (that is, no matter whether you are a Linux or a window, you can only have MySQL). The IP addresses of the two servers are 192.168.123.57 and 192.168.123.58, of which 192.168.123.58 is the master database and 192.168.123.57 is the slave database, The master and slave environments are the ubuntu environment under Linux (in order to facilitate learning, this article uses the MySQL 8.0 built by Docker) to configure the main database. Find the MySQL configuration The configuration file in this article is named my.cnf. Open the configuration file, search to find [mysqld], then break a new line and add the following configuration: # xxx below is the file directory, please replace it yourself Recommended:/var/log/mysql server-id = 1 #MySQL bin will automatically generate mysql-bin.000001 log-bin = 'xxxx/mysql-bin' #Error Log log-error= 'xxxx/mysql-error'; #Database ignored by master slave synchronization binlog-ignore = mysql #Databases to be synchronized during master slave synchronization Binlog do db=test Authorize from the database [Execute the command in the mysql command line] CREATE USER 'root' @ 192.168.123.% 'IDENTIFIED BY' mytest '; GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.123.%'; Refresh the authorization to make it effective - optional [Execute the command in the mysql command line] FLUSH PRIVILEGES; Restart the database service # It is recommended to restart the MySQL service online. In order to make the format look good, I naturally wrote this. In fact, I am a docker, so there is no such startup method. I restart the container directly Mysql.server restart Check whether the master configuration is successful [Execute the command in the mysql command line] show master status G; View the information from the database [Execute the command from the mysql command line] show master hosts G; Configure the configuration from the database to the database. File name: mysql. cnf # In the following, xxx is the file directory. Please replace it yourself Recommended:/var/log/mysql server-id = 1 #MySQL bin will automatically generate mysql-bin.000001 log-bin = 'xxxx/mysql-bin' #Error Log log-error= 'xxxx/mysql-error'; Databases to be synchronized Replicate do db=test From the database, you also need to authorize [execute the command in the mysql command line] change master to master_host='192.168.123.58', master_user='root', master_password='mytest', master_log_file='mybin-log.000001' , master_log_pos=323; #The above is a complete one line command: master_host is the address of the primary database, master_user is the account of the primary database, master_password is the password of the primary database, master_log_file is the name of the binary file of the primary database, and master_log_pos is the line number in the log file of the primary database, You can obtain the corresponding line number through<code>show master status G</code>. The slave database also needs to restart the database service, which is consistent with the master server. Start the synchronization process on the slave server [execute the command in the mysql command line] start slave; View the synchronization status [Execute the command in the MySQL command line] show slave status; #The following results will be obtained - for reference only mysql> show slave status \G; *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.123.58 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mybin-log.000001 Read_Master_Log_Pos: 632 Relay_Log_File: 044ebe077274-relay-bin.000004 Relay_Log_Pos: 633 Relay_Master_Log_File: mybin-log.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test 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: 632 Relay_Log_Space: 849 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: 1 Master_UUID: db775ba0-16f4-11ec-9463-0242ac150002 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec) Note here: check Slave_IO_Running and Slave_SQL_Running. You must make both values yes to be successful. If not yes. You can view the error displayed on Last_io_Errno, and then modify it according to the error to test the write operation in the master database to see whether the slave database has the same data. As shown below: Hidden content, please go to the inside page to check the details
