我們知道Oracle有DataGuard實(shí)時(shí)備份數(shù)據(jù)，可以做主備切換，而MySQL也有自己的一套備庫(kù)方案，稱之為主從復(fù)制。

搭建MySQL從庫(kù)是為了實(shí)時(shí)同步主庫(kù)數(shù)據(jù)，同時(shí)也可以分擔(dān)主庫(kù)的讀壓力，對(duì)數(shù)據(jù)庫(kù)端做成讀寫分離結(jié)構(gòu)。

搭建MySQL主從庫(kù)注意點(diǎn)：

1.主庫(kù)和從庫(kù)的 server-id 一定不能相同。

2.在主庫(kù)創(chuàng)建replication slave賬戶。

grant replication slave on *.* to 'repl'@'192.168.0.232' identified 'oracle';

3.查看主庫(kù)master狀態(tài)

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.配置從庫(kù)

change master to

-> master_host='192.168.0.232',

-> master_user='repl',

-> master_password='oracle',

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

-> master_log_pos=251651;

5. 啟動(dòng)從庫(kù)

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

注意：

如果從庫(kù)Slave_IO_Running: No/ Slave_SQL_Running: No

關(guān)閉slave

設(shè)置set GLOBALSQL_SLAVE_SKIP_COUNTER=1;

在開啟slave

更多信息請(qǐng)查看IT技術(shù)專欄