MySQL數據同步配置

MySQL數據同步配置
----------------------
要求MySQL版本>=3.23.54
<...>指代變量

1、Master端配置:

Slave同步賬號權限:
GRANT FILE ON . TO @ IDENTIFIED BY '';
或者
INSERT INTO user VALUES('','',passWord(''),'N','N','N','N','N','N','N','N','N','Y','N','N','N','N');
FLUSH PRIVILEGES;

Master啟動配置文件:
[MySQLd]
log-bin
binlog-do-db=
server-id=

或者:
[MySQLd]
log-bin
replicate-wild-do-table=.
replicate-wild-ignore-table=.
server-id=

2、Slave端配置

Slave啟動配置文件:
[MySQLd]
master-host=
master-user=
master-passWord=
master-port=
server-id=

3、停止服務

MySQLadmin -S -u root -p shutdown
MySQLadmin -S -u root -p shutdown

4、拷貝數據

5、啟動Master服務

/safe_MySQLd --defaults-file= <其他選項> &
Master數據庫目錄應該生成 -bin.<當前序號> 文件

6、啟動Slave服務

/master.info配置文件:
-bin.<當前序號,例如1>
<開始位置,初始必須為73>




<出錯重試間隔(s),例如60>

/safe_MySQLd --defaults-file= <其他選項> &

7、測試/監視

Master數據庫中執行
SHOW MASTER STATUS;
應顯示
+---------------------------+-----------+--------------+------------------+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+---------------------------+-----------+--------------+------------------+
|-bin.<當前序號>| || |
+---------------------------+-----------+--------------+------------------+

Slave數據庫中執行
SHOW SLAVE STATUS;
應顯示
+---------------+-------------+-------------+---------------+---------------------------+-----------+---------------+-----------------+---------------------+------------+------------+--------------+
| Master_Host | Master_User |