一、MySQL5.7的安装
1.安装Mysql
#命令1:
sudo apt-get update
#命令2:
sudo apt-get install mysql-server
2.配置MySQL
#命令:
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 (我的选项)
#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 (我的选项)
#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) : Y (我的选项)
#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 (我的选项)
#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 (我的选项)
3.检查MySQL服务状态
#命令:systemctl status mysql.service
二、MySQL的使用
1.首先使用根用户root进入mysql
#命令1:
sudo mysql -uroot -p
默认的MySQL安装之后根用户是没有密码的,命令执行后会让你设置root的密码,进入root后才能进行其他设置
#命令2:
use mysql;
输入这个命令后就可以开始操作mysql了
2.可以进入mysql后更换root用户的密码
#命令:
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "a12345";
3.新建数据库和用户,用root用户新建数据和用作远程访问的用户
#1 创建数据库spider
CREATE DATABASE spider;
#2 创建用户Ferdinand(密码a12345) 并赋予其spider数据库的所有权限
GRANT ALL PRIVILEGES ON spider.* TO Ferdinand@localhost IDENTIFIED BY "a12345";
4.配置远程访问或控制
在 /etc/mysql/mysql.conf.d 路径下输入命令: sudo gedit mysqld.cnf
注释此行:
bind-address = 127.0.0.1
至此,MySQL的安装及其配置就完成了。
三、MySQL的卸载
我们在配置过程中肯定会遇到各种各样的问题,但是我们可以选择卸载重装来再尝试。
1.首先先删除mysql:
#命令:sudo apt-get remove mysql-*
2.清理残留的数据:
#命令:dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P
这样,MySQL就已经卸载成功了。
四、解决MySQL Error 1698(28000)错误:Access denied for user'root'@'localhost'
当你在使用workbench或者第三方库pymysql尝试用root连接数据库是出现上述错误,你可以通过下述操作去解决这个问题。
mysql> select user, plugin from mysql.user;
+-----------+-----------------------+
| user | plugin |
+-----------+-----------------------+
| root | auth_socket |
| mysql.sys | mysql_native_password |
| dev | mysql_native_password |
+-----------+-----------------------+
3 rows in set (0.01 sec)
通过这里我们可以看到root用户的plugin是auth_socket,我们将其改为mysql_native_password就可以解决了。
a.输入命令:sudo mysql -uroot -p
输入密码后进入mysql
b.输入命令: use mysql;
进入mysql操作模式
c.输入命令:ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
d.输入命令:GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "a12345";
重新更改密码
e.输入命令:flush privileges;
更新一下配置
f.关闭并重启mysql后就解决问题了