mysql常用操作

日常使用mysql数据库时,经常需要创建用户、数据库、表以及授权等相关操作,整理在此供大家查询参考使用:

win10 mysql5.7.21免安装版配置

1. 下载mysql-5.7.21-winx64.zip
2. 复制mysql-5.7.21-winx64.zip到D:dev-envirment-setup
3. 解压mysql-5.7.21-winx64.zip至D:dev-envirment-setupmysql-5.7.21-winx64

4. 在D:dev-envirment-setupmysql-5.7.21-winx64目录新建my.ini文件,文件内容如下:

   [mysql] 
   default-character-set=utf8  # 设置mysql客户端默认字符集 
   
   [mysqld] 
   port = 3306  #设置3306端口 
   basedir=D:\dev-envirment-setup\mysql-5.7.21-winx64  # 设置mysql的安装目录 
   datadir=D:\dev-envirment-setup\mysql-5.7.21-winx64\data # 设置mysql数据库的数据的存放目录
   max_connections=200 # 允许最大连接数 
   character-set-server=utf8  # 服务端使用的字符集默认为8比特编码的latin1字符集 
   default-storage-engine=INNODB # 创建新表时将使用的默认存储引擎 

   表名大小写敏感配置

   /etc/my.cnf的[mysqld]节加入
   lower_case_table_names=1
   
   #If set to 0, table names are stored as specified and comparisons are case sensitive. 
   #If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive. 
   #If set to 2, table names are stored as given but compared in lowercase. 
   #This option also applies to database names and table aliases. 

5. 添加D:dev-envirment-setupmysql-5.7.21-winx64bin至PATH环境变量
6. 安装mysql服务：管理员权限运行cmd,cmd进入D:dev-envirment-setupmysql-5.7.21-winx64bin目录，执行mysqld install
7. 初始化：mysqld --initialize-insecure --user=mysql
8. 启动mysql：net start mysql
9. 设置root密码（非管理员权限cmd）：mysqladmin -u root password root （root密码为'root')
10. 停止mysql：net stop mysql

日常操作

1.创建数据库用户(dev)

-- mysql8.0的默认密码验证不再是password,需要加上mysql_native_password
CREATE USER 'dev'@'%' IDENTIFIED with mysql_native_password BY 'dev';

修改用户密码

$ mysql -e "select Host,User,Password from user" mysql -uroot -prootpwd
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *A7663C386E0231DEB41859368A584CDF1D355C29 |
| hero      | root | *A7663C386E0231DEB41859368A584CDF1D355C29 |
| 127.0.0.1 | root | *A7663C386E0231DEB41859368A584CDF1D355C29 |
| ::1       | root | *A7663C386E0231DEB41859368A584CDF1D355C29 |
+-----------+------+-------------------------------------------+

给root用户设置密码：
$ mysql -u root -p
Enter password: (enter root password here)
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD(' newpwd');
mysql> SET PASSWORD FOR ''@' host_name' = PASSWORD(' newpwd');
密码与操作系统密码无关
给匿名用户设置密码后或者删除匿名用户，就不能再匿名登录了
密码不正确报错如下：
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

任何用户都可以访问test或test_开头的数据库，通过mysql.db表控制
$ mysql -e "select Host,User,Db from db" mysql -uroot -prootpwd
+------+------+---------+
| Host | User | Db      |
+------+------+---------+
| %    |      | test    |
| %    |      | test\_% |
+------+------+---------+
$ mysql -u root -p
Enter password: (enter root password here)
mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';
mysql> FLUSH PRIVILEGES;

忘记密码怎么办

$ ./bin/mysqld_safe --skip-grant-tables
150407 11:38:25 mysqld_safe Logging to '/mydata/tmnew.err'.
150407 11:38:25 mysqld_safe Starting mysqld daemon with databases from /mydata
$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=password('newrootpwd') where user='root';
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2.创建数据库(testdb)

-- 建库
CREATE DATABASE IF NOT EXISTS `testdb` CHARACTER SET utf8;
-- 所有权限
GRANT ALL PRIVILEGES ON `testdb`.* TO 'dev'@'%';
-- DDL & DML权限
GRANT CREATE,DROP,ALTER,INSERT,DELETE,SELECT,UPDATE ON `testdb`.* TO 'dev'@'%'; 
-- routine & function权限
GRANT CREATE ROUTINE ON `testdb`.* TO 'dev'@'%'; 
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看权限
SHOW GRANTS FOR 'dev';

USAGE权限表示无权限，可以登录mysql数据库，但是不能操作任何数据；注意当mysql.db表中有匿名用户可访问的数据库配置记录时，即使是USAGE权限也可以操作。

3.准备建表sql文件(testdb.sql)

建表语句

-- ----------------------------
-- 测试表
-- ----------------------------
DROP TABLE IF EXISTS `t_test`;
CREATE TABLE `t_test`  (
  `id`               int(11)       UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `code`             varchar(32)   NOT NULL DEFAULT '' COMMENT '代码',
  `name`             varchar(255)  NOT NULL DEFAULT '' COMMENT '名称',
  `remark`           varchar(255)  NOT NULL DEFAULT '' COMMENT '备注信息',
  
  `create_at`        datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间戳',
  `update_at`        datetime      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间戳',
  `del_flag`         tinyint(1)    NOT NULL DEFAULT 0 COMMENT '删除标志:0-正常,1-删除',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uk_test_01` (`code`)
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '测试表';

修改表字段

ALTER TABLE t_test MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8 NOT NULL COMMENT '名称';

4.mysql命令客户端创建表

D:\testdb\sql>                                       #切换到sql所在目录
D:\testdb\sql>mysql -h127.0.0.1 -P3306 -udev -pdev   #连接mysql服务
mysql> use testdb                                    #切换数据库
mysql> source testdb.sql                             #执行建表sql文件

5.mysql查看版本与时间

mysql> select version() -- 查看版本
mysql> select now() -- 查看当前日期时间yyyy-MM-dd HH:mm:ss
mysql> select current_date -- 查看当前日期yyyy-MM-dd
mysql> select current_time -- 查看当前时间HH:mm:ss
mysql> show variables like '%char%' -- 查看字符集
    character_set_client    utf8mb4
    character_set_connection    utf8mb4
    character_set_database    utf8
    character_set_filesystem    binary
    character_set_results    utf8mb4
    character_set_server    utf8
    character_set_system    utf8
    character_sets_dir    D:\dev-envirment-setup\mysql-5.7.21-winx64\share\charsets\
mysql> show global variables like '%timeout' -- 查看操作超时时间
    connect_timeout    10
    delayed_insert_timeout    300
    have_statement_timeout    YES
    innodb_flush_log_at_timeout    1
    innodb_lock_wait_timeout    50
    innodb_rollback_on_timeout    OFF
    interactive_timeout    28800
    lock_wait_timeout    31536000
    net_read_timeout    30
    net_write_timeout    60
    rpl_stop_slave_timeout    31536000
    slave_net_timeout    60
    wait_timeout    28800

6.mysql执行外部sql脚本

方式一：命令行

$ mysql –u用户名 –p密码 –D数据库 < sql脚本全路径

注意：如果在sql脚本中使用了use 数据库，则-D数据库选项可以忽略

方式二: mysql控制台

mysql> source sql脚本全路径
mysql> \. sql脚本全路径

7.查看数据库、表、建表语句

mysql> select database() -- 查看数据库名称
mysql> show tables -- 查看当前库下所有表名
mysql> desc xxx -- 查看表结构,xxx为表名
mysql> show create table xxx -- 查看表DDL,xxx为表名

8.查看索引

DROP INDEX login_name_index ON user; 
ALTER TABLE user ADD INDEX login_name_index(login_name);
ALTER TABLE user ADD UNIQUE login_name_index(login_name);

mysql> show index from xxx -- 查看xxx表的索引
mysql> show keys from xxx -- 查看xxx表的索引

9.查看表外键

select 
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    POSITION_IN_UNIQUE_CONSTRAINT,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME 
from information_schema.key_column_usage a 
where 
a.table_schema='xxxschema' and REFERENCED_TABLE_NAME='xxxtable'

10.binlog

show variables like 'log_%'; #查看binlog配置
show binary logs; #查看binlog文件
show master status; #查看当前binlog文件
show binlog events in 'mysql-bin.000039'; #查看binlog文件内容

11.事件

# last_executed字段记录最近执行时间
SELECT * FROM information_schema.EVENTS;