mysql学习记录(二十四)--mysql相关工具_MySQL
程序员文章站
2022-04-18 17:38:33
...
一、理论:
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:错误代码查看工具
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 ~/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 | | | +-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+