企业官网建设流程全解析

一、主服务器操作

1.docker-compose文件

version: '3' services: mysql: image: mariadb:10.5.28 container_name: mariadb_1 restart: always ports: - 13306:3306 environment: MARIADB_ROOT_PASSWORD: 123456 # 设置root用户密码 TZ: Asia/Shanghai LANG: en_US.UTF-8 MARIADB_DATABASE: test01 # 初始化的数据库名称 volumes: - ./mysql_conf:/etc/mysql - ./mysql_data:/var/lib/mysql

2.配置文件编写

vim /data/mariadb/mysql_conf/mariadb.conf.d/50-server.cnf # 启用binlog日志 server-id = 1 log-bin=mysql1

3.重启服务

4.创建一个主从复制用户并授权

#用户授权 [root@mysql53 ~]# mysql mysql> create user repluser@"%" identified by "123456"; mysql> grant replication slave on *.* to repluser@"%"; create user dbuser@"%" identified by "dbuser"; grant replication slave on *.* to dbuser@"%"; 主库查一下,是否使用了密码插件 SELECT user, host, plugin FROM mysql.user WHERE user = 'dbuser';

5.查看信息

MariaDB [(none)]> show master status; +---------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---------------+----------+--------------+------------------+ | mysql1.000001 | 645 | | | +---------------+----------+--------------+------------------+ 1 row in set (0.000 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+--------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+--------------------------+ | mysql-bin.000057 | 342 | | mysql,information_schema | +------------------+----------+--------------+--------------------------+ 1 row in set (0.000 sec)

二、从服务器操作

1.docker-compose文件

2.配置文件编写

vim /data/mariadb/mysql_conf/mariadb.conf.d/50-server.cnf # 启用binlog日志 server-id = 2 relay-log=mysql-relay-bin # 中继日志

3.重启服务

4.登录mysql操作

[root@mysql54 ~]# mysql mysql> change master to master_host="192.168.88.13", master_port=13306, master_user="repluser", master_password="123456", master_log_file="mysql1.000001", master_log_pos=645; GET_MASTER_PUBLIC_KEY = 1; master_host="192.168.88.13", # 主服务器的ip master_port=13306, # 主服务器的端口 master_user="repluser", # 创建的同步用户名 master_password="123456", # 创建的同步用户密码 master_log_file="mysql1.000001", # 刚刚主查出来的 master_log_pos=645; # 刚刚主查出来的 mysqlm1.000003 | 686 | CHANGE MASTER TO MASTER_HOST='192.168.10.100', MASTER_PORT=23306, MASTER_USER='dbuser', MASTER_PASSWORD='dbuser', MASTER_LOG_FILE='mysqlm1.000003', MASTER_LOG_POS=686, GET_MASTER_PUBLIC_KEY=1; START SLAVE; SHOW SLAVE STATUS\G

4.启动从库上的复制相关线程

mysql> start slave ;

5.查看状态信息

mysql> show slave status \G

mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.88.53 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql53.000001 Read_Master_Log_Pos: 667 Relay_Log_File: mysql54-relay-bin.000002 Relay_Log_Pos: 322 Relay_Master_Log_File: mysql53.000001 Slave_IO_Running: Yes //IO线程 Slave_SQL_Running: Yes //SQL线程

三、测试

1.在master服务器建库 、建表、添加用户 、授权

[root@mysql53 ~]# mysql 连接服务 mysql> create database gamedb; 建库 mysql> create table gamedb.user(name char(10) , class char(3)); 建表 mysql> create user plj@"%" identified by "123456"; 创建用户 mysql> grant select,insert,update,delete on gamedb.* to plj@"%" ; 授予权限 create database gamedb; create table gamedb.user(name char(10) , class char(3)); create user plj@"%" identified by "123456"; grant select,insert,update,delete on gamedb.* to plj@"%" ;

2.在slave服务器查看库、表、用户

[root@mysql54 ~]# mysql 连接服务 mysql> show databases; 查看库 mysql> desc gamedb.user; 查看表头 mysql> select user from mysql.user where user="plj"; 查看用户 mysql> show grants for plj@"%" ; 查看权限 show databases; desc gamedb.user; select user from mysql.user where user="plj"; show grants for plj@"%" ;

3.连接master服务器存储数据

mysql -h192.168.88.53 -uplj -p123456 mysql> insert into gamedb.user values ("yaya","nsd"); mysql> select * from gamedb.user; +------+-------+ | name | class | +------+-------+ | yaya | nsd | +------+-------+ 1 row in set (0.01 sec) mysql> use gamedb; insert into gamedb.user values ("yaya","nsd"); select * from gamedb.user;

4.连接从服务器查看数据

[root@mysql50 ~]# mysql -h192.168.88.54 -uplj -p123456 Mysql> select * from gamedb.user; +------+-------+ | name | class | +------+-------+ | yaya | nsd | +------+-------+ Mysql>

四、问题解决

问题已经很明确了，不是网络不通，而是复制账号认证插件问题： Last_IO_Errno: 2061 Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. 意思是：主库上的复制用户 dbuser 使用了 MySQL 8 默认的 caching_sha2_password 认证插件，但从库连接时既没有 SSL，也没有允许获取主库公钥，所以认证失败。 推荐你用下面其中一种方式解决。 方案 1：从库开启 GET_MASTER_PUBLIC_KEY，推荐先试这个 在从库执行： STOP SLAVE; CHANGE MASTER TO GET_MASTER_PUBLIC_KEY = 1; START SLAVE;