默认的 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 'replicate_user'@'%' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.01 sec) ### 此处添加的用户 replicate_user 是允许任何IP 远程登陆 表示为 '%' ### 如果没有该需求 可以在创建的时候 指定允许某一个IP登陆 'user'@'IP' ### 确认添加成功 mysql> select user, Host from mysql.user; +------------------+---------------+ | user | Host | +------------------+---------------+ | replicate_user | % | | 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'在 /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