您的位置:澳门新葡8455最新网站 > 数据库管理 > mysql连接工具与MyISAM表压缩工具,mysql相关工具

mysql连接工具与MyISAM表压缩工具,mysql相关工具

发布时间:2019-12-02 18:02编辑:数据库管理浏览(162)

    后生可畏.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 语句用Republic of Croatia语分号隔开分离如   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能源,建议在服务器空闲的气象举行。

      能够将数据文件压缩到三成-五分之四,当今后使用表时,解压缩列要求的音信被读入内存。当访问具体的笔录时品质会更加好,因为你只须求解压缩三个记录。压缩后的表也将形成三个只读表,无法进行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文件大小。
    

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:mysql连接工具与MyISAM表压缩工具,mysql相关工具

    关键词: