MySQL高级学习笔记day03

MySQL主从复制

概述

生产环境中如果数据库只有一台服务器，容易产生单点故障问题，如这台服务器访问压力过大没有响应，服务不可用，或者硬盘坏了，整个数据库数据就丢失了为了保障数据安全可靠性，我们需要将数据复制多份部署在多台不同的服务器上，就算一台坏了其他还可以提供服务主从复制可以实现对数据库备份和读写分离主服务器负责读和写，从服务器只负责读

一主多从架构

主从复制原理

当master主服务器上的数据发生改变时，则将其改变写入二进制事件日志文件中salve，从服务器会在一定时间内对master主服务器上的二进制日志进行探测，探测器是否发生改变，如果探测master主服务器的二进制时间日志发生改变，则开始一个I/OThread请求master二进制事件日志，同时master主服务器为每个I/OThread启动一个dump Thread，用于向其发送二进制时间日志salve从服务器将接收到的二进制事件日志保存至自己本地的中继日志文件中salve从服务器将启动SQL Thread从中继日志中读取二进制日志，在本地重放，使得起数据和主服务器保持一致；最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。

主从复制流程图

MySQL多实例搭建

一台Linux服务器上同时启动多个MySQL数据库(技术研究和学习的场景)

每台服务器都分别安装MySQL(适用于实际线上生产环境)

1. 上传mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz到/usr/local/software

2.tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

3.mv mysql-5.7.24-linux-glibc2.12-x86_64 mysql-5.7.24

4.cd mysql-5.7.24

5**.mkdir data**

6.进入data目录 创建2个文件夹 mkdir 3307 3308

7.如果此Linux从未安装过MySQL 执行下面步骤

groupadd mysql

useradd -g mysql mysql

8.修改权限(cd /usr/local目录)

chgrp -R mysql mysql-5.7.24

chown -R mysql mysql-5.7.24

chown -R mysql mysql-5.7.24/data/

chown -R root mysql-5.7.24

9.添加MySQL环境变量

vim /etc/profile

在文件末尾添加

export MYSQL_HOME=/usr/local/software/mysql-5.7.24

export PATH=$MYSQL_HOME/bin:$PATH

需要执行 source /etc/profiles

10.执行数据库初始化

mysqld --initialize-insecure --basedir=/usr/local/software/mysql-5.7.24 --datadir=/usr/local/software/mysql-5.7.24/data/3307 --user=mysql

mysqld --initialize-insecure --basedir=/usr/local/software/mysql-5.7.24 --datadir=/usr/local/software/mysql-5.7.24/data/3308 --user=mysql

initialize-insecure 表示不生成MySQL数据库root用户的随机密码，

即root密码为空

11**.配置2个MySQL数据库服务的my.cnf文件**

在/data/3307,/data/3308两个目录下分别创建一个my.cnf文件

在两个my.cnf文件分别配置如下内容(注意:不同的实例下配置要修改 端口号)

[client]

port        = 3307

socket      = /usr/local/software/mysql-5.7.24/data/3307/mysql.sock

default-character-set=utf8

 

[mysqld]

port    = 3307

socket  = /usr/local/software/mysql-5.7.24/data/3307/mysql.sock

datadir = /usr/local/software/mysql-5.7.24/data/3307

log-error = /usr/local/software/mysql-5.7.24/data/3307/error.log

pid-file = /usr/local/software/mysql-5.7.24/data/3307/mysql.pid

 

character-set-server=utf8

lower_case_table_names=1

autocommit = 1

12.多实例启动

mysqld_safe --defaults-file=/usr/local/software/mysql-5.7.24/data/3307/my.cnf &

mysqld_safe --defaults-file=/usr/local/software/mysql-5.7.24/data/3308/my.cnf &

其中 --defaults-file 是指定配置文件，& 符合表示后台启动

13.打开两个XShell窗口 分别登录 密码为空
mysql -uroot -p -P3307 -h127.0.0.1

mysql -uroot -p -P3308 -h127.0.0.1

14.修改密码

alter user 'root'@'localhost' identified by '123';

15.授权远程访问(这样远程客户端才能访问)

grant all privileges on *.* to root@'%' identified by '123';

其中*.* 的第一个表示所有数据库名，第二个表示所有的数据库表

root@’%’ 中的root表示用户名

%表示所有ip地址，%也可以指定具体的ip地址，比如root@localhost， root@192.168.10.129

16.刷新权限

flush privileges;

17.多实例关闭

mysqladmin -uroot -p -P3307 -h127.0.0.1 shutdown

mysqladmin -uroot -p -P3308 -h127.0.0.1 shutdown

一主一从环境搭建

修改各个系统配置文件my.cnf

3307

log-bin=mysql-bin #表示启用二进制日志

server-id=3307 #表示server编号，编号要唯一

binlog_format=STATEMENT #设置logbin格式(默认)

log-bin=mysql-bin

server-id=3307

binlog_format=STATEMENT

3308

server-id=3308 #表示server编号，编号要唯一

模式的区别

STATEMENT模式

基于SQL语句的复制，每一条修改数据的sql语句会记录到binlog中。

优点

不需要记录每一条sql语句和每一行数据变化，减少binlog日志量

缺点

update xxx set xx time=now() where name=xxx

上面now函数在master上执行但在slave上执行时time就变了

ROW模式

基于行的复制

对于update xxx set xx time=now() where name=xxx

记录每一行的改变 直接把值复制过去

缺点

比方说有一个部门表 部门有CEO字段 一个公司的老总变了，

那么所有行CEO都会变，执行语句是update不加where条件就可以

比方说这个公司有10万人 每行都记录就会有10万行记录

会产生大量的日志，效率有问题

MIXED模式

结合上面两种模式,有函数切换到行模式，没函数切换到STATEMENT模式

缺点

如果有系统变量(@@hostname)也没有办法避免主从复制不一致问题

重启两个MySQL服务，启动时指定配置文件

mysqld_safe --defaults-file=/usr/local/software/mysql-5.7.24/data/3307/my.cnf &

mysqld_safe --defaults-file=/usr/local/software/mysql-5.7.24/data/3308/my.cnf &

主服务器设置

主服务器3307设置

需要登录到主服务器3307的客户端

mysql -uroot -p -P3307 -h127.0.0.1

在主服务器上创建复制数据的账号并授权

grant replication slave on *.* to 'copy'@'%' identified by '123';

查看主服务器状态

show master status;

mysql主服务器默认初始值：

File：mysql-bin.000001

Position：154

如果主服务状态不是初始状态，需要重置状态

reset master;

从服务器3308设置

a.需要登录到从服务器3308的客户端

mysql -uroot -p -P3308 -h127.0.0.1

b.如果从服务器不是初始状态，建议重置一下

show slave status \G;

stop slave; #停止复制，相当于终止从服务器上的IO和SQL线程

reset slave;

c.设置从服务器的master(在3308从服务器客户端执行)

change master to master_host='182.92.234.71',master_user='copy',

master_port=3307,master_password='123',

master_log_file='mysql-bin.000001',master_log_pos=154;

d.在从机器上执行开始复制命令

start slave;

e.若主从复制失效了 需要在主机上执行下面操作

reset master; (主)

stop slave; (从)

reset slave; (从)

start slave; (从)

主从验证

a.检查从服务器复制功能状态

在从服务器的客户端执行以下命令：show slave status \G;

\G表示格式化输出

如果 Slave_IO_Running和Slave_SQL_Running 均为YES，则表示主从 关系正常

b.在主服务器上创建数据库/表/数据 在从服务器上查看是否已经复制

1.在Navicat主服务器上创建库test，查看从服务器情况

2.在Navicat主服务器上test库创建表user(id,name)看从服务器情况

3.在Navicat主服务器上test库user中添加数据，查看从服务器情况

c.查看主从复制binlog日志文件内容

在主服务器客户端执行：show binlog events in ‘mysql-bin.000001’\G;