1. 創(chuàng)建遠(yuǎn)程登陸用戶

默認(rèn)的 mysql 配置僅允許本地登錄。先要修改配置 /etc/mysql/mysql.conf.d/mysql.cnf，把這一行注釋掉。

# By default we only accept connections from localhost
# bind-address   = 127.0.0.1

重啟 mysql 后，然后登錄 mysql 添加用戶。

### 查看用戶
mysql> select user, Host from mysql.user;
+------------------+---------------+
| user              | Host         |
+------------------+---------------+
| root              | 127.0.0.1    |
| root              | ::1          |
| debian-sys-maint  | localhost    |
| mysql.sys         | localhost    |
| root              | localhost    |
| root              | ubuntu-server|
+------------------+---------------+
6 rows in set (0.00 sec)

### 添加遠(yuǎn)程登錄用戶
mysql> grant replication slave on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

### 確認(rèn)添加成功
mysql> select user, Host from mysql.user;
+------------------+---------------+
| user              | Host         |
+------------------+---------------+
| root              | %            |
| root              | 127.0.0.1    |
| root              | ::1          |
| debian-sys-maint  | localhost    |
| mysql.sys         | localhost    |
| root              | localhost    |
| root              | ubuntu-server|
+------------------+---------------+
7 rows in set (0.00 sec)

### 查看所有用戶的權(quán)限
mysql> select * from mysql.user\G
...
### 略過非目標(biāo)用戶
...
*************************** 7. row ***************************
         Host: %
         User: replicate_user
     Select_priv: N
     Insert_priv: N
     Update_priv: N
     Delete_priv: N
     Create_priv: N
      Drop_priv: N
     Reload_priv: N
    Shutdown_priv: N
     Process_priv: N
      File_priv: N
      Grant_priv: N
   References_priv: N
      Index_priv: N
      Alter_priv: N
     Show_db_priv: N
      Super_priv: N
Create_tmp_table_priv: N
   Lock_tables_priv: N
     Execute_priv: N
   Repl_slave_priv: Y
   Repl_client_priv: N
   Create_view_priv: N
    Show_view_priv: N
 Create_routine_priv: N
  Alter_routine_priv: N
   Create_user_priv: N
      Event_priv: N
     Trigger_priv: N
Create_tablespace_priv: N
       ssl_type:
      ssl_cipher:
     x509_issuer:
     x509_subject:
    max_questions: 0
     max_updates: 0
   max_connections: 0
 max_user_connections: 0
        plugin: mysql_native_password
authentication_string: *7EEFBE4E8365990EC8454EE2182B97431575A5C8
   password_expired: N
password_last_changed: 2016-11-30 14:56:35
  password_lifetime: NULL
    account_locked: N
7 rows in set (0.00 sec)

### 從上面可以看到，我們剛建立的用戶只有一個 slave 權(quán)限，由于我們需要使用遠(yuǎn)程登錄，所有要再開一個權(quán)限
### 打開 supaer 權(quán)限
mysql> grant super on *.* to replicate_user@'%';

### 確認(rèn)已經(jīng)打開
mysql> select * from mysql.user\G
...
### 略過非目標(biāo)用戶行
...
*************************** 7. row ***************************
         Host: %
         User: replicate_user
     Select_priv: N
     Insert_priv: N
     Update_priv: N
     Delete_priv: N
     Create_priv: N
      Drop_priv: N
     Reload_priv: N
    Shutdown_priv: N
     Process_priv: N
      File_priv: N
      Grant_priv: N
   References_priv: N
      Index_priv: N
      Alter_priv: N
     Show_db_priv: N
      Super_priv: Y
Create_tmp_table_priv: N
   Lock_tables_priv: N
     Execute_priv: N
   Repl_slave_priv: Y
   Repl_client_priv: N
   Create_view_priv: N
    Show_view_priv: N
 Create_routine_priv: N
  Alter_routine_priv: N
   Create_user_priv: N
      Event_priv: N
     Trigger_priv: N
Create_tablespace_priv: N
       ssl_type:
      ssl_cipher:
     x509_issuer:
     x509_subject:
    max_questions: 0
     max_updates: 0
   max_connections: 0
 max_user_connections: 0
        plugin: mysql_native_password
authentication_string: *7EEFBE4E8365990EC8454EE2182B97431575A5C8
   password_expired: N
password_last_changed: 2016-11-30 14:56:35
  password_lifetime: NULL
    account_locked: N
7 rows in set (0.00 sec)

### 刷新生效
mysql>flush privileges;

打開防火墻 3306 端口。

# iptables -A FW_INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

在另一臺服務(wù)器的終端上嘗試遠(yuǎn)程登陸。

# mysql -h'172.16.2.228' -u'replicate_user' -p'123456'

2. 修改 mysql 配置文件，配置互主備份

在 /etc/mysql/mysql.conf.d/mysql.cnf 添加以下內(nèi)容：

# 作為 master 的配置
# 忽略系統(tǒng)的 DB
binlog_ignore_db   = information_schema
binlog_ignore_db   = mysql
binlog_ignore_db   = performance_schema
binlog_ignore_db   = sys
# 上傳 DB 
binlog_do_db     = database

# 作為 slave 的配置
replicate_do_db    = database
replicate_ignore_table = table

# multi-source replication
master_info_repository  = TABLE
relay_log_info_repository = TABLE

重啟 mysql。

# service mysql restart

3. 開始配置

### 停止 slave 運(yùn)行
mysql> stop slave;

### 設(shè)置 slave 通道
mysql> CHANGE MASTER TO
   MASTER_HOST='172.16.2.107',
   MASTER_USER='replicate_user',
   MASTER_PASSWORD='123456 ',
   MASTER_LOG_FILE='mysql-bin.000069',
   MASTER_LOG_POS=1902
   FOR CHANNEL 'cfmanager';

### 啟動 slave
mysql> start slave;

### 查看 slave 狀態(tài)
mysql> show slave status\G
...
      Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
...
### 只要保證以上兩項(xiàng)是 Yes 就可以了