1. 创建远程登陆用户
默认的 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 的配置
# 忽略系统的 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 状态
mysql> show slave status\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
### 只要保证以上两项是 Yes 就可以了
