一、常用命令:
1.用户权限管理:
查询用户:
use mysql
select User,Host,plugin from user;
创建用户:
CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
-- 新建一个用户并且授权全部操作权限
GRANT ALL ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
authentication_plugin: 默认caching_sha2_password。
为了兼容性可能需要使用:mysql_native_password
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
修改用户插件:
ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
赋权:
GRANT ALL ON *.* TO 'username'@'localhost';
删除用户:
DROP USER 'username'@'localhost';
查看用户权限:
SHOW GRANTS FOR 'username'@localhost;
2.MariaDB启动/停止/重启命令
启动MariaDB命令:
systemctl start mariadb.service
停止MariaDB命令:
systemctl stop mariadb.service
重启MariaDB命令:
systemctl restart mariadb.service
二、连接数据库
Python连接MariaDB,需要安装mariadb-connector-c,和MariaDB Connector/Python
brew install mariadb-connector-c
pip install mariadb
三、配置远程访问
树莓派:
修改配置文件:
/etc/mysql/mariadb.conf.d/50-server.cnf
注释 bind-address
#bind-address = 127.0.0.1
日志操作
1.使用RESET MASTER语句删除所有二进制日志文件
RESET MASTER;
四、mac下mysql安装:
通过brew 安装的mysql。使用 mysql.server start 启动是保存
启动报错:
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/var/mysql/xxxx-Air.local.pid).
通过卸载mysql,删除目录/usr/local/var下mysql文件夹,重新安装解决问题。
成功安装后的一些配置&启动命令:
We've installed your MySQL database without a root password. To secure it run:
mysql_secure_installation
MySQL is configured to only allow connections from localhost by default
To connect run:
mysql -uroot
To have launchd start mysql now and restart at login:
brew services start mysql
Or, if you don't want/need a background service you can just run:
mysql.server start
数据库配置
Ubuntu20 配置文件位置:mysqld.cnf(/etc/mysql/mysql.conf.d/mysqld.cnf)
[mysqld]
datadir = /Volumes/XXXXX/mysql #/usr/local/var/mysql/ 自定义数据目录
skip-log-bin #禁用 log_bin 日志
SSH隧道转发解决MySQL本地访问权限问题
# ssh -L 本地端口:127.0.0.1:远端端口 -NC 用户@服务器ip
ssh -L 3308:127.0.0.1:3306 -NC root@19.198.2.128
-N:不需要执行任何远程命令;
-C:对请求的所有数据进行压缩处理;
-f:让客户端SSH进入后台运行,可选。
参考文档: