1. 安装MySQL

在Ubuntu中，可以使用下述命令直接安装默认版本的mysql。

$ sudo apt-get update  #更新软件源
$ sudo apt-get install mysql-server  #安装mysql在U

Ubuntu20.04中执行上述操作默认安装的mysql是8.0.21版本的，若想要安装特定版本的mysql，可以采用下述三种方式安装：

1、直接下载二进制压缩包进行安装，解压并设置相关的参数就可以运行。·

2、下载你想要安装的版本的源，然后通过apt install mysql-server=5.7.31-1ubuntu18.04的方式进行安装。·

3、下载特定版本的deb文件，然后通过dpkg -i的方式安装。

2. 确认安装是否成功

输入mysql --version可查询当前使用的mysql的版本；

输入mysql -V也可以查询当前使用的mysql的版本（注意是大写V）

在这里插入图片描述

mysql --help是会有帮助指令的，也是有安装才会有帮助指令。

在这里插入图片描述

3.mysql基本使用

（1）启动mysql服务器

使用mysql默认安装完成就启动了mysql。若第（2）步操作无法完成，可以使用命令 service mysql start启动mysql服务器：

$ service mysql start

（2）确认是否启动成功，mysql节点处于LISTEN状态表示启动成功：

$ sudo netstat -tap | grep mysql

在这里插入图片描述
（3）进入mysql shell界面：
如果安装过程中提示设置了密码，使用命令mysql -u root -p，
由于我安装过程中未提示设置用户名和密码，使用该种方式进入会提示Access denied…没关系，我们往下看，是用另一种方式进入。

如果安装过程中未提示设置密码，则使用mysql -u root命令进入在这里插入图片描述
如上图所示，显示mysql>就证明可以使用mysql了。

4.密码查询及更改

若安装过程未设置密码或者设置了密码然后使用过程中忘记密码了，可以通过以下方式查询、修改密码。

首先使用以下命令查询当前的用户及密码，

sudo cat /etc/mysql/debian.cnf

即使安装过程中没有设置用户、密码，上述操作能够进入mysql shell的就表示有用户和密码，可以通过该方式查询。

结果如下图

在这里插入图片描述
图中绿框框里的就是用户和密码。

然后，输入mysql -u debian-sys-maint -p会提示输入密码，此处输入的密码就是上一步的结果图中绿框里的密码。

然后就可以修改密码了，mysql8.04以后的版本执行下述语句：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

MySQL 8.04前的版本，执行以下两条都可以：

SET PASSWORD=PASSWORD('[修改的密码]');

update user set password=password('123456') where user='root';

修改完密码之后就可以使用下述语句进入mysql shell了

$ mysql -u root -p

该步执行后会提示你输入密码，输入你刚才更改的密码就可以进入mysql shell了。

如前文3中所说，显示mysql>就证明可以使用mysql了。

###########################################################

MySQL 8 Set or reset user password

Reset of MySQL password can be done in several ways depending of the:

OS
are you connected
user rights

In this post:

MySQL 8 set new root password
MySQL problems related to root authentication
- Not able to connect with root and no password
- 1699 SET PASSWORD has no significance for user 'root'@'localhost' as authentication plugin does not support it.
- 1287 'SET PASSWORD FOR = PASSWORD('')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR = '' instead
MySQL remove root password
MySQL set new root password
Windows Resetting the Root Password
Ubuntu Resetting the Root Password
Reset the user by query
Reset password from MySQL Workbench

MySQL 8 set new root password

If you can log in to your MySQL server and you wanto to change your password by query you can do it by:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

or removing the root password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';

MySQL problems related to root authentication

Not able to connect with root and no password

If you remove your root password or set it to null then you may experience problems when you try to connect with root. It's advisable to create another DBA user while playing with these settings.

One of the reasons to not be able to connect would be: auth_socket plugin. This is a new change since 5.7:

If we want to configure a password, we need to change the plugin and set the password at the same time, in the same command. First changing the plugin and then setting the password won’t work, and it will fall back to auth_socket again. So, run:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

For more information you can check the link in references

1699 SET PASSWORD has no significance for user 'root'@'localhost' as authentication plugin does not support it.

In case that you are trying to change your root password(or other user password without success you can check previous section.

1287 'SET PASSWORD FOR = PASSWORD('')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR = '' instead

If you see this warning your can solve it just by removing:

SET PASSWORD FOR root@localhost=PASSWORD('1234');

SET PASSWORD FOR root@localhost='';

It seems that most DB tools are not updated with last changes and still produce old format queries

MySQL remove root password

If you want to remove the password from your root account in MySQL you can:

login in MySQL or by using a tool

mysql -u root -p

and run this command:

SET PASSWORD FOR root@localhost=PASSWORD('');

MySQL set new root password

As the previous section if you want to change your root password you can do it by running:

after MySQL 5.7.6:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPass';

prior MySQL 5.7.5:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newPass');

This way you will reset only the root password. If your forgot your root password than you can check next section.

Windows Resetting the Root Password

For this solution you will need administrative rights:

Stop the MySQL server(service)
Create new text file - C:\mysqlResetRoot.txt
Add this line(for earlier version use - see prevoius section):

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPass';

Open Command Prompt
- start menu, type cmd and enter
- WINDOWS+R and type cmd
Go to your MySQL installation folder:

cd "C:\Program Files\MySQL\MySQL Server 5.7\bin

Start MySQL with this command:

mysqld --init-file=C:\\mysqlResetRoot.txt

Ubuntu Resetting the Root Password

For Ubuntu and Linux Mint you can do these steps:

Open terminal
Login with the user running the MySQL service
Stop the MySQL server by

sudo systemctl stop mysql

for older versions of Ubuntu you can use:

sudo /etc/init.d/mysql stop

or

service mysqld stop

Create new text file - /home/user/mysqlResetRoot.txt
Add this line(for earlier version use - see prevoius section):

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPass';

Start MySQL with this command:

mysqld --init-file=/home/me/mysqlResetRoot.txt &

Reset the user by query

Another option to reset the password of your root account is by update statement. This is a simple update - set which will set new password:.

UPDATE mysql.user
    SET authentication_string = PASSWORD('newPass'), password_expired = 'N'
    WHERE User = 'root' AND Host = 'localhost';

Finally reload the table grants by:

FLUSH PRIVILEGES;

Reset password from MySQL Workbench

Open MySQL Workbench
Connect to your database
Sidebar
Management
User and Privileges
Select the user - root
Type a new password to reset it
Apply

##########################################################

问题及解决：

修改密码：
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '12345678';
提示：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
查看密码策略：
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.01 sec)
修改密码策略：
mysql> set global validate_password.policy=LOW;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.length=6;
Query OK, 0 rows affected (0.00 sec)
此时的密码策略：
mysql> SHOW VARIABLES LIKE 'validate_password%'; 
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 6     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.01 sec)
再次修改密码：
mysql> alter user 'root'@'localhost'IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

##########################################################

以root用户登录：
$ mysql -u root -p
提示：
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
解决：
$ sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = fH3Fuxxxxv2Mb3bh
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = fH3Fuxxxxv2Mb3bh
socket = /var/run/mysqld/mysqld.sock

$ mysql -u debian-sys-maint -p
mysql> use mysql
查看root密码格式：
mysql> select user,plugin from user;
+------------------+-----------------------+
| user | plugin |
+------------------+-----------------------+
| debian-sys-maint | caching_sha2_password |
| mysql.infoschema | caching_sha2_password |
| mysql.session | caching_sha2_password |
| mysql.sys | caching_sha2_password |
| root | auth_socket |
+------------------+-----------------------+
5 rows in set (0.00 sec)
修改root密码格式：
mysql> update user set plugin='mysql_native_password' where user='root';
再次查看root密码格式：
mysql> select user,plugin from user;
+------------------+-----------------------+
| user | plugin |
+------------------+-----------------------+
| debian-sys-maint | caching_sha2_password |
| mysql.infoschema | caching_sha2_password |
| mysql.session | caching_sha2_password |
| mysql.sys | caching_sha2_password |
| root | mysql_native_password |
+------------------+-----------------------+
5 rows in set (0.00 sec)

增加root的密码：
mysql> alter user 'root'@'localhost' identified by '123456';
刷新权限：
mysql> flush privileges;
重新启动mysql服务：
$ service mysql restart 或 sudo /etc/init.d/mysql restart

Ubuntu MySQL 安装与配置

1. 安装MySQL

2. 确认安装是否成功

3.mysql基本使用

4.密码查询及更改

MySQL 8 Set or reset user password

MySQL 8 set new root password

MySQL problems related to root authentication

Not able to connect with root and no password

1699 SET PASSWORD has no significance for user 'root'@'localhost' as authentication plugin does not support it.

1287 'SET PASSWORD FOR = PASSWORD('')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR = '' instead

MySQL remove root password

MySQL set new root password

Windows Resetting the Root Password

Ubuntu Resetting the Root Password

Reset the user by query

Reset password from MySQL Workbench

##########################################################

发表回复 取消回复

发表回复取消回复