運維版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該如何增刪改查，很多讀者朋友肯定多少有一定的了解，如需獲取更多的行業知識信息 ，可以持續關注我們的行業資訊欄目的。