下载mysql8对应 版本版本

https://dev.mysql.com/downloads/mysql/

mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz

上传到linux 目录

/usr/local

解压

tar -vxf mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz

mv mysql-8.2.0-linux-glibc2.28-x86_64 mysql

创建用户组

groupadd mysql

useradd -g mysql mysql

创建相关目录

mkdir /var/lib/mysql

mkdir /var/log/mysql

设置目录用户归属

chown -R mysql:mysql ./mysql

chown -R mysql:mysql /var/lib/mysql

chown -R mysql:mysql /var/log/mysql

chmod -R 775 /var/lib/mysql

设置mysql服务为系统服务

cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

chmod +x /etc/rc.d/init.d/mysql

chmod +x /etc/init.d/mysql

chkconfig --add mysql

 

 

配置全局环境变量

vi /etc/profile

最后一行增加：

export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH

保存后执行执行：

source /etc/profile

 

新增my.cnf配置

vi /etc/my.cnf

## my.cnf配置

[mysqld]
server-id=1001
skip-name-resolve
user=mysql
port=3306
## mysqlx_port=33060
basedir=/usr/local/mysql
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
## mysqlx_socket=/usr/local/mysql/mysqlx.sock
pid-file=/usr/local/mysql/mysqld.pid
log-error=/var/log/mysql/error.log
slow-query-log=1
slow-query-log-file=/var/log/mysql/slow.log
long_query_time=0.5
log-bin=bin.log
relay-log=relay.log
binlog_format=ROW
relay_log_recovery=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
innodb_buffer_pool_size=2G
join_buffer_size=128M
sort_buffer_size=2M
read_rnd_buffer_size=2M
log_timestamps=SYSTEM
lower_case_table_names=1
default_authentication_plugin=mysql_native_password
max_connections=500
wait_timeout=600
interactive_timeout=600
max_allowed_packet=500M
innodb_file_per_table=ON
innodb_log_file_size=1024M
innodb_strict_mode=0
bulk_insert_buffer_size=128M
thread_cache_size=8

[mysqldump]
user=backusr
password=123456_abc

 

初始化数据库

mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/var/lib/mysql --initialize --lower-case-table-names=1

启动数据库 服务

service mysql start

查看mysql默认密码 

cat /var/log/mysql/error.log | grep -i password

登录数据库

mysql -uroot -p

修改root密码

mysql> alter user 'root'@'localhost' identified with caching_sha2_password by 'yourpassword';

允许root外网访问

mysql> update user set host='%' where user='root';

mysql> flush privileges;

 

mysql数据库备份，卸载，还原备份相关操作：

mysql8 需要先设置dump的用户配置my.cnf

[mysqldump]
user=backusr
password=123456_abc

备份所有的数据库

mysqldump --all-databases > /root/back.sql

备份指定的数据库

mysqldump dbname > /root/dbname.sql

还原数据库，先mysql -uroot -p登录客户端

mysql> source </root/back.sql

 

如系统使用yum rpm 工具安装了老版本的mysql，需要先卸载

查看安装的包

rpm -qa | grep -i mysql 
yum list install mysql*  

卸载安装程序
yum remove mysql mysql-server 
rpm -e --nodeps mysql-community-libs-5.7.22-1.el7.x86_64
rpm -e –nodeps mysql57-community-release-el7-11.noarch

rpm -e mysql

chkconfig --list | grep -i mysql

chkconfig --del mysql

 

mysql升级期间其他系统访问多次报错后提示：blocked because of many connection errors

需要重置hosts的错误次数

mysql> flush hosts;

查看错误最大数设置，设置最大错误数，查看IP对应的错误数

show variables like '%_errors%';
## SET GLOBAL max_connect_errors = 5000;

## select SUM_CONNECT_ERRORS from performance_schema.host_cache where ip='xxx.xxx.xxx.xxx';

 

mysql 用户授权相关命令

create user 'username'@'host' identified with caching_sha2_password by 'password';
grant all privileges on *.* to 'username'@'%';

flush privileges;

REVOKE ALL PRIVILEGES ON *.* FROM user_name;
REVOKE ALL PRIVILEGES ON user_name.* FROM user_name;
#收回赋权权限
REVOKE GRANT OPTION ON *.* FROM user_name;

查询所有权限

SHOW GRANTS FOR  '用户名'@'%或者IP';

删除用户

DROP USER '用户名'@'%或者IP';