从零开始搭建MySQL主从复制架构-阿里云开发者社区

概述

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

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

MySQL安装

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

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

选择对应的环境下载即可。下载完成后我们可以通过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]# lltotal 649172drwxr-xr-x. 9 root root       129 Aug  208:20 mysql-5.7.29-linux-glibc2.12-x86_64
-rw-r--r--. 1 root root 664749587 Aug  207: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]# lltotal 649172-rw-r--r--. 1 root root 664749587 Aug  207:58 mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
[root@mysql-slave opt]# ll /usr/local/mysql/total 288drwxr-xr-x.  2 root root    4096 Aug  208:20 bin
drwxr-xr-x.  2 root root      55 Aug  208:20 docs
drwxr-xr-x.  3 root root    4096 Aug  208:19 include
drwxr-xr-x.  5 root root     230 Aug  208:20 lib
-rw-r--r--.  1716131415276202 Dec 182019 LICENSE
drwxr-xr-x.  4 root root      30 Aug  208:19 man
-rw-r--r--.  1716131415587 Dec 182019 README
drwxr-xr-x. 28 root root    4096 Aug  208:20 share
drwxr-xr-x.  2 root root      90 Aug  208: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 mysqluserdel: user 'mysql' does not exist
[root@mysql-slave mysql]# [root@mysql-slave mysql]# groupdel mysqlgroupdel: 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 --initialize2022-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=457902022-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=1max_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 environmentexportMYSQL_HOME=/usr/local/mysql
exportPATH=$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 startStarting MySQL.Logging to '/usr/local/mysql/data/mysql-slave.err'.
. SUCCESS!

登录MySQL

[root@mysql-slave init.d]# mysql -uroot -pEnter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2Server 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 restartShutting down MySQL..... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@mysql-slave init.d]#

设置开机启动

[root@mysql-slave init.d]# chkconfig --list mysqldNote: 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 -pEnter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2Server 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 inset (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 -pEnter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2Server 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: 0Master_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 inset (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主从复制架构

概述