mysql master slave 主從同步

下面cli中 $ 為普通許可權，# 為root許可權。

模式1

Master：SerA IP:10.0.2.81
Slave：SerB IP:10.0.2.82

A: my.cnf

server-id=1
#master
log-bin=mysql-bin
binlog-do-db=dbname       # 需要備份的資料庫名, 多個則重複設定 

A: mysql cmd

mysql> GRANT all privileges ON *.* TO 'backup'@'10.0.2.82' IDENTIFIED BY '123456'
mysql> flush privileges; 

匯出 dbname 資料庫

$ mysqldump -uroot -p dbname > dbname.sql

重啟mysql伺服器

# service mysql restart 

A: mysql cmd

mysql> show grants;          -- 檢視許可權生效 -- 
mysql> show master status\G; -- 錄下 FILE & Position 的值 -- 

記下 file & Position 的值，在配置 slave時會用到

許可權(需要時執行)

mysql> show grants;  -- 檢視許可權 --
mysql> GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO 'backup'@'10.0.2.82' IDENTIFIED BY '123456';
mysql> flush privileges; 

B: my.cnf

server-id=2
#master 
log-bin=mysql-bin
#slave
replicate-do-db=dbname  # 需要備份的資料庫名, 多個則重複設定
master-connect-retry=60 # 伺服器拓機重連 預設60s
slave-net-timeout=60    # 60s同步

B: mysql cmd

-- 新增 master 伺服器 --
mysql> stop slave; 
mysql> change master to 
    master_host='10.0.2.81', 
    master_port=3306, 
    master_user='backup', 
    master_password='123456'
    master_log_file='mysql-bin.000002',
    master_log_pos=98;
-- 其中 file & pos 到 A 的 master中找 --
mysql> start slave;
mysql> show slave status\G;
    -- Slave_IO_Running和Slave_SQL_Running的狀態都必須是YES --
mysql> show master stauts\G;

-- 匯入資料 --
mysql> stop slave;
mysql> create database `dbname`;
mysql> use dbname;
mysql> source /pwd/dbname.sql;
mysql> start slave;

如果需要則重啟mysql伺服器

# service mysql restart 

模式2

A: my.cnf

default-character-set=utf8
server-id = 1
# master
log-bin=mysql-bin 
binlog-do-db=dbname           # 需要備份的資料庫名, 多個則重複設定
binlog-ignore-db=dbname               # 不需要備份的資料庫

B: my.cnf

server-id =2                    # 不可重複
# master
log-bin=mysql-bin
relay-log=relay-bin
relay-log-index=relay-bin-index
# slave
master-host ='ip'
master-user =root
master-password =passwd
master-port =3306
master-connect-retry=30         # s
slave-net-timeout=60            # 60s同步
replicate-do-db=dbname          # 需要備份的資料庫名, 多個則重複設定

其餘參考模式1

password 重置

$ mysqladmin -uroot -p oldpassword newpasswd
$ mysql -uroot -p user 
mysql> UPDATE user SET password=PASSWORD(”new password”) WHERE user=’root’; 

區域網訪問 許可權

mysql> grant all privileges on *.* to root@"ip" identified by 'password' with grant option;
sql> flush privileges;