mysql
命令进入, mariadb 会自动检查当前系统用户是否为root.sudo mysql_secure_installation
脚本来进行安全设置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;
mysql -h host -u username -p password -P port
mysql -h host -u username -p
mysql> quit
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 database newdb;
show databases;
use newdb;
show tables
describe tablename
or desc tablename
ALTER TABLE
ALTER TABLE tablename RENAME TO new_tablename
ALTER TABLE tablename DROP COLUMN columnname
ALTER TABLE tablename ADD COLUMN columnname type
ALTER TABLE tablename CHANGE columnname new_columnname new_type
;GRANT ALL privileges ON newdb.* TO newname@localhost IDENTIFIED BY 'newpassword'; FLUSH privileges;
GRANT ALL privileges ON *.* TO username@'%'; FLUSH privileges;
show status like 'Conn%';
show status like '%onn%';
show processlist;
select a,b from tablename where [conditions] order by a
delete from tablename where conditions
update tablename set a=1 b=2 where conditions
insert into tablename(colname1, colname2, …) values(value1, value2, …)
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
ASC 可省略,默认正序。
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)
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
SHOW INDEX FROM table_name
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.
man mysqldump
mysqldump --databases [dbname1] [dbname2] > output.sql
mysql < output.sql
SHOW VARIABLES LIKE 'system_time_zone'
UPDATE sometable SET dtime=FROM_UNIXTIME(UNIX_TIMESTAMP(ttime));
SELECT TABLE_SCHEMA,TABLE_NAME,DATA_LENGTH,INDEX_LENGTH FROM information_schema.TABLES;
PURGE MASTER LOGS <params>
my.cnf
中 [mysqld]
下添加 skip-name-resolve
来忽略dns解析与IP反查。忽略后只能使用IP地址来连接数据库。