MySQL 数据库同步配置

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 '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: NCreate_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: NCreate_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_passwordauthentication_string: *7EEFBE4E8365990EC8454EE2182B97431575A5C8   password_expired: Npassword_last_changed: 2016-11-30 14:56:35  password_lifetime: NULL    account_locked: N7 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: YCreate_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: NCreate_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_passwordauthentication_string: *7EEFBE4E8365990EC8454EE2182B97431575A5C8   password_expired: Npassword_last_changed: 2016-11-30 14:56:35  password_lifetime: NULL    account_locked: N7 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 的配置# 忽略系统的 DBbinlog_ignore_db   = information_schemabinlog_ignore_db   = mysqlbinlog_ignore_db   = performance_schemabinlog_ignore_db   = sys# 上传 DB binlog_do_db     = database# 作为 slave 的配置replicate_do_db    = databasereplicate_ignore_table = table# multi-source replicationmaster_info_repository  = TABLErelay_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';### 启动 slavemysql> start slave;### 查看 slave 状态mysql> show slave status\G...      Slave_IO_Running: Yes      Slave_SQL_Running: Yes...### 只要保证以上两项是 Yes 就可以了