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 | | | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+</a.sql>

Heiße KI -Werkzeuge

Undresser.AI Undress
KI-gestützte App zum Erstellen realistischer Aktfotos

AI Clothes Remover
Online-KI-Tool zum Entfernen von Kleidung aus Fotos.

Undress AI Tool
Ausziehbilder kostenlos

Clothoff.io
KI-Kleiderentferner

AI Hentai Generator
Erstellen Sie kostenlos Ai Hentai.

Heißer Artikel

Heiße Werkzeuge

Notepad++7.3.1
Einfach zu bedienender und kostenloser Code-Editor

SublimeText3 chinesische Version
Chinesische Version, sehr einfach zu bedienen

Senden Sie Studio 13.0.1
Leistungsstarke integrierte PHP-Entwicklungsumgebung

Dreamweaver CS6
Visuelle Webentwicklungstools

SublimeText3 Mac-Version
Codebearbeitungssoftware auf Gottesniveau (SublimeText3)

Heiße Themen



GitePages statische Website -Bereitstellung fehlgeschlagen: 404 Fehlerbehebung und Auflösung bei der Verwendung von Gitee ...

Erkennen Sie den Lückeneffekt des Karten -Gutschein -Layouts. Beim Entwerfen von Karten -Gutschein -Layout begegnen Sie häufig die Notwendigkeit, Lücken zu Karten -Gutscheinen hinzuzufügen, insbesondere wenn der Hintergrund Gradient ist ...

Über die Verwendung von PNPM anstelle von NPM, um eine React -Anwendung mit NPX zu erstellen ...

Die H5 -Seite muss aufgrund von Faktoren wie Code -Schwachstellen, Browserkompatibilität, Leistungsoptimierung, Sicherheitsaktualisierungen und Verbesserungen der Benutzererfahrung kontinuierlich aufrechterhalten werden. Zu den effektiven Wartungsmethoden gehören das Erstellen eines vollständigen Testsystems, die Verwendung von Versionstools für Versionskontrolle, die regelmäßige Überwachung der Seitenleistung, das Sammeln von Benutzern und die Formulierung von Wartungsplänen.

Die Hauptquellen von H5 -Seitenmaterialien sind: 1. professionelle Material -Website (bezahlt, hoher Qualität, klares Urheberrecht); 2. hausgemachtes Material (hohe Einzigartigkeit, aber zeitaufwändig); 3. Open Source Material Library (kostenlos, muss sorgfältig abgeschrieben werden); 4. Bild-/Video -Website (Urheberrecht verifiziert ist erforderlich). Darüber hinaus sind ein einheitlicher Materialstil, Größenanpassung, Kompressionsverarbeitung und Urheberrechtsschutz wichtige Punkte, die beachtet werden müssen.

JavaScript -Benennungsspezifikation und Android ...

Warum werden Suchmaschinen nicht angezeigt, nachdem die Keywords der Website -Titel aktualisiert wurden? Bei der Optimierung einer Website ändern viele Webmaster die SEO -Einstellungen und strukturierten Daten der Website in der Hoffnung ...

Warum werden negative Margen in einigen Fällen nicht wirksam? Während der Programmierung negative Margen in CSS (negativ ...
