开发者社区> lxyangj> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

从零开始搭建MySQL主从复制架构

简介: MySQL CentOS7 主从复制
+关注继续查看

概述

当需要进行相关技术实验的时候往往都需要涉及到数据库。特别是现在微服务大行其道,公司都在进行微服务拆分,微服务拆分就涉及到数据库的拆分,常见的数据库拆分就是垂直拆分和水平拆分。对于新技术的掌握往往需要进行动手实验,微服务的拆分更是如此,不能停留在理论和头脑风暴中。搭建一套数据库的主从架构是基础的准备工作,下面将演示如何从零开始搭建MySQL的主从架构。此套主从架构只可用于个人实验。


首先需要准备两台虚拟机,然后在虚拟机上分别安装MySQL数据库,然后在进行两个数据库的主从配置。


MySQL安装

首先需要下载安装包,[下载地址](https://downloads.mysql.com/archives/community/)。

image.png

  • Product Version: MySQL数据库版本号
  • Operating System: 操作系统类型
  • OS Version: 操作系统版本号

image.png

选择对应的环境下载即可。下载完成后我们可以通过xftp等工具将下载好的安装包上传到Centos7系统中,然后进行安装。


删除历史版本

rpm -qa|grep mysql
rpm -e –nodeps filename
find / -name mysql
rm -rf filename

解压安装包

# 首先通过cd命令进入到安装包所在的目录,然后执行
cd /opt
tar –zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz

解压后文件情况

[root@mysql-slave opt]# ll
total 649172
drwxr-xr-x. 9 root root       129 Aug  2 08:20 mysql-5.7.29-linux-glibc2.12-x86_64
-rw-r--r--. 1 root root 664749587 Aug  2 07:58 mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz

移动文件目录

[root@mysql-slave opt]# mv mysql-5.7.29-linux-glibc2.12-x86_64 /usr/local/mysql
[root@mysql-slave opt]# ll
total 649172
-rw-r--r--. 1 root root 664749587 Aug  2 07:58 mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
[root@mysql-slave opt]# ll /usr/local/mysql/
total 288
drwxr-xr-x.  2 root root    4096 Aug  2 08:20 bin
drwxr-xr-x.  2 root root      55 Aug  2 08:20 docs
drwxr-xr-x.  3 root root    4096 Aug  2 08:19 include
drwxr-xr-x.  5 root root     230 Aug  2 08:20 lib
-rw-r--r--.  1 7161 31415 276202 Dec 18  2019 LICENSE
drwxr-xr-x.  4 root root      30 Aug  2 08:19 man
-rw-r--r--.  1 7161 31415    587 Dec 18  2019 README
drwxr-xr-x. 28 root root    4096 Aug  2 08:20 share
drwxr-xr-x.  2 root root      90 Aug  2 08:20 support-files
[root@mysql-slave opt]# 

创建目录

[root@mysql-slave opt]# cd /usr/local/mysql
[root@mysql-slave mysql]# mkdir data

创建用户

[root@mysql-slave mysql]# userdel mysql
userdel: user 'mysql' does not exist
[root@mysql-slave mysql]# 
[root@mysql-slave mysql]# groupdel mysql
groupdel: group 'mysql' does not exist
[root@mysql-slave mysql]# 
[root@mysql-slave mysql]# groupadd mysql
[root@mysql-slave mysql]# 
[root@mysql-slave mysql]# useradd -g mysql mysql

安装MySQL

[root@mysql-slave mysql]# cd /usr/local/mysql
[root@mysql-slave mysql]# ./bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize
2022-08-02T11:48:39.597275Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-08-02T11:48:40.515360Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-08-02T11:48:40.741128Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-08-02T11:48:40.836357Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0da07605-1259-11ed-8f9c-08002720936f.
2022-08-02T11:48:40.878466Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-08-02T11:48:43.727323Z 0 [Warning] CA certificate ca.pem is self signed.
2022-08-02T11:48:44.182192Z 1 [Note] A temporary password is generated for root@localhost: syheRm_:j5tb

移动文件

将安装后的路径/usr/local/mysql/support-files/中的mysql.server复制到/etc/init.d/mysqld

[root@mysql-slave etc]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

配置文件

如果/usr/local/mysql/support-files有my-default.cnf则将其拷贝到/etc/my.cnf

cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

如果没有则在/etc下新建my.cnf文件

touch my.cnf

编辑my.cnf配置文件

[root@mysql-slave etc]# vi my.cnf
[mysql]

# 设置mysql客户端默认字符集

default-character-set=utf8

[mysqld]

skip-name-resolve

#设置3306端口

port = 3306

# 设置mysql的安装目录

basedir=/usr/local/mysql

# 设置mysql数据库的数据的存放目录

datadir=/usr/local/mysql/data

# 允许最大连接数

max_connections=200

# 服务端使用的字符集默认为8比特编码的latin1字符集

character-set-server=utf8

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

lower_case_table_names=1

max_allowed_packet=16M

修改文件权限

[root@mysql-slave etc]# chown 777 /etc/my.cnf
[root@mysql-slave etc]# chmod +x /etc/init.d/mysqld

配置环境变量

[root@mysql-slave etc]# vi /etc/profile
#mysql environment

export MYSQL_HOME=/usr/local/mysql

export PATH=$PATH:$MYSQL_HOME/bin
[root@mysql-slave etc]# source /etc/profile

添加软链接

[root@mysql-slave etc]# ln -fs /usr/local/mysql/bin/mysql /usr/bin/mysql
[root@mysql-slave etc]# systemctl stop firewalld

添加软链接和配置环境变量都是为了便于执行脚本,这样执行MySQL命令的时候不用到MySQL的安装目录,在路径执行都可以。添加软链接和配置环境变量任选一个操作即可,同时都进行配置也没问题

设置防火墙

# 关闭服务
systemctl stop firewalld

# 关闭开机启动
systemctl disable firewalld

# 查看服务状态
systemctl status firewalld

启动MySQL

[root@mysql-slave etc]# cd init.d/
[root@mysql-slave init.d]# ./mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/mysql-slave.err'.
. SUCCESS!

登录MySQL

[root@mysql-slave init.d]# 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.29

Copyright (c) 2000, 2020, 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> 

修改root密码

mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

添加访问权限

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

mysql> 

重启MySQL

mysql> exit;
Bye
[root@mysql-slave init.d]# /etc/init.d/mysqld restart
Shutting down MySQL..... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-slave init.d]# 

设置开机启动

[root@mysql-slave init.d]# chkconfig --list mysqld

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

service mysqld supports chkconfig, but is not referenced in any runlevel (run 'chkconfig --add mysqld')
[root@mysql-slave init.d]# chkconfig --add mysqld

主从复制

master配置

修改配置文件

[root@mysql-master etc]# vi /etc/my.cnf
[mysqld]
#开启binlog日志
log-bin=mysql-bin
#设置服务id,主从不能一致
server-id=1
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#设置需要同步的数据库
binlog-do-db=monomer_order

创建主从复制权限的账户

[root@mysql-master init.d]# 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.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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 ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync';
Query OK, 0 rows affected, 1 warning (1.74 sec)

查看master状态

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW master STATUS;
+------------------+----------+---------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+---------------+---------------------------------------------+-------------------+
| mysql-bin.000001 |      592 | monomer_order | mysql,information_schema,performance_schema |                   |
+------------------+----------+---------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

slave配置

修改配置文件

[root@mysql-slave etc]# vi /etc/my.cnf
[mysqld]
#开启binlog日志
log-bin=mysql-bin
#设置服务id,主从不能一致
server-id=2

# 设置忽略的库
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
# 设置同步的库
replicate_wild_do_table=monomer_order.%

指定主库信息

[root@mysql-slave etc]# 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.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO
    -> master_host='192.168.1.111',
    -> master_port=3306,
    -> master_user='db_sync',
    -> master_password='db_sync',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=592;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
mysql> start slave;
Query OK, 0 rows affected (0.33 sec)

查看slave状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.111
                  Master_User: db_sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 592
               Relay_Log_File: mysql-slave-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             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: mysql.%,information_schema.%,performance_schema.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 592
              Relay_Log_Space: 533
              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: 1
                  Master_UUID: 9edae037-11f7-11ed-854c-080027a21804
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

ERROR: 
No query specified

主要是看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

测试主从同步

以下操作都是在主库操作

  • 创建数据库
mysql> CREATE DATABASE IF NOT EXISTS monomer_order
        -> DEFAULT CHARACTER SET utf8mb4;
  • 创建数据表
CREATE TABLE order_info (
      `id` bigint(32) NOT NULL AUTO_INCREMENT,
      `order_no` varchar(32) NOT NULL COMMENT '订单号',
      `order_amount` decimal(8,2) NOT NULL COMMENT '订单金额',
      `merchant_id` bigint(32) NOT NULL COMMENT '商户ID',
      `user_id` bigint(32) NOT NULL COMMENT '用户ID',
      `order_freight` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '运费',
      `order_status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '订单状态,10待付款,20待接单,30已接单,40配送中,50已完成,55部分退款,60全部退款,70取消订单',
      `trans_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '交易时间',
      `pay_status` tinyint(3) NOT NULL DEFAULT '2' COMMENT '支付状态,1待支付,2支付成功,3支付失败',
      `recharge_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '支付完成时间',
      `pay_amount` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '实际支付金额',
      `pay_discount_amount` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '支付优惠金额',
      `address_id` bigint(32) NOT NULL COMMENT '收货地址ID',
      `delivery_type` tinyint(3) NOT NULL DEFAULT '2' COMMENT '配送方式,1自提。2配送',
      `delivery_status` tinyint(3) DEFAULT '0' COMMENT '配送状态,0 配送中,2已送达,3待收货,4已送达',
      `delivery_expect_time` timestamp NULL DEFAULT NULL COMMENT '配送预计送达时间',
      `delivery_complete_time` timestamp NULL DEFAULT NULL COMMENT '配送送达时间',
      `delivery_amount` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '配送运费',
      `coupon_id` bigint(32) DEFAULT NULL COMMENT '优惠券id',
      `cancel_time` timestamp NULL DEFAULT NULL COMMENT '订单取消时间',
      `confirm_time` timestamp NULL DEFAULT NULL COMMENT '订单确认时间',
      `remark` varchar(512) DEFAULT NULL COMMENT '订单备注留言',
      `create_user` bigint(32) DEFAULT NULL COMMENT '创建用户',
      `update_user` bigint(32) DEFAULT NULL COMMENT '更新用户',
      `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
      `delete_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '逻辑删除标记',
      PRIMARY KEY (`id`,`order_no`),
      KEY `inx_user_id` (`user_id`),
      UNIQUE KEY `uinx_order_no` (`order_no`),
      KEY `inx_merchant_id_update_time` (`merchant_id`,`update_time`),
      KEY `inx_update_time` (`update_time`,`order_no`) USING BTREE,
      KEY `inx_create_time` (`create_time`,`order_no`)
) ENGINE=InnoDB AUTO_INCREMENT=84656407 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单表';


CREATE TABLE order_item_detail (
     `id` bigint(32) NOT NULL AUTO_INCREMENT,
     `order_no` varchar(32) NOT NULL COMMENT '订单号',
     `product_id` bigint(32) NOT NULL COMMENT '商品ID',
     `category_id` bigint(32) NOT NULL COMMENT '商品分类ID',
     `goods_num` int(8) NOT NULL DEFAULT '1' COMMENT '商品购买数量',
     `goods_price` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '商品单价',
     `goods_amount` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '商品总价',
     `product_name` varchar(64) DEFAULT NULL COMMENT '商品名',
     `discount_amount` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '商品优惠金额',
     `discount_id` bigint(32) DEFAULT NULL COMMENT '参与活动ID',
     `product_picture_url` varchar(128) DEFAULT NULL COMMENT '商品图片',
     `create_user` bigint(32) DEFAULT NULL COMMENT '创建用户',
     `update_user` bigint(32) DEFAULT NULL COMMENT '更新用户',
     `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
     `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
     `delete_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT '逻辑删除标记',
     PRIMARY KEY (`id`) USING BTREE,
     KEY `inx_item_order_no` (`order_no`),
     KEY `inx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=218311238 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单明细表';

master、slave都没有数据库monomer_order,这个时候在master创建数据库monomer_order,这个时候slave也会同步创建数据库。

master创建表也会同步到slave。


以上就是从零开始搭建MySQL主从复制架构的全部过程。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL架构与SQL的执行流程_2
MySQL架构与SQL的执行流程_2
39 0
MySQL架构与SQL的执行流程_1
MySQL架构与SQL的执行流程_1
37 0
如何使用Navicat连接宝塔面板上安装的mysql数据库?
如何使用Navicat连接宝塔面板上安装的mysql数据库?
256 0
Navicat的安装与mysql数据库的连接保姆级教程
概述 上节写了mysql的安装与使用的详细方法,接下来为大家介绍Navicat的安装与mysql的连接。
41 0
solr 入门 (安装 mysql数据库安装 )
solr  入门 (安装)     非数据库安装 ------------------------------------------------------------------------------------------------------------------------------ 1.
917 0
Windows安装两个mysql数据库步骤
由于新旧项目数据库版本差距太大,编码格式不同,引擎也不同,所以只好装两个数据库。 本次安装两个mysql数据库,版本分别是4.0.18,5.5.36。都是可执行文件直接安装。 本机上之前已经安装完5.5.36版本,单个数据库问题不大,一步一步下去便安装完毕。 另一个版本也直接点击下一步一直安装,要注意的地方是,另外找一个目录。比如: 5.5.36: d:\mysq
1210 0
CentOS 6.5系统上安装MySQL数据库
1、查看系统是否安装了MySQL      使用命令:      #rpm -qa | grep mysql 2、卸载已安装的MySQL       卸载mysql命令如下:        #rpm -e --nodeps  mysql-libs-5.
765 0
mysql数据库的安装以及常见优化设置
原文:mysql数据库的安装以及常见优化设置   本文根据优才网课程整理,面向web开发者,内容以实用为主,专业DBA可以绕行。 如果你在大公司,可能有专门的DBA来做这些事情,如果你在一个小公司当架构师或者技术总监,或者你自己创业,那DBA的活你也得干了。
974 0
windows 安装Go 环境 搭建Mysql数据库连接
在linux 上做go 环境搭建 已经可以了。并且 连接上了 mysql 数据库。   但是 开发 还是 在windows 上面的比较多。所以要研究下 windows 下如何弄 。   需要使用一个 mingw 的环境 。 要使用make 命令     官网: http://www.mingw.org/   下载   http://sourceforge.net/proj
1381 0
+关注
12
文章
1
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载