生产系统环境：[sky@sky9896 ~]$ cat /etc/redhat-release Centos release 6.8 (Final)1. 登录数据库：[s
生产系统环境：
[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
Copyright (c) 2000, 2016, oracle and/or its affiliates. All rights reserved.
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`
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; #查看表结构，方法一
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show columns from test; #查看表结构，方法二
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| name | varchar(16) | 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;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | YES | | NULL | |
| age | tinyint(2) | YES | | NULL | |
| name | varchar(16) | 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
;
;
;
;
;
;
;
;
;
;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
;
;
CREATE TABLE `test` (
`id` int(4) DEFAULT NULL,
`age` tinyint(2) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE;
;
INSERT INTO `test` VALUES (1,NULL,'sky9890'),(2,NULL,'坚持学习MySQL'),(3,NULL,'参加项管考试');
;
UNLOCK TABLES;
;
;
;
;
;
;
;
;
-- 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</opt/bak.sql
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
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
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;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | 0 | |
| age | tinyint(2) | YES | | NULL | |
| name | varchar(16) | 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;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | 0 | |
| age | tinyint(2) | YES | | NULL | |
| name | varchar(16) | YES | MUL | 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;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | 0 | |
| age | tinyint(2) | YES | | NULL | |
| name | varchar(16) | YES | MUL | NULL | |
| shouji | char(11) | 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 |
| 2 | NULL | ????MySQL | 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 |
| 2 | NULL | 坚持学习MySQL | NULL |
| 3 | NULL | 参加项管考试 | 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;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | 0 | |
| age | tinyint(2) | YES | | NULL | |
| name | varchar(16) | YES | MUL | NULL | |
| shouji | char(11) | YES | MUL | 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%';
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-------------+
| 1 | SIMPLE | test | range | index_name_shouji | index_name_shouji | 32 | NULL | 1 | Using where |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-------------+
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)
--结束END--
本文标题: Linux+MySQL运维的基础命令
本文链接: https://lsjlt.com/news/42111.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0