一、理论：
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:文本替换工具
二、实践：
[email protected]:~$ mysql -uroot -p(密码) --default-character-set=utf8Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 47Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, 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%';+--------------------------+----------------------------------+| 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[email protected]:~$ mysql -uroot -p(密码) --default-character-set=gbk;Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 48Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, 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%';+--------------------------+----------------------------------+| 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[email protected]:~$ mysql -uroot -p(密码);Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 49Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, 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 changed[email protected]:~$ 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 |+------------+-------------+----------+-----------+--------+---------------------+---------------------+[email protected]:~$ 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.99payment_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.99payment_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.99payment_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.99payment_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.99payment_date: 2005-06-15 21:08:46 last_update: 2006-02-15 22:12:30[email protected]:~$ cd ~/Downloads/[email protected]:~/Downloads$ mkdir mysql[email protected]:~/Downloads$ cd mysql/[email protected]:~/Downloads/mysql$ vi a.sql;(在此步添加了一些内容)[email protected]:~/Downloads/mysql$ more a.sql;insert into t2 values (1);insert into t2 values (2222222222222222222222222);insert into t2 values (3);[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码);Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 53Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, 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 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!Aborted[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test < a.sql;ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';+------+| id |+------+| 1 |+------+[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -f < a.sql ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';+------+| id |+------+| 1 || 3 |+------+[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -f -v<a.sql ;--------------insert into t2 values (1)----------------------------insert into t2 values (2a)--------------ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'--------------insert into t2 values (3)--------------[email protected]:~/Downloads/mysql$ more a.sql;insert into t2 values (1);insert into t2 values (2a);insert into t2 values (3);[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test < a.sql;[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';+------------+| id |+------------+| 1 || 3 || 1 || 3 || 1 || 2147483647 || 3 |+------------+[email protected]:/usr/local/mysql/bin$ mysqlbinlog --database = sakila -s ;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER /*!*/;mysqlbinlog: File 'sakila' not found (Errcode: 2)DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET [email protected]_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[email protected]:/usr/local/mysql/bin$ mysqlcheck -uroot -c testmysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect[email protected]:/usr/local/mysql/bin$ mysqlcheck -uroot -p(密码) -c testtest.t2 OK[email protected]:/usr/local/mysql/bin$ mysqlcheck -uroot -p(密码) -r testtest.t2note : The storage engine for the table doesn't support repair[email protected]:/usr/local/mysql/bin$ mysqlcheck -uroot -p(密码) -a testtest.t2 OK[email protected]:/usr/local/mysql/bin$ mysqlcheck -uroot -p(密码) -o testtest.t2note : Table does not support optimize, doing recreate + analyze insteadstatus : OK[email protected]:/usr/local/mysql/bin$ sudo mysqldump -uroot -p(密码) test > ~/test.txt;[email protected]:/usr/local/mysql/bin$ cd ~/[email protected]:~$ 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 [email protected]_TIME_ZONE */;/*!40101 SET [email protected]_SQL_MODE */;/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;/*!40014 SET [email protected]_UNIQUE_CHECKS */;/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;/*!40101 SET [email protected]_COLLATION_CONNECTION */;/*!40111 SET [email protected]_SQL_NOTES */;-- Dump completed on 2015-11-06 5:15:01[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact -d test t2 > ~/Downloads//t2.txt[email protected]:/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 */;[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact -c test t2 > ~/Downloads/t2c.txt[email protected]:/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);[email protected]:/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.44-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] Aborting[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact test t2 > ~/Downloads/testA.txt[email protected]:/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);[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact --default-character-set=latin1 test t2 > ~/Downloads/testA.txt[email protected]:/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);[email protected]:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码);+--------------------+| Databases |+--------------------+| information_schema || mysql || performance_schema || sakila || test || test1 |+--------------------+[email protected]:/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.[email protected]:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码) test --count;Database: test+--------+----------+------------+| Tables | Columns | Total Rows |+--------+----------+------------+| t2 | 1 | 13 |+--------+----------+------------+1 row in set.[email protected]:/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[email protected]:~/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);[email protected]:~/Downloads$ mysqlshow+--------------------+| Databases |+--------------------+| information_schema || test |+--------------------+[email protected]:~/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 | |+-------+---------+-----------+------+-----+---------+-------+---------------------------------+---------+[email protected]:~/Downloads$ mysql -uroot -p(密码);Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 88Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, 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 changed[email protected]:~/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[email protected]:~/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 | | |+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。