主从配置
主从简介
1.1主从作用
- 实时灾备,用于故障切换
- 读写分离,提供查询服务
- 备份,避免影响业务
1.2主从形式
- 一主一从
- 主主复制
- 一主多从—扩展系统读取的性能,因为读是在从库读取的
- 多主一从—5.7开始支持
- 联级复制
2. 主从复制原理
- 主库将所有的写操作记录到binlog日志中并生成一个log dump线程,将binlog日志传给从库的I/O线程
- 从库生成两个线程,一个I/O线程,一个SQL线程
- I/O线程去请求主库的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中
- SQL线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,达到最终数据一致的目的
主库会将用户的操作写到log日志中,当从库需要的时候从库的I/O线程会向主库发出请求 然后将主库的log日志传到从库让后写道(relay log)中继日志里 从库的SQL线程会将中继日志里的内容解析成具体的操作 来实现主从操作一致,到达主从数据库数据一致的需求。
3. 主从复制配置
主从复制配置步骤:
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库(修改配置文件)
- 配置从数据库(修改配置文件)
需求:
搭建两台MySQL服务器,一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作
关掉防火墙
[root@localhost ~]# systemctl disable --now firewalld.service
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@localhost ~]# setenforce 0
[root@localhost ~]# vi /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled //更改此行为disabled
# SELINUXTYPE= can take one of these three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
设置开机自启,并启动服务
[root@localhost ~]# systemctl status mysqld
?mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vend>
Active: inactive (dead)
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
[root@localhost ~]# systemctl enable --now mysqld
[root@localhost ~]# systemctl status mysqld
......
改密码
[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-08-01T14:05:18.134855Z 1 [Note] A temporary password is generated for root@localhost: lqp54=+PsEx)
[root@localhost ~]# mysql -uroot -p'lqp54=+PsEx)'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.37
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set password = password('1@');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set password = password('couldrush@');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -pcouldrush@
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.37 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
环境说明:
数据库角色 | ip | 应用与系统版本 | 有无数据 |
---|---|---|---|
主数据库 |