MySql初始化

yum安装Mysql

yum install -y mysql

报错 没有mysqld服务

[root@localhost ~]# systemctl start mysqld
Failed to start mysqld.service: Unit not found.

CentOS7安装mysql后无法启动服务，提示Unit not found

方法一：

yum install mariadb-server -y //如果已安装可以省略
systemctl start mariadb.service //启动服务
systemctl enable mariadb.service //开机启动服务
mysql -u root -p //登录mysql

使用MariaDB代替mysql数据库（MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。）

方法二：

这个方法就是在 /etc/systemd/system/mysqld.service 添加Unit,既然错误提示找不到Unit那我们添加一个就好了。在 /etc/systemd/system/mysqld.service下添加如下内容：

[Unit]
Description=MySQL Server
After=network.target
 
[Service]
ExecStart=/usr/bin/mysqld --defaults-file=/etc/mysql/my.cnf --datadir=/var/lib/mysql --socket=/var/run/mysqld/mysqld.sock User=mysql
Group=mysql
WorkingDirectory=/usr
 
[Install]
WantedBy=multi-user.target

然后运行：

systemctl daemon-reload

使用重启命令：

systemctl start mysql.service

初始化MySQL

mysql_secure_installation

设置root用户&移除匿名用户

root远程登录数据库&&移除测试数据库&&重新载入权限表

允许远程访问

方法一：改表

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

在执行update命令的时候报以下错误

报错

ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'

说明有多个ROOT用户纪录在USER表中，查看一下host是否已经有了%这个值，有就行。

select host, user from user;

使用Navicat连接数据库的时候报这个错误说明需要授权

方法二：授权

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

root使用123456从任何主机连接到mysql服务器

GRANT ALL PRIVILEGES ON *.* TO 'test'@’192.168.201.1’ IDENTIFIED BY '654321' WITH GRANT OPTION;

允许用户test从ip为192.168.201.1的主机连接到mysql服务器，并使用654321作为密码

配置完成后都需要flush privileges;重新载入权限表。

放开防火墙

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

验证登录

docker部署MySQL

1.搜索镜像

docker search mysql

2.拉取镜像

docker pull mysql:5.6

3.创建容器,设置端口映射、目录映射

mkdir ~/mysql
cd ~/mysql
 
docker run -id \
-p 3307:3306 \
--name=c_mysql \
-v $PWD/conf:/etc/mysql/conf.d \
-v $PWD/logs:/logs \
-v $PWD/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=bibitest12121354 \
mysql

参数说明：
p 3307:3306将容器3306端口映射到宿主机的3307端口
-v $PWD/conf:/etc/mysql/conf.d将主机当前目录下的conf/my.cnf挂载到容器的/etc/mysql/my.cnf配置目录
-v $PWD/logs:/logs 将主机当前目录下的logs目录挂载到容器的/logs 日志目录
-v $PWD/data:/var/lib/mysql 将主机当前目录下的data目录挂载到容器的/var/lib/mysql 数据目录
-e MYSQL_ROOT_PASSWORD=bibitest12121354 初始化root用户的密码

4.查看用户信息

select host,user,plugin,authentication_string from mysql.user;

MySQL8.0登录提示caching_sha2_password问题解决方法

Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found
mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]
mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client

方法1：第一次构建容器&&已安装完成后新增用户

配置 mysql.cnf配置默认身份验证插件

[mysqld]
default_authentication_plugin = mysql_native_password

验证是否生效：使用CLI进入MySQL

$ mysql -u root -p
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, plugin from user;
+-----------+------------------+-----------------------+
| Host      | User             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

root用户的身份验证器插件已经变为：mysql_native_password

方法2：当MySQL 已成功安装完成后

mysql> use mysql;
Database changed

mysql> SELECT Host, User, plugin from user;
+-----------+------------------+-----------------------+
| Host      | User             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

修改身份验证类型(修改密码)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

刷新权限：FLUSH PRIVILEGES;

修改结果

mysql> SELECT Host, User, plugin from user;
+-----------+------------------+-----------------------+
| Host      | User             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)