1. 主从原理
1.1 主从介绍
所谓 mysql 主从就是建立两个完全一样的数据库，其中一个为主要使用的数据库，另一个为次要的数据库，一般在企业中，存放比较重要的数据的数据库服务器需要配置主从，这样可以防止因数据库服务器宕机导致数据丢失，还能保证业务量太多、数据太多和访问人数太多时服务的质量（服务器响应速度），还能提供故障切换、读写分离、和备份等等功能
1.2 主从作用
实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务
1.3 主从形式
一主一从
主主复制：当作备份使用，当主服务器出现故障时，另一个主服务器会自动顶上。
一主多从：用来实现读写分离，当写操作较少时，读操作较多时使用，主服务器用来实现写操作，从服务器用来实现读操作。
多主一从：用来实现读写分离，当写操作较多时，读操作较少时使用，主服务器用来实现写操作，从服务器用来实现读操作。5.7 开始支持
联级复制：是指从主场地复制过来的又从该场地再次复制到其他场地，即 A 场地把数据复制到 B 场地，B 场地又把这些数据或其中部分数据再复制到其他场地。
1.4 主从复制原理
主从复制步骤：
主库将所有的写操作记录到 binlog 日志中并生成一个 log dump 线程，将 binlog 日志传给从库的 I/O 线程
从库生成两个线程，一个 I/O 线程，一个 SQL 线程
I/O 线程去请求主库的 binlog，并将得到的 binlog 日志写到 relay log（中继日志） 文件中
SQL 线程，会读取 relay log 文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的
2. 主从复制配置
主从复制配置步骤：
确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库（修改配置文件）
配置从数据库（修改配置文件）
需求：
搭建两台 MySQL 服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
环境说明：
数据库角色 IP 应用与系统版本 有无数据
主数据库 192.168.23.4 Centos7.8 mysql-5.7 有数据
从数据库 192.168.23.5 Centos7.8 mysql-5.7 无数据2.1Mysql安装
注意：需把安装包分别上传到两台服务器上分别执行下列命令进行安装
[root@localhost soft]# yum remove mysql-libs
[root@localhost soft]# rpm -qa | grep mariadb进行安装
将下载好的mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar上传
安装相应软件
[root@localhost soft]# yum install -y openssl-devel.x86_64 openssl.x86_64 yum install -y libaio.x86_64 libaio-devel.x86_64 yum install -y perl.x86_64 perl-devel.x86_64 yum install -y perl-JSON.noarch yum install -y autoconf yum install -y wget yum install -y net-tools
关闭防火墙
[root@localhost soft]# systemctl stop firewalld
[root@localhost soft]# systemctl disable firewalld
解压软件包：
[root@localhost soft]# tar -xvf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar[root@localhost soft]# rpm -ivh mysql-community-common-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-libs-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-libs-compat-5.7.22-1.el7.x86_64.rpm
[root@localhostsoft]#rpm -ivh mysql-community-embedded-compat-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-devel-5.7.22-1.el7.x86_64.rpm
[root@localhost soft]# rpm -ivh mysql-community-test-5.7.22-1.el7.x86_64.rpm
查看安装版本：
[root@localhost soft]# mysql -V编辑配置文件
vim /etc/my.cnf
#跳过登录验证 无需密码即可登录mysql
skip-grant-tables
# 设置默认字符集UTF-8
character_set_server=utf8
collation-server=utf8_general_ci
# 设置默认字符集UTF-8
init_connect='SET NAMES utf8'
# 设置数据库日志过期天数为14天
server_id=1
expire_logs_days=14
[client]
default-character-set=utf8
##添加
skip-grant-tables
character_set_server=utf8
collation-server=utf8_general_ci
init_connect='SET NAMES utf8'
server_id=1
expire_logs_days=14
[client]
default-character-set=utf8启动mysql
[root@localhost soft]# systemctl start mysqld.service
[root@localhost soft]# systemctl status mysqld获取mysql的root用户的初始密码
[root@localhost soft]# grep 'temporary password' /var/log/mysqld.log以获取mysql的root用户的初始密码登录数据库
[root@localhost soft]# mysql -u root -pAcqWlI64o:kG修改root密码
mysql> flush privileges;
mysql> set password for root@localhost=password('123456');
使密码即时生效
mysql> flush privileges;
允许以root身份远程登录mysql
mysql> grant all privileges on *.* to root@'%' identified by "123456";
mysql> flush privileges;2.2 mysql 主从配置
2.2.1 确保从数据库于主数据库的数据一样
为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中
//主库为master
[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# bash
[root@master ~]# //从库为slave
[root@localhost ~]# hostnamectl set-hostname slave
[root@localhost ~]# bash
[root@slave ~]# //先查看主库有哪些库
[root@master soft]# 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 |
| test |
+--------------------+
//在查看从库
[root@slave soft]# 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 soft]# mysqldump -uroot -p123456 --all-databases > /opt/acc-2022.sql;
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# ls /opt/[root@master soft]# scp /opt/acc-2022.sql root@192.168.23.5:/opt/ //解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye
[root@master ~]#
//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@slave ~]# mysql -uroot -p123456 < /opt/acc-2022.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[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 |
| test |
+--------------------+
[root@slave ~]#2.2.3 配置主数据库
[root@master soft]# vim /etc/my.cnf
//在[mysql]这段后面添加
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=mysql-bin //添加 启用binlog日志
server-id=1 //添加 数据库服务器唯一标识符，主库的server-id值必须比从库的小
# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid //重启数主库的mysql服务
[root@master soft]# systemctl restart mysqld
[root@master soft]# ss -anlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 [::]:3306 [::]:*
[root@master soft]#
//查看主库状态
[root@master soft]# mysql -uroot -p123456
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.22-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>2.2.4 配置从数据库
[root@slave ~]# vim /etc/my.cnf
//在[mysql]这段后面添加
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
server-id=2 //添加 从库的server-id比主库的大
relay-log=mysql-relay-bin //添加
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid//重启mysql服务
[root@slave ~]# systemctl restart mysqld
[root@slave ~]# ss -anlt
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:22 *:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 80 [::]:3306 [::]:*
[root@slave ~]#
//配置并启动主从复制
[root@slave ~]# mysql -u root -p123456
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.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> change master to
-> master_host='192.168.23.4',
-> master_user='hqd',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
//查看服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.23.4
Master_User: hqd
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:3 测试验证
在主服务器创建‘hqd’名称的库 查看从服务器是否同步
mysql> create database hqd;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hqd |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql>在从数据库中查看数据是否同步
[root@slave ~]# mysql -uroot -p123456
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 5
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hqd |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql>
