Linux+MySQL运维的基础命令

生产系统环境：

创新互联建站专业为企业提供河东网站建设、河东做网站、河东网站设计、河东网站制作等企业网站建设、网页设计与制作、河东企业网站模板建站服务，十余年河东做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

[sky@sky9896 ~]$ cat /etc/redhat-release

CentOS release 6.8 (Final)

1. 登录数据库：

[sky@sky9896 ~]$ MySQL –uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 151757

Server version: 5.5.49-cll-lve MySQL Community Server (GPL) by Atomicorp

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>

2. 查看数据库版本当前登录用户是什么

mysql> select version(); #查看数据库版本

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

| version() |

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

| 5.5.49-cll-lve |

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

1 row in set (0.00 sec)

mysql> select user(); #查看当前登录用户

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

| user() |

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

| root@localhost |

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

1 row in set (0.02 sec)

3.创建GBK字符集的数据库skyboy，并查看已建库的完整语句。

mysql> create database skyboy character set gbk collate gbk_chinese_ci;

Query OK, 1 row affected (0.03 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| a |

| back20150625ultrax |

| cacti |

| cacti20151220 |

| cacti20160104 |

| feifeicms |

| mysql |

| performance_schema |

| phpcom |

| skyboy |

| study |

| syslog |

| test |

| test1 |

| tt |

| ultrax |

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

17 rows in set (0.04 sec)

mysql> show create database skyboy\G #查看已建库的完整语句

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

Database: skyboy

Create Database: CREATE DATABASE `skyboy` /*!40100 DEFAULT CHARACTER SET gbk */

1 row in set (0.00 sec)

4.创建用户skyboy,使之可以管理数据库skyboy。

mysql> grant all on skyboy.* to skyboy@'localhost' identified by '123456';

Query OK, 0 rows affected (0.09 sec)

5.查看创建的用户skyboy拥有哪引起权限。

mysql> show grants for skyboy@'localhost'\G

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

Grants for skyboy@localhost: GRANT USAGE ON *.* TO 'skyboy'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

***************************2.row********************

Grants for skyboy@localhost: GRANT ALL PRIVILEGES ON `skyboy`.* TO 'skyboy'@'localhost'

2 rows in set (0.00 sec)

6.查看当前数据库里有哪些用户。

mysql> select user,host from mysql.user;

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

| user | host |

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

| cacti | % |

| cactiuser | % |

| root | % |

| cacti | * |

| root | * |

| root | 115.151.218.186 |

| cacti | 117.40.239.9 |

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| a1 | localhost |

| a2 | localhost |

| cactiuser | localhost |

| root | localhost |

| sky9896 | localhost |

| skyboy | localhost |

| | sky9896 |

| root | sky9896 |

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

18 rows in set (0.00 sec)

7.进入skyboy数据库

mysql> use skyboy;

Database changed

mysql> select database();

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

| database() |

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

| skyboy |

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

1 row in set (0.00 sec)

8.创建一innodb引擎字符集为GBK表test,字段为id和name varchar(16),查看建表结构及SQL语句。

mysql> create table test(

-> id int(4),

-> name varchar(16)

-> )ENGINE=innodb default charset=gbk;

Query OK, 0 rows affected (0.35 sec)

mysql> show tables;

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

| Tables_in_skyboy |

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

| test |

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

1 row in set (0.00 sec)

mysql> desc test; #查看表结构，方法一

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

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

| id | int(4) | YES | | NULL | |

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

2 rows in set (0.00 sec)

mysql> show columns from test; #查看表结构，方法二

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

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

| id | int(4) | YES | | NULL | |

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

2 rows in set (0.00 sec)

mysql> show create table test\G #查看表结构

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

Table: test

Create Table: CREATE TABLE `test` (

`id` int(4) DEFAULT NULL,

`name` varchar(16) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

9.插入一条数据1，skyboy

mysql> insert into test values(1,'skyboy');

Query OK, 1 row affected (0.06 sec)

mysql> select * from test;

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

| id | name |

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

| 1 | skyboy |

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

1 row in set (0.00 sec)

10.批量插入数据2，坚持学MySQL,3,备考项管。要求中文不能乱码。

mysql> insert into test values(2,'坚持学习MySQL'),(3,'参加项管考试'); #英文状态下的标点符号

Query OK, 2 rows affected (0.07 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test;

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

| id | name |

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

| 1 | skyboy |

| 2 | 坚持学习MySQL |

| 3 | 参加项管考试 |

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

3 rows in set (0.00 sec)

11.查询插入的所有记录，查询名字为skyboy的记录。查询id大于1的记录。

mysql> select * from test;

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

| id | name |

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

| 1 | skyboy |

| 2 | 坚持学习MySQL |

| 3 | 参加项管考试 |

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

3 rows in set (0.00 sec)

mysql> select * from test; #查询插入的所有记录

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

| id | name |

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

| 1 | skyboy |

| 2 | 坚持学习MySQL |

| 3 | 参加项管考试 |

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

3 rows in set (0.00 sec)

mysql> select * from test where name='skyboy'; #查询名字为skyboy的记录

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

| id | name |

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

| 1 | skyboy |

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

1 row in set (0.00 sec)

mysql> select * from test where id>1; #查询id大于1的记录

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

| id | name |

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

| 2 | 坚持学习MySQL |

| 3 | 参加项管考试 |

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

2 rows in set (0.00 sec)

12.把数据id等于 1的名字skyboy更改为sky9890。

mysql> update test set name='sky9890' where id=1;

Query OK, 1 row affected (0.05 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;

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

| id | name |

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

| 1 | sky9890 |

| 2 | 坚持学习MySQL |

| 3 | 参加项管考试 |

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

3 rows in set (0.00 sec)

13.在字段name前插入age 字段，类型tinyint(2)。

mysql> alter table test add age tinyint(2) after id;

Query OK, 3 rows affected (0.34 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

| id | int(4) | YES | | NULL | |

| age | tinyint(2) | YES | | NULL | |

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

3 rows in set (0.00 sec)

14.备份skyboy库

root@sky9896 ~]# mysqldump -uroot -p skyboy >/opt/bak.sql

Enter password:

[root@sky9896 ~]# ll /opt/bak.sql

-rw-r--r-- 1 root root 1923 8月 13 15:38 /opt/bak.sql

[root@sky9896 ~]# cat /opt/bak.sql

-- MySQL dump 10.13 Distrib 5.5.49, for Linux (x86_64)

-- Host: localhost Database: skyboy

-- ------------------------------------------------------

-- Server version 5.5.49-cll-lve

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Table structure for table `test`

DROP TABLE IF EXISTS `test`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `test` (

`id` int(4) DEFAULT NULL,

`age` tinyint(2) DEFAULT NULL,

`name` varchar(16) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk;

/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `test`

LOCK TABLES `test` WRITE;

/*!40000 ALTER TABLE `test` DISABLE KEYS */;

INSERT INTO `test` VALUES (1,NULL,'sky9890'),(2,NULL,'坚持学习MySQL'),(3,NULL,'参加项管考试');

/*!40000 ALTER TABLE `test` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2016-08-13 15:38:31

15.删除表中的所有数据，并查看。

mysql> delete from test; #逻辑删除表中的数据，一列一列的删除表中数据，速度慢

Query OK, 3 rows affected (0.07 sec)

mysql> truncate table test; #物理删除表中的数据，一次性全部都给清空，速度很快

Query OK, 0 rows affected (0.07 sec)

mysql> select * from test; #查看结果

Empty set (0.00 sec)

mysql> show tables;

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

| Tables_in_skyboy |

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

| test |

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

1 row in set (0.00 sec)

mysql> drop table test;

Query OK, 0 rows affected (0.07 sec)

mysql> show tables;

Empty set (0.00 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| a |

| back20150625ultrax |

| cacti |

| cacti20151220 |

| cacti20160104 |

| feifeicms |

| mysql |

| performance_schema |

| phpcom |

| skyboy |

| study |

| syslog |

| test |

| test1 |

| tt |

| ultrax |

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

17 rows in set (0.00 sec)

mysql> drop database skyboy;

Query OK, 0 rows affected (0.04 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| a |

| back20150625ultrax |

| cacti |

| cacti20151220 |

| cacti20160104 |

| feifeicms |

| mysql |

| performance_schema |

| phpcom |

| study |

| syslog |

| test |

| test1 |

| tt |

| ultrax |

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

16 rows in set (0.00 sec)

17.Linux命令行恢复以上删除的数据

恢复的时候，要先建一个skyboy空数据，然后在恢复。

[root@sky9896 ~]# mysql -uroot -p skyboy

Enter password:

mysql> use skyboy;

Database changed

mysql> show tables;

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

| Tables_in_skyboy |

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

| test |

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

1 row in set (0.00 sec)

mysql> select * from test;

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

| id | age | name |

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

| 1 | NULL | sky9890 |

| 2 | NULL | 坚持学习MySQL |

| 3 | NULL | 参加项管考试 |

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

3 rows in set (0.00 sec)

18.把GBK字符集修改为UTF8。

mysql> show variables like 'character_set%';

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

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

8 rows in set (0.00 sec)

mysql> set global character_set_database=gbk;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

[root@sky9896 ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 152566

Server version: 5.5.49-cll-lve MySQL Community Server (GPL) by Atomicorp

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> show variables like 'character_set%'; #数据库服务的字符集

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | gbk |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

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

8 rows in set (0.00 sec)

19.修改mysql密码

mysql> update mysql.user set password=PASSWORD('skyboy') where user='root'

and host='localhost';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

20.MySQL内中文数据乱码的原理及如何防止乱码？（可选）。

#客户端软件字符集要用utf8

[root@sky9896 ~]# cat /etc/sysconfig/i18n #修改字符配置文件

LANG="zh_CN.UTF-8"

21.在把id 列设置为主键，在Name字段上创建普通索引。

mysql> alter table skyboy.test add primary key(id);

Query OK, 3 rows affected (0.32 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> desc skyboy.test;

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

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

| id | int(4) | NO | PRI | 0 | |

| age | tinyint(2) | YES | | NULL | |

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

3 rows in set (0.00 sec)

mysql> create index index_name on skyboy.test(name);

Query OK, 0 rows affected (0.23 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc skyboy.test;

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

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

| id | int(4) | NO | PRI | 0 | |

| age | tinyint(2) | YES | | NULL | |

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

3 rows in set (0.00 sec)

22.在字段name后插入手机号字段(shouji)，类型char(11)。

mysql> alter table skyboy.test add shouji char(11) after name;

Query OK, 3 rows affected (0.23 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> desc skyboy.test;

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

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

| id | int(4) | NO | PRI | 0 | |

| age | tinyint(2) | YES | | NULL | |

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

4 rows in set (0.00 sec)

23.所有字段上插入 2条记录（自行设定数据）

mysql> insert into test values(4,21,'sky','20160813'),(5,98,'skyboy','20160810');

Query OK, 2 rows affected (0.04 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test;

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

| id | age | name | shouji |

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

| 1 | NULL | sky9890 | NULL |

| 3 | NULL | ?????? | NULL |

| 4 | 21 | sky | 20160813 |

| 5 | 98 | skyboy | 20160810 |

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

5 rows in set (0.00 sec)

以上显示了乱码,解决方式如下:

[root@sky9896 ~]# vi /etc/sysconfig/i18n

LANG="zh_CN.UTF-8"

远程退出

重新登录才能生效

mysql> use skyboy;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from test;

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

| id | age | name | shouji |

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

| 1 | NULL | sky9890 | NULL |

| 4 | 21 | sky | 20160813 |

| 5 | 98 | skyboy | 20160810 |

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

5 rows in set (0.00 sec)

24.在手机字段上对前 8个字符创建普通索引。

mysql> alter table test add index index_shouji(shouji(8));

Query OK, 0 rows affected (0.17 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

| id | int(4) | NO | PRI | 0 | |

| age | tinyint(2) | YES | | NULL | |

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

4 rows in set (0.00 sec)

25.查看创建的索引及索引类型等信息。

mysql> show index from skyboy.test\G

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

Table: test

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 5

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: test

Non_unique: 1

Key_name: index_name

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 5

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

*************************** 3. row ***************************

Table: test

Non_unique: 1

Key_name: index_shouji

Seq_in_index: 1

Column_name: shouji

Collation: A

Cardinality: 5

Sub_part: 8

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

3 rows in set (0.01 sec)

26.删除Name，shouji列的索引。

mysql> alter table test drop index index_name;

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> drop index index_shouji on test;

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from test\G;

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

Table: test

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 5

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

27.对Name列的前6 个字符以及手机列的前8个字符组建联

合索引。

mysql> alter table test add index index_name_shouji(name(6),shouji(8));

Query OK, 0 rows affected (0.15 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from test\G

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

Table: test

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 5

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: test

Non_unique: 1

Key_name: index_name_shouji

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 5

Sub_part: 6

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

*************************** 3. row ***************************

Table: test

Non_unique: 1

Key_name: index_name_shouji

Seq_in_index: 2

Column_name: shouji

Collation: A

Cardinality: 5

Sub_part: 8

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

3 rows in set (0.00 sec)

28.查询手机号以159开头的，名字为skybboy的记录。

mysql> select * from test where name='skyboy' and shouji like '159%';

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

| id | age | name | shouji |

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

| 5 | 98 | skyboy | 15907999899 |

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

1 row in set (0.00 sec)

29.查询上述语句的执行计划（是否使用联合索引等）。

mysql> explain select * from test where name='skyboy' and shouji like '159%';

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

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

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

1 row in set (0.00 sec)

mysql> explain select * from test where name='skyboy' and shouji like '159%'\G

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

id: 1

select_type: SIMPLE

table: test

type: range

possible_keys: index_name_shouji

key: index_name_shouji

key_len: 32

ref: NULL

rows: 1

Extra: Using where

1 row in set (0.00 sec)

30.把test表的引擎改成MyISAM。

MySQL 数据库 5.1 版本以前默认的引擎是 MyISAM

MySQL 数据库 5.5 版本以后默认的引擎都是 InnoDB

mysql> show create table test\G #查看test表引擎

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

Table: test

Create Table: CREATE TABLE `test` (

`id` int(4) NOT NULL DEFAULT '0',

`age` tinyint(2) DEFAULT NULL,

`name` varchar(16) DEFAULT NULL,

`shouji` char(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `index_name_shouji` (`name`(6),`shouji`(8))

) ENGINE=InnoDB DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

mysql> alter table test ENGINE=MyISAM; #修改默认引擎

Query OK, 5 rows affected (0.14 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> show create table test\G

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

Table: test

Create Table: CREATE TABLE `test` (

`id` int(4) NOT NULL DEFAULT '0',

`age` tinyint(2) DEFAULT NULL,

`name` varchar(16) DEFAULT NULL,

`shouji` char(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `index_name_shouji` (`name`(6),`shouji`(8))

) ENGINE=MyISAM DEFAULT CHARSET=gbk

1 row in set (0.00 sec)

名称栏目：Linux+MySQL运维的基础命令
文章起源：http://ybzwz.com/article/ggehoh.html

Linux+MySQL运维的基础命令

其他资讯