mysql native数据同步_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 '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 就可以了