一：系统环境搭建

系统:
   CentOS 8 Stream 

主机名称：
   cat /etc/hosts 
   ---
10.0.2.21       flyfish21
10.0.2.22       flyfish22
10.0.2.23       flyfish23
   ---

系统初始化：
  系统关闭selinux/firewalld 与清空iptables 防火墙规则
sudo systemctl stop firewalld
sudo systemctl disable firewalld
sudo iptables -F
sudo iptables -X
sudo iptables -Z
sudo sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
sudo reboot


系统安装包：
 yum install lrzsz git subversion gpm unzip wget curl tar

二：环境安装

1、去官网下载yum源

wget https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm

rpm -Uvh mysql80-community-release-el8-9.noarch.rpm

yum install mysql-server -y

2、看看密码或者初始化脚本

cd /var/log/mysql/mysqld.log |grep password

mysql -uroot -p 

set password = 'flyfish225';

grant system_user on *.* to 'root';

flush privileges;

exit 

mysql -uroot -pflyfish225 

use mysql;

update user set host = '%' where user = 'root';（设置root任意地址访问）

flush privileges;

exit

mysql -uroot -pflyfish225 

ALTER USER 'root'@'%' IDENTIFIED BY 'flyfish225' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'flyfish225';


flush privileges;

----------------

mysql_secure_installation

service mysqld restart

3、远程用户登录测试

下面远程natvcat 就可以登录了

----------

普通用户登录：

create database hive;
create user 'hive'@'%' identified by 'hive';
use mysql;
GRANT ALL PRIVILEGES ON *.* TO 'hive'@'%' WITH GRANT OPTION;
update user set host = '%' where user = 'hive';


grant system_user on *.* to 'root';

ALTER USER 'hive'@'%' IDENTIFIED BY 'hive' PASSWORD EXPIRE NEVER;
ALTER USER 'hive'@'%' IDENTIFIED WITH mysql_native_password BY 'hive';

flush privileges;

下面普通用户hive 就可以natvcat 远程登录了

三、主从配置

## 参数介绍：
---
[mysqld]
server-id=1 ###指定server-id server-id不能相同
log_bin=master-bin ###指定二进制文件
binlog-ignore-db=mysql #不复制的数据库
binlog-ignore-db=information_schema #不复制的数据库
binlog-ignore-db=performance_schema #不复制的数据库
binlog-ignore-db=test #不复制的数据库
innodb_flush_log_at_trx_commit=1 ##我们每次事务的结束都会触发Log Thread 将log buffer中的数据写入文件并通知文件系统同步文件。这个设置是最安全的设置，能够保证不论是MySQL Crash 还是OS Crash或者是主机断电都不会丢失任何已经提交的数据。
---


配置主从的的my.cnf 

主服务器1：

vim /etc/my.cnf



---
[mysqld]
server-id=1
log_bin=master-bin
#binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#binlog-ignore-db=test
#innodb_flush_log_at_trx_commit=1


---


从服务器2：

vim /etc/my.cnf

---
[mysqld]
server-id=2
log_bin=master-bin
#binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#binlog-ignore-db=test
#innodb_flush_log_at_trx_commit=1
---

从服务器3:

vim /etc/my.cnf

----
[mysqld]
server-id=3
log_bin=master-bin
#binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#binlog-ignore-db=test
#innodb_flush_log_at_trx_commit=1
----

全部从新启动 MySQL

service mysqld restart 

主库创建复制用户

mysql -uroot -pflyfish225

创建同步账户
master节点 执行

create user slave@'%' identified by 'requser';

GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%'WITH GRANT OPTION;

CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1;

flush privileges;

## mysql 5.7 这样操作：
#grant replication slave on *.* to "requser"@"10.0.2.22" identified by "requser";

##新建repuser用户（密码：repuser），slave可以登录master mariadb，对当前服务器任何数据库的 
## 任何表进行复制操作。

flush privileges; ##刷新权限表，使创建的用户repuser 权限生效

show master status;

配置从库2-3：
  mysql -uroot -pflyfish225

//执行前先停下slave
stop slave;

//这是从库和主库连接的关键一步，host是主库的ip，user是前面创建的slave用户，file和pos是主库show master status的信息
change master to
master_host='10.0.2.21',master_user='slave',master_password='requser',
master_log_file='master-bin.000001',master_log_pos=865;

#change master to
#master_host='10.208.96.86',master_user='slave',master_password='requser',
#master_log_file='master-bin.000002',master_log_pos=866;

//执行后启动slave
start slave;

四、测试

测试： 

show slave status \G;

测试创建
在master节点上执行SQL语句

create database db_test;
show databases;

在从库上面查看 mysql -uroot -pflyfish225  show databases;

在master的db_test库里面创建表

# 进入db_test库
use db_test;
# 创建一个my_user表：
CREATE TABLE `my_user` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `account` varchar(255) DEFAULT NULL,
  `passwd` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
# 插入数据：
INSERT INTO `my_user` VALUES ('1', 'admin', 'admin');
INSERT INTO `my_user` VALUES ('2', 'pu', '12345');
INSERT INTO `my_user` VALUES ('3', 'system', 'system');

# 查看插入数据
select * from db_test.my_user;

去从库上面查看：

use db_test;
show tables;

select * from my_user;