MySQL

Official site: mysql.com

MariaDB

Official site: mariadb.org
与 mysql 的差异: https://www.skeema.io/blog/2023/05/10/mysql-vs-mariadb-schema/
在root下刚安装完时，即可直接用 mysql 命令进入， mariadb 会自动检查当前系统用户是否为root.
执行 sudo mysql_secure_installation 脚本来进行安全设置

Ubuntu20.04 后 MariaDB 默认使用 unix_socket 插件来认证root用户，改回 mysql_native_password 插件的方式:

sudo su # get root access
mysql # connect db
update mysql.user set plugin = 'mysql_native_password' where User = 'root';
flush privileges;

Tools

Mac 下好用的工具：Sequal Pro
slashbase: 一个基于浏览器的数据库操作 IDE，有点类似 PHPMyAdmin，但使用 Go 语言编写，并且支持 PostgreSQL 和 MongoDB。

Manual

sql syntax

Connect

mysql -h host -u username -p password -P port
default port,no password: mysql -h host -u username -p
disconnect: mysql> quit

Create User

create user:

mysql -h host -u username -p
mysql> use mysql;
mysql> insert into user(Host,User,Password) values("localhost","newname",password("newpassword"));
mysql> flush privileges;

Database

Create: create database newdb;
List databases: show databases;
Use databases: use newdb;
List tables: show tables
show table : describe tablename or desc tablename
change table: ALTER TABLE
- change table name: ALTER TABLE tablename RENAME TO new_tablename
- remove column: ALTER TABLE tablename DROP COLUMN columnname
- add column: ALTER TABLE tablename ADD COLUMN columnname type
- modify column： ALTER TABLE tablename CHANGE columnname new_columnname new_type;

Add Privages

给 newname 用户添加 newdb 所有权限：

GRANT ALL privileges ON newdb.* TO newname@localhost IDENTIFIED BY 'newpassword';
FLUSH privileges;

不限数据库不限来源ip不指定密码的写法：

GRANT ALL privileges ON *.* TO username@'%';
FLUSH privileges;

Check Connections

Connection history: show status like 'Conn%';
Current connections: show status like '%onn%';
Threads connected: show processlist;

Query

select examples, select a,b from tablename where [conditions] order by a
delete syntax; delete from tablename where conditions
update syntax; update tablename set a=1 b=2 where conditions
insert syntax; insert into tablename(colname1, colname2, …) values(value1, value2, …)

The query mixes ASC and DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

ASC 可省略，默认正序。

Index

create index:
- ALTER TABLE table_name ADD INDEX index_name (column_list)
- ALTER TABLE table_name ADD UNIQUE (column_list)
- ALTER TABLE table_name ADD PRIMARY KEY (column_list)
remove index:
- DROP INDEX index_name ON talbe_name
- ALTER TABLE table_name DROP INDEX index_name
- ALTER TABLE table_name DROP PRIMARY KEY
show index:SHOW INDEX FROM table_name

ORDER BY

Tips

记录 mysql 命令行结果到文件

mysql> tee output.txt
Logging to file 'output.txt'
mysql> notee
Outfile disabled.
#或者
mysql> \T output.txt
Logging to file 'output.txt'
mysql> \t
Outfile disabled.

导入导出数据: mysqldump, 查看 man mysqldump
- mysqldump –databases [dbname1] [dbname2] > output.sql
- mysql < output.sql
获取时区设置 SHOW VARIABLES LIKE 'system_time_zone'

Timestamp 类型赋值给 Datetime 类型

UPDATE sometable SET dtime=FROM_UNIXTIME(UNIX_TIMESTAMP(ttime));

查看表大小

 SELECT TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH,INDEX_LENGTH FROM information_schema.TABLES;

mysql binlog日志自动清理及手动删除
- 关闭mysql主从，关闭binlog；
- 开启mysql主从，设置expire_logs_days；
- 手动清除binlog文件，PURGE MASTER LOGS <params>
DNS解析如果有问题的话会影响 mysql 连接速度，可在 my.cnf 中 [mysqld] 下添加 skip-name-resolve 来忽略dns解析与IP反查。忽略后只能使用IP地址来连接数据库。

目录