SQL优化 - 主从同步（原理+示例）

以下操作是建立在Windows和Linux各自已经安装了MySQL

授权访问远程连接数据库

授权访问： A -> B 则在B计算机的MySQL执行以下命令

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mysqladmin' WITH GRANT OPTION;
FLUSH PRIVILEGES;

如果仍然报错： 可能是防火墙没关闭；在B关闭防火墙 service iptables stop
出现远程连接问题参考： https://www.cnblogs.com/openmind-ink/p/14942802.html

🍎主从同步（主从复制）原理

1. master将改变的数据记录在本地二进制日志中(binary log): 该过程称之为： 二进制日志事件
2. slave将master的binary log拷贝到自己的relay log(中继日志文件) 中
3. 中继日志事件将数据读取到自己的数据库之中

MySQL主从复制时异步的，串行化的，有延迟 master:slave = 1:n

🔧实现主从复制

1. 在主结点中的配置(windows设置主结点)

Windows(mysql: my.ini) , Linux(mysql: my.cnf)

	配置前，为了无误，先将权限(远程访问)、防火墙等处理：
		关闭windows/linux防火墙： windows：右键“网络”   ,linux: service iptables stop
		🍎 Mysql允许远程连接(windowos/linux)：
			GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; -- 第一rootmaster上DB账号第二个root是master上DB的密码
			FLUSH PRIVILEGES;

在主结点(master-windows)中

my.ini
[mysqld]
#id
server-id=1
#二进制日志文件（注意是/  不是\）
log-bin="D:/MySQL/MySQL Server 5.5/data/mysql-bin"
#错误记录文件
log-error="D:/MySQL/MySQL Server 5.5/data/mysql-error"
#主从同步时 忽略的数据库
binlog-ignore-db=mysql
#(可选)指定主从同步时，同步哪些数据库
binlog-do-db=testdb

【大哥找小弟】Windows中的数据库服务器授权哪台计算机中的数据库时自己的从数据库

• 在MySQL5版本中

GRANT REPLICATION slave,reload,super ON *.* TO 'root'@'192.168.2.%' IDENTIFIED BY 'xxx'; 
-- root: 目标服务器的账户名称
-- 192.168.2.%: 目标服务器的IP地址
-- xx 是远程服务器中的数据库服务器的密码
 

• 在MySQL8版本中

在MySQL8版本中如果执行上面的语句会报' ... for the right syntax to use near 'identified by 'password' with grant option'错误

-- mysql版本8，在给新用户授权时，发生了变化：
#创建账户
create user 'root'@'%' identified by  'password'
 
#赋予权限，with grant option这个选项表示该用户可以将自己拥有的权限授权给别人
grant all privileges on *.* to 'root'@'%' with grant option
 
#改密码&授权超用户，flush privileges 命令本质上的作用是将当前user和privilige表中的用户信息/权限设置从mysql库(MySQL数据库的内置库)中提取到内存里
flush privileges;
 
	查看主数据库的状态（每次在左主从同步前，需要观察 主机状态的最新值）
		show master status;  （mysql-bin.000001、 107）
 
从机（以下代码和操作 全部在从机linux中操作）：
 
 
-- 原因分析 ：mysql8版本把将创建账户和赋予权限分开了。
-- 创建账户::create user ‘用户名’@‘访问主机’ identified by ‘密码’;
-- 赋予权限:grant 权限列表 on 数据库 to ‘用户名’@‘访问主机’ ;
-- with grant option这个选项表示该用户可以将自己拥有的权限授权给别人
 

2. 在子节点中的配置(Linux设置子结点)

在/etc/my.cnf中配置

[mysqld]
server-id=2
log-bin=mysql-bin
#(可选)指定主从同步时，同步哪些数据库
replicate-do-db=test

linux中的数据 授权哪台计算机中的数控 是自己的主计算机

🍎【先在主节点中】
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      673 | test         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
之后根据file的名称和Position的值进行配置
🍎【小弟找大哥】【后在子节点中根据主节点的file名称和Position进行配置】
CHANGE MASTER TO 
MASTER_HOST = '192.168.2.2', 
MASTER_USER = 'root', 
MASTER_PASSWORD = 'mysqladmin', 
MASTER_PORT = 3306,
master_log_file='mysql-bin.000006',
master_log_pos=673;
 
	-- 如果报错：This operation cannot be performed with a running slave; run STOP SLAVE first
        -- 解决：STOP SLAVE ;再次执行上条授权语句

在子节点配置完成后可以通过以下命令查看主节点的信息

开启主从同步

开启主从同步：
	从机linux:
	start slave ;
	检验  show slave status \G	主要观察： Slave_IO_Running和 Slave_SQL_Running，确保二者都是yes；如果不都是yes，则看下方的 Last_IO_Error。
本次 通过 Last_IO_Error发现错误的原因是 主从使用了相同的server-id， 检查:在主从中分别查看serverid:  show variables like 'server_id' ;
	可以发现，在Linux中的my.cnf中设置了server-id=2，但实际执行时 确实server-id=1，原因：可能是 linux版Mysql的一个bug，也可能是 windows和Linux版本不一致造成的兼容性问题。
	解决改bug： set global server_id =2 ;
 
	stop slave ;
	 set global server_id =2 ;
	start slave ;
	 show slave status \G
 
	演示：
	主windows =>从
 
	windows:
	将表，插入数据  
	观察从数据库中该表的数据
 

测试主从同步

主从同步遇到 Got fatal error 1236 from master when reading data from binary log: 'Could not find first log...: https://blog.csdn.net/weixin_30602505/article/details/98448322

🍎测试slave_io_running和slave_sql_running是否接通

🍎测试数据的变化