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中的数据一致。

废话不多说，直接操作

本次通过docker来实现主从和主主的实现

第一步 通过docker 下载mysql

docker pull mysql:5.7

查看是否安装成功

docker images

然后创建两个mysql数据库

docker run -d -p 3333:3306 --name master -e MYSQL_ROOT_PASSWORD=123456 docker.io/mysql:5.7

docker run -d -p 3334:3306 --name slave -e MYSQL_ROOT_PASSWORD=123456 docker.io/mysql:5.7

PS: 3333:3306 前边映射的是宿主机的端口 后边为docker本地端口 

        -e MYSQL_ROOT_PASSWORD=123456 设置数据库的密码

        --name slave 数据库别名

第二步 进入mysql 更改mysql的配置文件

首先获取到两个mysql的容器id

docker ps

然后进入容器更改数据库的配置 

docker exec -it 21d9f3f97d33 /bin/bash

进入到容器中 找到my.cnf这个文件然后更改里边配置即可

 首先进入后可以通过 ls命令查看文件夹，然后进入 etc 文件夹中。有些人的my.cnf文件就在这里，也有的人可能在 etc/mysql里边，博主的my.cnf就在etc/mysql里边。然后使用 vi 编辑文件即可。

PS：新安装的docker 是没有vim 需要下载下

第一步
apt update //更新源
第二步
apt-get install vim //安装 vim

接着就可以修改配置文件(首先是master的配置信息)

[mysqld]
#配置服务器的服务号
server-id =1
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment =2
auto-increment-offset= 1
slave-skip-errors = all

(salve的配置信息) 

[mysqld]
#配置服务器的服务号
server-id =2
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed
auto-increment-increment =2
auto-increment-offset= 2
slave-skip-errors = all

如果docker的vi编辑使用不了的话，可以把文件拷贝到本地，然后在本地进行更改，本地更改完成后，再重新拷贝会docker中，代码流程如下

//把本地文件复制到docker容器
docker cp 本地路径（全路径） 容器id:docker路径
//注意容器id后边不能有空格 要不然会报错
例如: docker cp G:\home\docker_mysql\sql1\mysql\my.cnf 5a69d9cc57b2:/etc/ mysql
//把docker容器内文件复制到本地
docker cp 容器id:docker路径  本地路径

更改完成后打开我们的navicat

新建两个连接分别连接到我们新建的两个数据库(如图)

 创建完成后，打开我们的主库（master）然后新建查询，依次执行一下代码

按顺序执行！！！

按顺序执行！！！

按顺序执行！！！（重要的事情说三遍）

CREATE USER salve IDENTIFIED BY 'root'; //创建一个账号为 salve 密码为root的用户
CREATE USER 'salve'@'172.17.0.1' IDENTIFIED BY 'root'; //创建一个用户名为 salve 密码为root 并且只为 172.17.0.1 ip开放的用户
CREATE USER 'salve'@'192.168.2.14' IDENTIFIED BY 'root';//这里的ip 需要使用自己的IP地址

grant replication slave on *.* to 'salve'@'%';//授予用户 salve 从任何 IP 地址（% 表示任意地址）连接并执行复制从服务器相关操作的权限，且权限范围是所有的数据库（*.* 表示所有数据库和所有表）
grant replication slave on *.* to 'salve'@'172.17.0.1';//授予用户 salve 从 IP 地址为 172.17.0.1 进行连接并执行复制从服务器相关操作的权限，权限范围是所有的数据库和所有的表
grant replication slave on *.* to 'salve'@'192.168.2.14';//此地同理

FLUSH PRIVILEGES; //强制重新加载权限表

alter user 'salve'@'%' identified with mysql_native_password by 'root';//修改用户 salve ，该用户可以从任何 IP 地址（% 代表任意地址）进行连接，更改其认证方式为 mysql_native_password ，并将密码设置为 root 。
alter user 'salve'@'172.17.0.1' identified with mysql_native_password by 'root';//修改用户 salve ，该用户只能从 IP 地址为 172.17.0.1 进行连接，将其认证方式更改为 mysql_native_password ，并将密码设置为 root 。
alter user 'salve'@'192.168.2.14' identified with mysql_native_password by 'root';//注意ip

然后执行下列代码

show master status;//显示主数据库服务器的当前二进制日志文件的状态信息。

 结果大致如上（由于博主是在公司电脑上测的，文章是回来写的忘给截图了，只能借用下别人的了）

记住这两个字段后边会用到

接着打开我们的从库（salve）按顺序执行以下代码

CHANGE MASTER TO 
MASTER_HOST = '192.168.2.14',  // 主库ip地址
MASTER_USER = 'salve', // 刚刚授权的账号
MASTER_PASSWORD = 'root',//刚刚授权的密码
MASTER_PORT = 3333,//主库的端口
MASTER_LOG_FILE='mysql-bin.000001',// 主库show master status后的 file值
MASTER_LOG_POS=2384,//主库 show master status后的position值
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000; 

start slave;  stop slave // 停止

完成后执行下列代码

show slave status;//显示从服务器的状态信息

//Slave_IO_State：描述从服务器的 I/O 线程的当前状态。
//Master_Host：主服务器的主机名或 IP 地址。
//Master_User：用于连接主服务器的用户名。
//Master_Port：主服务器的端口号。
//Connect_Retry：连接主服务器的重试间隔。
//Master_Log_File：从服务器正在读取的主服务器的二进制日志文件名。
//Read_Master_Log_Pos：从服务器在主服务器二进制日志中的读取位置。
//Relay_Log_File：从服务器的中继日志文件名。
//Relay_Log_Pos：在中继日志中的位置。
//Relay_Master_Log_File：对应的主服务器二进制日志文件名。
//Slave_IO_Running：表示从服务器的 I/O 线程是否正在运行，值为 Yes 或 No 。
//Slave_SQL_Running：表示从服务器的 SQL 线程是否正在运行，值为 Yes 或 No 。

当下列两个字段全为 YES 的时候 则表示成功  必须全为 YES 

如若第一个不是 yes 那就说明你可能哪一步错了 重新搞一下应该就可以了

 

 到此 主从数据库就结束了 我们就实现了主从数据库，但是呢从库只能进行查询操作，不能进行更改操作，一旦进行了更改操作那么两个数据库将不再同步 需要执行以下代码才可继续同步

//先停止
stop salve

//表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =l;

//启动
start salve

//查看
show slave status

//Slave_IO_Running: Yes 
//Slave_SQL_Running: Yes  
//当两个都为 yes的时候说明成功了

 接下来就是我们的主从数据库之主主数据库了

从上也不难理解所谓的主主也就是，二者互为主从关系，主库更改从库会跟着更改，从库更改主库也会跟着更改

其实通过上述主从数据库的操作也就大致能猜出主主数据库是怎么操作的了

 其实实现起来也很简单，那就是在从库也添加一个授权账号和密码，在主库在进行下配置就可以了。废话不多说直接上代码

首先在从库的查询中添加以下代码，同样需要依次执行。

CREATE USER zhu IDENTIFIED BY '123456';
CREATE USER 'zhu'@'172.17.0.1' IDENTIFIED BY '123456';
CREATE USER 'zhu'@'192.168.2.14' IDENTIFIED BY '123456';


grant replication slave on *.* to 'zhu'@'%';
grant replication slave on *.* to 'zhu'@'172.17.0.1';
grant replication slave on *.* to 'zhu'@'192.168.2.14';

FLUSH PRIVILEGES;

alter user 'zhu'@'%' identified with mysql_native_password by '123456';
alter user 'zhu'@'172.17.0.1' identified with mysql_native_password by '123456';
alter user 'zhu'@'192.168.2.14' identified with mysql_native_password by '123456';

show master status;

然后就是 主库里进行配置了 

CHANGE MASTER TO 
MASTER_HOST = '192.168.2.14',  
MASTER_USER = 'zhu', 
MASTER_PASSWORD = '123456',
MASTER_PORT = 3334,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=2330,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000; 

start slave;

stop slave

show slave status

其实你看到这也就明白，也就是进行了一次反向的主从绑定， 当然当你执行完最后一条代码看到两个YES的时候就说明，你已经成功了

码字不易，感谢大家三连支持

PS: 设置互为主从(即主主)的时候主键不要自增, 可能会出现主键冲突问题, 尽量自己设计主键的生成规则