1. 創(chuàng)建遠程登陸用戶
默認的 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)
### 添加遠程登錄用戶
mysql> grant replication slave on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
### 確認添加成功
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)
### 查看所有用戶的權限
mysql> select * from mysql.user\G
...
### 略過非目標用戶
...
*************************** 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 權限列另，由于我們需要使用遠程登錄，所有要再開一個權限
### 打開 supaer 權限
mysql> grant super on *.* to replicate_user@'%';
### 確認已經打開
mysql> select * from mysql.user\G
...
### 略過非目標用戶行
...
*************************** 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
在另一臺服務器的終端上嘗試遠程登陸页衙。
# mysql -h'172.16.2.228' -u'replicate_user' -p'123456'
2. 修改 mysql 配置文件，配置互主備份
在 /etc/mysql/mysql.conf.d/mysql.cnf 添加以下內容：
# 作為 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 運行
mysql> stop slave;
### 設置 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
...
### 只要保證以上兩項是 Yes 就可以了
