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