数据库的三机数据备份

三台服务器的数据热备,按照思路说那就是两主一从,AB主主,AC主备,C作为一个备份的数据

A,B数据库主主备份:

保证各服务器上的数据库中的数据一致，因此需要开启数据库同步机制。由于是一整套系统，并且系统内含数据库。由于任何一台服务器都有可能被选中，因此要让所有的数据库上的数据都是最新的，任何一个服务器上的数据发生变化时都要自动的同步到其他的服务器上。

三维可视化管理系统使用的时Mysql数据库，这里采用的时主-主备份机制进行同步的。

A数据库:192.168.10.247

B数据库:192.168.10.9

C数据库:192.168.10.10

A数据库:

编辑数据库

vi /etc/my.cnf

# 开启二进制同步
log-bin=mysql-bin

# 设置ID,不能重复,可以使用IP最后一位数字 
server-id=129

# 需要同步的数据名称,多个数据库则要重复设置: bin-do-db,bin-ignore-db为互斥关系, 只需设置其中一项即可
replicate-do-db=itv

# 自增长字段初始值为1
auto-increment-offset=2

# 自增长字段增量值
auto-increment-increment=2

# 跳过所有复制的错误
slave-skip-errors=all

systemctl restart mysqld   //重启mysql服务

B数据库:

编辑数据库

vi /etc/my.cnf

# 开启二进制同步
log-bin=mysql-bin

# 设置ID,不能重复,可以使用IP最后一位数字 
server-id=128

# 需要同步的数据名称,多个数据库则要重复设置: bin-do-db,bin-ignore-db为互斥关系, 只需设置其中一项即可
replicate-do-db=itv

# 自增长字段初始值为1
auto-increment-offset=1

# 自增长字段增量值
auto-increment-increment=2

# 跳过所有复制的错误
slave-skip-errors=all

systemctl restart mysqld   //重启mysql服务

C数据库:

编辑数据库

vi /etc/my.cnf

# 开启二进制同步
log-bin=mysql-bin

# 设置ID,不能重复,可以使用IP最后一位数字 
server-id=130

# 需要同步的数据名称,多个数据库则要重复设置: bin-do-db,bin-ignore-db为互斥关系, 只需设置其中一项即可
replicate-do-db=itv

# 自增长字段初始值为1
auto-increment-offset=1

# 自增长字段增量值
auto-increment-increment=2

# 跳过所有复制的错误
slave-skip-errors=all

systemctl restart mysqld   //重启mysql服务

创建mysql复制账户:

在A数据库操作:

为B的所在的ip创建一个账号密码为root1,这样就能够允许远程访问本机数据库了

登录mysql:

mysql -uroot -proot   //登录mysql

use mysql;

grant replication slave on *.* to root1@'192.168.10.9' identified by 'root1';

赋予权限:如果就这样连接的话虽然能访问但还是看不了数据的,所以需要赋予root1权限

update user set `Select_priv` = 'Y',`Insert_priv` = 'Y',`Update_priv` = 'Y',`Delete_priv` = 'Y',`Create_priv` = 'Y',`Drop_priv` = 'Y',
`Reload_priv` = 'Y',`Shutdown_priv` = 'Y',`Process_priv` = 'Y',`File_priv` = 'Y',`Grant_priv` = 'Y',`References_priv` = 'Y',
`Index_priv` = 'Y',`Alter_priv` = 'Y',`Show_db_priv` = 'Y',`Super_priv` = 'Y',`Create_tmp_table_priv` = 'Y',
`Lock_tables_priv` = 'Y',`Execute_priv` = 'Y',`Repl_slave_priv` = 'Y',`Repl_client_priv` = 'Y',`Create_view_priv` = 'Y',
`Show_view_priv` = 'Y',`Create_routine_priv` = 'Y',`Alter_routine_priv` = 'Y',`Create_user_priv` = 'Y',`Event_priv` = 'Y',
`Trigger_priv` = 'Y',`Create_tablespace_priv` = 'Y'
where user='root1';

立即生效:

flush privileges;

在B数据库操作:

为A的所在的ip创建一个账号密码为root1,这样就能够允许远程访问本机数据库了

登录mysql:

mysql -uroot -proot   //登录mysql

use mysql;

grant replication slave on *.* to root1@'192.168.10.247' identified by 'root1';

赋予权限:如果就这样连接的话虽然能访问但是访问不了itv的数据,所以需要赋予root1权限

linux下:

update user set `Select_priv` = 'Y',`Insert_priv` = 'Y',`Update_priv` = 'Y',`Delete_priv` = 'Y',`Create_priv` = 'Y',`Drop_priv` = 'Y',
`Reload_priv` = 'Y',`Shutdown_priv` = 'Y',`Process_priv` = 'Y',`File_priv` = 'Y',`Grant_priv` = 'Y',`References_priv` = 'Y',
`Index_priv` = 'Y',`Alter_priv` = 'Y',`Show_db_priv` = 'Y',`Super_priv` = 'Y',`Create_tmp_table_priv` = 'Y',
`Lock_tables_priv` = 'Y',`Execute_priv` = 'Y',`Repl_slave_priv` = 'Y',`Repl_client_priv` = 'Y',`Create_view_priv` = 'Y',
`Show_view_priv` = 'Y',`Create_routine_priv` = 'Y',`Alter_routine_priv` = 'Y',`Create_user_priv` = 'Y',`Event_priv` = 'Y',
`Trigger_priv` = 'Y',`Create_tablespace_priv` = 'Y'
where user='root1';

windows下:

GRANT ALL PRIVILEGES ON *.* TO 'root1'@'192.168.10.38' IDENTIFIED BY 'root1' WITH GRANT OPTION;

grant all privileges on *.* to 'sk'@'localhost';  //试下这条

立即生效:

flush privileges;

测试能否访问:

在A登录B的数据库:

mysql -h192.168.10.9 -uroot1 -p    //登录主二的数据库

发现连接失败

我们查看3306端口发现被占用,我们开启3306端口,我们在主备机都需要开放此端口

应该是防火墙的原因,

firewall-cmd --zone=public --add-port=3306/tcp --permanent

然后重启防火墙

systemctl restart firewalld.service

继续连接,发现可以连接成功,并且可以查看数据

show databases;  //查看数据库

同理,在B的数据库可以访问A的数据库看下:

mysql -h192.168.10.247 -uroot1 -p    //登录主一的数据库

show databases;  //查看数据库

重启数据库

主数据库1: systemctl restart mysqld

主数据库2: systemctl restart mysqld

互告bin-log信息

登录本地的mysql

主主同步还有主从同步都是基于binlog的原理进行,相当于我们打开了这个开关,每次操作数据库都会产生一个binlog日志,然后把binlog日志进行发给对方进行执行,这样数据就保持同步了

首先进入MySQL命令行:

在A数据库操作:

查看日志: show master status;

设置同步:

change master to master_host = '192.168.10.9',master_user='root1',master_password='root1',master_port=3306,master_log_file='mysql-bin.000002',master_log_pos=154;

备注: master_log_file与File值一致, master_log_pos与Position值一致

开始同步: start slave;

查看同步情况:

show slave status\G;

当看到了两个yes，即：Slave_IO_Running: Yes

Slave_SQL_Running: Yes

说明已经配置成功了,但是这里有一个显示为no,明显有问题

解决办法:

stop slave;

reset slave;

start slave;

然后我们继续查看同步情况;

show slave status\G;

灰常完美

在B数据库操作:

这里其实跟上面的主数库一是一样的操作

show master status;

change master to master_host = '192.168.10.247',master_user='root1',master_password='root1',master_port=3306,master_log_file='mysql-bin.000002',master_log_pos=154;

备注: master_log_file与File值一致, master_log_pos与Position值一致

开始同步:

start slave;

查看同步情况:

show slave status\G;

当看到了两个yes，即：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

说明已经配置成功了,如果这里的slave_io_Running显示no依然采用上面的办法操作即可

测试:

这是A服务器的数据

这是B的数据

当我们改动A的数那么B的数据应该也会修改的,我们开始测试

我们在A数据库下操作

update cctv set url='cctv_test' where id='2ca1';

看看结果,A表的数据已经修改成功,我们看看B的数据库

B的数据库也成功修改

A,C数据库主从同步

当A数据库产生变化后,会同步数据到C

在A服务器下操作:

创建从服务器的用户和权限:

进入mysql数据库

[root@localhost mysql]# mysql -uroot -p
Enter password:
 
#创建从数据库的masterbackup用户和权限
mysql> grant replication slave on *.* to root1@'192.168.10.10' identified by 'root1';

立即生效:

flush privileges;

下面C服务器会用到这个

#退出mysql
mysql> exit;

C服务器操作

进入mysql数据库

[root@localhost mysql]# mysql -uroot -p

Enter password:

创建从数据库的masterbackup用户和权限

grant replication slave on *.* to root1@'192.168.10.247' identified by 'root1';

show master status;

change master to master_host='192.168.10.247',master_port=3306,master_user='root1',master_password='root1',master_log_file='mysql-bin.000247',master_log_pos=461;

启动复制进程

start slave;

查看节点信息

show slave status\G;

可以看到两个yes

当A修改了数据库后,B,C的数据也会同步修改