MySQL应用管理常用命令

145次阅读

共计 16433 个字符，预计需要花费 42 分钟才能阅读完成。

登录 MySQL
mysql -urot -p 单实例
mysql -uroot -p -S /data/3306/mysql.sock 多实例
MySQL 帮助命令 help
mysql> help
For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
https://shop.mysql.com/
List of all MySQL commands:
Note that all text commands must be first on line and end with ‘;’
? (\?) Synonym for `help’.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
mysql> show databases like “my%”;
+—————-+
| Database (my%) |
+—————-+
| mysql |
+—————-+
1 row in set (0.00 sec)
mysql> show grants for root@localhost;
+———————————————————————+
| Grants for root@localhost |
+———————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION |
| GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT OPTION |
+———————————————————————+
2 rows in set (0.00 sec)
mysql> grant all privileges on *.* to system@’localhost’ identified by ‘123456’ with grant option;
增加 system 用户，并提升为超级管理员，即和 root 等价的用户，只是名称不同
mysql> delete from mysql.user; 删除所有 mysql 中的用户
为 root 账户设置密码方法
[root@CentOS02 tools]# mysqladmin -uroot password ‘123456’ 没有密码的用户设置密码命令
[root@centos02 tools]# mysqladmin -uroot -p’123456′ password ‘654321’ -S /data/3306/mysql.sock 适合多实例
修改管理员 root 密码方法 1
[root@centos02 tools]# mysqladmin -uroot -p’123456′ password ‘martin’
[root@centos02 tools]# mysqladmin -uroot -p’654321′ password ‘martin’ -S /data/3306/mysql.sock 适合多实例
修改管理员 root 密码方法 2
mysql> update mysql.user set password=password(‘martin’) where user=’root’;
mysql> flush privileges;
第一个 password 代表要修改的字段第二个 password 代表是一个函数
此方法适合密码丢失后通过 –skip-grant-tables 参数启动数据库后修改密码
mysql> select user,host,password from mysql.user;
+——–+———–+——————————————-+
| user | host | password |
+——–+———–+——————————————-+
| root | localhost | |
| root | centos02 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | centos02 | |
| system | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+——–+———–+——————————————-+
mysql> update mysql.user set password=password(‘martin’) where user=’system’;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)
mysql> quit
Bye
[root@centos02 tools]# mysql -usystem -pmartin
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.49 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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>
找回丢失的 mysql 密码
1、先停止数据库
[root@centos02 tools]# /etc/init.d/mysqld stop
2、使用 –skip-grant-tables 启动 mysql，忽略授权登录验证
[root@centos02 tools]# /application/mysql/bin/mysqld_safe –skip-grant-tables –user=mysql &
[root@centos02 tools]# mysql -uroot -p 登录时空密码
mysql> update mysql.user set password=password(‘123456′) where user=’root’ and host=’localhost’; #修改密码为 123456
Query OK, 0 rows affected (0.06 sec)
Rows matched: 1 Changed: 0 Warnings:
mysql> flush privileges;
Query OK, 0 rows affected (0.11 sec)
[root@centos02 tools]# mysqladmin -uroot -p123456 shutdown 优雅的关闭数据库
160802 13:01:47 mysqld_safe mysqld from pid file /application/mysql/data/centos02.pid ended
[1]+ Done /application/mysql/bin/mysqld_safe –skip-grant-tables –user=mysql
[root@centos02 tools]#
[root@centos02 tools]# /etc/init.d/mysqld start
Starting MySQL.. [OK]
[root@centos02 tools]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.49 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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>
多实例找回丢失的密码
killall mysqld
mysqld_safe –defaults-file=/data/3306/my.cnf –skip-grant-tables &
mysql -uroot -p -S /data/3306/mysql.sock 登录时空密码
mysql> update mysql.user set password=password(‘123456′) where user=’root’ and host=’localhost’; #修改密码为 123456
mysql> flush privileges;
killall mysqld
/data/3306/mysql start
1、创建数据库
mysql> create database martin;
Query OK, 1 row affected (0.05 sec)
mysql> show create database martin\G;
*************************** 1. row ***************************
Database: martin
Create Database: CREATE DATABASE `martin` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)
mysql> create database martin_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; #gbk 代表字符集 gbk_chinese_ci 代表校对规则
Query OK, 1 row affected (0.00 sec)
mysql> show create database martin_gbk\G;
*************************** 1. row ***************************
Database: martin_gbk
Create Database: CREATE DATABASE `martin_gbk` /*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.00 sec)
mysql> create database martin_utf8 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; #utf8 编码，字符集的不一致时数据库乱码的罪魁祸首
Query OK, 1 row affected (0.00 sec)
mysql> show create database martin_utf8\G;
*************************** 1. row ***************************
Database: martin_utf8
Create Database: CREATE DATABASE `martin_utf8` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
编译时候指定了特定的字符集，则以后创建的数据库就不需要指定字符集了
企业环境怎么创建数据库
1、根据开发的程序确定字符集建议 utf8
2、编译时候指定字符集
3、然后建库的时候默认创建即可
显示数据库
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| martin |
| martin_gbk |
| martin_utf8 |
| mysql |
| performance_schema |
| test |
+——————–+
7 rows in set (0.21 sec)
mysql> show databases like ‘mar%’;
+—————–+
| Database (mar%) |
+—————–+
| martin |
| martin_gbk |
| martin_utf8 |
+—————–+
3 rows in set (0.00 sec)
删除数据库
mysql> drop database martin;
Query OK, 0 rows affected (0.27 sec)
连接数据库
mysql> use mysql;
Database changed
查看当前连接的数据库
mysql> select database();
+————+
| database() |
+————+
| mysql |
+————+
1 row in set (0.00 sec)
查看当前的用户
mysql> select user();
+—————-+
| user() |
+—————-+
| root@localhost |
+—————-+
1 row in set (0.00 sec)
查看当前数据库包含的表信息
mysql> show tables;
+—————————+
| Tables_in_mysql |
+—————————+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+—————————+
24 rows in set (0.03 sec)
删除用户
语法：drop user ‘user’@’ 主机名 ’
mysql> select user,host from mysql.user;
+——–+———–+
| user | host |
+——–+———–+
| root | 127.0.0.1 |
| root | ::1 |
| root | centos02 |
| root | localhost |
| system | localhost |
+——–+———–+
5 rows in set (0.00 sec)
mysql> drop user ‘root’@’::1′;
Query OK, 0 rows affected (0.05 sec)
mysql> select user,host from mysql.user;
+——–+———–+
| user | host |
+——–+———–+
| root | 127.0.0.1 |
| root | centos02 |
| root | localhost |
| system | localhost |
+——–+———–+
4 rows in set (0.00 sec)
创建 mysql 用户及赋予用户权限
mysql> grant all on martin.* to zabbix@’localhost’ identified by ‘123456’; #在创建用户时候同时进行授权
Query OK, 0 rows affected (0.05 sec)
mysql> select user,host from mysql.user;
+——–+———–+
| user | host |
+——–+———–+
| root | 127.0.0.1 |
| root | centos02 |
| root | localhost |
| system | localhost |
| zabbix | localhost |
+——–+———–+
5 rows in set (0.00 sec)
mysql> show grants for zabbix@’localhost’;
+—————————————————————————————————————+
| Grants for zabbix@localhost |
+—————————————————————————————————————+
| GRANT USAGE ON *.* TO ‘zabbix’@’localhost’ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ |
| GRANT ALL PRIVILEGES ON `martin`.* TO ‘zabbix’@’localhost’ |
+—————————————————————————————————————+
2 rows in set (0.00 sec)
mysql> grant all on martin.* to zabbix@’localhost’ identified by ‘123456’; #在创建用户时候同时进行授权
等同于下面两句话
mysql> create user martin@’localhost’ identified by ‘123456’; 创建用户及设置用户密码
grant all on zabbix.* martin@’localhost’; 授权
mysql> create user martin123@’localhost’ identified by ‘123456’; 创建用户不进行授权，默认是 USAGE 权限
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show grants for martin123@’localhost’;
+——————————————————————————————————————+
| Grants for martin123@localhost |
+——————————————————————————————————————+
| GRANT USAGE ON *.* TO ‘martin123’@’localhost’ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ |
+——————————————————————————————————————+
1 row in set (0.00 sec)
授权局域网内主机远程连接数据库
mysql> grant all on martin.* to zabbix@’172.16.80.%’ identified by ‘123456’;
通过实验获得 all privilege 到底有哪些权限
mysql> show grants for zabbix@’localhost’;
+—————————————————————————————————————+
| Grants for zabbix@localhost |
+—————————————————————————————————————+
| GRANT USAGE ON *.* TO ‘zabbix’@’localhost’ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ |
| GRANT ALL PRIVILEGES ON `martin`.* TO ‘zabbix’@’localhost’ |
+—————————————————————————————————————+
2 rows in set (0.00 sec)
mysql> revoke insert on martin.* from ‘zabbix’@’localhost’; #回收 insert 权限
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for zabbix@’localhost’;
+———————————————————————————————————————————————————————————————————————————-+
| Grants for zabbix@localhost |
+———————————————————————————————————————————————————————————————————————————-+
| GRANT USAGE ON *.* TO ‘zabbix’@’localhost’ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9’ |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `martin`.* TO ‘zabbix’@’localhost’ |
+———————————————————————————————————————————————————————————————————————————-+
2 rows in set (0.00 sec)
可以看到剩下的权限有
SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE
CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER
mysql> select * from mysql.user\G;
*************************** 1. row ***************************
Host: localhost
User: root
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
企业生产环境如何授权用户权限
1、博客 cms 等产品的数据库授权：
对于 web 连接用户授权尽量采用最小化原则，很多开源软件都是 web 界面安装，因此在安全期间除了
select insert update delete 4 个权限外，还需要 create drop 等比较危险的权限
mysql> grant select,insert,update,delete,create,drop on blog.* to blog@’172.16.80.%’ identified by ‘123456’;
2、生成数据库表后，要回收 create drop 授权
revoke create on blog.* from blog @’172.16.80.%’;
revoke drop on blog.* from blog @’172.16.80.%’;
创建表语法
create table 表名 (字段名 1 类型 1，字段名 n，类型 n);
mysql> create table student(id int(4) not null,name char(20) not null, age tinyint(20) not null default 0,dept varchar(16) default null);
Query OK, 0 rows affected (0.21 sec)
查看建表的结构
mysql> desc student;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
| age | tinyint(20) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
4 rows in set (0.03 sec)
查看已建表的语句
mysql> show create table student \G;
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL,
`age` tinyint(20) NOT NULL DEFAULT ‘0’,
`dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.03 sec)
为表的字段创建索引
索引就像书的目录一样，如果在字段上建立了索引，那么以索引列为查询条件时可以加快查询速度，这是 mysql 优化的重要内容之一
建立主键索引
mysql> create table student1(id int(4) not null AUTO_INCREMENT,
name char(20) not null, age tinyint(20) not null default 0,dept varchar(16) default null,
primary key(id),
key index_name(name)
);
primary key(id) 表示主键
key index_name(name) name 字段普通索引
mysql> desc student1;
+——-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+—————-+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(20) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+——-+————-+——+—–+———+—————-+
4 rows in set (0.00 sec)
利用 alter 命令修改 id 列为自增主键列（一般在建表的时候就该创建主键，所以一般不这样使用）
mysql> desc student;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(4) | NO | | NULL | |
| name | char(20) | NO | | NULL | |
| age | tinyint(20) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
4 rows in set (0.00 sec)
mysql>
mysql>
mysql> alter table student change id id int primary key auto_increment;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+——-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| age | tinyint(20) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+——-+————-+——+—–+———+—————-+
4 rows in set (0.00 sec)
增加索引
mysql> alter table student add index index_name(name);
Query OK, 0 rows affected (1.63 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+——-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | MUL | NULL | |
| age | tinyint(20) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+——-+————-+——+—–+———+—————-+
4 rows in set (0.00 sec)
删除索引
mysql> alter table student drop index index_name;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+——-+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL | |
| age | tinyint(20) | NO | | 0 | |
| dept | varchar(16) | YES | | NULL | |
+——-+————-+——+—–+———+—————-+
4 rows in set (0.00 sec)
问题 1：既然索引可以加快查询速度，那么给所有的列建索引吧？
解答：因为索引不但占用系统空间，更新数据库时还需要维护索引数据的，因此，索引是一把双刃剑，并不是越多越好
问题 2：需要在哪些列上创建索引？
解答：select user,host from mysql.user where host=… 索引一定要创建在 where 后的条件列上，而不是 select 后的选择数据的列