MySQL master-slave notes

I have been working for several years, but I still write a simple curd. This is no good. I need to do some work on mysql master-slave

get ready

Two servers with mysql (meaning that no matter you are a Linux or a window, as long as you have mysql)

The IP addresses of the two servers are 192.168.123.57 and 192.168.123.58

among 192.168.123.58 Primary Database

192.168.123.57 From database for

In this paper, the master and slave environments are linux Lower ubuntu Environment (To facilitate learning, this article uses the Docker to build MySQL 8.0)

text

1. Configure Master Database 。

   • Find the MySQL configuration The configuration file in this article is named my.cnf

   • Open the configuration file and search for it [mysqld] , then wrap the 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 the slave 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 authorization to make it effective - optional [Execute the command in the mysql command line]

   FLUSH PRIVILEGES;

• Restart the database service

   #It is recommended on the Internet to restart the MySQL service. 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 is configured successfully [Execute the command in the mysql command line]

   show master status \G;

• View information from the database [Execute the command in the mysql command line]

   show master hosts \G;

2. Configure Slave Database

   • From the database configuration, the file name is mysql.cnf

    #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'; Databases to be synchronized replicate-do-db=test

   • Authorization is also required from the database [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 command line. The master_host is the address of the primary database, the master_user is the account of the primary database, the master_password is the password of the primary database, the master_log_file is the name of the binary file of the primary database, and the master_log_pos is the line number in the log file of the primary database. The corresponding line number can be obtained through<code>show master status  G</code>

   • The secondary database also needs to restart the database service, which is consistent with the primary server

   • Start the synchronization process on the slave server [Execute the command in the mysql command line]

      start slave;

   • View 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 errors displayed on Last_io_Errno, and then modify them according to the errors

3. Perform a write operation test on the primary database to check whether the slave database has the same data.
   As follows: