mysql主从安装

#解压
tar -xzvf mysql-8.0.28-el7-x86_64.tar.gz

新建文件夹 data log tmp

#配置用户mysql并赋权
groupadd mysql
useradd -r -g mysql mysql

chown -R mysql:mysql /data/nstcapp/mysql
chmod -R 755 /data/nstcapp/mysql
chmod -R 755 /data/nstcapp/mysql/data
chmod -R 755 /data/nstcapp/mysql/log
chmod -R 755 /data/nstcapp/mysql/tmp

#初始化mysql
./mysqld --initialize --user=mysql --datadir=/data/nstcapp/mysql/data --basedir=/data/nstcapp/mysql

2023-06-25T08:30:46.804564Z 1 [Note] A temporary password is generated for root@localhost: *ymNY*abh8-0

#添加配置文件my.cnf

#配置软链接
ln -s /data/nstcapp/mysql/my.cnf /etc/my.cnf
ln -s /data/nstcapp/mysql/support-files/mysql.server /etc/init.d/mysql 
ln -s /data/nstcapp/mysql/bin/mysql /usr/bin/mysql

service mysql restart

#进入mysql终端
mysql -u root -p

# 使用以下命令修改密码
set password for root@localhost = password('123456');
# 报错使用下面命令修改
# 从8.0开始修改密码有了变化，在user表加了字段authentication_string，修改密码前先检查authentication_string是否为空
# 1.如果不为空
use mysql;
update user set authentication_string='' where user='root';--将字段置为空
ALTER user 'root'@'localhost' IDENTIFIED BY '123456';--修改密码为root
# 2.如果为空，直接修改
ALTER user 'root'@'localhost' IDENTIFIED BY 'root';--修改密码为root
#重启时用脚本重置密码
./mysqld --init-file=/data/nstcapp/mysql/mysqlc.txt --console

# 主从配置
grant replication slave on *.* to '你的数据库账号'@'你的从库的IP地址' identified by '你的数据库密码’; 
grant replication slave on *.* to 'root'@'10.2.1.197' identified by '123456';
# 刷新权限
flush privileges;

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000004
         Position: 857
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

--从库执行
change master to master_host='10.2.1.196',master_user='root',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=857;
--开启同步
start slave;
--查看从库状态
show slave status\G;
Slave_IO_Running和Slave_SQL_Running的状态都为Yes时，说明从库配置成功。

mysql -uroot -p
use mysql;
update user set host='%' where user='root' and host = 'localhost';
#赋权
grant all privileges on *.* to 'root'@'%' with grant option;
#主库内的从库配置也要赋权
grant all privileges on *.* to 'root'@'10.2.1.197' with grant option;
# 使设置权限生效
flush privileges;