返回
数据
分类

这里的server-id用于标识唯一的数据库,环境说明

日期: 2020-01-25 16:35 浏览次数 : 171

环境说明

3mysql数据同步实现原理:

安装环境

2.1    硬件环境(两台机器的硬件环境相同)

CPU

Inter(R) Pentium(R) Dual CPU E5200 @2.50GHz

内存大小

384MB

硬盘大小

8GB

其它说明

本机器为虚拟机,其有两块网卡,一对网卡直连,一对接入交换机

 

CPU

Inter(R) Pentium(R) Dual CPU E5200 @2.50GHz

内存大小

384MB

硬盘大小

8GB

其它说明

本机器为虚拟机,其有两块网卡,一对网卡直连,一对接入交换机

 

即读写操作在两台服务器上进行,每台服务器即主也是从。当其中的任何一台服务器收到操作请求时,其进行相应的数据变化,并把变化的数据复制到另一台服务器中。

操作系统 :CentOS 6.5

2.2    软件环境(两台机器的软件环境相同)

系统

Server 2003 Enterprise Edition

Java环境

jdk1.6.0

web平台

apache-tomcat-5.0

数据库

MySQL Server 5.0

ldap数据库

Sun ONE Directory Server 5.2

ha软件

蓝科泰达科技Pluswell

 

 

 

4 数据同步实现

数据库版本:MySQL 5.6.27

2.3    网络环境

主机名

ha001

IP地址

192.168.0.188

子网掩码

255.255.255.0

网关

192.168.0.1

DNS

192.168.0.1

公用虚拟IP

192.168.0.187

IP地址02

192.168.8.188

子网掩码02

255.255.255.0

 

主机名

ha002

IP地址

192.168.0.189

必赢手机登录网址 ,子网掩码

255.255.255.0

网关

192.168.0.1

DNS

192.168.0.1

公用虚拟IP

192.168.0.187

IP地址02

192.168.8.189

子网掩码02

255.255.255.0

 

3  mysql数据同步实现原理:

4.1mysql数据双向同步

主机A:192.168.1.1 (Master)

 

 

即读写操作在两台服务器上进行,每台服务器即主也是从。当其中的任何一台服务器收到操作请求时,其进行相应的数据变化,并把变化的数据复制到另一台服务器中。

4.1.1配置服务器ha001

主机B:192.168.1.2 (Slave)

4 数据同步实现

Ø初始服务器

Master的配置

4.1    mysql数据双向同步

通过mysql工具连接服务器ha001后,新建两个数据库audit,idm。导入初始化数据库文件,完成数据库的初始化

在Linux环境下MySQL的配置文件的位置是在 /etc/my.cnf ,在该文件下指定Master的配置如下:

4.1.1    配置服务器ha001

Ø    初始服务器

通过mysql工具连接服务器ha001后,新建两个数据库audit,idm。导入初始化数据库文件,完成数据库的初始化

Ø    给用户授权

从开始菜单中打开mysql5的命令行,输入正确的密码,进入mysql控制台命令行模式后,输入如下命令:

#授权来自192.168.0.189的backup用户拥有对所有库的复制数据的权限,该用户的密码设为123456

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.0.189' IDENTIFIED BY '123456';

#刷新权限设置

FLUSH PRIVILEGES ;

Ø    修改配置文件

修改主目录中的my.inf文件,在mysqld下面加入如下内容

server-id = 1

log-bin=mysql-bin

binlog-do-db = audit

binlog-do-db = idm

binlog-ignore-db = information_schema

binlog-ignore-db = mysql

binlog-ignore-db = test

 

master-host     = 192.168.0.189

master-user     = backup

master-password = 123456

master-port     = 3306

replicate-do-db = audit

replicate-do-db = idm

master-connect-retry = 60

 

Ø给用户授权

log-bin=mysql-bin

4.1.2    配置服务器ha002

Ø    初始服务器

通过mysql工具连接服务器ha002后,新建两个数据库audit,idm。导入初始化数据库文件,完成数据库的初始化

Ø    给用户授权

从开始菜单中打开mysql5的命令行,输入正确的密码,进入mysql控制台命令行模式后,输入如下命令:

#授权来自192.168.0.188的backup用户拥有对所有库的复制数据的权限,该用户的密码设为123456

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.0.188' IDENTIFIED BY '123456';

#刷新权限设置

FLUSH PRIVILEGES ;

 

Ø    修改配置文件

修改主目录中的my.inf文件,在mysqld下面加入如下内容

server-id = 2

master-host     = 192.168.0.188

master-user     = backup

master-password = 123456

master-port     = 3306

replicate-do-db = audit

replicate-do-db = idm

master-connect-retry = 60

 

log-bin=mysql-bin

binlog-do-db = audit

binlog-do-db = idm

binlog-ignore-db = information_schema

binlog-ignore-db = mysql

binlog-ignore-db = test

 

从开始菜单中打开mysql5的命令行,输入正确的密码,进入mysql控制台命令行模式后,输入如下命令:

server-id=2

4.1.3    启动两台服务器上的mysql从服务

在两台服务器上,均完成如下操作:

从开始菜单中打开mysql5的命令行,输入正确的密码,进入mysql控制台命令行模式后,输入如下命令:

#开启从服务器

slave start;

 

#授权来自192.168.0.189的backup用户拥有对所有库的复制数据的权限,该用户的密码设为123456

binlog-ignore-db=information_schema

4.1.4    服务状态检查

在两台服务器上,均完成如下操作:

从开始菜单中打开mysql5的命令行,输入正确的密码,进入mysql控制台命令行模式后,输入如下命令:

#显示主服务器状态

Show master status;

#显示从服务器状态

Show slave status G;

Ø    查看连接配置状态:

Slave_IO_Running: Yes -----此项为No代表与主库连接有问题。

Slave_SQL_Running: Yes ----如果此项为NO代表配置有问题。

Ø    查看对比pos点以及记录文件是否一致:

如果不一致,在从MySQL运行

#改变从服务器的pos点为98,记录文件为mysql-bin.000001

CHANGE MASTER TO Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;

运行此命令前需要stop slave;之后再start slave;

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.0.189' IDENTIFIED BY '123456';

binlog-ignore-db=cluster

4.1.5    双向同步测试

Ø    检查当在两台机器的任何一台mysql中修改数据时,另外的那台的数据是否也修改

Ø    关闭其中一台mysql的服务后,更新另一台mysql的数据,再启动已停止的那台mysql服务器,查看数据是否修改

#刷新权限设置

binlog-ignore-db=mysql

4.1.6    特别注意事项

Ø    请在任何时候都不要非法关机,否则将会造成同步数据的错乱。

Ø    当出现了非法关机时,为不影响数据,请执行如下操作:

在该非法关机的机器启动后,检查该机器,查看数据是否与一直正常运行的机器一致了。确认其数据一致后。进入一直正常运行的机器,从开始菜单中打开mysql5的命令行,输入正确的密码,进入mysql控制台命令行模式后,输入如下命令:

#关闭slave服务

Slave stop;

#开启slave服务

Slave start;

通过这步后,一切即恢复正常

Ø    如果数据已经完全错乱,请先在两台机器上停止slave服务,然后将数据正常的服务器上的数据导入到不正常的服务器上,保证两边的服务器数据一致。然后到数据正常的服务器的mysql控制台下输入:

#显示主服务器状态

Show master status;

获取其状态后,根据这个状态到数据不正常服务器上,通过命令:

#改变从服务器的pos点为xx,记录文件为mysql-bin.0000xx

CHANGE MASTER TO Master_Log_File='mysql-bin.0000xx',Master_Log_Pos=xx;

修改其参数后,再通过

#开启从服务器

Start slave;

#显示从服务器状态,检查是否和主一致。

#检查Slave_IO_Running,Slave_SQL_Running状态是否正常

Show slave status G;

确保一切正常后。再到数据不正常的服务器的mysql控制台下输入:

#显示主服务器状态

Show master status;

获取其状态后,根据这个状态到数据正常的服务器上,通过命令:

#改变从服务器的pos点为xx,记录文件为mysql-bin.0000xx

CHANGE MASTER TO Master_Log_File='mysql-bin.0000xx',Master_Log_Pos=xx;

修改其参数后,再通过

#开启从服务器

Start slave;

#显示从服务器状态,检查是否和主一致。

#检查Slave_IO_Running,Slave_SQL_Running状态是否正常

Show slave status G;

确保也正常。最后在到两台服务器上修改数据,查看数据是否已经同步

Ø    同步一旦配置好后,请不要修改服务器机器名

FLUSH PRIVILEGES ;

binlog-do-db=ufind_db

Ø修改配置文件

这里的server-id用于标识唯一的数据库,这里设置为2,在设置从库的时候就需要设置为其他值。

修改主目录中的my.inf文件,在mysqld下面加入如下内容

binlog-ignore-db:表示同步的时候ignore的数据库

server-id = 1

binlog-do-db:指定需要同步的数据库

log-bin=mysql-bin

1、然后重启mysql:service mysqld restart

binlog-do-db = audit

2、进入mysql:[root@VM_221_4_centos ~]# mysql -u root -p 回车,输入mysql密码进入。

binlog-do-db = idm

3、 赋予从库权限帐号,允许用户在主库上读取日志,赋予192.168.1.2也就是Slave机器有File权限,只赋予Slave机器有File权限还不行,还要给它REPLICATION SLAVE的权限才可以。

binlog-ignore-db = information_schema

在Master数据库命令行中输入:

binlog-ignore-db = mysql

>GRANT FILE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password';

binlog-ignore-db = test

>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password';

master-host     = 192.168.0.189

>FLUSH PRIVILEGES

master-user     = backup

4、重启mysql,登录mysql,显示主库信息

master-password = 123456

mysql> show master status;

master-port     = 3306

1

replicate-do-db = audit

1

replicate-do-db = idm

mysql> show master status;

master-connect-retry = 60

+------------------+----------+--------------+----------------------------------+-------------------+

4.1.2配置服务器ha002

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB                | Executed_Gtid_Set |

Ø初始服务器

+------------------+----------+--------------+----------------------------------+-------------------+

通过mysql工具连接服务器ha002后,新建两个数据库audit,idm。导入初始化数据库文件,完成数据库的初始化

| mysql-bin.000004 |    28125 | ufind_db    | information_schema,cluster,mysql |                  |

Ø给用户授权

+------------------+----------+--------------+----------------------------------+-------------------+

从开始菜单中打开mysql5的命令行,输入正确的密码,进入mysql控制台命令行模式后,输入如下命令:

1 row in set (0.00 sec)

#授权来自192.168.0.188的backup用户拥有对所有库的复制数据的权限,该用户的密码设为123456

Slave的配置

GRANT REPLICATION SLAVE ON *.* TO 'backup'@'192.168.0.188' IDENTIFIED BY '123456';

1、从库的配置,首先也是修改配置文件:/etc/my.cnf 如下:

#刷新权限设置

log-bin=mysql-bin

FLUSH PRIVILEGES ;

server-id=3

Ø修改配置文件

binlog-ignore-db=information_schema

修改主目录中的my.inf文件,在mysqld下面加入如下内容

binlog-ignore-db=cluster

server-id = 2

binlog-ignore-db=mysql

master-host     = 192.168.0.188

replicate-do-db=ufind_db

master-user     = backup

replicate-ignore-db=mysql

master-password = 123456

log-slave-updates

master-port     = 3306

slave-skip-errors=all

replicate-do-db = audit

slave-net-timeout=60

replicate-do-db = idm

2、修改完/etc/my.cnf 文件之后,重启一下MySQL(service mysqld restart)

master-connect-retry = 60

进入Slave mysql控制台,执行:

log-bin=mysql-bin

这里写图片描述

这里的server-id用于标识唯一的数据库,环境说明。binlog-do-db = audit

mysql> stop slave;  #关闭Slave

binlog-do-db = idm

mysql> change master to master_host='192.168.1.1',master_user='root',master_password='123456',master_log_file='mysql-bin.000004', master_log_pos=28125;

binlog-ignore-db = information_schema

mysql> start slave;  #开启Slave

binlog-ignore-db = mysql

在这里指定Master的信息,master_log_file是在配置Master的时候的File选项, master_log_pos是在配置Master的Position 选项,这里要进行对应。

binlog-ignore-db = test

然后可以通过mysql> show slave status; 查看配置的信息:

4.1.3启动两台服务器上的mysql从服务

mysql> show slave status G;

在两台服务器上,均完成如下操作:

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

从开始菜单中打开mysql5的命令行,输入正确的密码,进入mysql控制台命令行模式后,输入如下命令:

Slave_IO_State: Waiting for master to send event

#开启从服务器

Master_Host: 192.167.1.1

slave start;

Master_User: root

4.1.4服务状态检查

Master_Port: 3306

在两台服务器上,均完成如下操作:

Connect_Retry: 60

从开始菜单中打开mysql5的命令行,输入正确的密码,进入mysql控制台命令行模式后,输入如下命令:

Master_Log_File: mysql-bin.000004

#显示主服务器状态

Read_Master_Log_Pos: 28125

Show master status;

Relay_Log_File: VM_128_194_centos-relay-bin.000004

#显示从服务器状态

Relay_Log_Pos: 26111

Show slave status G;

Relay_Master_Log_File: mysql-bin.000004

Ø查看连接配置状态:

Slave_IO_Running: Yes

Slave_IO_Running: Yes -----此项为No代表与主库连接有问题。

Slave_SQL_Running: Yes

Slave_SQL_Running: Yes ----如果此项为NO代表配置有问题。

Replicate_Do_DB: ufind_db

Ø查看对比pos点以及记录文件是否一致:

Replicate_Ignore_DB: mysql

如果不一致,在从MySQL运行

Replicate_Do_Table:

#改变从服务器的pos点为98,记录文件为mysql-bin.000001

Replicate_Ignore_Table:

CHANGE MASTER TO Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;

Replicate_Wild_Do_Table:

运行此命令前需要stop slave;之后再start slave;

Replicate_Wild_Ignore_Table:

4.1.5双向同步测试

Last_Errno: 0

Ø检查当在两台机器的任何一台mysql中修改数据时,另外的那台的数据是否也修改

Last_Error:

Ø关闭其中一台mysql的服务后,更新另一台mysql的数据,再启动已停止的那台mysql服务器,查看数据是否修改

Skip_Counter: 0

4.1.6特别注意事项

Exec_Master_Log_Pos: 28125

Ø请在任何时候都不要非法关机,否则将会造成同步数据的错乱。

Relay_Log_Space: 26296

Ø当出现了非法关机时,为不影响数据,请执行如下操作:

Until_Condition: None

在该非法关机的机器启动后,检查该机器,查看数据是否与一直正常运行的机器一致了。确认其数据一致后。进入一直正常运行的机器,从开始菜单中打开mysql5的命令行,输入正确的密码,进入mysql控制台命令行模式后,输入如下命令:

Until_Log_File:

#关闭slave服务

Until_Log_Pos: 0

Slave stop;

Master_SSL_Allowed: No

#开启slave服务

Master_SSL_CA_File:

Slave start;

Master_SSL_CA_Path:

通过这步后,一切即恢复正常

Master_SSL_Cert:

Ø如果数据已经完全错乱,请先在两台机器上停止slave服务,然后将数据正常的服务器上的数据导入到不正常的服务器上,保证两边的服务器数据一致。然后到数据正常的服务器的mysql控制台下输入:

Master_SSL_Cipher:

#显示主服务器状态

Master_SSL_Key:

Show master status;

Seconds_Behind_Master: 0

获取其状态后,根据这个状态到数据不正常服务器上,通过命令:

Master_SSL_Verify_Server_Cert: No

#改变从服务器的pos点为xx,记录文件为mysql-bin.0000xx

Last_IO_Errno: 0

CHANGE MASTER TO Master_Log_File='mysql-bin.0000xx',Master_Log_Pos=xx;

Last_IO_Error:

修改其参数后,再通过

Last_SQL_Errno: 0

#开启从服务器

Last_SQL_Error:

Start slave;

Replicate_Ignore_Server_Ids:

#显示从服务器状态,检查是否和主一致。

Master_Server_Id: 2

#检查Slave_IO_Running,Slave_SQL_Running状态是否正常

Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd

Show slave status G;

Master_Info_File: /data/mysqldb/master.info

确保一切正常后。再到数据不正常的服务器的mysql控制台下输入:

SQL_Delay: 0

#显示主服务器状态

SQL_Remaining_Delay: NULL

Show master status;

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

获取其状态后,根据这个状态到数据正常的服务器上,通过命令:

Master_Retry_Count: 86400

#改变从服务器的pos点为xx,记录文件为mysql-bin.0000xx

Master_Bind:

CHANGE MASTER TO Master_Log_File='mysql-bin.0000xx',Master_Log_Pos=xx;

Last_IO_Error_Timestamp:

修改其参数后,再通过

Last_SQL_Error_Timestamp:

#开启从服务器

Master_SSL_Crl:

Start slave;

Master_SSL_Crlpath:

#显示从服务器状态,检查是否和主一致。

Retrieved_Gtid_Set:

#检查Slave_IO_Running,Slave_SQL_Running状态是否正常

Executed_Gtid_Set:

Show slave status G;

Auto_Position: 0

确保也正常。最后在到两台服务器上修改数据,查看数据是否已经同步

1 row in set (0.00 sec)

Ø同步一旦配置好后,请不要修改服务器机器名

(l)、 Master_Log_File,Read_Master_Log_Pos 记录了IO thread读到的当前master binlog文 件和位置, 对应master的binlog文件和位置。

(2)、 Relay_Log_File,Relay_Log_Pos记录了SQL thread执行到relay log的那个文件和位置,对应的是slave上的relay log文件和位置。

(3)、 Relay_Master_Log_File,Exec_Master_Log_Pos记录的是SQL thread执行到master binlog的文件和位置,对应的master上binlog的文件和位置。

可以看到,已经配置成功。


主从不一致处理:

stop slave;

set global sql_slave_skip_counter =1;

start slave;


主从不一致处理:

1.先进入主库,进行锁表,防止数据写入

mysql> flush tables with read lock;

2.进行数据备份

[root@server01 mysql]#mysqldump -uroot -p test > test.sql

3.查看master 状态

mysql> show master status;

+-------------------+----------+--------------+-------------------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |

+-------------------+----------+--------------+-------------------------------+

| mysqld-bin.000001 |    3260 |              | mysql,test,information_schema |

+-------------------+----------+--------------+-------------------------------+

1 row in set (0.00 sec)

4.把mysql备份文件传到从库机器,进行数据恢复

#使用scp命令

[root@server01 mysql]# scp test.sql root@192.168.128.101:/tmp/

5.停止从库的状态

mysql> stop slave;

6.然后到从库执行mysql命令,导入数据备份

mysql> source /tmp/test.sql

7.设置从库同步,注意该处的同步点,就是主库show master status信息里的| File| Position两项

change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;

8.重新开启从同步

mysql> start slave;

9.查看同步状态

mysql> show slave statusG  查看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

好了,同步完成啦。