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

rpm -qa|grep mysqlrpm -e –nodeps filenamefind / -name mysqlrm -rf filename

# 首先通过cd命令进入到安装包所在的目录,然后执行cd /opttar –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  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]# lltotal 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 288drwxr-xr-x.  2 root root    4096 Aug  2 08:20 bindrwxr-xr-x.  2 root root      55 Aug  2 08:20 docsdrwxr-xr-x.  3 root root    4096 Aug  2 08:19 includedrwxr-xr-x.  5 root root     230 Aug  2 08:20 lib-rw-r--r--.  1 7161 31415 276202 Dec 18  2019 LICENSEdrwxr-xr-x.  4 root root      30 Aug  2 08:19 man-rw-r--r--.  1 7161 31415    587 Dec 18  2019 READMEdrwxr-xr-x. 28 root root    4096 Aug  2 08:20 sharedrwxr-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 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

[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

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

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

touch 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

# 关闭服务systemctl stop firewalld
# 关闭开机启动systemctl disable firewalld
# 查看服务状态systemctl status firewalld

[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!

[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 itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

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> 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 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

[root@mysql-master etc]# vi /etc/my.cnf[mysqld]#开启binlog日志log-bin=mysql-bin#设置服务id,主从不能一致server-id=1#屏蔽系统库同步binlog-ignore-db=mysqlbinlog-ignore-db=information_schemabinlog-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 itsaffiliates. Other names may be trademarks of their respectiveowners.
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)

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> 

[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 itsaffiliates. Other names may be trademarks of their respectiveowners.
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)

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 in set (0.01 sec)
ERROR: No query specified

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='订单明细表';