Mysql learning record (24) -- mysql related tool _ MySQL

I. theory: 1 mysql: client connection tool a-u: specify the user name B-p: specify the password c-host: specify the server IP address or domain name d-P: specify the connection port e -- default-character-set: client character set option f-e: execution option g-E: display the output mode in field order. 1. theory:
1. mysql: client connection tool
A.-u: specifies the user name.
B.-p: specify the password.
C.-host: specifies the server IP address or domain name.
D.-P: specify the connection port
E. -- default-character-set: client character set option
F.-e: execution option
G.-E: display the output mode in the field order.
H.-f: execute SQL forcibly
I.-v: show more information
2. myisampack: myisam table compression tool
3. mysqladmin: mysql management tool
4. mysqlbinlog: log management tool
A.-d: specifies the database name and only lists the specified database operations.
B.-o: ignore the first n lines in the log.
C.-r: output the output text format log to the specified file
D.-s: simple display format. some information is omitted.
E. -- set-charset = char-name: when the output is in text format, add set names char-nam on the first line of the file (which can be used to load data)
F. -- start-datetime = name: all logs within the specified date interval
G. -- start-position: all logs within the specified location interval
5. mysqlcheck: MyISAM table maintenance tool
A.-c: Checklist
B.-r: repair the table
C.-a: Analysis Table
D.-o: optimize the table
6. mysqldump: Data export tool
A.-u: User name
B.-p: password
C.-h: server IP address or domain name
D.-P: connection port
E. -- add-drop-database: add drop database before each database creation statement.
F. -- add-drop-table: add drop table before each table creation statement.
G.-n: does not contain the database creation statement.
H.-t: Create statement that does not contain data tables
I.-d: does not contain data
J. -- compact: does not contain comments in the default options.
7. mysqlhostcopy: MyISAM table hot backup tool
8. mysqlimport: data import tool
9. mysqlshow: database object viewing tool
10. perror: error code viewing tool

11. replace: Text Replacement tool

II. practice:

Abc @ ubuntu :~ $ Mysql-uroot-p (password) -- default-character-set = utf8Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 47 Server version: 5.5.44-log Source distributionCopyright (c) 2000,201 5, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'Or' \ H' for help. type '\ C' to clear the current input statement. mysql> show variables like 'char % '; + bytes + | Variable_name | Value | + bytes + | character_set_client | utf8 | character_set_connection | utf8 | character_set_database | utf8 | character_set_filesystem | binary | characte R_set_results | utf8 | character_set_server | utf8 | character_set_system | utf8 | character_sets_dir |/usr/local/mysql/share/charsets/| + rows + 8 rows in set (0.00 sec) mysql> show variables like 'char % '; Ctrl-C -- exit! Abortedabc @ ubuntu :~ $ Mysql-uroot-p (password) -- 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 distributionCopyright (c) 2000,201 5, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'Or' \ H' for help. type '\ C' to clear the current input statement. mysql> show variables like 'char % '; + character + | Variable_name | Value | + character + | 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/| + rows + 8 rows in set (0.00 sec) mysql> Ctrl-C -- exit! Abortedabc @ ubuntu :~ $ Mysql-uroot-p (password); Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 49 Server version: 5.5.44-log Source distributionCopyright (c) 2000,201 5, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. 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 namesYou can turn off this feature to get a quicker startup with-ADatabase changedabc @ ubuntu :~ $ Mysql-uroot-p (password)-e 'use sakila; select * from payment limit 5 ;'; + ------------ + hour + ---------- + ----------- + -------- + hour + | payment_id | customer_id | staff_id | financial_id | amount | payment_date | last_update | + ---------- + hour + -------- + --------------------- + ------------------- + | 1 | 1 | 1 | 76 | 2.99 | 5 11:30:37 | 22:12:30 | 2 | 1 | 1 | 573 | 0.99 | 10:35:23 | 22:12:30 | 3 | 1 | 1 | 1185 | 5.99 | 00:54:12 | 22:12:30 | | 4 | 1 | 2 | 1422 | 0.99 | 18:02:53 | 22:12:30 | 5 | 1 | 2 | 1476 | 9.99 | 21:08:46 | 22:12:30 | + ----------------- + ---------- + ----------- + -------- + --------------------- +- -------------------- + Abc @ ubuntu :~ $ Mysql-uroot-p (password)-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.99payment _ date: 2005-05-25 11:30:37 last_update: 22:12:30 ****************************** 2. row ************************** payment_id: 2 customer_id: 1 staff_id: 1 rental_id: 573 amount: 0.99 pa Yment_date: 10:35:23 last_update: 22:12:30 ********************************** 3. row ************************** payment_id: 3 customer_id: 1 staff_id: 1 rental_id: 1185 amount: 5.99payment _ date: 2005-06-15 00:54:12 last_update: 22:12:30 ****************************** 4. row ************************** payment_id: 4 customer_id: 1 staff_id: 2 rental_id: 1422 amount: 0.99 paymen T_date: 18:02:53 last_update: 22:12:30 ********************************** 5. row ************************** payment_id: 5 customer_id: 1 staff_id: 2 rental_id: 1476 amount: 9.99payment _ date: 2005-06-15 21:08:46 last_update: 2006-02-15 22: 12: 30abc @ ubuntu :~ $ Cd ~ /Downloads/abc @ ubuntu :~ /Downloads $ mkdir mysqlabc @ ubuntu :~ /Downloads $ cd mysql/abc @ ubuntu :~ /Downloads/mysql $ vi a. SQL; (some content is added in this step) 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 (password); Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 53 Server version: 5.5.44-log Source distributionCopyright (c) 2000,201 5, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' f Or help. type '\ C' to clear the current input statement. mysql> use test; Database changedmysql> create table t2 (-> id int (11)->) engine = innodb charset = utf8; Query OK, 0 rows affected (0.05 sec) mysql> Ctrl-C -- exit! Abortedabc @ ubuntu :~ /Downloads/mysql $ mysql-uroot-p (password) test <. SQL; ERROR 1054 (42S22) at line 2: Unknown column '2a 'in 'Field list' abc @ ubuntu :~ /Downloads/mysql $ mysql-uroot-p (password) test-e 'select * from t2 '; + ------ + | id | + ------ + | 1 | + ------ + abc @ ubuntu :~ /Downloads/mysql $ mysql-uroot-p (password) test-f <. SQL ERROR 1054 (42S22) at line 2: Unknown column '2a 'in 'Field list' abc @ ubuntu :~ /Downloads/mysql $ mysql-uroot-p (password) test-e 'select * from t2 '; + ------ + | id | + ------ + | 1 | 3 | + ------ + abc @ ubuntu :~ /Downloads/mysql $ mysql-uroot-p (password) 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 = '+ '*/;/*! 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: 01abc @ ubuntu:/usr/local/mysql/bin $ mysqldump-uroot-p (password) -- compact-d test t2> ~ /Downloads // t2.txtabc @ 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 (password) -- 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 400151106 5:18:52 [Note] mysqld (mysqld 5.5.4 4-log) starting as process 74255... 151106 5:18:52 [Warning] Can't create test file/usr/local/mysql/data/ubuntu. lower-test151106 5:18:52 [Warning] Can't create test file/usr/local/mysql/data/ubuntu. lower-test151106 5:18:52 [Warning] One can only use the -- user switch if running as rootmysqld: File '/usr/local/mysql/data/mysql-bin.index' not found (Errcode: 13) 151106 5:18:52 [ERROR] Abortingabc @ u Buntu:/usr/local/mysql/bin $ mysqldump-uroot-p (password) -- 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 (password) -- 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 (password ); + -------------------- + | Databases | + -------------------- + | information_schema | mysql | performance_schema | sakila | test | test1 | + -------------------- + abc @ ubuntu: /usr/local/mysql/B In $ mysqlshow-uroot-p (password) -- count; + Databases + -------- + -------------- + | 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/bi N $ mysqlshow-uroot-p (password) test -- count; Database: test + -------- + ---------- + ------------ + | Tables | Columns | Total Rows | + -------- + ---------- + ------------ + | t2 | 1 | 13 | + -------- + ---------- + ------------ + 1 row in set. abc @ ubuntu:/usr/local/mysql/bin $ mysqlshow-uroot-p (password) test a -- count; mysqlshow: Cannot get record count for db: test, table: a: Table 'test. a 'doesn' t existabc @ 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 (password) test t2 -- count; Database: test Table: t2 Rows: 13 + ------- + --------- + ------ + ----- + --------- + ------- + hour + --------- + | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | + ------- + --------- + ----------- + ------ + ----- + --------- + ------- + ------------------------------- + --------- + | id | int (11) | YES | Select, insert, update, references | + ------- + --------- + ----------- + ------ + ----- + --------- + ------- + ------------------------------- + --------- + abc @ ubuntu :~ /Downloads $ mysql-uroot-p (password); Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 88 Server version: 5.5.44-log Source distributionCopyright (c) 2000,201 5, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for El P. type '\ C' to clear the current input statement. mysql> use sakila; Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedabc @ ubuntu :~ /Downloads $ mysqlshow-uroot-p (password) test t2-k; Database: test Table: t2 + ------- + --------- + ----------- + ------ + ----- + --------- + ------- + hour + --------- + | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | + ------- + --------- + ----------- + ------ + ----- + --------- + ------- + ------------------------------- + --------- + | id | int (11) | YES | selec T, insert, update, references | + ------- + --------- + ----------- + ------ + ----- + --------- + ------- + ------------------------------- + --------- + Table has no keysabc @ ubuntu :~ /Downloads $ mysqlshow-uroot-p (password) sakila actor-k; Database: sakila Table: actor + ------------- + principal + ----------------- + ------ + ----- + ------------------- + principal + --------- + | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | + ------------- + -------------------- + ----------------- + ------ + ----- + --------------------- + --------------------------- + response + --------- + | 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 | + ------------- + periods + ------ + ----- + periods + --------- ++ ------- + ------------ + periods + -------------- + ------------- + ----------- + ------------ + -------- + ------ + ------------ + --------- + bytes + | Table | Non_unique | Key_name | partition | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | + ------- + ------------ + principal + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------------ + --------- + principal + | actor | 0 | PRIMARY | 1 | actor_id | A | 201 | | BTREE | actor | 1 | idx_actor_last_name | 1 | last_name | A | 201 | BTREE | + ------- + ------------ + bytes + -------------- + ------------- + ----------- + ------------- + ---------- + -------- + ------ + ------------ + --------- + --------------- +