menu Chancel's Blog
rss_feed lightbulb_outline

我的笔记

MariaDB - "'Access denied for user 'root'@'localhost"

安装的MariaDB登录root账户

chancel@ubuntu1804-server:~$ mysql -uroot -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

chancel@ubuntu1804-server:~$ mysql -V
mysql  Ver 15.1 Distrib 10.1.47-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

登录失败,确认过Root的密码没有错,使用 sudo mysql -p可以登录,确认是Root用户不允许使用密码登录

参考ERROR 1698 (28000): Access denied for user 'root'@'localhost'解决方案一,可以设置root用户允许使用密码登录

chancel@ubuntu1804-server:~$ sudo mysql -u root

MariaDB [(none)]> USE mysql;
MariaDB [mysql]> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> exit;

再次尝试,可以使用密码登录root账户

Develop Base Database 2021-01-19 11:48:54

Mariadb - 配置远程访问显示"connect reset"错误

今天在内网机器上搭建一个测试MariaDB数据库,按照习惯配置远程访问

CREATE USER chancel@chancel IDENTIFIED BY 'chancel';
GRANT ALL PRIVILEGES ON chancel_api.* TO 'chancel'@'192.168.11.13' IDENTIFIED BY 'chancel' WITH GRANT OPTION;

然后在远程机器上访问到时候弹出了connect reset,这很明显说连接到端口就被拒绝了,看了下防火墙也是打开3306的默认端口,猜测下可能是MariaDB的问题

查查3306端口的情况

chancel@chancel-nas /etc/mysql/mariadb.conf.d » netstat -ntulp | grep 3306
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      127.0.0.1:3306            0.0.0.0:*               LISTEN 

显然说db绑定了127.0.0.1的本地端口导致外网连接不了,找一下MariaDB到配置文件,编辑‘127.0.0.1’并改成'0.0.0.0'即可

chancel@chancel-nas ~» vim  /etc/mysql/mariadb.conf.d/50-server.cnf

...
bind-address        = 0.0.0.0 # 编辑‘127.0.0.1’并改成'0.0.0.0'即可
...

Develop Base Database 2020-01-07 17:05:19

MySQL - 创建、修改、删除、授权

-- 创建数据库
create schema <databse_name> default character set utf8 collate utf8_general_ci;

-- 用户授权数据库
GRANT select,update,insert,delete,create on <数据库名>.* to <用户名>;

-- 立即生效
flush  privileges;

-- 取消用户所有数据库(表)权限
REVIKE ALL ON *.* from <用户名>;

-- 立即生效
flush  privileges;

-- 删除数据库
drop database <数据库名>;

-- 创建表
CREATE TABLE `sample_database`.`t_sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

-- 修改表编码
alter table `sample_database`.`t_sample` convert to character set utf8;

-- 恢复自增序号为1
DELETE FROM `sample_database`.`t_sample` ;
ALTER TABLE `sample_database`.`t_sample` auto_increment = 1;

-- 恢复自增序号为1
TRUNCATE TABLE t_books_author;

-- 删除表
DROP TABLE `sample_database`.`t_sample` ;

Develop Base Database 2019-10-12 14:16:36

1 of 1 | A total of 3