Linux企业实战—mysql的主从（异步）复制

迈不过友情╰ 2023-07-13 10:50 14阅读 0赞

## 1.MYSQL的主从复制（异步复制）的基本信息 ##

特别提醒：  
异步复制（主从复制）master节点不会关心slave节点的状态，只需要写自己的数据即可  
能不能完成复制看slave节点的io线程和sql线程是否开启

**主从复制的要求：**

> （1）主库开启binlog日志（设置log-bin参数）  
> （2）主从server-id不同  
> （3）从库服务器能连同主库

主从复制的原理：

mysql的主从配置又叫replication，AB复制，基于binlog二进制日志，主数据库必须开启binlog二进制日志才能进行复制  
（1） master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；  
（2）从库生成两个线程，一个i/o线程，一个SQL线程，i/o线程去请求主库的binlog，sql线程进行日志回放来复制  
（3） slave将master的binary log events拷贝到它的中继日志(relay log)；  
（4）slave重做中继日志中的事件，将更改应用到自己的数据上。

mysql的主从复制（异步复制）（基于position）把一个事件拆开来复制，并不是以一个完整的事件为单位来进行复制

> 一开始两个mysql必须一模一样，否则会报错  
> master自己做自己的，写在自己的日志里  
> slave能否同步成功取决于IO线程，和SQL线程回放日志  
> IO通过联系master拿到master的二进制日志，SQL回放日志  
> slave节点的数据总比master节点的数据慢

异步复制：在主节点写入日志即返回成功，默认情况下MySQL5.5/5.6/5.7和mariaDB10.0/10.1的复制功能是异步的  
异步复制可以实现最佳的性能，主库把binlog日志发送给从库，这一动作就结束了，并不验证从库，会造成主从库数据不一致

## 2.实验环境 ##

<table> 
 <thead> 
  <tr> 
   <th>主机信息</th> 
   <th>主机的功能</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>server1（172.25.1.1）</td> 
   <td>数据库的master节点</td> 
  </tr> 
  <tr> 
   <td>server2（172.25.1.2）</td> 
   <td>数据库的slave节点</td> 
  </tr> 
  <tr> 
   <td>真机（172.25.12.250）</td> 
   <td>测试</td> 
  </tr> 
 </tbody> 
</table>

## 3.基于position的主从复制 ##

异步复制：顾名思义就是两个数据库不是同步的，创建数据的时候肯定有前有后，并不是两个同时创建起数据  
先创建数据的数据库叫做master节点，后面复制master节点数据的数据库叫做slave节点  
因此把这种异步复制的方式也可以叫做主从复制

（1）从真机上面给server1（master节点）和server2（slave节点）各传一个mysql安装包

cd 到mysql安装包存放的路径下面
    scp mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar root@172.25.1.1:/root
    scp mysql-5.7.24-1.el7.x86_64.rpm-bundle.tar root@172.25.1.2:/root

（2）在server1（master节点）上进行配置

cd  切换到/root下面
    ls  查看传过来的包
    du -sh mysql...查看包的大小
    tar xf mysql...解包
    ls查看

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3_size_16_color_FFFFFF_t_70]  
安装mysql(注意安装下面安装包的安装顺序)

[root@server1 ~]# yum install mysql-community-client-5.7.28-1.el7.x86_64.rpm mysql-community-common-5.7.28-1.el7.x86_64.rpm mysql-community-libs-5.7.28-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm mysql-community-server-5.7.28-1.el7.x86_64.rpm -y

[root@server1 ~]# vim /etc/my.cnf
    开始配置主节点数据库
    在最后面写入:
    log-bin=mysql-bin
    server-id=1(每个节点的序号是唯一的)

systemctl start mysqld

开启服务之后生成了一个临时密码，使用临时密码进行数据库安全初始化

[root@server1 ~]# cat /var/log/mysqld.log | grep password
    2020-03-11T07:28:02.777807Z 1 [Note] A temporary password is generated for root@localhost: l8lMz-.tcbD.
    [root@server1 ~]# mysql -uroot -pl8lMz-.tcbD

此时会生成一个临时密码
    cat /var/log/mysqld.log | grep password复制密码
    mysql -uroot -p
    粘贴密码，登录成功
    show databases;发现必须进行初始化

安全初始化登陆的时候使用的是临时密码，接下来要自己设置数据库的密码，这个密码必须有特殊字符，英文字母的大小写还有数字，这里设置的密码是`Westos+001`

[root@server1 ~]# mysql_secure_installation 
    
    Securing the MySQL server deployment.
    
    Enter password for user root: 
    The 'validate_password' plugin is installed on the server.
    The subsequent steps will run with the existing configuration
    of the plugin.
    Using existing password for root.
    
    Estimated strength of the password: 100 
    Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
    
    New password: 
    
    Re-enter new password: 
    
    Estimated strength of the password: 100 
    Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
    By default, a MySQL installation has an anonymous user,
    allowing anyone to log into MySQL without having to have
    a user account created for them. This is intended only for
    testing, and to make the installation go a bit smoother.
    You should remove them before moving into a production
    environment.
    
    Remove anonymous users? (Press y|Y for Yes, any other key for No) : 
    
     ... skipping.
    
    
    Normally, root should only be allowed to connect from
    'localhost'. This ensures that someone cannot guess at
    the root password from the network.
    
    Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 
    
     ... skipping.
    By default, MySQL comes with a database named 'test' that
    anyone can access. This is also intended only for testing,
    and should be removed before moving into a production
    environment.
    
    
    Remove test database and access to it? (Press y|Y for Yes, any other key for No) : 
    
     ... skipping.
    Reloading the privilege tables will ensure that all changes
    made so far will take effect immediately.
    
    Reload privilege tables now? (Press y|Y for Yes, any other key for No) : 
    
     ... skipping.
    All done! 
    [root@server1 ~]# mysql -uroot -pWestos+001
    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 13
    Server version: 5.7.28-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 database;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+

master节点的数据库初始化结束，接下来可以使用设置的密码登录数据库了

mysql -uroot -pWestos+001
    show databases;
    grant replication slave on *.* to repl@"172.25.1.%" identified by "Westos+001" ；授权为可以复制master节点数据的slave节点
    flush privileges;刷新
    show master status;查看master节点的状态

注意下图中MASTER进程的相关数据，从库设置中会用到  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3_size_16_color_FFFFFF_t_70 1]

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.25.1.%' identified by 'Westos+001';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000002 |     2840 |              |                  |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.01 sec)

（3）在server2主机上面（slave节点）进行配置

cd进入/root家目录下面
    ls查看刚才传过来的mysql安装包
    du -sh mysql...查看大小
    tar xf mysql...解压
    ls查看

[root@server2 ~]# yum install mysql-community-client-5.7.28-1.el7.x86_64.rpm mysql-community-common-5.7.28-1.el7.x86_64.rpm mysql-community-libs-5.7.28-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm mysql-community-server-5.7.28-1.el7.x86_64.rpm -y

[root@server2 ~]# vim /etc/my.cn
    开始配置备节点数据库
    在最后面写入:
    server-id=2(每个节点的序号是唯一的)
    
    
    systemctl start mysqld

`ll /var/lib/mysql`,第一次启动前要保证这个目录里没有数据此时会生成一个临时密码  
和server1一样安全初始化，修改密码

cat /var/log/mysqld.log | grep password复制密码
    mysql -uroot -p
    粘贴密码，登录成功
    show databases;发现必须进行初始化

mysql -uroot -pWestos+101   #登录，可以查看

在server2上设置mysql的主从复制：

mysql> CHANGE MASTER TO   #在这个slave节点上面设置管理它的master节点
        -> MASTER_HOST='172.25.1.1',  #主机信息
        -> MASTER_USER='repl',用户
        -> MASTER_PASSWORD='Westos+001',密码
        -> MASTER_LOG_FILE='mysql-bin.000002',基于position的主从复制的重要信息
        -> MASTER_LOG_POS=2840;基于position的主从复制的重要信息
    start slave;开启本节点的slave
    show slave status\G；查看slave节点的状态

![在这里插入图片描述][20200311180934133.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][20200311181128353.png]  
（4）测试：  
在server1（master节点）上面创建数据库，表格，数据，看slave节点能不能复制过去

[root@server1 mysql]# mysql -uroot -pWestos+001
    mysql> CREATE DATABASE westos;						##创建数据库test
    mysql> USE westos;									##进入到数据库test
    mysql> SHOW TABLES;									##查看表
    mysql> CREATE TABLE usertb (						##创建表redhat
        -> username varchar(10) not null,
        -> password varchar(15) not null);
    mysql> DESC usertb;									##查看表的结构
    mysql> INSERT INTO usertd VALUES ('user1','123');	##向表中插入信息
    mysql> SELECT * FROM usertb;						##查看表的所有信息

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3_size_16_color_FFFFFF_t_70 3]（7）在server2（slave节点）上面查看刚才在server1上面创建的数据库有没有复制过来

show databases;可以看到刚才创建的数据库  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3_size_16_color_FFFFFF_t_70 4]

在server2（slave节点）上面查看有没有把master节点的数据复制过来  
desc usertb; 查看表格信息  
![在这里插入图片描述][20200311181728744.png]  
select \* from westos.usertb;可以看到刚才插入的表格内容  
![在这里插入图片描述][20200311181741755.png]  
所以这个异步复制（基于position的主从复制）就成功了，实验效果很明显  
数据已同步！！！  
以上是基于position的主从复制

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200311175137337.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200311180314841.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3,size_16,color_FFFFFF,t_70
[20200311180934133.png]: https://img-blog.csdnimg.cn/20200311180934133.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200311181057270.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3,size_16,color_FFFFFF,t_70
[20200311181128353.png]: https://img-blog.csdnimg.cn/20200311181128353.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2020031118145478.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200311181552441.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDE3Njc3,size_16,color_FFFFFF,t_70
[20200311181728744.png]: https://img-blog.csdnimg.cn/20200311181728744.png
[20200311181741755.png]: https://img-blog.csdnimg.cn/20200311181741755.png