MySQL Master/Slave Database Configuration Guide and error handling

MySQL Master/Slave Database Configuration Guide and error handling

MySQL master-slave replication Configuration

1. Requirements

1.1 System Platform consistency

1.2 consistent database versions

2. Modify the my. cnf file. The master server and slave server must not have the same server-id.

3. Start databases on both sides

4. Create an account on the master server and authorize the slave to be accessible from the server.

Grant replication slave on *. * to 'mysql _ sync' @ '10. 10.88.101 'identified by '123 ';

5. view the master server status

Mysql> show master status;

+ --------------- + ---------- + -------------- + -------------------- + ----------------- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+ --------------- + ---------- + -------------- + -------------------- + ----------------- +

| Dbtest.000005 | 414 |

+ --------------- + ---------- + -------------- + -------------------- + ----------------- +

1 row in set (0.15 sec)

Note: Do not operate MYSQL on the master server after this step is completed to prevent the status of the master server from changing.

6. Configure the Slave server Slave

Mysql> change master to master_host = '10. 10.88.100 ', master_user = 'mysql _ sync', master_password = '000000', master_log_file = 'dbtest. 100', master_log_pos = 123456;

Mysql> start slave; // start the slave Server replication function

 

Mysql> change master to master_host = '10. 10.88.100 ', master_user = 'mysql _ sync', master_password = '000000', master_log_file = 'dbtest. 100', master_log_pos = 123456;

Query OK, 0 rows affected, 2 warnings (0.12 sec)

 

Mysql> start slave;

Query OK, 0 rows affected (0.24 sec)

 

7. Check the slave server copy function status (perform a check on the slave server ):

Mysql> show slave status \ G

 

 

 

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.88.100 -- master server IP Address

Master_User: mysql_sync -- master server database account name

Master_Port: 3306 -- database Port

Connect_Retry: 60

Master_Log_File: dbtest.000005

Read_Master_Log_Pos: 414

Relay_Log_File: DBTEST2-relay-bin.000002

Relay_Log_Pos: 280

Relay_Master_Log_File: dbtest.000005

Slave_IO_Running: Yes -- Status, which must be Yes

Slave_ SQL _Running: Yes -- Status, which must be 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: 414

Relay_Log_Space: 455

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: 73b077c6-626d-11e5-8f5b-000c29e840c1

Master_Info_File:/app/mysqldata/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.00 sec)

 

 

 

 

During the configuration process, because all the data files from the master database cp are used as the initial environment of the slave database, the following error is returned:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

After analysis, it is because all files in the master database cp are consistent with server_uuid, so the solution is as follows:

1. Enter the data directory

Cd/app/mysqldata

2. Back up auto. cnf so that the database can automatically generate a new uuid.

Cp auto. cnf auto. cnf. bak

Rm-rf auto. cnf

3. Restart the master database and slave Database

View the status of the master database, reset the slave database status, and start slave.

This article permanently updates the link address: