Centos7 安装MySQL8

1.删除之前安装的MySQL包
[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@localhost ~]# yum erase -y mariadb-libs-5.5.60-1.el7_5.x86_64

2.解压缩MySQL包
tar -xf mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz

3.重命名并移动到/usr/local/mysql
mv mysql-8.0.25-linux-glibc2.12-x86_64 /usr/local/mysql

4.创建mysql用户和组
groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql

5.创建数据目录，日志目录，变更权限
mkdir /usr/local/mysql/data
mkdir /usr/local/mysql/log
chown -R mysql:mysql /usr/local/mysql/

6.编辑mysql配置文件
vim /usr/local/mysql/etc/my.cnf
[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
port = 3306
mysqlx_port = 33060
mysqlx_socket = /usr/local/mysql/data/mysqlx.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = /usr/local/mysql/log/error.log

这个就是用之前的身份认证插件

default-authentication-plugin = mysql_native_password

保证日志的时间正确

log_timestamps = SYSTEM

设置忽略大小写

lower_case_table_names=1

开启bin_log日志

log_bin = /usr/local/mysql/log/bin-log

指定索引文件的位置

log_bin_index = /usr/local/mysql/log/mysql-bin.index

删除超出这个变量保留期之前的全部日志被删除

expire_logs_days = 7

指定一个集群内的 MySQL 服务器 ID，如果做数据库集群那么必须全局唯一，一般来说不推荐 指定 server_id 等于 1。

server_id = 1

设置方面提到过的三种 Binlog 的日志模式

binlog_format = ROW

控制对哪些数据库进行收集，如果不配置，默认全部数据库都会进行日志收集

binlog_do_db=xxxDbName

7.初始化数据库,并查看日志
cd /usr/local/mysql
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
8.找到自己的error.log日志文件，执行自己的命令，红色标记的部分为初始化密码。
grep 'temporary password' /data/mysql/error.log
mysql -uroot -p
9.设置启动文件，设置环境变量
这个时候我们只是单纯的启动了mysql，并没有做到开机启动
我们设置开机启动需要将mysql.server 文件复制到 /etc/rc.d/init.d/ 目录下mysql 文件 我们的mysql.server 文件一般都在安装的根目录下的 support-files 目录下
cp /usr/local/mysql/mysql/support-files/mysql.server /etc/rc.d/init.d/mysql
3、复制成功后我们需要给赋予权限
chmod +x /etc/init.d/mysql
4、 添加为服务：
chkconfig --add mysql
5、查看服务列表：
chkconfig --list
看到3、4、5状态为开或者为 on 则表示成功。 如果是 关或者 off 则执行一下：
chkconfig --level 345 mysqld on

vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
source /etc/profile

systemctl start/stop/restart/status mysql

9.登录并配置远程登录
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

这里就是重置临时密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '1qaz@WSX';
Query OK, 0 rows affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root | *611725B3AA4055897CDB648E55E0A0EA856389E2 | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

这里就可以看到root@localhost这里的密码已经是mysql_native_password方式了

这就是创建一个远程用户登录

mysql> create user 'root'@'%' identified by '1qaz@WSX';
Query OK, 0 rows affected (0.05 sec)

mysql> grant all privileges on . to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.04 sec)

mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | *43CAAB27D90B4E33EC75DEEFA02577F7E2BACE93 | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root | *611725B3AA4055897CDB648E55E0A0EA856389E2 | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

mysql> exit

---

使用图形化连接数据库后进行导入sql文件的时候会报错误1418
解决方案：
1.在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1;
2.MySQL启动时，加上–log-bin-trust-function-creators选贤，参数设置为1
3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1

max_binlog_size ，binary log 最大的大小

binlog_cache_size ，当前的多少事务cache在内存中

binlog_cache_disk_use ，当前有多少事务暂存在磁盘上的，如果这个值有数值的话，就应该要注意调优了。

max_binlog_cache_size ，最大能有多少事务cache在内存中

binlog_do_db和binlog_ingore_db ，是一对控制对哪些数据库进行收集的选项。

sync_binlog ，这个值控制cache的数据commit多少次才刷到磁盘上。默认是0，也就是让数据库自己决定同步的频率。如设置成1的话，则每commit一次就会将cache的数据同步到磁盘上，这样做最安全，但是性能最差。

检查一下bin_log是否开启
show global variables like 'log_bin';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
show master status;

+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| bin-log.000002 | 498 | xxxDbName | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
日志文件查询
-- 查看binlog文件目录
show variables like 'log_%';