Mysql 复制原理以及配置 简要分析

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

173816275.jpg

这个图是我根据自己的理解将Mysql的主从复制的流程给画了出来,关于什么是Mysql主从复制技术,这里简单说一下。

   有一次去一家刚刚成立的游戏公司面试,面试官问用过Mysql主从技术吗?我说:用过,我在产品中使用了双主复制。然后他质疑:复制是什么?不是都说主从吗?  

   然后我就石化了,他可能只听过主从,却不知复制,这里阐述 一下,主从表示的是架构类型,主服务器跟从服务器,而复制是实现这种架构的技术,不管是主从,还是主主都是Mysql的复制技术实现的。一个是服务器架构层面,一个是技术层面,这是个插曲。

使用场景: 数据库数据做冗余策略 /  数据库读写过于频繁,用于读写分离

架构类型: 主从,主主,一主多从,双主多从,环形复制 等

配置复杂:  不复杂 :)


好了,现在来简单描述一下上图的流程。

首先需要做复制,必备条件:主服务器开启二进制日志[binlog]功能(主从架构中,从服务器不用开启二进制日志功能)

二进制日志文件主要用于存放对数据产生了影响的语句,对数据未产生影响的,比如select查询语句是不会被记录下来的,如果需要对数据库进行优化操作,有必要找出需要优化的SQL查询语句,则开启慢查询日志,用于记录性能低下的SQL语句(keystone先生 如果看到这边注意下吧: ) )

从服务器上面有个中继日志[relay-log] 以及两个线程(I/O线程以及SQL线程),当主服务器上面的数据发生了变化,二进制日志文件会记录相应的SQL语句,从服务器然后通过I/O线程请求并接受主服务器其的二进制日志文件内容,记录到本地的中继日志中,最后通过SQL线程将中继日志中的SQL语句再次执行,由此保证本地的数据跟主服务器一致。


以上就是主从复制的一个大概流程,也可以看出,复制的时候并不是主服务器的数据发生变化后,从服务器上的数据立即同步的,中间总会有这样的过程,所以复制技术被定义为:异步复制。所以复制只能从最大程度上面保证主从服务器的数据 ,而并不能完全保证,所以后来出现了“半同步复制技术”,特别是在一主多从复制中用的比较广泛,旨在保证至少一台从服务器跟主服务器完全一致。


那么什么是“半同步复制”呢? 听名字半同步也就知道肯定还是异步了,其实这个半同步复制个人觉得跟什么异步复制特性关系不大了,也应该从架构上面去分析,半同步复制指的是:主服务器必须保证其一个从服务器的中继日志完整之后才会提交操作,然后将数据从日志文件中写入磁盘。



     现在简单说下如何配置Mysql的主从复制吧(mysql版本最好保证一致性或者从服务器高于主服务器)

1.安装主从服务器Mysql (版本5.6.11)

主服务器信息:

[root@master ~]# hostname  --fqdn
master.clovem.com
[root@master ~]# ip addr show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:a7:b7:56 brd ff:ff:ff:ff:ff:ff
    inet 192.168.3.176/24 brd 192.168.3.255 scope global eth0
    inet6 fe80::5054:ff:fea7:b756/64 scope link
       valid_lft forever preferred_lft forever

从服务器信息:

root@slave ~]# hostname  --fqdn
slave.clovem.com
[root@slave ~]# ip addr show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:63:dd:89 brd ff:ff:ff:ff:ff:ff
    inet 192.168.3.130/24 brd 192.168.3.255 scope global eth0
    inet6 fe80::5054:ff:fe63:dd89/64 scope link
       valid_lft forever preferred_lft forever

检查依赖包,开始安装mysql软件:

[root@master ~]# rpm -q gcc gcc-c++ make cmake ncurses-devel bison
gcc-4.4.6-4.el6.x86_64
gcc-c++-4.4.6-4.el6.x86_64
make-3.81-20.el6.x86_64
cmake-2.6.4-5.el6.x86_64
ncurses-devel-5.7-3.20090208.el6.x86_64
bison-2.4.1-5.el6.x86_64
[root@master ~]# cd mysql-5.6.11
[root@master mysql-5.6.11]# cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql/ \
-DWITH_INNOBASE_STORAGE_ENGINE=1\
 -DENABLED_LOCAL_INFILE=1\
 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 \
 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_DEBUG=0
[root@master mysql-5.6.11]#make ; make install

主要参数解释:
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql/      指定安装路径

-DWITH_INNOBASE_STORAGE_ENGINE=1              启用INNODB存储引擎

-DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8   允许所有字符集,默认字符集为utf8

-DDEFAULT_COLLATION=utf8_general_ci           指定字符校验规则为utf8_general_ci

2. 安装结束,初始化系统数据:

[root@master ~]# useradd  mysql
[root@master ~]# mkdir /data
[root@master ~]# chown  mysql.mysql /data/
[root@master ~]# /usr/local/mysql/scripts/mysql_install_db  --user=mysql --basedir=/usr/local/mysql/ --datadir=/data


3. 修改配置文件以及添加启动脚本:

[root@master ~]# cat /etc/my.cnf
[mysqld]
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data
socket=/data/mysql.sock
pid-file=/data/mysql.pid
log-bin=master-bin
server-id=10

slave节点配置文件,主要区别server-id=11,数值需要大于主节点,优先级低

[root@slave ~]# cat /etc/my.cnf
[mysqld]
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/data
socket=/data/mysql.sock
pid-file=/data/mysql.pid
log-bin=slave-bin
server-id=11

配置启动脚本:

[root@slave ~]# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
[root@slave ~]# chmod +x  /etc/init.d/mysqld
[root@slave ~]#chkconfig --add mysqld
[root@slave ~]#chkconfig mysqld  on //设为开机启动

4. 启动mysql

[root@master ~]# service mysqld start
Starting MySQL SUCCESS!
[root@master ~]# ps -ef |grep mysql
root     10903     1  0 06:01 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data --pid-file=/data/mysql.pid
mysql    11110 10903  0 06:01 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/master.clovem.com.err --pid-file=/data/mysql.pid --socket=/data/mysql.sock --port=3306
[root@slave ~]# service mysqld start
Starting MySQL..          [  OK  ]
[root@slave ~]# ps -ef |grep mysql
root     31033     1  1 14:12 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data --pid-file=/data/mysql.pid
mysql    31238 31033  7 14:12 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/slave.clovem.com.err --pid-file=/data/mysql.pid --socket=/data/mysql.sock --port=3306

5.创建具备复制权限的用户rep_slave

[root@master ~]# mysql -S /data/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.11-log Source distribution
Copyright (c) 2000, 2011, 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> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO rep_slave@'slave.clovem.com' IDENTIFIED BY 'slave123456';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> QUIT
Bye

6.验证用户是否创建成功

[root@slave ~]# mysql -urep_slave -pslave123456 -hmaster.clovem.com
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.11-log Source distribution
Copyright (c) 2000, 2011, 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 GRANTS;
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for rep_slave@slave.clovem.com                                                                                                                   |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep_slave'@'slave.clovem.com' IDENTIFIED BY PASSWORD '*F30CB7DBFF2ADA396886C76E18C15F6B8AE108C8' |
+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> QUIT
Bye


7.记录主服务器当前二进制日志文件信息

  (1)查看二进制日志文件功能是否开启

  (2)对所有的表加读锁,不可以写入数据【没有配置完成之前退出该mysql终端】

  (3)查看当前二进制日志文件信息:当前二进制日志文件名[master-bin.000002]以及位置[437]

[root@master ~]# mysql -S /data/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.11-log Source distribution
Copyright (c) 2000, 2011, 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> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE '%log_bin%';
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| log_bin                         | ON                     |
| log_bin_basename                | /data/master-bin       |
| log_bin_index                   | /data/master-bin.index |
| log_bin_trust_function_creators | OFF                    |
| log_bin_use_v1_row_events       | OFF                    |
| sql_log_bin                     | ON                     |
+---------------------------------+------------------------+
6 rows in set (0.00 sec)
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |      437 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>


8. 指向主服务器

[root@slave ~]# mysql -S /data/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.11-log Source distribution
Copyright (c) 2000, 2011, 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> CHANGE MASTER TO
    -> MASTER_HOST='master.clovem.com',
    -> MASTER_USER='rep_slave',
    -> MASTER_PASSWORD='slave123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000002',
    -> MASTER_LOG_POS=437;
Query OK, 0 rows affected, 2 warnings (0.36 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master.clovem.com
                  Master_User: rep_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 437
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              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: 437
              Relay_Log_Space: 457
              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: 0
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: 10
                  Master_UUID: 0a2335d4-ce2b-11e2-bde9-525400a7b756
             Master_Info_File: /data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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
1 row in set (0.00 sec)
mysql>

成功标志:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

这两个线程就是上文所说的读取二进制日志,写中继日志的I/O进程

                            以及将中继日志中的SQL语句执行成功的SQL进程

状态均为YES,基本表明配置成功。

9.在主服务器上面创建数据进行测试

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE DATABASE clovemdb;
Query OK, 1 row affected (0.00 sec)
mysql> USE  clovemdb;
Database changed
mysql> CREATE TABLE t1(id INT, name VARCHAR(10));
Query OK, 0 rows affected (0.20 sec)
mysql> INSERT INTO t1  SELECT 1,'clovemfong';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> INSERT INTO t1  SELECT 2,'wanghao';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> INSERT INTO t1  SELECT 3,'lichunfeng';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1;
+------+------------+
| id   | name       |
+------+------------+
|    1 | clovemfong |
|    2 | wanghao    |
|    3 | lichunfeng |
+------+------------+
3 rows in set (0.00 sec)
mysql>

10.在从服务器上面验证数据是否同步

注意:由于没有在配置文件中指定需要复制的数据库或者表,默认复制主服务器上面的全部库以及表数据

[root@slave ~]# mysql -S /data/mysql.sock  -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| clovemdb           |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@slave ~]# mysql -S /data/mysql.sock  clovemdb -e  "SHOW TABLES;"
+--------------------+
| Tables_in_clovemdb |
+--------------------+
| t1                 |
+--------------------+
[root@slave ~]# mysql -S /data/mysql.sock  clovemdb -e  "SELECT * FROM t1;"
+------+------------+
| id   | name       |
+------+------------+
|    1 | clovemfong |
|    2 | wanghao    |
|    3 | lichunfeng |
+------+------------+




通过上面的步骤可以成功配置两台mysql服务器为主从架构了,希望大家成功!


下面贴出一些生产环境中一些问题棘手问题的参数:

slave-skip-errors=all          

//该参数用来跳过所有SQL报错的,一般情况下面,如果复制过程中发生了语句报错信息,比如主服务器删除一个有外键的表数据,结果删除失败,这个时候整个复制都会终止,就需要重新启动slave进程,如果对自己的产品中的语句信心没那么大的话,建议加上此参数,否则在主服务器宕机之后,你会很悲剧的发现原来从服务器没有最新数据!

log-slave-updates

//这个参数比较有意思了,这边暂时不阐述,后面有机会写双主多从的时候再去涉及,作用是非常大滴。

replicate-do-db = clovemdb

//这个参数用于定义需要复制的数据库,指定之后就不会复制所有的库了

auto-increment-increment = 2  
auto-increment-offset = 1

//以上两个 参数是在配置主主复制的时候使用的,主要防止自动增长列发生重复

auto-increment-increment   该参数的值是定义“步长”,一般设置为主节点的个数,主主复制就是2

auto-increment-offset           该参数的值是定义初始化值,从1到主节点个数最大值。










本文转自 暗黑魔君 51CTO博客,原文链接:http://blog.51cto.com/clovemfong/1217103,如需转载请自行联系原作者
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2天前
|
缓存 算法 关系型数据库
Mysql(3)—数据库相关概念及工作原理
数据库是一个以某种有组织的方式存储的数据集合。它通常包括一个或多个不同的主题领域或用途的数据表。
20 5
Mysql(3)—数据库相关概念及工作原理
|
18天前
|
关系型数据库 MySQL 数据安全/隐私保护
docker应用部署---MySQL的部署配置
这篇文章介绍了如何使用Docker部署MySQL数据库,包括搜索和拉取MySQL镜像、创建容器并设置端口映射和目录映射、进入容器操作MySQL,以及如何使用外部机器连接容器中的MySQL。
docker应用部署---MySQL的部署配置
|
1天前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1512 3
|
4天前
|
SQL 关系型数据库 MySQL
MySQL 更新1000万条数据和DDL执行时间分析
MySQL 更新1000万条数据和DDL执行时间分析
19 4
|
5天前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
|
15天前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
48 2
|
18天前
|
关系型数据库 MySQL Go
go抽取mysql配置到yaml配置文件
go抽取mysql配置到yaml配置文件
|
21天前
|
SQL 关系型数据库 MySQL
MySQL主从配置
MySQL主从配置
|
3天前
|
SQL 自然语言处理 关系型数据库
Vanna使用ollama分析本地MySQL数据库
这篇文章详细介绍了如何使用Vanna结合Ollama框架来分析本地MySQL数据库,实现自然语言查询功能,包括环境搭建和配置流程。
20 0
|
3天前
|
数据可视化 关系型数据库 MySQL
【IDEA】配置mysql环境并创建mysql数据库
【IDEA】配置mysql环境并创建mysql数据库
24 0