整理是最好的记忆

    运维经常用到的sql语句，长期更新~~~~~

1、查看有哪些用户

mysql> select user,host from mysql.user;

+------------+-----------+

| user       | host      |

+------------+-----------+

| root       | 127.0.0.1 |

| mysql_data | localhost |

| root       | localhost |

| zabbix     | localhost |

+------------+-----------+

2、查看mysql版本 位数

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.6.32    |

+-----------+

mysql> show variables like ‘%version_%‘;

+-------------------------+------------------------------+

| Variable_name           | Value                        |

+-------------------------+------------------------------+

| slave_type_conversions  |                              |

| version_comment         | MySQL Community Server (GPL) |

| version_compile_machine | i686                         |

| version_compile_os      | Linux                        |

+-------------------------+------------------------------+

4 rows in set (0.00 sec)

3、查看当前登录用户

mysql> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

4.查看二进制日志是否开启

mysql> show variables like "log_bin";

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin       | OFF   |

+---------------+-------+

5.查看默认存储引擎

mysql> show variables like ‘storage_engine%‘;

+----------------+--------+

| Variable_name  | Value  |

+----------------+--------+

| storage_engine | MyISAM |

+----------------+--------+

6.数据库

mysql> create database zabbix;

Query OK, 1 row affected (0.12 sec)

mysql> create database zabbix character set utf8;     --->创建数据库并设置字符集

Query OK, 1 row affected (0.00 sec)

mysql> show databases;   ---> 显示数据库

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| testdb             |

| zabbix             |

+--------------------+

mysql> use zabbix;   ---> 选择数据库

Database changed

mysql> show create database zabbix;  ---> 查看建库的完整语句

+----------+-----------------------------------------------------------------+

| Database | Create Database                                                 |

+----------+-----------------------------------------------------------------+

| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+-----------------------------------------------------------------+

mysql> drop database zabbix;  --->删除数据库

7.数据表

mysql> create table zabbix(user_id int primary key,user_name varchar(20),user_gender   varchar(20));   --->创建一个表zabbix

Query OK, 0 rows affected (0.43 sec)

mysql> show tables;  --->显示所有的表

+------------------+

| Tables_in_zabbix |

+------------------+

| zabbix           |

+------------------+

mysql> desc zabbix;   --->显示表结构

+-------------+-------------+------+-----+---------+-------+

| Field       | Type        | Null | Key | Default | Extra |

+-------------+-------------+------+-----+---------+-------+

| user_id     | int(11)     | NO   | PRI | NULL    |       |

| user_name   | varchar(20) | YES  |     | NULL    |       |

| user_gender | varchar(20) | YES  |     | NULL    |       |

+-------------+-------------+------+-----+---------+-------+

Insert into TableName (列1,列2.... 列n） Values (值1,值2,....值n)

     如果没有声明列明,则默认插入所有列.因此,值应该与全部列,按顺序一一对应.

mysql> insert into zabbix values(‘1‘,‘xiaoming‘,‘boy‘); --->向表中插入一列数据

mysql> select * from zabbix;

+---------+-----------+-------------+

| user_id | user_name | user_gender |

+---------+-----------+-------------+

|       1 | xiaoming  | boy         |

|       2 | laowang   | boy         |

|       3 | marong    | girl        |

+---------+-----------+-------------+

Update 表名 Set 列1 = 新值 1,列2 = 新值2,列n = 新值n..... Where  **

mysql> update zabbix set user_gender=‘girl‘ where user_id="1";        --->修改数据

mysql> select * from zabbix;

+---------+-----------+-------------+

| user_id | user_name | user_gender |

+---------+-----------+-------------+

|       1 | xiaoming  | girl        |

mysql> truncate zabbix;     --->清空表数据

mysql> select * from zabbix;

Empty set (0.00 sec)

mysql> drop table zabbix;     --->删除表

Query OK, 0 rows affected (0.05 sec)

增加主键

alter table tbName add primary key(主键所在列名);

例:alter table goods add primary key(id)

该例是把主键建立在id列上

修改表之删除主键

alter table tbName　drop primary key;

修改表之增加索引

alter table tbName add [unique|fulltext] index 索引名(列名);

修改表之删除索引

alter table tbName drop index 索引名;

8.备份数据库

mysqldump -u root -p zabbix>/zabbix.sql ----备份数据库zabbix

mysql -uroot -p zdj</mysql/zdj.sql ----恢复数据库

mysqldump -uroot -p --all-databases >all2.sql 备份所有的库

mysql -uroot -p <all2.sql 恢复所有的库

9.用户授权管理

格式：grant 权限 on 数据库名.表名 to 用户@登录主机 identified by "用户密码";

@ 后面是访问mysql的客户端IP地址（或是 主机名） % 代表任意的客户端，如果填写 localhost 为本地访问（那此用户就不能远程访问该mysql数据库了）。

mysql> grant all privileges on *.* to zabbix@‘%‘ identified by "123456";

mysql> show grants for zabbix\G;   ---- 》查看创建用户的权限

*************************** 1. row ***************************

Grants for zabbix@%: GRANT ALL PRIVILEGES ON *.* TO ‘zabbix‘@‘%‘ IDENTIFIED BY PASSWORD ‘*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9‘

mysql> delete from mysql.user where user=‘zabbix‘ and host=‘%‘;---> 删除用户

10.查看库大小：

MariaDB [information_schema]> SELECT SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) FROM information_schema.tables WHERE TABLE_SCHEMA=‘drcom‘;

+------------------------------------+

| SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) |

+------------------------------------+

| 26051771392 |

+------------------------------------+

1 row in set (0.42 sec)

结果是以字节为单位，除1024为K，除1048576为M。

11.查看表总数：

SELECT count(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA=‘drcomweixin‘;-->

12.查看表大小：

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA=‘库‘ AND TABLE_NAME=‘表名‘;

13.show processlist;

http://renxiangzyq.iteye.com/blog/835397

mysql线程数

[root@vps /]# mysqladmin processlist -uroot -p |wc -l

Enter password:

24

14.查看最大连接数

[root@vps /]# mysql -uroot -p -e "show variables like ‘%max_connections%‘;"

Enter password:

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| extra_max_connections | 1     |

| max_connections       | 1000  |

+-----------------------+-------+

15.查看当前连接数

[root@vps /]# mysql -uroot -p -e "show status like ‘Threads%‘;"

Enter password:

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Threads_cached    | 5     |

| Threads_connected | 16    |

| Threads_created   | 1697  |

| Threads_running   | 3     |

+-------------------+-------+

16.mysql查看状态

mysql> show status;

[root@vps ~]# mysqladmin -uroot -p*****  status

Warning: Using a password on the command line interface can be insecure.

Uptime: 8135940  Threads: 17  Questions: 117931987  Slow queries: 0  Opens: 215  Flush tables: 1  Open tables: 208  Queries per second avg: 14.495

~~~~~未完待续

本文出自 “拔电源的运维空间” 博客，请务必保留此出处http://zhangdj.blog.51cto.com/9210512/1878480

运维版MySQL增删改查