Create a MySQL slave Database

Create a MySQL slave Database

We know that Oracle has DataGuard to back up data in real time, which can be used for master-slave switchover. MySQL also has its own backup database solution, which is called master-slave replication.

The MySQL slave database is set up to synchronize data in the master database in real time. It can also share the read Pressure on the master database and form a read/write splitting structure for the database.

Note:

1. The server-id of the master database and slave database must not be the same.

2. Create a replication slave account in the master database.

Grant replication slave on *. * to 'repl' @ '192. 168.0.20.' identified 'oracle ';

3. View master Status of the master database

Mysql> show master status/G

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

File: mysql-bin.000005

Position: 251651

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

4. Configure slave Database

Change master

-> Master_host = '1970. 168.0.20 ',

-> Master_user = 'repl ',

-> Master_password = 'oracle ',

-> Master_log_file = 'mysql-bin.000005 ',

-> Master_log_pos = 251651;

5. Start slave Database

Slave start

Show slave status/G

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

Slave_IO_State: Waiting for master to send event

Master_Host :***********

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 463725968

Relay_Log_File: mysql-relay-bin.000006

Relay_Log_Pos: 463726114

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_ SQL _Running: 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: 463725968

Relay_Log_Space: 873569451

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: 100

Note:

If the slave database Slave_IO_Running: No/Slave_ SQL _Running: No

Disable slave

Set GLOBALSQL_SLAVE_SKIP_COUNTER = 1;

When slave is enabled