您好,登錄后才能下訂單哦!
本文主要給大家介紹運(yùn)維版MySQL該如何增刪改查,文章內(nèi)容都是筆者用心摘選和編輯的,具有一定的針對(duì)性,對(duì)大家的參考意義還是比較大的,下面跟筆者一起了解下運(yùn)維版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版本 位數(shù)
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、查看當(dāng)前登錄用戶
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
4.查看二進(jìn)制日志是否開啟
mysql> show variables; 會(huì)把所有的mysql參數(shù)都顯示出來(lái)
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
5.查看默認(rèn)存儲(chǔ)引擎
mysql> show variables like 'storage_engine%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
6.創(chuàng)建數(shù)據(jù)庫(kù)
mysql> create database zabbix;
Query OK, 1 row affected (0.12 sec)
mysql> create database zabbix character set utf8; --->創(chuàng)建數(shù)據(jù)庫(kù)并設(shè)置字符集
Query OK, 1 row affected (0.00 sec)
mysql> show databases; ---> 顯示數(shù)據(jù)庫(kù)
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
| zabbix |
+--------------------+
mysql> use zabbix; ---> 選擇數(shù)據(jù)庫(kù)
Database changed
mysql> show create database zabbix; ---> 查看建庫(kù)的完整語(yǔ)句
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| zabbix | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
mysql> drop database zabbix; --->刪除數(shù)據(jù)庫(kù)
7.數(shù)據(jù)表
mysql> create table zabbix(user_id int primary key,user_name varchar(20),user_gender varchar(20)); --->創(chuàng)建一個(gè)表zabbix
Query OK, 0 rows affected (0.43 sec)
mysql> show tables; --->顯示所有的表
+------------------+
| Tables_in_zabbix |
+------------------+
| zabbix |
+------------------+
mysql> desc zabbix; --->顯示表結(jié)構(gòu)
+-------------+-------------+------+-----+---------+-------+
| 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)
如果沒(méi)有聲明列明,則默認(rèn)插入所有列.因此,值應(yīng)該與全部列,按順序一一對(duì)應(yīng).
mysql> insert into zabbix values('1','xiaoming','boy'); --->向表中插入一列數(shù)據(jù)
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"; --->修改數(shù)據(jù)
mysql> select * from zabbix;
+---------+-----------+-------------+
| user_id | user_name | user_gender |
+---------+-----------+-------------+
| 1 | xiaoming | girl |
mysql> truncate zabbix; --->清空表數(shù)據(jù)
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.備份數(shù)據(jù)庫(kù)
mysqldump -u root -p zabbix>/zabbix.sql ----備份數(shù)據(jù)庫(kù)zabbix
mysql -uroot -p zdj</mysql/zdj.sql ----恢復(fù)數(shù)據(jù)庫(kù)
mysqldump -uroot -p --all-databases >all2.sql 備份所有的庫(kù)
mysql -uroot -p <all2.sql 恢復(fù)所有的庫(kù)
9.用戶授權(quán)管理
格式:grant 權(quán)限 on 數(shù)據(jù)庫(kù)名.表名 to 用戶@登錄主機(jī) identified by "用戶密碼";
@ 后面是訪問(wèn)mysql的客戶端IP地址(或是 主機(jī)名) % 代表任意的客戶端,如果填寫 localhost 為本地訪問(wèn)(那此用戶就不能遠(yuǎn)程訪問(wèn)該mysql數(shù)據(jù)庫(kù)了)。
mysql> grant all privileges on *.* to zabbix@'%' identified by "123456";
mysql> show grants for zabbix\G; ---- 》查看創(chuàng)建用戶的權(quán)限
*************************** 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.查看庫(kù)大?。?/span>
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)
結(jié)果是以字節(jié)為單位,除1024為K,除1048576為M。
11.查看表總數(shù):
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.查看表大?。?/span>
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='庫(kù)' AND TABLE_NAME='表名';
13.show processlist;
http://renxiangzyq.iteye.com/blog/835397
mysql線程數(shù)
[root@vps /]# mysqladmin processlist -uroot -p |wc -l
Enter password:
24
14.查看最大連接數(shù):
[root@vps /]# mysql -uroot -p -e "show variables like '%max_connections%';"
Enter password:
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| extra_max_connections | 1 |
| max_connections | 1000 |
+-----------------------+-------+
15.查看當(dāng)前連接數(shù):
[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查看狀態(tài):
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.查看當(dāng)前使用庫(kù):
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)
看完以上關(guān)于運(yùn)維版MySQL該如何增刪改查,很多讀者朋友肯定多少有一定的了解,如需獲取更多的行業(yè)知識(shí)信息 ,可以持續(xù)關(guān)注我們的行業(yè)資訊欄目的。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。