MySQL 8.0 yum安装和配置
MySQL 8.0
centos7.5 x86_64
一、yum安装
1.先卸载机器和mysql有关的东西，有的安装了mariab-lib，会对安装有干扰，卸载了它。
[root@localhost ~]# rpm -qa | grep mariadb mariadb-libs-5.5.60-1.el7_5.x86_64
2.卸载命令
yum erase -y mariadb-libs-5.5.60-1.el7_5.x86_64
二.安装mysql 8.0 社区版yum仓库
1.官方网站
2.下载仓库包
wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm
3.安装仓库
rpm -ivh mysql80-community-release-el7-1.noarch.rpm
4.安装mysql 8.0版
yum install -y mysql-community-{server,client,common,libs}-*
5.启动mysql 8.0
systemctl start mysqld
systemctl enable mysqld
6.查看日志，找到临时密码，这里还一个问题，日志这个时间不正确，与本地差好几个小时呢 ，这个在安装完了，再调下。
[root@localhost ~]# tailf /var/log/mysqld.log 2019-01-12T13:59:34.558708Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server in progress as process 7038 2019-01-12T13:59:36.873412Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: :ZSytWyMp6Q> 2019-01-12T13:59:38.113827Z 0 [System] [MY-013170] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server has completed 2019-01-12T13:59:39.798256Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 7085 2019-01-12T13:59:40.949981Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2019-01-12T13:59:41.019836Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.13' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. 2019-01-12T13:59:41.190008Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
这个就启动了 第二行 最后那个就是密码
7.登录
把那个临时密码输入进去，就可以登录了
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 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>
8.修改临时密码
跟之前的版本不一样，得把临时密码给改了，之前set password=password('mima') 这个命令已经不好使了
mysql> show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql>
yum 安装还有一个问题，就是密码还有复杂性要求,这东西策略我还不知道怎么改
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'dgdb20I5'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'dgdB20I5!@#'; Query OK, 0 rows affected (0.02 sec) mysql>
用新密码重新登录就行了
9.修改日志时间问题
mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-01-12 22:22:19 | +---------------------+ 1 row in set (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'log_timestamps'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | log_timestamps | UTC | +----------------+-------+ 1 row in set (0.00 sec) mysql> SET GLOBAL log_timestamps = SYSTEM; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GLOBAL VARIABLES LIKE 'log_timestamps'; +----------------+--------+ | Variable_name | Value | +----------------+--------+ | log_timestamps | SYSTEM | +----------------+--------+ 1 row in set (0.00 sec) mysql> exit
且默认安装后error_log,slow_log 日志时间戳默认为UTC，因此会造成与系统时间不一致，与北京时间相差8个小时
因为log_timestamps 是一个GLOBAL的全局参数,所以直接在登录后去set全局参数,重启后就会直接失效
因此需要在mysql的配置文件中[mysqld]中增加一条log_timestamps的配置
vim /etc/my.cnf
[mysqld]
log_timestamps=SYSTEM
重启下MySQL
systemctl restart mysqld
再查看下日志，果然时间就对了
[root@localhost ~]# tailf /var/log/mysqld.log 2019-01-12T13:59:38.113827Z 0 [System] [MY-013170] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server has completed 2019-01-12T13:59:39.798256Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 7085 2019-01-12T13:59:40.949981Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2019-01-12T13:59:41.019836Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.13' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. 2019-01-12T13:59:41.190008Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060 2019-01-12T22:29:25.655750+08:00 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL. 2019-01-12T22:29:26.338014+08:00 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 24698 2019-01-12T22:29:26.856796+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2019-01-12T22:29:26.878264+08:00 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.13' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. 2019-01-12T22:29:27.007610+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060
10.修改MySQL数据目录位置
a.查询MySQL 8.0默认数据目录
mysql> show variables like '%dir%'; +-----------------------------------------+--------------------------------+ | Variable_name | Value | +-----------------------------------------+--------------------------------+ | basedir | /usr/ | | binlog_direct_non_transactional_updates | OFF | | character_sets_dir | /usr/share/mysql-8.0/charsets/ | | datadir | /var/lib/mysql/ | | innodb_data_home_dir | | | innodb_directories | | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90.000000 | | innodb_max_dirty_pages_pct_lwm | 10.000000 | | innodb_temp_tablespaces_dir | ./#innodb_temp/ | | innodb_tmpdir | | | innodb_undo_directory | ./ | | lc_messages_dir | /usr/share/mysql-8.0/ | | plugin_dir | /usr/lib64/mysql/plugin/ | | slave_load_tmpdir | /tmp | | tmpdir | /tmp | +-----------------------------------------+--------------------------------+ 16 rows in set (0.00 sec)
显而易见，datadir在 /var/lib/myql
b.先把MySQL停下来
systemctl stop mysqld
c.创建数据目录，复制数据文件（加入我把数据目录放到/home/下）
mkdir /home/mysql_data cp -r /var/lib/mysql/* /home/mysql_data/ chown -R mysql:mysql /home/mysql_data
d.编辑配置文件
vim /etc/my.cnf datadir=/home/mysql_data socket=/home/mysql_data/mysql.sock #下面这得加上，不然服务能起来，你客户端不能登录 [mysql] socket=/home/mysql_data/mysql.sock
e.启动并查询
systemctl start mysqld [root@localhost my.cnf.d]# 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 MySQL Community Server - GPL 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> show variables like '%dir%'; +-----------------------------------------+--------------------------------+ | Variable_name | Value | +-----------------------------------------+--------------------------------+ | basedir | /usr/ | | binlog_direct_non_transactional_updates | OFF | | character_sets_dir | /usr/share/mysql-8.0/charsets/ | | datadir | /home/mysql_data/ | | innodb_data_home_dir | | | innodb_directories | | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90.000000 | | innodb_max_dirty_pages_pct_lwm | 10.000000 | | innodb_temp_tablespaces_dir | ./#innodb_temp/ | | innodb_tmpdir | | | innodb_undo_directory | ./ | | lc_messages_dir | /usr/share/mysql-8.0/ | | plugin_dir | /usr/lib64/mysql/plugin/ | | slave_load_tmpdir | /tmp | | tmpdir | /tmp | +-----------------------------------------+--------------------------------+ 16 rows in set (0.01 sec)
11.mysql免密码登录
直接在[mysql]下面添加root password就行了
vim /etc/my.cnf [mysql] user='root' password='dgdB20I5!@#'
下次直接输入mysql就可以登录了，方便的很，生产环境谨慎使用
[root@localhost ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.0.13 MySQL Community Server - GPL 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> exit Bye
12.配置MySQL远程连接配置
[root@localhost ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 15 Server version: 8.0.13 MySQL Community Server - GPL 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> 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 | $A$005$LP^CZmMk T4'S0<PcRj0oL/YI9p7IGi1Q59wifPwX/93nRiZez4GboEPK/ | caching_sha2_password | +-----------+------------------+------------------------------------------------------------------------+-----------------------+ 4 rows in set (0.00 sec) mysql>
这里看到都是localhost，所以还不能远程连接
root账户为默认的密码加密方式是：caching_sha2_password；而现在很多客户端工具还不支持这种加密认证方式，连接测试的时候就会报错：client does not support authentication protocol requested by server; consider upgrading MySQL client，这里的错误信息就是不支持身份认证方式 新创建的用户有效，老用户还是不行的
所以，我们需要修改下配置文件，修改下默认加密方式,在[mysqld]下面添加一行default-authentication-plugin=mysql_native_password
vim /etc/my.cnf [mysqld] default-authentication-plugin=mysql_native_password
重启MySQL
systemctl restart mysqld
修改用户远程访问权限
mysql> grant all on *.* to 'root'@'%' identified by 'Zhang87073!'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by 'Zhang87073!'' at line 1
MySQL 8.0 这里报错了。。。之前的版本都是这样一行就搞定了 。。。所以 。。。
#这里先创建一个用户
mysql> create user 'root'@'%' identified by 'Zhang87073!'; Query OK, 0 rows affected (0.06 sec) #在进行授权 mysql> grant all privileges on *.* to 'root'@'%' with grant option; Query OK, 0 rows affected (0.05 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 | +-----------+------------------+------------------------------------------------------------------------+-----------------------+ | % | 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 | $A$005$LP^CZmMk T4'S0<PcRj0oL/YI9p7IGi1Q59wifPwX/93nRiZez4GboEPK/ | caching_sha2_password | +-----------+------------------+------------------------------------------------------------------------+-----------------------+ 5 rows in set (0.00 sec)
远程连接测试
13.修改密码策略
yum 安装的时候 遇到了密码策略的问题，我查询了一下，现在得到了答案，且发现二进制包安装完，这个密码策略是空的。
a.查看当前的密码策略
mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password.check_user_name | ON | | validate_password.dictionary_file | | | validate_password.length | 8 | | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | MEDIUM | | validate_password.special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.00 sec)
b.密码策略的解释
validate_password.check_user_name 这个参数用来检查用户名
validate_password_dictionary_file 字典文件
validate_password_length密码长度的最小值(这个值最小要是4)。
validate_password_mixed_case_count大小写的最少个数
validate_password_number_count 密码中数字的最少个数
validate_password_policy 这个参数用于控制validate_password的验证策略 0-->low 1-->MEDIUM 2-->strong。
validate_password_special_char_count 特殊字符的最小个数
c.修改密码策略
举个例子 知道怎么搞就行了 (我觉得这东西还是复杂点没坏处)
mysql > set global validate_password.policy=0; mysql > set global validate_password.policy=0; mysql > set global validate_password.length=4; mysql > set global validate_password.check_user_name=OFF; mysql > set global validate_password.number_count=0; mysql > set global validate_password.special_char_count=0; mysql > flush privileges; mysql > ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root' ; mysql > update user set host='%' where user ='root';
OK了 就写这么多吧。MySQL 8.0我也是第一次用
