两侧同时换到之前的修订记录 前一修订版 后一修订版 | 前一修订版 |
public:it:mysql [2016/03/29 16:06] – [Tips] oakfire | public:it:mysql [2023/08/11 11:34] (当前版本) – [Tools] oakfire |
---|
| |
| |
====== MySQL ====== | ====== MySQL ====== |
* Offcial site: [[http://mysql.com|mysql.com]] | * Official site: [[http://mysql.com|mysql.com]] |
| ===== MariaDB ===== |
| * Official site: [[https://mariadb.org/|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'' 插件的方式:<code bash> |
| sudo su # get root access |
| mysql # connect db |
| update mysql.user set plugin = 'mysql_native_password' where User = 'root'; |
| flush privileges; |
| </code> |
| ===== Tools ===== |
| * Mac 下好用的工具:[[https://www.sequelpro.com/|Sequal Pro]] |
| * [[https://github.com/slashbaseide/slashbase|slashbase]]: 一个基于浏览器的数据库操作 IDE,有点类似 PHPMyAdmin,但使用 Go 语言编写,并且支持 PostgreSQL 和 MongoDB。 |
===== Manual ===== | ===== Manual ===== |
* [[http://dev.mysql.com/doc/refman/5.7/en/sql-syntax.html|sql syntax]] | * [[https://dev.mysql.com/doc/refman/5.7/en/sql-statements.html|sql syntax]] |
==== Connect ==== | ==== Connect ==== |
* ''mysql -h host -u username -p password -P port'' | * ''mysql -h host -u username -p password -P port'' |
mysql> flush privileges;</code> | mysql> flush privileges;</code> |
==== Database ==== | ==== Database ==== |
* Create: ''mysql> create database newdb;'' | * Create: ''create database newdb;'' |
* List databases: ''mysql> show databases;'' | * List databases: ''show databases;'' |
* Use databases: ''mysql> use newdb;'' | * Use databases: '' use newdb;'' |
* List tables: ''mysql> show tables'' | * List tables: ''show tables'' |
* show table : ''mysql> describe tablename'' or ''desc tablename'' | * show table : ''describe tablename'' or ''desc tablename'' |
* change table: ''ALTER TABLE'' | * change table: ''ALTER TABLE'' |
* change table name: ''ALTER TABLE tablename RENAME TO new_tablename'' | * change table name: ''ALTER TABLE tablename RENAME TO new_tablename'' |
| |
==== Add Privages ==== | ==== Add Privages ==== |
* ''mysql> grant all privileges on newdb.* to newname@localhost identified by 'newpassword';'' | * 给 newname 用户添加 newdb 所有权限:<code sql> |
* ''mysql> flush privileges'' | grant all privileges on newdb.* to newname@localhost identified by 'newpassword'; |
| flush privileges; |
| </code> |
| * 不限数据库不限来源ip不指定密码的写法:<code sql> |
| grant all privileges on *.* to username@'%'; |
| flush privileges; |
| </code> |
| |
==== Check Connections ==== | ==== Check Connections ==== |
- Connection history ''mysql> show status like 'Conn%' '' | * Connection history: ''show status like 'Conn%'; '' |
- Current connections ''mysql> show status like '%onn%' '' "Threads_connected" | * Current connections: ''show status like '%onn%'; '' |
- ''mysql> show processlist'' | * Threads connected: ''show processlist;'' |
==== Query ==== | ==== Query ==== |
* [[http://dev.mysql.com/doc/refman/5.7/en/examples.html|select examples]], ''select a,b from tablename where [conditions] order by a'' | * [[http://dev.mysql.com/doc/refman/5.7/en/examples.html|select examples]], ''select a,b from tablename where [conditions] order by a'' |
* [[http://dev.mysql.com/doc/refman/5.7/en/update.html|update syntax]]; '' update tablename set a=1 b=2 where conditions'' | * [[http://dev.mysql.com/doc/refman/5.7/en/update.html|update syntax]]; '' update tablename set a=1 b=2 where conditions'' |
* [[http://dev.mysql.com/doc/refman/5.7/en/insert.html|insert syntax]]; '' insert into tablename(colname1, colname2, ...) values(value1, value2, ...)'' | * [[http://dev.mysql.com/doc/refman/5.7/en/insert.html|insert syntax]]; '' insert into tablename(colname1, colname2, ...) values(value1, value2, ...)'' |
| * The query mixes ASC and DESC:<code sql>SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;</code>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 ===== | ===== Tips ===== |
</code> | </code> |
* 导入导出数据: mysqldump, 查看 ''man mysqldump'' | * 导入导出数据: mysqldump, 查看 ''man mysqldump'' |
| * ''mysqldump --databases [dbname1] [dbname2] > output.sql'' |
| * ''mysql < output.sql'' |
* 获取时区设置 ''SHOW VARIABLES LIKE 'system_time_zone' '' | * 获取时区设置 ''SHOW VARIABLES LIKE 'system_time_zone' '' |
| * Timestamp 类型赋值给 Datetime 类型<code sql>update sometable set dtime=FROM_UNIXTIME(UNIX_TIMESTAMP(ttime));</code> |
| * 查看表大小<code sql> |
| select TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH,INDEX_LENGTH from information_schema.TABLES; |
| </code> |
| * [[http://coolnull.com/154.html|mysql binlog日志自动清理及手动删除]] |
| * 关闭mysql主从,关闭binlog; |
| * 开启mysql主从,设置expire_logs_days; |
| * 手动清除binlog文件,''PURGE MASTER LOGS <params>'' |
| * DNS解析如果有问题的话会影响 mysql 连接速度,可在 ''my.cnf'' 中 ''[mysqld]'' 下添加 ''skip-name-resolve'' 来忽略dns解析与IP反查。忽略后只能使用IP地址来连接数据库。 |
| |
| |