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>

Alat AI Hot

Undresser.AI Undress
Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover
Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool
Gambar buka pakaian secara percuma

Clothoff.io
Penyingkiran pakaian AI

AI Hentai Generator
Menjana ai hentai secara percuma.

Artikel Panas

Alat panas

Notepad++7.3.1
Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina
Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1
Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6
Alat pembangunan web visual

SublimeText3 versi Mac
Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Topik panas



Giteepages Statik Laman Web Penggunaan Gagal: 404 Penyelesaian Masalah dan Resolusi Ralat Semasa Menggunakan Gitee ...

Acara Onblur yang melaksanakan penyuntingan baris Avue-Crud di Perpustakaan Komponen Avue secara manual mencetuskan komponen Avue-Crud. Ia menyediakan fungsi penyuntingan dalam talian yang mudah, tetapi kadang-kadang kita perlu ...

Bagaimana dengan cepat membina halaman front-end dalam pembangunan back-end? Sebagai pemaju backend dengan tiga atau empat tahun pengalaman, dia telah menguasai asas JavaScript, CSS dan HTML ...

Automasi laman web dan pengiraan berangka: pelaksanaan berdasarkan skrip tertanam penyemak imbas Artikel ini akan meneroka cara menggunakan skrip tertanam penyemak imbas untuk melaksanakan operasi dan nombor halaman laman web ...

Cara mengubahsuai dan memainkan semula kuki yang diminta dengan berkesan di Chromedevtools menggunakan Chrome ...

Mengenai menggunakan PNPM dan bukannya NPM untuk membuat aplikasi React menggunakan NPX ...

Menyedari kesan jurang susun atur kupon kad. Semasa merancang susun atur kupon kad, anda sering menemui keperluan untuk menambah jurang pada kupon kad, terutamanya apabila latar belakang adalah gradien ...

Melaksanakan kesan penetapan halaman bar dan elemen skrol bergerak secara bebas dalam reka bentuk web, kadang -kadang kita perlu mencapai kesan khas, iaitu, apabila tatal tatal tatal ...
