mysql主从同步

m0_73799676

已于 2024-08-27 00:51:28 修改

阅读量878

点赞数 17

文章标签： mysql

于 2024-08-24 23:25:18 首次发布

本文链接：https://blog.csdn.net/m0_73799676/article/details/141503226

版权

使用的ip地址

ip地址
192.168.101.65（主）
192.168.101.75
192.168.101.85

安装mysql

wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
sudo yum install mysql-server

进入mysql

mysql -uroot

会报错
Can 't connect to local MySQL server through socket '/var/lib/mysql.sock '(2)

修改/var/lib/mysql的拥有者

sudo chown -R miaoruntu:miaoruntu /var/lib/mysql

重启服务，再次进入mysql就不会报错了

service mysqld restart

修改主节点的配置文件

vi /etc/my.cnf

添加以下内容

#服务器唯一ID
server-id=1
启用二进制日志,指名路径
log-bin=mysql-bin
#0表示读写，1表示只读
read-only=0
#日志文件保留的时长，单位天
expire_logs_days=1
#设置binlog格式
binlog_format=STATEMENT

binlog格式设置：
STATEMENT：基于SQL语句的复制
ROW：基于行的复制
MIXED：混合模式复制

修改从节点配置文件

同样的路径，添加以下内容

server-id=2
#server-id=3
read-only=1
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index

添加用户

在主节点添加用户（@分割用户名和主机名，左边是用户名，右边是主机名，‘%’ 是一个通配符，表示"任何主机"。该用户可以从任何IP地址或主机名连接到MySQL服务器，也可以限定ip地址，eg:‘supeizhi’@‘192.168.101.75’）
REPLICATION：允许用户查看复制状态和二进制日志信息
SLAVE：维护主从复制环境

GRANT REPLICATION SLAVE ON *.* TO 'supeizhi'@'%' IDENTIFIED BY 'supeizhi123';
FLUSH PRIVILEGES;

获取主节点服务器状态（记住file和position的值）

SHOW MASTER STATUS;

在这里插入图片描述

配置从节点连接到主节点

file和position看上面

CHANGE MASTER TO
  MASTER_HOST='192.168.101.65',
  MASTER_USER='supeizhi',
  MASTER_PASSWORD='123',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=120;

启动从节点复制进程

START SLAVE;

检查复制状态（确保Slave_IO_Running和Slave_SQL_Running都是Yes）

SHOW SLAVE STATUS\G

在这里插入图片描述

测试

在主节点添加test_table表

CREATE TABLE test_table (id INT PRIMARY KEY, name VARCHAR(50));

从节点
在这里插入图片描述

在主节点执行插入语句

INSERT INTO test_table (id, name) VALUES (1, 'Test');

从节点
在这里插入图片描述

在从节点执行插入语句

INSERT INTO test_table (id, name) VALUES (2, 'Slave');

在这里插入图片描述
会发现执行成功了，查看只读设置

SHOW VARIABLES LIKE 'read_only';

在这里插入图片描述
只读开启了，检查当前用户权限

SHOW GRANTS FOR CURRENT_USER();

在这里插入图片描述
是root用户，带所有权限，所以可以执行成功（配置文件的只读对普通用户生效，对具有super权限的用户不生效，如果将root也设置成只读，从库失去写操作，从库虽然可以读取binlog但是不能应用binlog，数据不会发生变化，相当于阻止了主从同步）
退出登陆到supeizhi账号

exit;
mysql -h 192.168.101.65 -u supeizhi -p

在这里插入图片描述
每个 MySQL 实例（无论是主节点还是从节点）都有自己独立的用户管理系统。在主节点上创建的用户账号不会自动复制或同步到从节点。

在从节点创建用户

CREATE USER 'supeizhi'@'%' IDENTIFIED BY '123';
CREATE USER 'supeizhi'@'rabbitmq02‘ IDENTIFIED BY '123';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'supeizhi'@'%';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'supeizhi'@'rabbitmq02';
FLUSH PRIVILEGES;

supeizhi@rabbitmq02只能通过本地登录，而supeizhi@%可以通过任意主机登录。这两个账户需要同时存在。如果没有本地账户supeizhi@rabbitmq02，使用supeizhi帐户登录时，mysql_install_db将优先考虑创建一个本地的匿名帐户，匿名帐户拥有一个比supeizhi@%账户更加具体的host字段值。
（之前设置了主机名为rabbitmq02）

没有创建supeizhi@rabbitmq02
在这里插入图片描述
创建以后，用密码登陆

use mysql;

在这里插入图片描述
执行以下指令授权

GRANT SELECT ON mysql.* TO 'supeizhi'@'%';
GRANT SELECT ON mysql.* TO 'supeizhi'@'rabbitmq02';
FLUSH PRIVILEGES;

在这里插入图片描述
从节点执行语句

INSERT INTO test_table (id, name) VALUES (3, 'supeizhi');

在这里插入图片描述
从节点只读

主从库结构差异

1.索引
主库可以减少一些只用于读操作的索引（涉及到where的写操作加索引效率更高），从库可以加一些在主库上可能影响性能的索引（多列的大型复合索引、全文索引）

2.存储引擎
主库使用InnoDB引擎（支持事务、崩溃恢复即redo log），从库可以使用读性能更好的MyISAM（表级锁、索引和数据分开存储）

3.临时表
主库写操作，可以将临时表放到从库读操作里

4.线程
主库有binlog dump线程，从库没有（如果从库升主库有或者作为别的从库的主库）
主库没有IO线程和SQL线程，这两个线程存在于从库中（如果主库也作为别的主库的从库就有这俩线程）。IO线程主要负责连接到主库，请求binlog日志，并将接收到的二进制日志事件写入到从库的中继日志（relay log）中。SQL线程负责读取中继日志中的事件，并在从库上重放这些事件，从而使从库的数据与主库保持同步。
IO线程：单线程，保证事务顺序
SQL线程：多线程，提高执行效率（写集合，跟踪每个事务修改的行。允许不冲突的事务并行执行，提交时遵循逻辑时钟顺序，冲突的事务按照逻辑时钟串行执行）

5.检查点
主库相比于从库有更多的检查点。频繁的检查点可以防止脏页累计过多、缩短恢复时间并控制主从复制延迟。从库的检查点主要用于中断恢复，频繁的检查点反而影响读性能。