mysql 常用管理命令-白红宇

mysql 常用管理命令

阅读量：6202 次

发布时间：2019-06-21

本文共 9118 字，大约阅读时间需要 30 分钟。

mysql提示符执行shell

方法一： system shell-command

方法二： \! shell-command

查看mysql user:

select user,host,password from mysql.user

创建用户

注:

不推荐使用GRANT语句创建用户，而推荐使用CREATE USER语句创建。

不推荐使用GRANT语句修改账户属性，而仅用于账户赋权。账户属性通过CREATE USER或者ALTER USER在创建或修改时赋予或者改动；

不推荐使用IDENTIFIED BY PASSWORD ‘hash_string’ 语法，推荐使用IDENTIFIED WITH auth_plugin AS ‘hash_string’ ；

不推荐使用SET PASSWORD语句和PASSWORD()函数，而推荐使用ALTER USER来修改账户密码；

不推荐使用old_password系统变量。

CREATE USER 'user1'@'localhost' IDENTIFIED BY 'aA123456';

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user1'@'localhost';

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'aA123456';

GRANT ALL ON *.* TO 'devuser'@'%' IDENTIFIED BY 'devuser123';

GRANT ALL ON *.* TO 'devuser'@'%' IDENTIFIED BY '1234.abc.ABC';

GRANT SELECT ON video.t_video TO 'devuser'@'%' IDENTIFIED BY '123456';

GRANT SELECT, INSERT, UPDATE, DELETE,DROP ON video20150407.t_video_search TO 'devuser'@'%' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

ALTERUSER'jeffrey'@'localhost'IDENTIFIEDBY'new_password' PASSWORDEXPIRENEVER;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'aA123456' PASSWORD EXPIRE NEVER;

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'aA123456.';

ALTER USER 'root'@'%' IDENTIFIED BY 'aA123456.' PASSWORD EXPIRE NEVER;

mysql> CREATE USER 'devuser'@'%' IDENTIFIED BY 'aA123456.';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'aA123456' WITH GRANT OPTION;

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'aA123456.3E4R';

Mysql授权插件

Property	Value
Command-Line Format	--default-authentication-plugin=plugin_name
System Variable
Scope	Global
Dynamic	No
Hint Applies	No
Type	enumeration
Default Value (>= 8.0.4)	caching_sha2_password
Default Value (<= 8.0.3)	mysql_native_password
Valid Values (>= 8.0.3)	mysql_native_password sha256_password caching_sha2_password
Valid Values (<= 8.0.2)	mysql_native_password sha256_password

复杂的授权

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, REFERENCES, INDEX, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON test.* TO 'tester'@'%' IDENTIFIED BY '123456';

显示用户权限： show grants for tester\G;

删除用户 drop user devuser;

drop user devuser%192.168.1.125;

更改root密码

SET PASSWORD = PASSWORD('aA123.abc');

SET PASSWORD = PASSWORD('123.abc.ABC'); //2017-4-13

ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

无权限启动mysql

mysqld_safe --skip-grant-tables &

UPDATE mysql.user SET Password = PASSWORD('slieer') WHERE User = 'root';

FLUSH PRIVILEGES;

为账户赋予密码的另一种方法是执行SET PASSWORD 语句：

mysql> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');

如果是更改自己的密码，可以省略for 语句：

mysql> SET PASSWORD = PASSWORD('biscuit');

查看数据库表的状态

SHOW TABLE STATUS;

show table status from test\G;

显示MySQL所安装的插件

SHOW PLUGINS;

INSTALL PLUGIN plugin_name SONAME 'shared_library_name'

显示系统变量

SHOW VARIABLES;

SHOW VARIABLES like '%innodb%';

SHOW VARIABLES WHERE VALUE > 0;

SHOW VARIABLES WHERE Variable_Name NOT LIKE '%myisam%' AND Variable_Name NOT LIKE '%innodb%';

设置全局变量

set global wait_timeout=10;

set @@global.wait_timeout=10;

select @@global.wait_timeout=10;

show global variables like 'wait_timeout'

设置局部变量

set wait_timeout=10;

set session wait_timeout=10;

set local wait_timeout=10;

然后查看设置是否成功:

mysql> select @@wait_timeout;

mysql> select @@session.wait_timeout;

mysql> select @@local.wait_timeout;

mysql> show variables like 'wait_timeout';

mysql> show local variables like 'wait_timeout';

mysql> show session variables like 'wait_timeout';

declare定义的是局部变量, 只能用在存储过程或函数中, 其有效区间就是存储过程或函数中声明该变量的begin end区间.

@var属于用户变量(自己声明的), 其有效区间就是该session, 你既可以在存储过程或函数中使用, 也可以在自己写的SQL语句中使用

还有@不需要声明类型，declare必须指定类型

显示数据库表信息

show table status from ota

删除数据库

drop database tt;

统计各个数据库的各类连接的数量

select db, command, count(*) from information_schema.processlist

group by db, command order by db, command;

查询指定数据库的活动连接详情列表

select id, time, command, state, info from information_schema.processlist

where db='tvguide' and state<>'' order by state, info; PS: 发现死锁或异常查询，先复制下查询语句以作为问题分析，然后使用Kill Query {id}命令中止这个查询。

改表名

alter table xml rename to xml_test;

修改数据库默认编码

CREATE DATABASE `ad` /*!40100 DEFAULT CHARACTER SET utf8 */

alter database swadmin default character set 'utf8';

修改表的字符集

ALTER TABLE t_widget_download_log CONVERT TO CHARACTER SET 'utf8';

ALTER TABLE `t_user_binding` CHARACTER SET = utf8 , COLLATE = utf8_unicode_ci ;

修改字符编码

alter table field_test CHARACTER SET 'utf8';

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

ALTER TABLE`t_user_common_address`

CHANGE COLUMN `name` `name` VARCHAR(20) CHARACTER SET 'utf8' NULL DEFAULT NULL ;

修改字符编码为utf8mb4, 正确存储四字节字符

ALTER SCHEMA `book` DEFAULT CHARACTER SET utf8mb4 ;

ALTER TABLE `book`.`tb_user`

CHANGE COLUMN `user_name` `user_name` VARCHAR(45) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci' NULL DEFAULT NULL ;

修改timestamp字段

alter table field_test add column stamp timestamp;

alter table field_test modify column stamp timestamp default current_timestamp;

添加字段

alter table ui_cell add column apkFileName varchar(30);

alter table ui_cell add column apkWeiPoint SMALLINT unsigned;

修改字段

alter table t_widget_version modify column pcmId varchar(5000);

修改字段的顺序

ALTER TABLE `yws`.`t_hits`

CHANGE COLUMN `level` `level` INT(1) NOT NULL COMMENT '星级' AFTER `min_value`;

查看表索引

show index from tab_archive;

添加主键

一种特殊的唯一索引，不允许有空值

alter table tab_archive add primary key (id)

让已存在的表主键自增长

alter table t_user_login_record modify id int AUTO_INCREMENT

多列索引

ALTER TABLE tablename ADD INDEX [索引的名字]( 列1, 列2, 列3);

加索引

ALTER TABLE t_payment_goo ADD INDEX idx_name(d);

CREATE INDEX indexName ON tableName(tableColumns(length));

一次添加多个索引

ALTER TABLE `live`.`t_tv_stat_failed`

ADD INDEX `idx_create_date` (`create_time` ASC),

ADD INDEX `idx_uri_id` (`url_meta_id` ASC);

唯一索引

CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);

ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);

ALTER TABLE `table_name` ADD UNIQUE (`column`)

alter table lee add unique index idx_32col (name,birthday);

create unique index idx_cat_code on cat(category_code);

FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

外键索引

SET FOREIGN_KEY_CHECKS = 0; 关闭外键检查,

alter table table_name add constraint fk_column_id foreign key(column) references 主键表 (column_id);

删除外键

ALTER TABLE `t_tv_stat_failed` DROP FOREIGN KEY `FK_dvmxlrihqkew4j5ephxprgrpf`;

添加外键，并级联

ALTER TABLE `live`.`t_tv_stat_failed` ADD CONSTRAINT `idx_channel_id`

FOREIGN KEY (`url_meta_id`) REFERENCES `live`.`t_channel_live_url`(`id`)

ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `test_shida`.`t_activity_review`

ADD INDEX `fk_act_id_idx` (`activity_id` ASC);

添加外键

ALTER TABLE `test_shida`.`t_activity_review`

ADD CONSTRAINT `fk_act_id`

FOREIGN KEY (`activity_id`)

REFERENCES `test_shida`.`t_activity` (`activity_type`)

ON DELETE NO ACTION

ON UPDATE NO ACTION;

修改表主键自增长的开始值

ALTER TABLE `ad`.`t_ott_customer` AUTO_INCREMENT = 5000;

alter table add index vs create index

With CREATE INDEX, we must provide a name for the index. With ALTER TABLE, MySQL creates an index name automatically if you don’t provide one.Unlike ALTER TABLE, the CREATE INDEX statement can create only a single index per statement. In addition, only ALTER TABLE supports the use of PRIMARY KEY.

mysql 大小写问题

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名、表名、表别名、变量名是严格区分大小写的；

2、列名与列的别名在所有的情况下均是忽略大小写的；

MySQL在Windows下都不区分大小写。

[mysqld]

lower_case_file_system=OFF #ON说明对文件名的大小写不敏感，OFF表示敏感。

lower_case_table_names=0 #0：区分大小写　　1：不区分大小写, 表名在硬盘上以小写保存。

查询时区分varchar字段值的大小写

一种方法是可以设置表或行的collation，使其为binary或case sensitive。

A、创建表时设置：

CREATE TABLE T( A VARCHAR(10) BINARY );