MySQL主从复制

主从复制

通过将MySQL的某一台主机（master）的数据复制到其他主机（slaves）上，并重新执行一遍来执行复制过程中一台服务器充当主服务器，而其他一个或多个其他服务器充当从服务器。

为什么做主从

灾备
数据分布
负载平衡
读写分离
提高并发能力

基本原理

基于MySQL的二进制日志
主要包括了三个进程（2个I/O进程和1个SQL进程）
其中master(主)中一个SQL进程和一个I/O进程，slave(从)中一个I/O进程。

具体步骤

1. 当master库中的数据变化时，将数据变化记录到binlog(二进制文件)中，并创建log dump线程通知slave数据发送更改，要注意更新自己的数据信息。

2. I/O thread 线程是在slave中创建，其实用于请求master，master会返回binlog中记录的数据更新的日志的副本，之后slave将其保存到relay log(中继日志，中继日志也是记录数据更新的信息。)中。

3. SQL线程也是在Slave中创建的，当Slave检测到中继日志有更新，就会将更新的内容同步到Slave数据库中，这样就保证了主从的数据的同步

操作(配置一主一从)

准备工作：两台虚拟机，并均已安装好mysql，注意mysql的版本必须一致，并启用主库二进制日志文件，选择一个唯一的server-id并关闭从库的二进制日志文件，并在主机上创建具有复制权限的用户

打开配置文件找到[mysqld]，添加如下配置：

# master中
vim /etc/my.cnf

[mysqld]
log_bin = mysql-bin
server_id = 120

# slave 中
#log_bin = /data/mysql/data/mysql-bin
server_id = 130

注意：当我们每一次去修改配置文件时，必须重启服务，从而加载配置新的配置文件，无论什么服务均必须如此。

systemctl restart mysqld

1. 创建授权同步账号：

# 创建
create user 'rep7'@'%' identified by '123456';
# 授权
grant all privileges on *.* to rep7@'%';

# 为了安全起见在此，对数据库进行锁表只读
flush tables with read lock;

# 查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| mysql-bin.000008 |      197 |              |                  | 2c9133e1-1d42-11ed-ac9d-000c2953d507:1,
9175ec3c-10e3-11ed-a692-000c295420e2:1-10095 |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 记住 Position 的编号和mysql-bin的日志编号，在后面配置从服务器需要。

注意：在8的版本中必须先创建用户，才可以授权，在5中不需要如此，若8版本在创建用户时，遇到密码不符合时，可点击查看其中的密码插件修改即可

2. 备份主库的数据，并将备份文件传到slave中

# master 备份数据库
[root@master ~]# mysqldump -uroot -p123456 -A -B | gzip > /all_da.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master ~]# cd /
[root@master /]# ll
total 1056
-rw-r--r--.   1 root root 513027 Aug 23 11:25 all_da.gz


# 将备份文件传入slave中
[root@lmaster /]# scp all_da.gz 192.168.204.155:/
The authenticity of host '192.168.204.155 (192.168.204.155)' can't be established.
ECDSA key fingerprint is SHA256:db/6a40qGB7yJgwPMFVktWj9grJX7CgwdAuIjMRdUjA.
ECDSA key fingerprint is MD5:ed:a3:05:20:49:5d:e3:61:50:f6:14:49:42:34:1e:20.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.204.155' (ECDSA) to the list of known hosts.
root@192.168.204.155's password: 
all_da.gz                                                        100%  501KB  80.1MB/s   00:00 



# 从库还原数据
[root@slave /]# zcat all_da.gz | mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.

# 登录数据库并查看是否拥有主服务器的数据
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| cf                 |
| db1                |
| information_schema |
| it                 |
| myemployees        |
| mysql              |
| performance_schema |
| sys                |
| t                  |
| test               |
| work               |
+--------------------+

注意：在这之前一定需要关闭服务器的防火墙 systemctl stop firewalld.service
3. 设置从库同步

mysql> change master to
    -> MASTER_HOST='192.168.204.156',
    -> MASTER_PORT=3306,
    -> MASTER_USER='rep7',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000008',
    -> MASTER_LOG_POS=197;
Query OK, 0 rows affected, 9 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to source
                  Master_Host: 192.168.204.156
                  Master_User: rep7
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 197
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4. 测试主从同步：
   主库创建一个数据库：

[root@lmaster /]# mysql -uroot -p -e 'create database test_m_s;'

从库检查：

[root@slave /]# mysql -uroot -p -e 'show databases;' |grep "test_m_s"