企业级数据库集群[Mysql]——————mysql的异步复制（基于position的主从复制）

最新推荐文章于 2024-07-24 16:20:20 发布

mango_kid

最新推荐文章于 2024-07-24 16:20:20 发布

阅读量133

点赞数

分类专栏：数据库文章标签： mysql 数据库 linux 主从同步

本文链接：https://blog.csdn.net/mango_kid/article/details/108364714

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

文章目录

1. MYSQL的异步复制
2. 异步复制
- 2.1 安装mysql安装包

1. MYSQL的异步复制

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

2）主从复制的要求：

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

注：什么是二进制日志：

log-bin日志：保存所有对数据库更改（增删改）的操作（create、drop、update）
修改my.cnf配置文件开启binlog记录功能。log-bin=mysql-bin就可以修改。
查看mysqlbinlog用一下命令：

在这里插入代码片

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重做中继日志中的事件，将更改应用到自己的数据上。

4）什么是异步复制：

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

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

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

2. 异步复制

2.1 安装mysql安装包

实验准备：

两台虚拟机分别做主、备。
一台虚拟机进行测试。

2.1.1 配置主节点

主节点配置文件开启二进制日志、添加唯一的server-id；
安全初始化主节点设置root用户的密码。
建立一个内网网段的远程登录用户；
将它设置为从的身份；
查看二进制文件的position值。

1）官网下载mysql的5.7版本安装包：。

2）建立mysql目录，将需要的安装包保存在目录下：

[root@master tar]# mkdir mysql
[root@master tar]# mv mysql-community-client-5.7.24-1.el7.x86_64.rpm mysql-community-common-5.7.24-1.el7.x86_64.rpm mysql-community-libs-* mysql-community-server-* mysql
[root@master tar]# cd mysql/
[root@master mysql]# ls
mysql-community-client-5.7.24-1.el7.x86_64.rpm
mysql-community-common-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-5.7.24-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.24-1.el7.x86_64.rpm
mysql-community-server-5.7.24-1.el7.x86_64.rpm

3）yum安装所有的安装包：

[root@master mysql]# yum install -y *

Installed:
  mysql-community-client.x86_64 0:5.7.24-1.el7                                  
  mysql-community-common.x86_64 0:5.7.24-1.el7                                  
  mysql-community-libs.x86_64 0:5.7.24-1.el7                                    
  mysql-community-libs-compat.x86_64 0:5.7.24-1.el7                             
  mysql-community-server.x86_64 0:5.7.24-1.el7                                  

Dependency Installed:
  net-tools.x86_64 0:2.0-0.17.20131004git.el7                                   

Replaced:
  mariadb-libs.x86_64 1:5.5.52-1.el7                                            

Complete!

4）配置主节点参数，开启二进制日志，设定唯一的服务节点id。（写在最后就可以了）

[root@master mysql]# vim /etc/my.cnf
log-bin=mysql-bin # 开启二进制日志
server-id=1 # 设定唯一的服务节点id
：wq

5）开启mysql服务：

[root@master mysql]# systemctl restart mysqld
[root@master mysql]#

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

7）查看mysql日志复制密码：

[root@master mysql]# cat /var/log/mysqld.log | grep password
2020-09-03T07:08:40.540112Z 1 [Note] A temporary password is generated for root@localhost: quBR<nOs9BV?

8）粘贴密码，登录成功，发现必须进行初始化；

[root@master mysql]# mysql -uroot -p
Enter password: # 输入刚才的密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-log

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;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

9）安全初始化：

[root@master ~]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: # 粘帖之前生成的密码

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 
 ... Failed! Error: Your password does not satisfy the current policy requirements 
 # 建立新的密码，要求大小字母、数字、字符都要含有。

New password: 

Re-enter new password: 
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

其他设定都需要y

10）建立一个内网（172.25.5.0）的远程登录用户：

[root@master ~]# mysql -uroot -pDaisy+147
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 6
Server version: 5.7.24-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 DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE USER 'repl'@'172.25.5.%' IDENTIFIED BY 'Repl+147';
Query OK, 0 rows affected (0.01 sec)

11）授权为可以复制master节点数据的slave节点：用户repl在172.25.5.%网段就可以作为从节点复制主节点的所有库下的所有表。

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.25.5.%';
Query OK, 0 rows affected (0.01 sec)

12）刷新，查看二进制文件的poistion：

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

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1565 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.1.2 配置从节点

1）安装所有的mysql需要的安装包：

[root@slave mysql]# yum install * -y

Installed:
  mysql-community-client.x86_64 0:5.7.24-1.el7                                      
  mysql-community-common.x86_64 0:5.7.24-1.el7                                      
  mysql-community-libs.x86_64 0:5.7.24-1.el7                                        
  mysql-community-libs-compat.x86_64 0:5.7.24-1.el7                                 
  mysql-community-server.x86_64 0:5.7.24-1.el7                                      

Dependency Installed:
  net-tools.x86_64 0:2.0-0.17.20131004git.el7                                       

Replaced:
  mariadb-libs.x86_64 1:5.5.52-1.el7                                                

Complete!

2）配置文件中写入自己唯一的server-id=2：写在最后就可以了

[root@slave mysql]# vim /etc/my.cnf

server-id=2
:wq

3）开启mysql服务：

systemctl restart mysqld

4）查看生成的密码：

[root@slave mysql]# cat /var/log/mysqld.log | grep password
2020-09-03T07:41:15.678592Z 1 [Note] A temporary password is generated for root@localhost: s*GQ7zGI*451

5）安全初始化设定密码：

[root@slave mysql]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 
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: 100Re-enter new password: 

Estimated strength of the password: 100
其他都选y

6）登录自己的mysql数据库：

[root@slave mysql]# mysql -uroot -pDaisy+147
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.24 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 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

7）设置主节点的主从复制：

mysql> CHANGE MASTER TO MASTER_HOST='172.25.5.10', # 主库的IP
    -> MASTER_USER='repl', # 使用哪个用户登录
    -> MASTER_PASSWORD='Repl+147', # 这个用户的登录密码
    -> MASTER_LOG_FILE='mysql-bin.000002', # 复制的二进制文件名
    -> MASTER_LOG_POS=1565; # 二进制文件的状态码
Query OK, 0 rows affected, 2 warnings (0.04 sec)

8）查看备库的IO线程和SQL线程是否开启：开启成功。

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.5.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1874
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 629
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:

2.1.3 测试异步复制成功

1）在主库里建立一个数据库song：

[root@master ~]# mysql -uroot -pDaisy+147
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 12
Server version: 5.7.24-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> CREATE DATABASE song;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| song               |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

2）从库进行查看：从库复制成功。

[root@slave mysql]# mysql -uroot -pDaisy+147
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 10
Server version: 5.7.24 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 |
| mysql              |
| performance_schema |
| song               |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mango_kid

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
企业级数据库集群[Mysql]——————mysql的异步复制（基于position的主从复制）

文章目录1. MYSQL的异步复制1. MYSQL的异步复制1）异步复制（主从复制）master节点不会关心slave节点的状态，只需要写自己的数据即可。能不能完成复制看slave节点的io线程和sql线程是否开启。2）主从复制的要求：（1）主库开启binlog日志（设置log-bin参数）（2）主从server-id不同（3）从库服务器能连同主库注：什么是二进制日志：log-bin日志：保存所有对数据库更改（增删改）的操作（create、drop、update）修改my.cnf配置
复制链接

扫一扫

专栏目录