mysql学习记录（二十四）--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:文本替换工具

二、实践：

abc@ubuntu:~$ 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
abc@ubuntu:~$ 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
abc@ubuntu:~$ 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

abc@ubuntu:~$ 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 |
+------------+-------------+----------+-----------+--------+---------------------+---------------------+
abc@ubuntu:~$ 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
abc@ubuntu:~$ cd ~/Downloads/
abc@ubuntu:~/Downloads$ mkdir mysql
abc@ubuntu:~/Downloads$ cd mysql/
abc@ubuntu:~/Downloads/mysql$ vi a.sql;
(在此步添加了一些内容)
abc@ubuntu:~/Downloads/mysql$ more a.sql;
insert into t2 values (1);
insert into t2 values (2222222222222222222222222);
insert into t2 values (3);
abc@ubuntu:~/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
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码) test < a.sql;
ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';
+------+
| id   |
+------+
|    1 |
+------+
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码) test -f < a.sql 
ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';
+------+
| id   |
+------+
|    1 |
|    3 |
+------+
abc@ubuntu:~/Downloads/mysql$ mysql -uroot -p(密码) test -f -v ~/test.txt;
abc@ubuntu:/usr/local/mysql/bin$ cd ~/
abc@ubuntu:~$ 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 @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 `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 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 2015-11-06  5:15:01
abc@ubuntu:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact -d test t2 > ~/Downloads//t2.txt
abc@ubuntu:/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 */;
abc@ubuntu:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact -c test t2 > ~/Downloads/t2c.txt
abc@ubuntu:/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);

abc@ubuntu:/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

abc@ubuntu:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact test t2 > ~/Downloads/testA.txt
abc@ubuntu:/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);
abc@ubuntu:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact --default-character-set=latin1 test t2 > ~/Downloads/testA.txt
abc@ubuntu:/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);

abc@ubuntu:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码);
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| test1              |
+--------------------+
abc@ubuntu:/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.
abc@ubuntu:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码) test --count;
Database: test
+--------+----------+------------+
| Tables | Columns  | Total Rows |
+--------+----------+------------+
| t2     |        1 |         13 |
+--------+----------+------------+
1 row in set.

abc@ubuntu:/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
abc@ubuntu:~/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);
abc@ubuntu:~/Downloads$ mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| test               |
+--------------------+
abc@ubuntu:~/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 |         |
+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+
abc@ubuntu:~/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
abc@ubuntu:~/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
abc@ubuntu:~/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      |         |               |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+