运维版MySQL该如何增删改查

本文主要给大家介绍运维版MySQL该如何增删改查，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下运维版MySQL该如何增删改查吧。

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; 会把所有的mysql参数都显示出来

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)

mysql> desc zabbix.hosts;

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

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

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

| hostid             | bigint(20) unsigned | NO   | PRI | NULL    |       |

| proxy_hostid       | bigint(20) unsigned | YES  | MUL | NULL    |       |

| host               | varchar(128)        | NO   | MUL |         |       |

| status             | int(11)             | NO   | MUL | 0       |       |

。。。。。。此处省略多行

mysql> select host from zabbix.hosts;

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

| host                            |

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

| AC-1               |

| AC-2             |

。。。。。。

mysql> select host,status from zabbix.hosts;

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

| host                            | status |

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

| Template OS Linux               |      3 |

| Template App Zabbix Server      |      3 |

| Template App Zabbix Proxy       |      3 |

| Template App Zabbix Agent       |      3 |

| Template SNMP Interfaces        |      3 |

。。。。。。

mysql> select host,status from zabbix.hosts where status like '0';

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

| host            | status |

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

|  ddd            |      0 |

| {#VM.UUID}      |      0 |

| {#HV.UUID}      |      0 |

| zabbix server   |      0 |

。。。。。。

增加主键

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';-->

mysql> select count(*) from mysql.user; 查看user表中有多少行

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

| count(*) |

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

|        7 |

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

1 row in set (0.00 sec)

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

17.查看当前使用库：

mysql> select database();

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

| database() |

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

| mysql      |

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

1 row in set (0.00 sec)

18.修改密码：

mysql> update mysql.user set password=password('123456') where user='root';

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

看完以上关于运维版MySQL该如何增删改查，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的行业资讯栏目的。