MySQL集群之 主从复制 主主复制 一主多从 多主一丛 实现方式

一. 安装MySQL

1.更换yum源:

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

具体的更换方法可参考我的另一篇博文:
#CentOS 7更换阿里和清华大学yum源
https://editor.csdn.net/md/?articleId=107546030

2. 下载repo配置文件:

wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

3. 安装yum源:

rpm -ivh mysql57-community-release-el7-9.noarch.rpm

4. 进入/etc/yum.repos.d/目录执行安装:

yum install mysql-server

因为MySQL服务器在国外,下载十分缓慢,如果你懂得下面我说的技术,可参考我的另一篇博文,实现下载加速:

#CentOS 7桥接模式访问本地SSR
https://blog.csdn.net/fengxueliuke/article/details/107570055

5. 启动:

systemctl start mysqld

6. 查看临时密码:

grep 'temporary password' /var/log/mysqld.log

#如果查不到临时密码,按照以下步骤,执行命令尝试:
#删除mysql残留的数据
rm -rf /var/lib/mysql
#重新启动mysql,会重新生成
systemctl restart mysqld
grep 'temporary password' /var/log/mysqld.log

7. 登陆:

mysql -u root -p

8. 修改密码:

ALTER USER 'root'@'localhost' IDENTIFIED BY '@Abcd1234';

9. 开启远程连接:

#root 用户名,% 所有人都可以访问 password 密码
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '@Abcd1234' WITH GRANT OPTION;

#重新加载
FLUSH PRIVILEGES;

10. 修改编码:

#修改etc目录下的my.cnf文件,添加下面两句话:
character_set_server=utf8 
init_connect='SET NAMES utf8'

11. 补充命令:

#停止mysql
systemctl stop mysqld 
#重启MySQL
systemctl restart mysqld 
#设置开机启动
systemctl enable mysqld 
#关闭开机启动
systemctl disable mysqld 

二. 什么是数据库二进制文件

在了解数据库集群之前呢,我们需要了解MySQL的二进制日志文件,如何通过二进制文件恢复数据库数据.二进制日志(log-bin 日志) :所有对数据库状态更改的操作(create、 drop、 update 等)。

1. 开启二进制文件:

#执行下面的命令
vi /etc/my.cnf
#在配置文件中添加以下指令
log-bin=mysql-bin
#server-id一般为ip结尾
server-id=171             

2.查看生成的二进制文件

开启之后,我们在/var/lib/mysql目录会发现一个叫mysql-bin.000001的文件:

3. 查看mysql-bin.000001文件内容:

mysqlbinlog  二进制文件名

4. 创建测试数据库

创建一个测试数据库,建立一个表,往表中新增几条数据.

5.再次查看二进制文件:

我们对应的操作就已经被记录了下来.

6. 删除数据库文件,模拟数据丢失.

7. 根据二进制文件还原数据:

有两种方式,一种是根据时间还原,第二种是根据大小还原.

⑴. 按照时间还原指令:

--start-datetime  #开始时间
--stop-datetime   #结束时间
格式:mysqlbinlog --start-datetime 'yyyy-MM-dd HH:mm:ss ' --stop-datetime 'yyyy-MM-dd HH:mm:ss' 二进制文件名 | mysql -u root -p

示例:

mysqlbinlog --start-datetime '2020-07-23 16:33:33' --stop-datetime '2020-07-23 65:52:44' mysql-bin.000001 | mysql -u root -p

⑵. 按照文件大小还原:

--start-position  #开始大小
--stop-position   #结束大小
格式:
mysqlbinlog --start-position=int -stop-position=int 二进制文件名 | mysql -u root -p

示例:

mysqlbinlog --start-position=180 --stop-position=496 mysql-bin.000027 | mysql -uroot -p

三. 主从备份:

1. 修改my.cnf配置文件:

server-id= 一般为ip结尾,便于区分.两台MySQL服务器都需要修改,且server-id不能一致.

2. 重启MySQL.

3. 在服务器上授权:

#slave on *.*  代表所有库和所有表.
#192.168.31.% 后面百分比代表改网段下所有ip都可以正常访问主服务器,如果允许所有ip地址,只写一个%号即可.
grant replication slave on *.*  to 'slave'@'192.168.31.%' identified by '@Abcd1234';
#重新加载权限表
flush privileges;

4. 查看主服务器的二进制文件:

show master status;

5. 记录文件名和大小:

6. 从服务器保存授权的信息:

mysql> change master to
    -> master_user='slave',                 #授权用户
    -> master_password='@Abcd1234',         #密码
    -> master_host='192.168.31.100',        #主服务器地址
    -> master_log_file='mysql-bin.000001',  #二进制文件名
    -> master_log_pos=601;                  #当前二进制文件大小
Query OK, 0 rows affected, 2 warnings (0.06 sec)

7. 查看从服务器产生的授权文件:

cat  /var/lib/mysql/master.info

8. 开启从服务器并查看:

mysql> start slave;     #启动从服务器.
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G;   #查看从服务器内容;
*************************** 1. row ***************************
                  Master_Host: 192.168.31.100
                  Master_User: slave
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 601
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes     #线程启用
            Slave_SQL_Running: Yes     #线程启用

9. 测试:

MYSQL主从复制原理:
Mysql主从同步其实是一个异步复制的过程，要实现复制首先需要在master上开启bin-log日志功能，整个过程需要开启3个线程，分别是Master开启IO线程，slave开启IO线程和SQL线程。
1）在从服务器执行slave
start，从服务器上IO线程会通过授权的用户连接上master，并请求master从指定的文件和位置之后发送bin-log日志内容。
2）Master服务器接收到来自slave服务器的IO线程的请求后，master服务器上的IO线程根据slave服务器发送的指定bin-log日志之后的内容，然后返回给slave端的IO线程。（返回的信息中除了bin-log日志内容外，还有本次返回日志内容后在master服务器端的新的binlog文件名以及在binlog中的下一个指定更新位置）
3）Slave的IO线程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的
bin-log的文件名和位置记录到master.info文件中，以便在下一次读取的时候能够清楚的告诉Master"我需要从某个bin-log的哪
个位置开始往后的日志内容，请发给我"；
4）Slave的Sql线程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。
引用地址:https://www.yangxingzhen.com/364.html

原理图:

四. 主主备份:

1. 修改主1服务器配置:

replicate-ignore-db = mysql              #忽略同步的数据库
replicate-ignore-db = information_schema #忽略同步的数据库
replicate-ignore-db = performance_schema #忽略同步的数据库
auto_increment_offset =1                 #防止自增主键冲突
auto_increment_increment =2
log-slave-updates

2. 主2配置:

replicate-ignore-db = mysql              #忽略同步的数据库
replicate-ignore-db = information_schema #忽略同步的数据库
replicate-ignore-db = performance_schema #忽略同步的数据库
auto_increment_offset =2                 #防止自增主键冲突
auto_increment_increment =2
log-slave-updates                        #将复制事件写入binlog,一台服务器既做主库又做从库此选项须要开启

3. 授权:

主1授权:

grant replication slave on *.*  to 'slave'@'192.168.31.%' identified by '@Abcd1234';
#重新加载权限表
flush privileges;

主2保存授权信息:

mysql> change master to
    -> master_user='slave',                 #授权用户
    -> master_password='@Abcd1234',         #密码
    -> master_host='192.168.31.100',        #主服务器地址
    -> master_log_file='mysql-bin.000001',  #二进制文件名
    -> master_log_pos=601;                  #当前二进制文件大小
Query OK, 0 rows affected, 2 warnings (0.06 sec)

主2授权:

grant replication slave on *.*  to 'slave'@'192.168.31.%' identified by '@Abcd1234';
#重新加载权限表
flush privileges;

主1保存授权信息:

mysql> change master to
    -> master_user='slave',                 #授权用户
    -> master_password='@Abcd1234',         #密码
    -> master_host='192.168.31.101',        #主服务器地址
    -> master_log_file='mysql-bin.000001',  #二进制文件名
    -> master_log_pos=601;                  #当前二进制文件大小
Query OK, 0 rows affected, 2 warnings (0.06 sec)

主一主二执行命令:

start slave

4. 查看:

主1信息:

主2信息:

5. 测试:

五. 一主多从:

通过上面的文章,我们配置了主主配置,现在mysql01和mysql02互为主从,我们现在把一号变为主服务器,二号变为从服务器,

1. 停止MySQL01的线程

mysql01服务器执行以下命令:

#停止I/O 线程和SQL线程的操作。
stop slave

2. 修改第三台服务器上的配置文件:

 vi /etc/my.cnf

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
init_connect='SET NAMES utf8'
log-bin=mysql-bin
#server-id一般为ip结尾
server-id=102   

3. 授权

主1授权:

#这些指令其实不用执行,因为我们之前执行过,但为了流程更加详细,在此贴上.
grant replication slave on *.*  to 'slave'@'192.168.31.%' identified by '@Abcd1234';
#重新加载权限表
flush privileges;

查看mysql01日志文件:

mysql> mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

4. mysql03接收授权:

#如果下面的分句sql语句觉得麻烦,复制下面一行即可:

change master to master_user='slave',master_password='@Abcd1234', master_host='192.168.31.100',master_log_file='mysql-bin.000006', master_log_pos=454;

#上面的命令等价于上面的命令
mysql> change master to
    -> master_user='slave',
    -> master_password='@Abcd1234',
    -> master_host='192.168.31.100',
    -> master_log_file='mysql-bin.000006',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

5. 查看授权:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 

6. 测试:

六. 多主一从,也称为多源复制

1. 修改从库配置:

server-id = 102         #必须唯一
log_bin = mysql-bin

binlog-ignore-db = mysql        #不需要同步的数据库
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys

#不加此参数会报To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.错误
master_info_repository  =table
relay_log_info_repository =table

2. 查询MySQL01二进制文件信息:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      941 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3. 查询MySQL02二进制文件信息:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      941 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从服务器执行下下面命令:

change master to master_user='slave', master_password='@Abcd1234', master_host='192.168.31.100', master_log_file='mysql-bin.000008', master_log_pos=941 for channel'100'

change master to master_user='slave', master_password='@Abcd1234', master_host='192.168.31.101', master_log_file='mysql-bin.000008', master_log_pos=941 for channel'101';

#定义通道名称
for channel '100';

4. 查看授权信息:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.31.100
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 941
               Relay_Log_File: localhost-relay-bin-100.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 941
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 100
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.31.101
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 941
               Relay_Log_File: localhost-relay-bin-101.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 941
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 101
           Master_TLS_Version: 

5. 测试: