MySQL 主从-CSDN博客

本文链接：https://blog.csdn.net/weixin_46632489/article/details/105978007

MySQL 主从

MySQL 主从

MySQL 主从

1.主从简介

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，在B上则会自动更新数据，实现两者数据实时同步。

2.主从作用

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

3.主从形式

一主一从
主主复制
一主多从——扩展系统读取的性能，因为读是在从库读取的。
多主一从——5.7开始支持
联级复制

4.主从复制原理

主从复制原理图：
主从复制步骤：
1、主上面把所有的写操作记录到binlog日志中并生成一个log dump线程，然后将binlog日志传给从库的I/O线程。
2、从库生成两个线程，一个I/O线程，一个SQL线程：
I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log(中继日志)文件中；
SQL线程会读取relay log文件中的日志，并解析成具体的操作，来实现主从的操作一致，从而达到最终数据一致的目的。

5.主从复制配置

5.1 配置步骤与环境说明

主从复制配置步骤：

确保从数据库与主数据库里的数据一样
在主数据库里创建一个同步账号授权给从数据库使用
配置主数据库（修改配置文件）
配置从数据库（修改配置文件）

需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作。
环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.206.129	centos7/redhat7 mysql-5.7	有数据
从数据库	192.168.206.130	centos7/redhat7 mysql-5.7	无数据

5.2 MySQL 安装

服务端与客户端都使用二进制进行安装

**//服务端与客户端安装操作相同，这里只展示服务端安装过程**
**//下载二进制格式的MySQL软件包**
[root@localhost ~]# cd /usr/src/ 
[root@localhost src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.25-linux-glibc2.12x86_64.tar.gz 
………下载过程略

 **//创建用户和组**
[root@localhost ~]# groupadd -r mysql 
[root@localhost ~]# useradd -M -s /sbin/nologin -g mysql mysql

**//解压MySQL二进制软件包到指定位置**
[root@localhost src]# ls 
debug  kernels  mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz 
[root@localhost src]# tar xf mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz 
-C /usr/local/ 
[root@localhost ~]# ls /usr/local/ 
bin  games    lib    libexec    sbin   src   share
etc  include  lib64  mysql-5.7.22-linux-glibc2.12-x86_64   

**//创建软链接**
[root@localhost ~]# cd /usr/local/ 
[root@localhost local]# ln -sv mysql-5.7.25-linux-glibc2.12-x86_64/ mysql
‘mysql’ -> ‘mysql-5.7.25-linux-glibc2.12-x86_64/’ 

**//修改/usr/local/mysql的属主和属组** 
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql 

**//创建环境变量**
[root@localhost ~]# ls /usr/local/mysql 
bin  COPYING  docs  include  lib  man  README  share  support-files
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh 
[root@localhost ~]# source  /etc/profile.d/mysql.sh 

**//创建数据存放目录**
[root@localhost ~]# mkdir /opt/data 
[root@localhost ~]# chown -R mysql.mysql /opt/data/ 
[root@localhost ~]# ll  -d /opt/data 
drwxr-xr-x 2 mysql mysql 6 Aug 14 16:54 data

**//初始化数据库**
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/opt/data/
2018-08-15T07:57:46.168380Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-15T07:57:50.542516Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-08-15T07:57:50.927286Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-08-15T07:57:51.071260Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: e8600890-a060-11e8-b1a2-000c294c50b4.
2018-08-15T07:57:51.074566Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-08-15T07:57:51.078089Z 1 [Note] A temporary password is generatedfor root@localhost: hfGdwnViq6,,
请注意，这个命令的最后会生成一个临时密码，此处密码是hfGdwnViq6,,
密码是随机的，每个人的密码都不同！！！

**//生成配置文件**
[root@localhost ~]# cat > /etc/my.cnf <<EOF 
> [mysqld] 
> basedir = /usr/local/mysql 
> datadir = /opt/data 
> socket = /tmp/mysql.sock 
> port = 3306 
> pid-file = /opt/data/mysql.pid 
> user = mysql 
> skip-name-resolve 
> EOF

**//配置服务启动脚本**
[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 
[root@localhost ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld 
[root@localhost ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

**//启动mysql（默认端口：3306）**
[root@localhost ~]# service mysqld start
Starting MySQL.. SUCCESS!  
[root@localhost ~]# ss -antl   
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port
LISTEN      0      128         *:22                      *:*
LISTEN      0      100    127.0.0.1:25                      *:*
LISTEN      0      128        :::22                     :::*
LISTEN      0      100       ::1:25                     :::*
LISTEN      0      80         :::3306                   :::* 

**//修改密码**
**//首次登陆使用临时密码登录**
[root@localhost ~]# mysql -uroot -p’hfGdwnViq6,,‘
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

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> set password = password('12345');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye

**//使用新密码登录**
[root@localhost ~]# mysql -uroot -p12345
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

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>

5.3 MySQL 主从配置

5.3.1 实现主数据库与从数据库数据相同

//为了实现主库与从库的数据同步需要关闭服务端与客户端的防火墙。
[root@localhost ~]# systemctl  stop  firewalld
[root@localhost ~]# systemctl  disable  firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@localhost ~]# vim  /etc/selinux/config
 **//将第六行中的 enforcing 改为 disabled**
# 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      

[root@localhost ~]# setenforce   0

**//查看主数据库，并全备主数据库**
[root@localhost ~]# mysql  -uroot  -p12345
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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 |
| mysql              |
| performance_schema |
| spider             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use spider;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show  tables;
+------------------+
| Tables_in_spider |
+------------------+
| lpl              |
| man              |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from lpl;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | list      |   20 |
+----+-----------+------+
6 rows in set (0.00 sec)

mysql> select * from man;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  1 | kitty    |   16 |
|  2 | hello    |   18 |
|  3 | jerry    |   20 |
|  4 | boy      |   99 |
|  5 | girl     |   88 |
|  6 | dhkajshd |   12 |
+----+----------+------+
6 rows in set (0.00 sec)

mysql> exit
Bye

**//全备主库时需要用服务端ip在开一个终端，给主数据库加上读锁，避免备份期间有其他人在写入数据导致数据不一致**。
mysql> flush  tables with read lock;
Query OK, 0 rows affected (0.00 sec)

**//全备主库并将备份文件传给从库**
[root@localhost ~]# mysqldump  -uroot  -p12345 --all-databases  > /root/all-2020.sql
[root@localhost ~]# ls
all-2020.sql  anaconda-ks.cfg 
[root@localhost ~]# scp  all-2020.sql  root@192.168.206.130:/root
root@192.168.206.130's password: 
all-2020.sql                           100%  775KB  21.0MB/s   00:00    

**//解除额外开的终端上的读锁状态，直接退出。**
mysql> exit
Bye

**//在从库上恢复主库的全备，从而保持主库与从库的数据一致**
[root@super ~]# ls
all-2020.sql  anaconda-ks.cfg
[root@super ~]# ls
all-2020.sql  anaconda-ks.cfg
[root@super ~]# mysql -uroot  -p12345 < all-2020.sql 
[root@super ~]# mysql  -uroot  -p12345
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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 |
| mysql              |
| performance_schema |
| spider             |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql>

5.3.2 配置主数据库

**//在主库上创建一个同步账户授权给从库使用**
mysql> create user 'pig'@'192.168.206.130' identified by '12345';
Query OK, 0 rows affected (0.00 sec)

mysql> grant   replication slave on *.*  to 'pig'@'192.168.206.130';
Query OK, 0 rows affected (0.00 sec)

mysql> flush  privileges;
Query OK, 0 rows affected (0.00 sec)

**//修改主数据库配置（缺什么加什么，多的不影响。）**
[root@localhost ~]# vim  /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data/
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql-bin
server-id=1
symbolic-links=0
log-error=/var/log/mysqld.log

**//重启数据库**
[root@localhost ~]# service mysqld  restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@localhost ~]# ss  -antl
State      Recv-Q Send-Q           Local Address:Port                          Peer Address:Port              
LISTEN     0      128                          *:22                                       *:*                  
LISTEN     0      100                  127.0.0.1:25                                       *:*                  
LISTEN     0      128                         :::22                                      :::*                  
LISTEN     0      100                        ::1:25                                      :::*                  
LISTEN     0      80                          :::3306                                    :::*                  

**//查看主库状态**
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>

5.3.2 配置从数据库

**//修改从数据库配置（少什么加什么，多的不影响。）**
[root@super ~]# vim  /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data/
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=2
relay-log=mysql-relay-bin
symbolic-links=0
log-error=/var/log/mysqld.log

**//重启数据库**
[root@super ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@super ~]# ss  -antl
State      Recv-Q Send-Q           Local Address:Port                          Peer Address:Port              
LISTEN     0      128                          *:111                                      *:*                  
LISTEN     0      128                          *:22                                       *:*                  
LISTEN     0      100                  127.0.0.1:25                                       *:*                  
LISTEN     0      128                         :::111                                     :::*                  
LISTEN     0      128                         :::22                                      :::*                  
LISTEN     0      100                        ::1:25                                      :::*                  
LISTEN     0      80                          :::3306                                    :::*                  

**//配置并启动主从复制**
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.206.129',
    -> MASTER_USER='pig',
    -> MASTER_PASSWORD='12345',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.61 sec)

mysql> start  slave;
Query OK, 0 rows affected (0.00 sec)

**//查看服务器状态**
mysql> show  slave status  \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.206.129
                  Master_User: pig
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: YES     **//此处必须是YES**
            Slave_SQL_Running: YES	   **//此处必须是YES**
              Replicate_Do_DB: 
			…………
			…………
			…………

5.3.3 测试检验

**//在主库上创建一个数据库**
[root@localhost ~]# mysql  -uroot  -p12345
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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 |
| mysql              |
| performance_schema |
| spider             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> create  database lpl;
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| spider             |
| sys                |
| lpl                |
+--------------------+
6 rows in set (0.00 sec)

**//从数据库上实现数据同步**
[root@super ~]# mysql  -uroot  -p12345
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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 |
| mysql              |
| performance_schema |
| spider             |
| sys                |
| lpl                |
+--------------------+
6 rows in set (0.00 sec)