Ubuntu20.04安装mysql8.x

1.1 查看数据源中有哪些版本

# madison主要用于列出软件包的版本历史及其来源。‌
sudo apt-cache madison mysql-server

# policy用于显示软件包的安装策略和版本信息。‌
sudo apt-cache policy mysql-server

# showpkg则提供了软件包的常规描述信息，‌包括状态、‌版本和依赖关系等。‌
sudo apt-cache showpkg mysql-server

 

1.2、使用命令安装mysql

#更新源
sudo apt-get update

#安装msql-server
sudo apt-get install mysql-server
#指定版本
sudo apt-get install mysql-server=<version>
#例如
sudo apt-get install mysql-server=8.0.28-0ubuntu4

#可以通过
mysql> select version();

#或者
mysql> status;

#来查看MYSQL的版本信息

 

2、初始化配置

安装的是最新版本mysql8.x

在初始化配置（sudo mysql_secure_installation）前先设置root密码，否则会报以下错：

Re-enter new password: 
 ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

root@myubuntu:~# mysql

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

然后再初始化配置：

sudo mysql_secure_installation

#1

VALIDATE PASSWORD PLUGIN can be used to test passwords...
Press y|Y for Yes, any other key for No: N (选择N ,不会进行密码的强校验)

#2

#已经设置过密码了，这步会直接略过！
Please set the password for root here...
New password: (输入密码)
Re-enter new password: (重复输入)

#3

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them...
Remove anonymous users? (Press y|Y for Yes, any other key for No) : N (选择N，不删除匿名用户)

#4

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network...
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N (选择N，允许root远程连接)

#5

By default, MySQL comes with a database named 'test' that
anyone can access...
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N (选择N，不删除test数据库)

#6

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y (选择Y，修改权限立即生效)

检查mysql服务状态

systemctl status mysql.service

 

3、配置root远程登录

3.1 修改bind-address，默认mysql只能本地访问

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
#找到 bind-address: 127.0.0.1 并注释掉

#重启mysql
sudo /etc/init.d/mysql restart

3.2 root登录

#查询用户
mysql> use mysql;
mysql> select host,user,plugin from user;

#设置权限与密码
#使用mysql_native_password修改加密规则
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

#设置root密码永不过期
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;

#允许远程访问
mysql> UPDATE user SET host = '%' WHERE user = 'root';

#刷新权限
mysql> flush privileges; 

 

4、新增用户

8.0之后的mysql不支持 授权的时候就进行用户创建，所以创建 之后才能授权；

#创建用户
mysql> create user 'abcd'@'localhost' identified by '123456';

#给用户赋权限
mysql> grant all privileges on *.* to 'abcd'@'localhost';

新创建的用户，加密方式为：caching_sha2_password

#修改加密方式
mysql> alter user 'abcd'@'localhost' identified with mysql_native_password by '123456';
mysql> flush privileges; 

 

5、root用户登录不了怎么办？

mysql有一个默认用户：debian-sys-maint

在这个文件中：/etc/mysql/debian.cnf

sudo  cat /etc/mysql/debian.cnf

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint（这个就是默认用户）
password = X4k1p11iFNCzUGQn （这个就是密码）
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = X4k1p11iFNCzUGQn
socket   = /var/run/mysqld/mysqld.sock

#然后我们就可以用这个文件里的默认用户登录，并修改root密码就可以了
mysql -udebian-sys-maint -pX4k1p11iFNCzUGQn

 

6、mysql常用命令

#1、检查服务状态

systemctl status mysql.service
#或
sudo service mysql status

#2、mysql服务启动停止

#启动
sudo service mysql start

#停止
sudo service mysql stop

#3、进入mysql数据库

mysql -u root -p

#4、退出mysql

mysql> quit;

#5、查看数据库版本

mysql> status;

#6、查看字符集

mysql> show variables like 'character%';

修改数据库默认字符集

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
character-set-server=utf8mb4

[mysql]
default-character-set=utf8mb4

[client]
default-character-set=utf8mb4

mysqld.cnf基本配置

[mysqld]
# pid存储文件
pid-file = /var/run/mysqld/mysqld.pid
# sock存储文件
socket = /var/lib/mysql/mysql.sock
# 数据存储目录
datadir = /var/lib/mysql
# 数据库端口
port = 3306
# 连接数
max_connections = 1000
# 报错日志文件
log-error=/var/log/mysqld.log

 

7、卸载mysql

#1、在终端中查看MySQL的依赖项

#在终端中查看MySQL的依赖项
dpkg --list|grep mysql

#卸载
sudo apt-get remove mysql-common
sudo apt-get autoremove --purge mysql-server-8.0

#2、清理残留数据

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

#3、再次查看MySQL的剩余依赖项，继续卸载

#再次查看MySQL的剩余依赖项：
dpkg --list|grep mysql

#继续删除剩余依赖项：
sudo apt-get autoremove --purge mysql-apt-config

#4、删除原先配置文件

sudo rm -rf /etc/mysql/ /var/lib/mysql
sudo apt autoremove
sudo apt autoreclean  #如果提示指令有误，就把reclean改成clean