linux服务-mysql主从配置_主从备份的数据库,主库表结构变化

1.MYSQL主从配置的作用：

可以实现从数据库与主数据库的数据和结构保持一致，主发生变化，从会跟着变化。

2.MYSQL主从配置的前提：

主数据库的库与从数据库的数据结构，在配置前需要一致，可以都为空，也可以数据结构一致。

3.MYSQL内置的主从机制的原理：

第一步：master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

第二步：slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

 第三步：SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。

4.MYSQL主从常见架构：

1）一主多备

2）M-S(中继)-S

3）互为主备：向主或从数据库插入数据，主从数据库都会保持一致，即这种架构从数据库也可以进行写操作。

5.一主多备配置：

1）先在两台服务器上准备2个数据结构都一致的数据库：

①可以数据结构都为空

②主数据库若有数据和结构，则通过mysqldump命令同步

主库导出：mysqldump -uroot -proot -B hotel>hotel.sql  # -B参数会把数据库导出，并且生成建库语句；

把hotel.sql传到从库的服务器：scp hotel.sql root@192.168.5.12:/root

从库导入：mysql -uroot -proot <hotel.sql

2)  在主库中授权允许从库登录的账号：

mysql> grant replication slave on *.* to slave1@192.168.5.11  identified by "123456";

3）修改主库配置文件/etc/my.cnf，添加以下项：

log-bin=mysql-bin-master  #启用二进制日志，这个值就是二进制日志文件名的前缀

server-id=1   #本机数据库ID 标示

binlog-do-db=hotel #可以被从服务器复制的库, 二进制需要同步的数据库名

binlog-ignore-db=mysql  #不可以被从服务器复制的库

4）重启主库：

#systemctl restart mariadb(或mysqld)

5）查看主库master状态：

mysql> show master status;

6）查看主库二进制日志事件：

mysql> show master status;

7）修改从库配置文件/etc/my.cnf,添加以下项：

server-id = 2 #从库ID

8）重启从库：

#systemctl restart mariadb

9）向从库添加主库配置：

mysql>stop slave;    #停止slave

mysql> change master to master_host='10.10.10.63',master_user='slave',master_password='123456';

mysql> start slave;    #启动slave

10）查看从库slave状态：

mysql> show slave status\G  查看状态

Slave_IO_Running ：一个负责与主机的io通信

Slave_SQL_Running：负责自己的slave mysql进程

两个为YES 就成功了！

11）向主库测试插入数据，从库也能查到这条数据。

6.互为主备配置：

在上面第5点的基础上，反着做一遍配置即可。

由此可见，mysql数据库内置有2个角色，一个master，一个slave

7.M-S(中继)-S配置:

 

环境：

XUEGOD68   master        mysql5.7.20     10.10.10.68

XUEGOD69   slave中继   mysql5. 7.20     10.10.10.69

XUEGOD70   slave          mysql5. 7.20    10.10.10.70

 

首先确保3个库的数据结构一致：

导出主服务器HA库完整备份， 拷贝到 中继服务器 和slave服务器

[root@xuegod68 ~]# mysqldump -uroot -p123456 -B HA>ha.sql

[root@xuegod68 ~]# scp ha.sql 10.10.10.69:/root

[root@xuegod68 ~]# scp ha.sql 10.10.10.70:/root

[root@xuegod69 ~]# mysql –uroot –p123456 <ha.sql

[root@xuegod70 ~]# mysql –uroot –p123456 <ha.sql

 

部署master---->XUEGOD68:

1）在主库上授权用户：

mysql> grant replication slave on *.* to repl@'10.10.10.%' identified by '123456';

mysql> flush privileges;

2）修改主库配置文件：

[root@xuegod68 ~]# vim /etc/my.cnf   #修改配置 增加以下选项

server-id=1

binlog-do-db=HA

log-bin=mysql-bin-master

binlog-ignore-db=mysql

sync-binlog=1

binlog-format=row

3）重启主库

[root@xuegod68 ~]# systemctl restart mysqld

 

部署slave中继------> XUEGOD69

 

1）授权一个用户给slave(xuegod70):

mysql> grant replication slave on *.* to 'repl'@'10.10.10.70' identified by '123456';

mysql> flush privileges;

2）配置my.cnf

[root@xuegod69 ~]#vim /etc/my.cnf

server-id= 2

#修改主配置文件也要开启bin-log：

log-bin=mysql-bin-slave1

log-slave-updates=1   #把它从relay-log当中读取出来的二进制日志并且这本机上执行的操作也记录这自己的二进制日志里面，这样才能使第三台slave通过中继slave读取到相应数据变化

binlog-format=row

3）重启服务：

[root@xuegod69 ~]# systemctl restart mysqld

4）授权主库配置：

mysql> stop slave;

mysql> change master to master_host='10.10.10.68',master_user='repl',master_password='123456';

mysql> start slave;

查看中继服务的状态

mysql> show slave status \G

部署slave------>xuegod70

 

1）配置my.cnf

server-id = 3

log-bin=mysql-bini-slave2

binlog-format=row

2）重启服务

[root@xuegod70 ~]# systemctl restart mysqld 重启

3）指定slave中继服务作为xuegod70的主：

mysql> stop slave;

mysql> change master to master_host='10.10.10.69',master_user='repl',master_password='123456';

mysql> start slave;

查看从服务的状态

mysql> show slave status \G

从master上插入数据测试：

mysql> insert into test values (1,'AA');

Query OK, 1 row affected (0.09 sec)

然后分别在slave中继，与slave上查看

 

 

中继服务器可以仅仅起到一个分发主库日志的作用，而不存储数据：

修改表的引擎：

mysql> alter table t1 ENGINE=blackhole; (先关闭日志记录再修改set sql_log_bin=off)