mysql学习记录（二十四）--mysql相关工具_MySQL
- WBOYOriginal
- 2016-05-30 17:10:04804Durchsuche
一、理论：
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>
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：MySQL 多主多活 Galera 集群部署使用_MySQLNächster Artikel：mysql学习记录（二十）--MysqlServer参数调整_MySQL