I. Background Introduction
- On the morning of September 25, 2017, when I went to work, I found that the company database was kidnapped by hackers and wanted ransom. We use MySql 5.6.37 on the sky wing cloud. Call the customer service and suggest to reinstall the system and MySql. Simply install master-slave synchronization.
- Installation environment: Linux version 3.10.0-514.el7.x86_64 (builder@kbuilder.dev.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-11) (GCC) #1 SMP Tue Nov 22:16:42:41 UTC 2016; Mysql 5.6.37
- Description: [192.168.1.203 (main library) 192.168.1.202 (slave library)] Placed on two servers respectively
- The environment is installed. Look down. Thank you
II. Rough steps
-
[main library]
-
Modify the MySql configuration file and add some configuration (I have set up master-slave synchronization of two databases here)
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES log-bin=mysql-bin # To identify a unique database, you need to set other values when setting slave Libraries server-id=1 # A database that is ignored when representing synchronization binlog-ignore-db=information_schema binlog-ignore-db=cluster binlog-ignore-db=mysql # Specify databases that need to be synchronized binlog-do-db=mydb binlog-do-db=mydb_store -
[Enter the main library, execute sql] Take this as an example, create a user named master slave, password 123, from 192.168.1.202 (that is, slave library), allow the user to read logs on the main library, give File permission, only give File permission is not feasible, but also give it REPLICATION SLAVE permission.
GRANT FILE ON *.* TO 'masterslave'@'192.168.1.202' IDENTIFIED BY '123'; GRANT REPLICATION SLAVE ON *.* TO 'masterslave'@'192.168.1.202' IDENTIFIED BY '123'; FLUSH PRIVILEGES; -
Restart mysql to query the main database information
service mysql restart; show master status;The results of the query are as follows (remember the values of File and Position):
mysql> show master status ; +------------------+----------+-----------------+----------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+-----------------+----------------------------------+-------------------+ | mysql-bin.000027 | 896 | mydb,mydb_store | information_schema,cluster,mysql | | +------------------+----------+-----------------+----------------------------------+-------------------+ 1 row in set (0.00 sec)
-
-
[from library]
-
Modify MySql configuration file to add some configuration
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES log-bin=mysql-bin server-id=2 # To identify a unique database, you need to set other values when setting slave Libraries binlog-ignore-db=information_schema # A database that is ignored when representing synchronization binlog-ignore-db=cluster # A database that is ignored when representing synchronization binlog-ignore-db=mysql # A database that is ignored when representing synchronization replicate-ignore-db=mysql replicate-do-db=mydb # Specify databases that need to be synchronized replicate-do-db=mydb_store # Specify databases that need to be synchronized log-slave-updates slave-skip-errors=all slave-net-timeout=60 -
Restart MySql
service mysql restart;
-
Enter mysql. Close slave, set parameters, and open slave
stop slave; # [master_log_file is the File written above and master_log_pos is the position written above] change master to master_host='192.168.1.203',master_user='masterslave',master_password='123',master_log_file='mysql-bin.000027', master_log_pos=896; show slave status \G; -
If nothing unexpected happens, you will see the following information:
mysql> show slave status \G; Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.203 Master_User: masterslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000027 Read_Master_Log_Pos: 896 Relay_Log_File: ecs-749d-0005-relay-bin.000002 Relay_Log_Pos: 1059 Relay_Master_Log_File: mysql-bin.000027 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: mydb,mydb_store Replicate_Ignore_DB: mysql 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: 896 Relay_Log_Space: 1240 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: 6f9f3a92-a1ce-11e7-b74d-fa163eca40a4 Master_Info_File: /usr/local/mysql/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 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 1 row in set (0.01 sec) ERROR: No query specified mysql> -
If both lines are YES, it's a success.
Slave_IO_Running: Yes Slave_SQL_Running: Yes If this is not the case for both lines, then there is a problem with the configuration of a link, as is often the case.
Slave_IO_Running: No
perhaps
Slave_IO_Running: Connecting
See the fields in slave status above [Last_IO_Errno and Last_IO_Error]
-