mysql学习记录（二十四）--mysql相关工具_MySQL
tool
一、理论：
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 | | |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+</a.sql>Copy after login
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
How Long Does It Take To Beat Split Fiction?
1 months ago
By DDD
R.E.P.O. Best Graphic Settings
2 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Assassin's Creed Shadows: Seashell Riddle Solution
1 weeks ago
By DDD
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
2 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Which libraries in Go are developed by large companies or provided by well-known open source projects?
Apr 02, 2025 pm 04:12 PM
Which libraries in Go are developed by large companies or well-known open source projects? When programming in Go, developers often encounter some common needs, ...
Gitee Pages static website deployment failed: How to troubleshoot and resolve single file 404 errors?
Apr 04, 2025 pm 11:54 PM
GiteePages static website deployment failed: 404 error troubleshooting and resolution when using Gitee...
How to manually trigger the onBlur event of a cell in Avue-crud row editing mode?
Apr 04, 2025 pm 02:00 PM
The onBlur event that implements Avue-crud row editing in the Avue component library manually triggers the Avue-crud component. It provides convenient in-line editing functions, but sometimes we need to...
How to quickly build a foreground page in a React Vite project using AI tools?
Apr 04, 2025 pm 01:45 PM
How to quickly build a front-end page in back-end development? As a backend developer with three or four years of experience, he has mastered the basic JavaScript, CSS and HTML...
How to use browser embedded scripts to automate web page elements operations and numerical calculations?
Apr 04, 2025 pm 12:09 PM
Web page automation and numerical calculation: Implementation based on browser embedded scripts This article will explore how to use browser embedded scripts to implement web page elements operations and numbers...
How to create a React application with pnpm instead of npm?
Apr 04, 2025 pm 06:45 PM
About using pnpm instead of npm to create a React application using npx...
What is the current audience status of the Go framework? Is it more suitable for different business needs to choose gRPC or GoZero?
Apr 02, 2025 pm 03:57 PM
Analysis of the audience status of Go framework In the current Go programming ecosystem, developers often face choosing the right framework to meet their business needs. Today we...
How to achieve gap effect on the card and coupon layout with gradient background?
Apr 05, 2025 am 07:48 AM
Realize the gap effect of card coupon layout. When designing card coupon layout, you often encounter the need to add gaps on card coupons, especially when the background is gradient...
