linux下配置系列 九 mysql数据库

一、下载mysql

https://downloads.mysql.com/archives/community/，选择版本、系统及系统位数，下载

二、查看服务器是否已安装mysql

rpm -qa | grep mysql

三、下载解压替换

cd /home/
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz
xz -d mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz
tar xvf  mysql-8.0.12-linux-glibc2.12-x86_64.tar 
mv mysql-8.0.12-linux-glibc2.12-x86_64 mysql
#创建data存放数据
cd mysql && mkdir data
 

四、创建mysql用户及组

groupadd mysql
useradd -r -g mysql mysql

groups mysql

五、修改mysql目录拥有者为mysql用户

## 创建mysql_install_db安装文件
cd /home/mysql && mkdir mysql_install_db &&  chmod 777 ./mysql_install_db
chown -R mysql.mysql /home/mysql/

六、MySQL 初始化

## 初始化，记录好自己的临时密码
bin/mysqld --initialize --user=mysql --basedir=/home/mysql --datadir=/home/mysql/data

注意：初始化步骤要记录号临时里面： [MY-010454] [Server] A temporary password is generated for root@localhost: rnf)fw=jH3dZ ，jH3dZ为临时密码，后面会用到

七、msyql配置

修改 /etc/my.cnf 文件

[mysqld]
basedir=/home/mysql   
datadir=/home/mysql/data
socket=/home/mysql/mysql.sock
character-set-server=utf8
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[client]
socket=/home/mysql/mysql.sock
default-character-set=utf8

八、建立MySQL服务

先把mysql.server文件里的/usr/local 替换成/home

cp  -a /home/mysql/support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
#添加服务到系统服务
chkconfig --add mysql
#查看是否添加成功
chkconfig  --list mysql

九、配置全局变量

vi /etc/profile 增加

export PATH=$PATH:/home/mysql/bin:/home/mysql/lib
export PATH

#设置环境变量立即生效
source /etc/profile

十、创建日志文件

mkdir -p /var/log/mariadb/
echo "" > /var/log/mariadb/mariadb.log
chown -R mysql.mysql /var/log/mariadb

住：/etc/my.cnf 文件有配置日志位置，如不编辑怎无法启动

十一、启动MySQL服务

service mysql start

十二、设置密码

#登陆msyql
mysql -uroot -p123456
#刷新权限
FLUSH PRIVILEGES;
#修改密码，修改完密码，即时生效，无需重启msyql
ALTER USER root'@'%' IDENTIFIED BY '654321';
#修改密码，并修改root账号的密码验证插件类型为mysql_native_password
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '654321';
#刷新权限
FLUSH PRIVILEGES;

注：

1. 如修改密码报错ERROR 1396，解决方法详见十五、问题5
2. 初始化mysl必须修改密码，不然报错，详见十五、问题6

设置可以远程登录

use mysql
update user set host='%' where user='root' limit 1;
flush privileges;
quit;

十三、开放3306端口

#查看防火墙状态，如果是开启的则需要开放3306端口
firewall-cmd --state
#检查3306端口是否开放
firewall-cmd --zone=public --query-port=3306/tcp 
#开放3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
#重启防火墙
firewall-cmd --reload

十四、另一种重启方式

使用本地电脑发现可以ping通 但是telnet 3306端口不通

/home/mysql/support-files/mysql.server restart

十五、问题

1.初始化时如果遇到问题libnuma.so.1

解决方法，安装

yum install libnuma
yum -y install  numactl
yum install libaio1 libaio-dev

2.启动mysql报错mysqld_safe error: log-error set to ‘/var/log/mariadb/mariadb.log’

mkdir -p /var/log/mariadb/mariadb
echo "" > /var/log/mariadb/mariadb.log
chown -R mysql.mysql /var/log/mariadb

3.输入临时密码，报：ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)，密码无效

解决方法：重置密码

#1.停止mysql
service mysql stop
#2.在/etc/my.cnf [mysqld]的段中加上一句 
#3.启动mysql
systemctl start mysql
#4.输入下面代码，一直回车
 mysql -uroot -p
#5 看到mysql> ，执行下面命令
FLUSH PRIVILEGES;
#6.修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
注意：如果在执行该步骤的时候出现ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement 错误。则执行下 flush privileges 命令，再执行该命令即可。
#8.退出
quit;
#9.到vi /etc/my.cnf 删除skip-grant-tables 
#10.修改完密码，即时生效，无效重启msyql

4. navicat连接myql报错

navicat连接myql时报出了ERROR 2059 (HY000): Authentication plugin ‘caching_sha2_password’ cannot be loaded: ÕÒ²»µ½Ö¸¶¨µÄÄ£¿é¡£.起初认为是没设置密码的原因。便在命令行模式下设置了密码值得一提的是在登陆root之后

SELECT `user`, `host`, `authentication_string`, `plugin` FROM mysql.user;
#修改root账号的密码验证插件类型为mysql_native_password，更新一下用户的密码 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '654321'; 
FLUSH PRIVILEGES; #刷新权限

5.ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@‘localhost’

mysql> use mysql;
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

注意root，host是'%'
我执行的是:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123';
需改成:
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123';

6. 未修改密码报错：ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

#修改密码，修改完密码，即时生效，无需重启msyql
ALTER USER root'@'%' IDENTIFIED BY '654321';