mysql 开发进阶篇系列 31 工具篇(mysql连接工具与MyISAM表压缩工具)

图片 2

一.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。

  1. 优化表

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文件大小。

七、mysqlimport
(数据导入工具)

您可能感兴趣的文章:

  • 5个常用的MySQL数据库管理工具详细介绍
  • Mysql导入导出工具Mysqldump和Source命令用法详解
  • MYSQL
    ZIP免安装版配置步骤及图形化管理工具mysql-workbench
  • MySQL性能分析工具profile使用教程
  • Mysqlslap MySQL压力测试工具
    简单教程
  • 利用Xtrabackup工具备份及恢复(MySQL
    DBA的必备工具)
  • mysql中优化和修复数据库工具mysqlcheck详细介绍
  • Mysql自带profiling性能分析工具使用分享
  • 五款常用mysql slow
    log分析工具的比较分析
  • mytop 使用介绍
    mysql实时监控工具

使用方法:

shell> mysqldump -h192.18.10.10 -p3306 -uroot -p test > test.sql

mysqlshow
客户端对象查找工具,用来很快的查找存在哪些数据库,数据库中的表、表中的列或索引,和
mysql 客户端工具很类似,不过有些特性是 mysql 客户端工具所不具备的。

如果不指定数据库,则显示每个数据库的名称、表数量、记录数量;

  • -c, –check (检查表)
  • -r, –repair (修复表)
  • -a, –analyze (分析表)
  • -o, –optimize (优化表)

其中,默认选项是 -c (检查表)

1. 连接选项

shell> mysqlimport [options] db_name textfile1
root@bogon:/usr/local/mysql/bin# ./mysqldump t2 emp -T ./bak
  1. 检查表

myisampack 是一个表压缩工具,可以使用很高的压缩率来对 myisam
存储引擎的表进行压缩,使得压缩后的表占用比压缩前小得多的空间。但是压缩后的表将成为一个只读表,不能进行
DML 操作。

“-E” 选项类似于 mysql 里面执行 sql 语句后加 “G”, 经常和 -e 一起使用。

示例:

mysqladmin
是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。它的功能与
mysql 客户端非常类似,主要区别在于它更侧重于一些管理方面的功能。

zj@bogon:/usr/local/mysql/bin$ sudo ./mysqlbinlog --no-defaults /data/mysql/mysql-bin.000001 -o 20 -r ./logfile
zj@bogon:/usr/local/mysql/bin$ sudo more ./logfile
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
...

总结

示例:

由于服务器生成的二进制文件以二进制格式保存,所以如果想要检查这些文件的文本格式,就会用到
mysqlbinlog 日志管理工具。

八、mysqlshow
(数据库对象查看工具)

zj@bogon:/data/mysql$ mysqlcheck -c t2
t2.test1      OK
t2.test2      OK
  1. 分析表
root@bogon:/usr/local/mysql/bin# more ./bak/emp.sql
-- MySQL dump 10.13 Distrib 5.7.18, for Linux (x86_64)
--
-- Host: localhost Database: t2
-- ------------------------------------------------------
-- Server version 5.7.18-log

/*!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 utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `emp`
--

DROP TABLE IF EXISTS `emp`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp` (
 `id` int(11) NOT NULL DEFAULT '0',
 `name` varchar(10) DEFAULT NULL,
 `context` text,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

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

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!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 2017-09-21 12:07:38


root@bogon:/usr/local/mysql/bin# more ./bak/emp.txt 
1 a a
2 b b
  • -u, –user=name    指定用户名
  • -p, –password[=name]    指定密码 
  • -h, –host=name    指定服务器 IP 或者域名 
  • -P, –port=#    指定连接端口
[client]
user=root
password=000000
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4
# 这三种方式都是可以的
shell> mysql -u root
shell> mysql -uroot
shell> mysql -user=root

此选项显示了两部分内容,一部分是指定表的表结构,另一部分中是指定表的当前索引信息

mysqlimport 是客户端数据导入工具,用来导入 mysqldump 加 -T
选项后导出的文本文件。

可以在备份期间使用,使得数据无法被更新,从而使备份的数据保持一致性,可以配合
-F 选项一起使用。

root@bogon:/usr/local/mysql/bin# ls ./bak
emp.sql emp.txt

举例:

root@bogon:/usr/local/mysql/bin# mkdir bak
  1. –start-position=# 和 –stop-position=#,
    与日期范围类似,不过可以更精确的表示范围。
  1. 查看 bak 目录,发现两个文件
  1. 将 t2 数据库下的表 emp 备份到 bak 目录下
  1. 示例准备:创建新日志,新建库 t1 和 t2, 以及分别新建表 test1 和 test2

-c 或者 –complete-insert 选项使得输出文件中的 insert
语句包括字段名称,默认是不包括字段名称的。

  1. 修复表

如果指定具体数据库中的具体表,则显示表的字段信息。

root@bogon:/usr/local/mysql/bin# ./mysqldump --compact t2 emp > emp.sql
root@bogon:/usr/local/mysql/bin# more emp.sql
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp` (
 `id` int(11) NOT NULL DEFAULT '0',
 `name` varchar(10) DEFAULT NULL,
 `context` text,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `emp` VALUES (1,'a','a'),(2,'b','b');

在默认情况下,这两个参数都自动加上。

zj@bogon:/usr/local/mysql/bin$ mysqladmin -uroot -p shutdown
Enter password: 
zj@bogon:~$ mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 19
...
shell> mysqladmin [options] command [command-options]...
perror [options] [errorcode [errorcode]]

zj@bogon:/usr/local/mysql/bin$ perror 30
OS error code 30: Read-only file system
zj@bogon:/usr/local/mysql/bin$ perror 60
OS error code 60: Device not a stream
zj@bogon:/usr/local/mysql/bin$ perror 30 60
OS error code 30: Read-only file system
OS error code 60: Device not a stream
  1. -k 或者 –keys (显示指定表中的所有索引)
You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图