MYSQL主从
文章目录
1.主从介绍
所谓mysql主从就是建立两个完全一样的数据库,其中一个为主要使用的数据库,另一个为次要的数据库,一般在企业中,存放比较重要的数据的数据库服务器需要配置主从,这样可以防止因数据库服务器宕机导致数据丢失,还能保证业务量太多、数据太多和访问人数太多时服务的质量(服务器响应速度),还能提供故障切换、读写分离、和备份等等功能
2.主从复制配置
主从复制配置步骤:
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库(修改配置文件)
- 配置从数据库(修改配置文件)
需求:
搭建两台MySQL
服务器,一台作为主服务器,一台作为从服务器,主服务器进行写操作,从服务器进行读操作
环境说明:
数据库角色 | IP | 应用与系统版本 | 有无数据 |
---|---|---|---|
主数据库 | 192.168.48.150 | centos8/redhat8 mysql-5.7 | 有数据 |
从数据库 | 192.168.48.151 | centos8/redhat8 mysql-5.7 | 无数据 |
2.1mysql安装
分别在主从两台服务器上安装mysql-5.7
版
[root@localhost ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
[root@localhost ~]# yum module disable mysql //禁用mysql
[root@localhost ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
//安装完后设置开机自启动
[root@localhost ~]# systemctl enable --now mysqld
//关闭防火墙与selinux
[root@localhost ~]# systemctl disable --now firewalld
[root@localhost ~]# setenforce 0
[root@localhost ~]# vim /etc/selinux/config
SELINUX=disabled
//在日志中找出密码
[root@localhost ~]# grep "password" /var/log/mysqld.log
2022-08-02T00:50:49.548909Z 1 [Note] A temporary password is generated for root@localhost: dZ6U,0hj=tX9
//使用临时密码登录mysql
[root@localhost ~]# mysql -uroot -pdZ6U,0hj=tX9
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 2
Server version: 5.7.39
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> //登陆成功
//修改mysql登录密码
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> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
//为避免mysql自动升级,需要卸载最开始安装的yum源
[root@localhost ~]# rpm -e mysql57-community-release
2.2 mysql主从配置
2.2.1 确保从数据库于主数据库的数据一样
为确保从数据库与主数据库里的数据一样,先全备主数据库并还原到从数据库中
//主库
[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# bash
[root@master ~]#
//从库
[root@localhost ~]# hostnamectl set-hostname slave
[root@localhost ~]# bash
[root@slave ~]#
//先查看主库有哪些库
[root@master ~]# mysql -uroot -p123456 -e'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
//在查看从库
[root@slave ~]# mysql -uroot -p123456 -e'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
//全备主库
//全备主库时需要另开一个终端,给数据库加上读锁,避免在备份期间有其他人在写入导致数据不一致
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
//此锁表的终端必须在备份完成以后才能退出
//备份主库并将备份文件传送到从库
[root@master ~]# mysqldump -uroot -p123456 --all-databases > /opt/all-2022.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# ls /opt/
all-2022.sql
[root@master ~]# scp /opt/all-2022.sql root@192.168.48.151:/opt/
The authenticity of host '192.168.48.151 (192.168.48.151)' can't be established.
ECDSA key fingerprint is SHA256:tpW2ftwuhQj+MLGZs/ZmJ7SLF6T48bYluZQ/XYm3wBY.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.48.151' (ECDSA) to the list of known hosts.
root@192.168.48.151's password:
all-2022.sql 100% 859KB 40.1MB/s 00:00
//解除主库的锁表状态,直接退出交互式界面即可
mysql> quit
Bye
[root@master ~]#
//在从库上恢复主库的备份并查看从库有哪些库,确保与主库一致
[root@slave ~]# mysql -uroot