linux安装mysql8

rpm包安装方式

orcle官网找rpm8版本
https://dev.mysql.com/downloads/repo/yum/

1、wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm（下载rpm
2、rpm -ivh mysql-community-release-el7-5.noarch.rpm（安装rpm包）
3、rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
3、yum install mysql-server  （安装）

如果提示yum问题

yum clean all
 
rpm --rebuilddb
 
yum -y update

chmod -R 777 /var/lib/mysql

如果是centos容器报了权限问题
chmod -R 777 /var

chown mysql:mysql -R /var/lib/mysql
mysqld --initialize
systemctl start mysqld
查看状态
systemctl status mysqld

查看密码

/var/log/mysql/mysqld.log文件查看root初始密码

修改root密码

mysqladmin -u用户名 -p旧密码 password 新密码

设置允许远程连接

mysql> use mysql;
mysql> update user set host='%' where user='root';
mysql> update user set authentication_string="" where user="root";
mysql> flush privileges;
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
mysql> flush privileges;

设置时区
vim etc/my.cnf.d/mysql-server.cnf

# 东八区时区，北京时区
default-time-zone = '+8:00'

重启数据库

systemctl restart mysqld

或者
systemctl start mysql

tar包安装方式，推荐，适合离线安装

docker run -d -p 3306:3306 --name mysql3306 --privileged=true -v /data/docker_var/mysql3306soft:/var/mysql3306soft eeb6ee3f44bd /usr/sbin/init

检查是否有mariadb

rpm -qa|grep mariadb
rpm -e --nodeps mariadb-server

安装mysql-8.0.31-el7-x86_64.tar.gz包

cd data

tar -xvf  mysql-8.0.31-el7-x86_64.tar.gz

mv mysql-8.0.31-el7-x86_64 mysql 

修改mysql配置
vi /etc/my.cnf 覆盖以下全部内容，注意格式

[client]
port=3306
socket=/data/tmp/mysql/mysql.sock

[mysqld]
port=3306
user=mysql
socket=/data/tmp/mysql/mysql.sock
basedir=/data/mysql
datadir=/data/mysql/data
log-error=error.log 

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
max_connections = 4096

transaction_isolation = READ-COMMITTED
character-set-server = utf8mb4
#collation-server = utf8mb4_general_ci
collation-server = utf8mb4_bin
lower_case_table_names = 1

#生产环境注释
skip-grant-tables

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/data/tmp/mysql/log/mysqld.log
pid-file=/data/tmp/mysql/run/mysqld/mysqld.pid


#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid


#my.ini配置文件中更改允许的最大错误连接数
max_connect_errors = 8000

初始化mysql配置

		groupadd mysql 
        useradd -g mysql mysql -s /sbin/nologin -M 
        mkdir -p /data/tmp/mysql/log 
        touch /data/tmp/mysql/log/mysqld.log 
        chown -R mysql:mysql /data/tmp/mysql 
        chown -R mysql:mysql /data/mysql
        chmod -R 755 /data/tmp/mysql /data/mysql
        
/data/mysql/bin/mysqld --initialize --user=mysql --basedir=/data/mysql/ --datadir=/data/mysql/data/

依赖安装

 报错解决，安装依赖 yum install -y libaio 
还有报错，安装依赖 yum -y install numactl

cp /data/mysql/support-files/mysql.server /etc/init.d/mysql
/etc/init.d/mysql start

修改mysql初始密码

ln -s /data/mysql/bin/mysql /usr/bin

/data/mysql/bin/mysql -u root -p
use mysql;
update user set authentication_string='' where user='root';
flush privileges;

vi /etc/my.cnf

  注释 skip-grant-tables
  /etc/init.d/mysql start  重启mysql
  /data/mysql/bin/mysql -u root -p  (空密码登陆)
  alter user 'root'@'localhost' identified by '123456';
  use mysql;
  update user set host='%' where user='root';
  flush privileges;

配置mysql主从同步

主库
vi /etc/my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin
binlog-ignore-db=information_schema,mysql,performance_schema
relay_log=mysqld-relay-bin
binlog_format=mixed

/etc/init.d/mysql restart
/data/mysql/bin/mysql -u root -p

主库创建用户,授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限,用于在主从库之间同步数据

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

//语句中的%代表所有服务器都可以使用这个用户，如果想指定特定的ip，将%改成ip即可

grant replication slave, replication client on *.* to 'slave'@'%';

修改mysql8特有的密码规则

alter user 'slave'@'%' identified with mysql_native_password by '123456';

///查看主mysql的状态,记录下File和Position的值
show master status\G;

从库编辑

vi /etc/my.cnf

[mysqld]
server-id=2
log-bin=mysql-bin
binlog-ignore-db=information_schema,mysql,performance_schema
relay_log=mysqld-relay-bin
binlog_format=mixed

/etc/init.d/mysql restart

/data/mysql/bin/mysql -u root -p;

change master to master_host='127.0.0.1',master_port=3306,master_user='slave',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos= 984,master_connect_retry=30;

master_log_file 就是主库的file值
master_log_pos就是主库的Position值

master_host ：Master的地址
master_port：Master的端口号
master_user：用于数据同步的用户
master_password：用于同步的用户的密码
master_log_file：指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos：从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒

//查看从库状态, SlaveIORunning 和 SlaveSQLRunning 都是No，因为我们还没有开启主从复制过程

show slave status \G;
//开启主从复制
start slave;

//停止主从复制
stop slave;

//再次查看同步状态
show slave status \G;

SlaveIORunning 和 SlaveSQLRunning 都是Yes说明主从复制已经开启。
若SlaveIORunning一直是Connecting，有下面4种原因：
1、网络不通，检查ip端口
2、密码不对，检查用于同步的用户名和密码
3、pos不对，检查Master的Position

如果有问题就查看一下日志

主从库验证
//主库创建数据库
create database if not exists nacos default charset utf8mb4 collate utf8mb4_bin;
//从库查看是否同步
show databases;

修改账号密码

mysqladmin -u用户名 -p旧密码 password 新密码

修改最大连接数

set global max_connections=10000;

软连接mysql路径

ln -s /data/mysql/bin/mysql /usr/bin

java中切换数据源

在java中如果要切换数据源，可以用mybatis的@Ds注解
如果控制层的注解是@Ds(“slave”)，其中有方法需要调用主库，可以开启多线程或者消息队列的方式调用主库，比如

    @GetMapping(value = "testSlave")
    @DS("slave")
    public AjaxResult testLog(){
       IntellChat intellChat= intellChatMapper.selectByPrimaryKey(1);
       slowExecutor.execute(()->{
           this.updateData();
       });
       return AjaxResult.success();
    }

    @DS("master")
    private void updateData(){
        IntellChat intellChat= intellChatMapper.selectByPrimaryKey(1);
        intellChat.setChatType(3);
        intellChatMapper.updateByPrimaryKey(intellChat);
    }

Coordinator stopped because there were error(s) in the worker(s)报错

select * from performance_schema.replication_applier_status_by_worker \G

定位错误原因，大部分因为表不同步造成的，日志表可以删除不同步的日志

主从复制异常

reset slave;

然后依据上面步骤重连一下

mysql被锁表行

SELECT * FROM information_schema.INNODB_TRX;
KILL   try_mysql_thread_id;