mysql连接工具与MyISAM表压缩工具,mysql学习记录

2020-01-05 作者:数据库资讯   |   浏览(165)

一.mysql 连接工具  

  在mysq提供的工具中,DBA使用最频繁的莫过于mysql。这里的mysql是指连接数据库的客户端工具。

  1.1 连接选项

-u, -- user=name 指定用户名
-p ,--password[=name] 指定密码
-h ,--host=name 指定服务器IP或者域名
-p, --port=# 指定连接端口

在实际连接中命令如下:mysql -u 用户名 -p 密码
[root@hsr ~]#  mysql -u root -p

-- 完整的连接命令如下
[root@hsr ~]#  mysql -u root -p -h 172.168.18.201 -P 3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.

  1.2 执行选项
    通过 "-e,--execute=name 执行sql语句并退出" 此选项不需要连接到mysql数据库后再执行,对于一些批处理脚本很方便,下面是从客户端直接查询mysql数据库中user表的User和Host字段:

[root@hsr ~]#  mysql -u root -p  mysql -e "select User,Host from user"
Enter password: 

    图片 1
  连接多个sql 语句用英文分号隔开如   select User,Host from user; select xxx。

mysql学习记录(二十四)--mysql相关工具

一、理论:
1.mysql:客户端连接工具
a.-u:指定用户名
b.-p:指定密码
c.-host:指定服务器IP或者域名
d.-P:指定连接端口
e.--default-character-set:客户端字符集选项
f.-e:执行选项
g.-E:将输出方式按照字段顺序显示
h.-f:强制执行sql
i.-v:显示更多信息
2.myisampack:myisam表压缩工具
3.mysqladmin:mysql管理工具
4.mysqlbinlog:日志管理工具
a.-d:指定数据库名称,只列出指定的数据库相关操作
b.-o:忽略掉日志中的前n行命令
c.-r:将输出的文本格式日志输出到指定文件
d.-s:显示简单格式,省略掉一些信息
e.--set-charset=char-name:在输出为文本格式时,在文件第一行上加上set names char-nam(可用于装载数据)
f.--start-datetime=name:指定日期间隔内的所有日志
g.--start-position:指定位置间隔内的所有日志
5.mysqlcheck:MyISAM表维护工具
a.-c:检查表
b.-r:修复表
c.-a:分析表
d.-o:优化表
6.mysqldump:数据导出工具
a.-u:用户名
b.-p:密码
c.-h:服务器IP或者域名
d.-P:连接端口
e.--add-drop-database:每个数据库创建语句前加上drop database
f.--add-drop-table:在每个表创建语句前加上drop table
g.-n:不包含数据库的创建语句
h.-t:不包含数据表的创建语句
i.-d:不包含数据
j.--compact:不包含默认选项中的各种注释
7.mysqlhostcopy:MyISAM表热备份工具
8.mysqlimport:数据导入工具
9.mysqlshow:数据库对象查看工具
10.perror:错误代码查看工具

11.replace:文本替换工具

二、实践:

 

[email protected]:~$ mysql -uroot -p(密码) --default-character-set=utf8
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 47
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, 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 'char%';
 -------------------------- ---------------------------------- 
| Variable_name            | Value                            |
 -------------------------- ---------------------------------- 
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
 -------------------------- ---------------------------------- 
8 rows in set (0.00 sec)

mysql> show variables like 'char%';Ctrl-C -- exit!
Aborted
[email protected]:~$ mysql -uroot -p(密码) --default-character-set=gbk;
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 48
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, 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 'char%';
 -------------------------- ---------------------------------- 
| Variable_name            | Value                            |
 -------------------------- ---------------------------------- 
| character_set_client     | gbk                              |
| character_set_connection | gbk                              |
| character_set_database   | utf8                             |
| character_set_filesystem | binary                           |
| character_set_results    | gbk                              |
| character_set_server     | utf8                             |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
 -------------------------- ---------------------------------- 
8 rows in set (0.00 sec)

mysql> Ctrl-C -- exit!
Aborted
[email protected]:~$ mysql -uroot -p(密码);
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 49
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, 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> use sakila;
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

[email protected]:~$ mysql -uroot -p(密码) -e 'use sakila;select * from payment limit 5;';
 ------------ ------------- ---------- ----------- -------- --------------------- --------------------- 
| payment_id | customer_id | staff_id | rental_id | amount | payment_date        | last_update         |
 ------------ ------------- ---------- ----------- -------- --------------------- --------------------- 
|          1 |           1 |        1 |        76 |   2.99 | 2005-05-25 11:30:37 | 2006-02-15 22:12:30 |
|          2 |           1 |        1 |       573 |   0.99 | 2005-05-28 10:35:23 | 2006-02-15 22:12:30 |
|          3 |           1 |        1 |      1185 |   5.99 | 2005-06-15 00:54:12 | 2006-02-15 22:12:30 |
|          4 |           1 |        2 |      1422 |   0.99 | 2005-06-15 18:02:53 | 2006-02-15 22:12:30 |
|          5 |           1 |        2 |      1476 |   9.99 | 2005-06-15 21:08:46 | 2006-02-15 22:12:30 |
 ------------ ------------- ---------- ----------- -------- --------------------- --------------------- 
[email protected]:~$ mysql -uroot -p(密码) -e 'use sakila;select * from payment limit 5;' -E;
*************************** 1. row ***************************
  payment_id: 1
 customer_id: 1
    staff_id: 1
   rental_id: 76
      amount: 2.99
payment_date: 2005-05-25 11:30:37
 last_update: 2006-02-15 22:12:30
*************************** 2. row ***************************
  payment_id: 2
 customer_id: 1
    staff_id: 1
   rental_id: 573
      amount: 0.99
payment_date: 2005-05-28 10:35:23
 last_update: 2006-02-15 22:12:30
*************************** 3. row ***************************
  payment_id: 3
 customer_id: 1
    staff_id: 1
   rental_id: 1185
      amount: 5.99
payment_date: 2005-06-15 00:54:12
 last_update: 2006-02-15 22:12:30
*************************** 4. row ***************************
  payment_id: 4
 customer_id: 1
    staff_id: 2
   rental_id: 1422
      amount: 0.99
payment_date: 2005-06-15 18:02:53
 last_update: 2006-02-15 22:12:30
*************************** 5. row ***************************
  payment_id: 5
 customer_id: 1
    staff_id: 2
   rental_id: 1476
      amount: 9.99
payment_date: 2005-06-15 21:08:46
 last_update: 2006-02-15 22:12:30
[email protected]:~$ cd ~/Downloads/
[email protected]:~/Downloads$ mkdir mysql
[email protected]:~/Downloads$ cd mysql/
[email protected]:~/Downloads/mysql$ vi a.sql;
(在此步添加了一些内容)
[email protected]:~/Downloads/mysql$ more a.sql;
insert into t2 values (1);
insert into t2 values (2222222222222222222222222);
insert into t2 values (3);
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码);
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 53
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, 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> use test;
Database changed
mysql> create table t2(
    -> id int(11)
    -> ) engine = innodb charset = utf8;
Query OK, 0 rows affected (0.05 sec)

mysql> Ctrl-C -- exit!
Aborted
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test < a.sql;
ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';
 ------ 
| id   |
 ------ 
|    1 |
 ------ 
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -f < a.sql 
ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';
 ------ 
| id   |
 ------ 
|    1 |
|    3 |
 ------ 
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -f -v ~/test.txt;
[email protected]:/usr/local/mysql/bin$ cd ~/
[email protected]:~$ more ~/test.txt 
-- MySQL dump 10.13  Distrib 5.5.44, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version 5.5.44-log

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t2`
--

DROP TABLE IF EXISTS `t2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t2`
--

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
INSERT INTO `t2` VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET [email protected]_TIME_ZONE */;

/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;

-- Dump completed on 2015-11-06  5:15:01
[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact -d test t2 > ~/Downloads//t2.txt
[email protected]:/usr/local/mysql/bin$ more ~/Downloads/t2.txt;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact -c test t2 > ~/Downloads/t2c.txt
[email protected]:/usr/local/mysql/bin$ more ~/Downloads/t2c.txt
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t2` (`id`) VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);

[email protected]:/usr/local/mysql/bin$ mysqld --verbose --help | grep 'default-character-set' | grep -v name ;
151106  5:18:52 [Warning] option 'table_definition_cache': unsigned value 100 adjusted to 400
151106  5:18:52 [Note] mysqld (mysqld 5.5.44-log) starting as process 74255 ...
151106  5:18:52 [Warning] Can't create test file /usr/local/mysql/data/ubuntu.lower-test
151106  5:18:52 [Warning] Can't create test file /usr/local/mysql/data/ubuntu.lower-test
151106  5:18:52 [Warning] One can only use the --user switch if running as root

mysqld: File '/usr/local/mysql/data/mysql-bin.index' not found (Errcode: 13)
151106  5:18:52 [ERROR] Aborting

[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact test t2 > ~/Downloads/testA.txt
[email protected]:/usr/local/mysql/bin$ more ~/Downloads/testA.txt 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t2` VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);
[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact --default-character-set=latin1 test t2 > ~/Downloads/testA.txt
[email protected]:/usr/local/mysql/bin$ more ~/Downloads/testA.txt 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t2` VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);

[email protected]:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码);
 -------------------- 
|     Databases      |
 -------------------- 
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| test1              |
 -------------------- 
[email protected]:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码) --count;
 -------------------- -------- -------------- 
|     Databases      | Tables |  Total Rows  |
 -------------------- -------- -------------- 
| information_schema |     40 |        20863 |
| mysql              |     24 |         2214 |
| performance_schema |     17 |           14 |
| sakila             |     33 |        50132 |
| test               |      1 |           13 |
| test1              |     20 |           67 |
 -------------------- -------- -------------- 
6 rows in set.
[email protected]:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码) test --count;
Database: test
 -------- ---------- ------------ 
| Tables | Columns  | Total Rows |
 -------- ---------- ------------ 
| t2     |        1 |         13 |
 -------- ---------- ------------ 
1 row in set.

[email protected]:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码) test a --count;
mysqlshow: Cannot get record count for db: test, table: a: Table 'test.a' doesn't exist
[email protected]:~/Downloads$ more t2c.txt
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `t2` (`id`) VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);
[email protected]:~/Downloads$ mysqlshow
 -------------------- 
|     Databases      |
 -------------------- 
| information_schema |
| test               |
 -------------------- 
[email protected]:~/Downloads$ mysqlshow -uroot -p(密码) test t2 --count;
Database: test  Table: t2  Rows: 13
 ------- --------- ----------- ------ ----- --------- ------- --------------------------------- --------- 
| Field | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
 ------- --------- ----------- ------ ----- --------- ------- --------------------------------- --------- 
| id    | int(11) |           | YES  |     |         |       | select,insert,update,references |         |
 ------- --------- ----------- ------ ----- --------- ------- --------------------------------- --------- 
[email protected]:~/Downloads$ mysql -uroot -p(密码);
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 88
Server version: 5.5.44-log Source distribution

Copyright (c) 2000, 2015, 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> use sakila;
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
[email protected]:~/Downloads$ mysqlshow -uroot -p(密码) test t2 -k;
Database: test  Table: t2
 ------- --------- ----------- ------ ----- --------- ------- --------------------------------- --------- 
| Field | Type    | Collation | Null | Key | Default | Extra | Privileges                      | Comment |
 ------- --------- ----------- ------ ----- --------- ------- --------------------------------- --------- 
| id    | int(11) |           | YES  |     |         |       | select,insert,update,references |         |
 ------- --------- ----------- ------ ----- --------- ------- --------------------------------- --------- 
Table has no keys
[email protected]:~/Downloads$ mysqlshow -uroot -p(密码) sakila actor -k;
Database: sakila  Table: actor
 ------------- ---------------------- ----------------- ------ ----- ------------------- ----------------------------- --------------------------------- --------- 
| Field       | Type                 | Collation       | Null | Key | Default           | Extra                       | Privileges                      | Comment |
 ------------- ---------------------- ----------------- ------ ----- ------------------- ----------------------------- --------------------------------- --------- 
| actor_id    | smallint(5) unsigned |                 | NO   | PRI |                   | auto_increment              | select,insert,update,references |         |
| first_name  | varchar(45)          | utf8_general_ci | NO   |     |                   |                             | select,insert,update,references |         |
| last_name   | varchar(45)          | utf8_general_ci | NO   | MUL |                   |                             | select,insert,update,references |         |
| last_update | timestamp            |                 | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references |         |
 ------------- ---------------------- ----------------- ------ ----- ------------------- ----------------------------- --------------------------------- --------- 
 ------- ------------ --------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 ------- ------------ --------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| actor | 0          | PRIMARY             | 1            | actor_id    | A         | 201         |          |        |      | BTREE      |         |               |
| actor | 1          | idx_actor_last_name | 1            | last_name   | A         | 201         |          |        |      | BTREE      |         |               |
 ------- ------------ --------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 

一、理论: 1.mysql:客户端连接工具 a.-u:指定用户名 b.-p:指定密码 c.-host:指定服务器IP或者域名 d.-P:指...

二. myisampack(MyISAM表压缩工具)

  myisampack是一个表压缩工具。在压缩之前需要确认mysqld已关闭或者要压缩的表不会有其他的sql操作;而且压缩过程会很占用cpu资源,建议在服务器空闲的状态进行。

  可以将数据文件压缩到40%-70%,当以后使用表时,解压缩列需要的信息被读入内存。当访问具体的记录时性能会更好,因为你只需要解压缩一个记录。压缩后的表也将成为一个只读表,不能进行DML操作。

--压缩前,查看test库中表的数据文件大小,其中有个a表是myisam类型, 将压缩MYD文件

    [root@hsr ~]# cd /usr/local/mysql/data

    [root@hsr data]# cd test
    [root@hsr test]# ls -ltr
    总用量 24
    -rw-r----- 1 mysql mysql   61 8月  24 16:43 db.opt
    -rw-r----- 1 mysql mysql 8566 8月  28 14:29 a.frm
    -rw-r----- 1 mysql mysql 1024 8月  28 14:32 a.MYI
    -rw-r----- 1 mysql mysql  456 8月  28 14:32 a.MYD

-- myisampack先到基地址里面找到该命令(用rpm安装可以在根目录下直接用该命令)
[root@hsr ~]# cd /usr/local/mysql/bin

图片 2

-- 压缩a表,提示太小无法压缩
[root@hsr bin]# ./myisampack /usr/local/mysql/data/test/a
/usr/local/mysql/data/test/a is too small to compress
如果压缩成功,该表就只能读,不能写。可以在查看MYD文件大小。

本文由yzc216亚洲城发布于数据库资讯,转载请注明出处:mysql连接工具与MyISAM表压缩工具,mysql学习记录

关键词: yzc216亚洲城 www.yzc216.c