public:it:mysql

MySQL

  • Official site: mariadb.org
  • 在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;
  • Mac 下好用的工具:Sequal Pro
  • slashbase: 一个基于浏览器的数据库操作 IDE,有点类似 PHPMyAdmin,但使用 Go 语言编写,并且支持 PostgreSQL 和 MongoDB。
  • mysql -h host -u username -p password -P port
  • default port,no password: mysql -h host -u username -p
  • disconnect: mysql> quit
  • 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;
  • 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;
  • 给 newname 用户添加 newdb 所有权限:
    GRANT ALL privileges ON newdb.* TO newname@localhost IDENTIFIED BY 'newpassword';
    FLUSH privileges;
  • 不限数据库不限来源ip不指定密码的写法:
    GRANT ALL privileges ON *.* TO username@'%';
    FLUSH privileges; 
  • Connection history: show status like 'Conn%';
  • Current connections: show status like '%onn%';
  • Threads connected: show processlist;
  • 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 可省略,默认正序。

  • 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
  • 记录 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主从,设置expire_logs_days;
    • 手动清除binlog文件,PURGE MASTER LOGS <params>
  • DNS解析如果有问题的话会影响 mysql 连接速度,可在 my.cnf[mysqld] 下添加 skip-name-resolve 来忽略dns解析与IP反查。忽略后只能使用IP地址来连接数据库。
  • public/it/mysql.txt
  • 最后更改: 2023/08/11 11:34
  • oakfire