mysql主從同步及清除資訊

標籤：

主:reset master;

從:reset slave all;

mysql主從配置：

1.MySQL主設定檔增加如下：
default-storage-engine = innodb
innodb_flush_log_at_trx_commit = 2
sort_buffer_size = 2M

server-id = 1
log-bin=/data1/mysql3336/mysql3336-bin
binlog-do-db=tc_selflot
binlog-ignore-db=mysql

2.MySQL從設定檔增加如下：
default-storage-engine = innodb
innodb_flush_log_at_trx_commit = 2
sort_buffer_size = 2M

server-id = 2
log-bin=/data1/mysql3336/mysql3336-bin
binlog-do-db=tc_selflot
binlog-ignore-db=mysql

3.重啟主從mysql服務

4.進入MySQL主要資料庫授權同步帳號；
use mysql;
grant file,select,replication slave on *.* to [email protected]‘192.168.188.%‘ identified by ‘111111‘;
flush privileges;

5.查看主庫狀態並記錄File 及Position值，以便配置從庫時用到
show master status;

6.進入從庫設定slave資訊；
mysql> change master to
-> master_host=‘192.168.188.11‘,
-> master_user=‘slave‘,
-> master_password=‘111111‘,
-> master_port=3336,
-> master_log_file=‘mysql3336-bin.000001‘,
-> master_log_pos=120;

7.啟動salve開始同步
mysql> start slave;

8.查看從庫狀態
mysql> show slave status \G

Slave_IO_Running: Yes 代表成功串連到master並且下載日誌
Slave_SQL_Running: Yes 代表成功執行日誌中的SQL語句

 

mysql主從同步及清除資訊