美国vps服务器的MySQL主主数据同步

已关闭留言

美国vps服务器的MySQL主主数据同步

环境

操作系统版本:CentOS7 64

MySQL版本:mysql5.6.33

节点1IP192.168.1.205 主机名:edu-mysql-01

节点2IP192.168.1.206 主机名:edu-mysql-02

MySQL 主从复制官方文档:http://dev.mysql.com/doc/refman/5.6/en/replication.html

注意:

1> 主从服务器操作系统版本和位数要保持一致

2> MasterSlave数据库的版本要一致

3> MasterSlave数据库中的数据要一致

配置

配置之前先参考《MySQL5.7安装与配置(YUM)》安装好MySQL(注意本文演示的是5.6版本,需要修改文章中的yum源为5.6

1、安全配置

1> 防火墙

添加mysql通信端口(默认为3306

shell> vim /etc/sysconfig/iptables

-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT

shell> service iptables restart

shell> vim /etc/sysconfig/iptables

-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT

shell> service iptables restart

或关闭防火墙

shell> service iptables stop

1shell> service iptables stop

2> 关闭selinux

shell> vi /etc/selinux/config

SELINUX=disabled

shell> vi /etc/selinux/config

SELINUX=disabled

SELINUX的值修改为disabled

2. 节点1配置(192.168.1.205

2.1 添加数据同步配置

shell> vim /etc/my.cnf

1shell> vim /etc/my.cnf

[mysqld]中增加以下配置项:

# 服务器的ID,必须唯一,一般设置自己的IP

server_id=205

# 复制过滤:不需要备份的数据库(MySQL库一般不同步)

binlog-ignore-db=mysql

# 开启二进制日志功能,名字可以随便取,最好有含义(比如项目名)

log-bin=edu-mysql-bin

# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存

binlog_cache_size=1M

# 主从复制的格式(mixed,statement,row,默认格式是 statement)

binlog_format=mixed

# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。

expire_logs_days=7

## 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。

## :1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致

slave_skip_errors=1062

# 作为从服务器时的中继日志

relay_log=edu-mysql-relay-bin

# log_slave_updates 表示 slave 将复制事件写进自己的二进制日志

log_slave_updates=1

# 主键自增规则,避免主从同步ID重复的问题

auto_increment_increment=2 # 自增因子(每次加2

auto_increment_offset=1 # 自增偏移(从1开始),单数

2.2 Master配置

# 先重启一下服务

shell> service mysqld restart

# 登录到mysql

shell> mysql -uroot -p

# 创建数据库同步用户,并授予相应的权限

mysql> grant replication slave, replication client on *.* to ‘repl’@’192.168.1.206’ identified by ‘root123456’;

# 刷新授权表信息

mysql> flush privileges;

# 查看binlog文件的position(偏移)和File(日志文件)的值,从机上需要用到

mysql> show master status;

+———————-+———-+————–+——————+——————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+———————-+———-+————–+——————+——————-+

| edu-mysql-bin.000001 | 120 | | mysql | |

+———————-+———-+————–+——————+——————-+

1 row in set (0.00 sec)

2.3 Slave配置

# master_usermaster_password:在206上执行grant replication slave…创建的用户和密码

# master_log_filemaster_log_pos:在206上运行show master status;命令执行结果对应FilePosition字段的值

mysql> change master to master_host=’192.168.1.206′,master_user=’repl’, master_password=’root123456′, master_port=3306, master_log_file=’edu-mysql-bin.000001′, master_log_pos=439, master_connect_retry=30;

# 查看作为从节点的状态信息

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.1.206

Master_User: repl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: edu-mysql-bin.000001

Read_Master_Log_Pos: 439

Relay_Log_File: edu-mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: edu-mysql-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: No

# 省略其它配置。。。

由于此时从节点还没有启动,Slave_IO_State的值为空,Slave_IO_RunningSlave_SQL_Running线程为No表示也没有运行。

2.4 启动Slave

注意:要在节点2上创建同步帐户后再启动,否则会报连不上master错误

# 启动从节点,开始工作接收主节点发送事件(数据库数据变更的所有事件)

mysql> start slave;

# 此时再查看slave节点的状态

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.206

Master_User: repl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: edu-mysql-bin.000001

Read_Master_Log_Pos: 439

Relay_Log_File: edu-mysql-relay-bin.000002

Relay_Log_Pos: 287

Relay_Master_Log_File: edu-mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

# …省略其它配置

3. 节点2配置(192.168.1.206

3.1 添加数据同步配置

shell> vim /etc/my.cnf

1

[mysqld]中增加以下配置项:

server_id=206

binlog-ignore-db=mysql

log-bin=edu-mysql-bin

binlog_cache_size=1M

binlog_format=mixed

expire_logs_days=7

slave_skip_errors=1062

relay_log=edu-mysql-relay-bin

log_slave_updates=1

#ID自增从2开始,双数

auto_increment_increment=2

auto_increment_offset=2

3.2 Master配置

# 先重启一下服务

shell> service mysqld restart

# 登录到mysql

shell> mysql -uroot -p

# 创建数据库同步用户,并授予相应的权限(只允许repl用户从192.168.1.205上登录)

mysql> grant replication slave, replication client on *.* to ‘repl’@’192.168.1.205’ identified by ‘root123456’;

# 刷新授权表信息

mysql> flush privileges;

# 查看binlog文件的position(偏移)和File(日志文件)的值,从机上需要用到

mysql> show master status;

+———————-+———-+————–+——————+——————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+———————-+———-+————–+——————+——————-+

| edu-mysql-bin.000001 | 439 | | mysql | |

+———————-+———-+————–+——————+——————-+

1 row in set (0.00 sec)

时可以启动节点1(205)slave服务

3.3 Slave配置

# master_log_filemaster_log_pos205节点上执行show master status;对应Fileposition的值

mysql> change master to master_host=’192.168.1.205′,master_user=’repl’, master_password=’root123456′, master_port=3306, master_log_file=’edu-mysql-bin.000001′, master_log_pos=120, master_connect_retry=30;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.1.205

Master_User: repl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: edu-mysql-bin.000001

Read_Master_Log_Pos: 120

Relay_Log_File: edu-mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: edu-mysql-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

#…省略其它配置

3.4、启动Slave

shell> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.205

Master_User: repl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: edu-mysql-bin.000001

Read_Master_Log_Pos: 439

Relay_Log_File: edu-mysql-relay-bin.000002

Relay_Log_Pos: 287

Relay_Master_Log_File: edu-mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

省略其它配置

4、验证

# 登录205创建一个数据库

shell> mysql -u root -p

mysql> create database if not exists mydb default character set utf8 collate utf8_general_ci;

mysql> create table user (id int, username varchar(30), password varchar(30));

mysql> insert into user values (1, ‘yangxin’, ‘123456’);

# 下面是在206节点上的操作

#1、登录206查询所有库,是否包含mydb数据库

#2、切换到mydb库,是否包含user表,并有一条数据

#3、在206mydb.user表插入一条数据,查看205是否同步过去

mysql> insert into user values (2,’yangxin2′,’123456′)

环境

操作系统版本:CentOS7 64

MySQL版本:mysql5.6.33

节点1IP192.168.1.205 主机名:edu-mysql-01

节点2IP192.168.1.206 主机名:edu-mysql-02

MySQL 主从复制官方文档:http://dev.mysql.com/doc/refman/5.6/en/replication.html

注意:

1> 主从服务器操作系统版本和位数要保持一致

2> MasterSlave数据库的版本要一致

3> MasterSlave数据库中的数据要一致

配置

配置之前先参考《MySQL5.7安装与配置(YUM)》安装好MySQL(注意本文演示的是5.6版本,需要修改文章中的yum源为5.6

1、安全配置

1> 防火墙

添加mysql通信端口(默认为3306

shell> vim /etc/sysconfig/iptables

-A INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT

shell> service iptables restart

或关闭防火墙

shell> service iptables stop

1

2> 关闭selinux

shell> vi /etc/selinux/config

SELINUX=disabled

1

2

SELINUX的值修改为disabled

2. 节点1配置(192.168.1.205

2.1 添加数据同步配置

shell> vim /etc/my.cnf

1

[mysqld]中增加以下配置项:

# 服务器的ID,必须唯一,一般设置自己的IP

server_id=205

# 复制过滤:不需要备份的数据库(MySQL库一般不同步)

binlog-ignore-db=mysql

# 开启二进制日志功能,名字可以随便取,最好有含义(比如项目名)

log-bin=edu-mysql-bin

# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存

binlog_cache_size=1M

# 主从复制的格式(mixed,statement,row,默认格式是 statement)

binlog_format=mixed

# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。

expire_logs_days=7

## 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。

## :1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致

slave_skip_errors=1062

# 作为从服务器时的中继日志

relay_log=edu-mysql-relay-bin

# log_slave_updates 表示 slave 将复制事件写进自己的二进制日志

log_slave_updates=1

# 主键自增规则,避免主从同步ID重复的问题

auto_increment_increment=2 # 自增因子(每次加2

auto_increment_offset=1 # 自增偏移(从1开始),单数

2.2 Master配置

# 先重启一下服务

shell> service mysqld restart

# 登录到mysql

shell> mysql -uroot -p

# 创建数据库同步用户,并授予相应的权限

mysql> grant replication slave, replication client on *.* to ‘repl’@’192.168.1.206’ identified by ‘root123456’;

# 刷新授权表信息

mysql> flush privileges;

# 查看binlog文件的position(偏移)和File(日志文件)的值,从机上需要用到

mysql> show master status;

+———————-+———-+————–+——————+——————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+———————-+———-+————–+——————+——————-+

| edu-mysql-bin.000001 | 120 | | mysql | |

+———————-+———-+————–+——————+——————-+

1 row in set (0.00 sec)

2.3 Slave配置

# master_usermaster_password:在206上执行grant replication slave…创建的用户和密码

# master_log_filemaster_log_pos:在206上运行show master status;命令执行结果对应FilePosition字段的值

mysql> change master to master_host=’192.168.1.206′,master_user=’repl’, master_password=’root123456′, master_port=3306, master_log_file=’edu-mysql-bin.000001′, master_log_pos=439, master_connect_retry=30;

# 查看作为从节点的状态信息

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.1.206

Master_User: repl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: edu-mysql-bin.000001

Read_Master_Log_Pos: 439

Relay_Log_File: edu-mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: edu-mysql-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: No

# 省略其它配置。。。

由于此时从节点还没有启动,Slave_IO_State的值为空,Slave_IO_RunningSlave_SQL_Running线程为No表示也没有运行。

2.4 启动Slave

注意:要在节点2上创建同步帐户后再启动,否则会报连不上master错误

# 启动从节点,开始工作接收主节点发送事件(数据库数据变更的所有事件)

mysql> start slave;

# 此时再查看slave节点的状态

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.206

Master_User: repl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: edu-mysql-bin.000001

Read_Master_Log_Pos: 439

Relay_Log_File: edu-mysql-relay-bin.000002

Relay_Log_Pos: 287

Relay_Master_Log_File: edu-mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

# …省略其它配置

3. 节点2配置(192.168.1.206

3.1 添加数据同步配置

shell> vim /etc/my.cnf

1

[mysqld]中增加以下配置项:

server_id=206

binlog-ignore-db=mysql

log-bin=edu-mysql-bin

binlog_cache_size=1M

binlog_format=mixed

expire_logs_days=7

slave_skip_errors=1062

relay_log=edu-mysql-relay-bin

log_slave_updates=1

#ID自增从2开始,双数

auto_increment_increment=2

auto_increment_offset=2

3.2 Master配置

# 先重启一下服务

shell> service mysqld restart

# 登录到mysql

shell> mysql -uroot -p

# 创建数据库同步用户,并授予相应的权限(只允许repl用户从192.168.1.205上登录)

mysql> grant replication slave, replication client on *.* to ‘repl’@’192.168.1.205’ identified by ‘root123456’;

# 刷新授权表信息

mysql> flush privileges;

# 查看binlog文件的position(偏移)和File(日志文件)的值,从机上需要用到

mysql> show master status;

+———————-+———-+————–+——————+——————-+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+———————-+———-+————–+——————+——————-+

| edu-mysql-bin.000001 | 439 | | mysql | |

+———————-+———-+————–+——————+——————-+

1 row in set (0.00 sec)

这时可以启动节点1(205)slave服务

3.3 Slave配置

# master_log_filemaster_log_pos205节点上执行show master status;对应Fileposition的值

mysql> change master to master_host=’192.168.1.205′,master_user=’repl’, master_password=’root123456′, master_port=3306, master_log_file=’edu-mysql-bin.000001′, master_log_pos=120, master_connect_retry=30;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.1.205

Master_User: repl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: edu-mysql-bin.000001

Read_Master_Log_Pos: 120

Relay_Log_File: edu-mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: edu-mysql-bin.000001

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

#…省略其它配置

3.4、启动Slave

shell> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.205

Master_User: repl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: edu-mysql-bin.000001

Read_Master_Log_Pos: 439

Relay_Log_File: edu-mysql-relay-bin.000002

Relay_Log_Pos: 287

Relay_Master_Log_File: edu-mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

省略其它配置

4、验证

# 登录205创建一个数据库

shell> mysql -u root -p

mysql> create database if not exists mydb default character set utf8 collate utf8_general_ci;

mysql> create table user (id int, username varchar(30), password varchar(30));

mysql> insert into user values (1, ‘yangxin’, ‘123456’);

# 下面是在206节点上的操作

#1、登录206查询所有库,是否包含mydb数据库

#2、切换到mydb库,是否包含user表,并有一条数据

#3、在206mydb.user表插入一条数据,查看205是否同步过去

mysql> insert into user values (2,’yangxin2′,’123456′)