Ubuntu默认方式安装mysql的相关配置及mysql常见错误解决

最新推荐文章于 2024-06-14 15:15:32 发布

苏打呀

最新推荐文章于 2024-06-14 15:15:32 发布

阅读量340

点赞数

文章标签： mysql ubuntu

本文链接：https://blog.csdn.net/weixin_49393427/article/details/109997280

版权

Ubuntu通过apt默认安装的mysql

密码文件：/etc/mysql/debian.cnf

开启mysql远程访问：
文件：/etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 127.0.0.1改成bind-address = 0.0.0.0

允许用户远程访问：

mysql -u root - p
use mysql;
update user set host = “%” where user =“root”;
flush privileges;
select “host”,“user” from user where user=“root”;

重启mysql服务：systemctl restart mysql.service

mysql备份恢复：

备份：mysqldump -u[username] -p[password] [databasename] > [backupname].sql
恢复：mysql -u[username] -p[password] [databasename] < [backupname].sql
恢复前提是数据库里有这个库。

常见错误：

默认身份验证插件问题

报错：django.db.utils.OperationalError: (2059, "Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory")

解决方案：

添加default-authentication-plugin=mysql_native_password到mysqld配置文件。
mysql配置文件可以使用mysqld --verbose --help查询

新用户：CREATE USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';
老用户：ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';