1. master mysql 中修改/etc/my.cnf 在mysqld下添加如下兩行:
log-bin=/var/lib/mysql/mysql-bin.log
server-id=1
#binlog-do-db=DB1
#binlog-do-db=DB2 #如果備份多個數據庫，重復設置這個選項即可
2.重啟mysql,添加slave replication 用戶
GRANT REPLICATION SLAVE ON *.* TO slave@192.168.100.10 IDENTIFIED BY slave_password;
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 890 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
unlock tables;
記下 file名和binlog的位置.
4.在slave mysql上操作.
編輯從服務器的配置文件：/etc/my.cnf
[mysqld]
#sync data
server-id=2 #註意不能重復
master-host=192.168.100.10
master-user=slave
master-password=slave_password
master-port=3306
master-connect-retry=60
#replicate-do-db=DB1
#replicate-do-db=DB2
5.重啟slave mysql .
stop slave;
CHANGE MASTER TO MASTER_HOST=192.168.100.13, MASTER_USER=slave, MASTER_PASSWORD=slave_password, MASTER_LOG_FILE=mysql-bin.000001, MASTER_LOG_POS=890;
start slave;
5.在master mysql上創建數據庫測試,從庫是否同步.或者show slave statusG 查看
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
兩個yes為同步.
補充：
在從服務器上使用show slave statusG
Slave_IO_Running,為No,
則說明IO_THREAD沒有啟動，請執行start slave io_thread
Slave_SQL_Running為No
則復制出錯,查看Last_error字段排除錯誤後執行start slave sql_thread
查看Slave_IO_State字段空 //復制沒有啟動
Connecting to master//沒有連接上master
Waiting for master to send event//已經連上
主服務器上的相關命令：
show master status
show slave hosts
show logs
show binlog events
purge logs to log_name
purge logs before date
reset master(老版本flush master)
set sql_log_bin=
從服務器上的相關命令:
slave start
slave stop
SLAVE STOP IO_THREAD //此線程把master段的日志寫到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此線程把寫到本地的日志應用於數據庫
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //動態改變master信息
PURGE MASTER [before date] 刪除master端已同步過的日志