Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏多维度架构

第 20 章 Client and Utility Programs

目录

20.1. mysql - the MySQL command-line tool
20.1.1. ~/.my.cnf
20.1.2. 屏幕输出到文件
20.1.3. 终端编码
20.1.4. Unix Socket
20.1.5. 重定向巧用
20.1.6. --sigint-ignore 忽略 Ctrl + C
20.1.7. mysql log
20.2. mysqldump - a database backup program
20.2.1. 备份数据库并压缩文件
20.2.2. 备份数据库/表
20.2.3. 备份到文件
20.2.4. 备份数据库,无结构,只有数据
20.2.5. 使用完整的insert插入数据
20.2.6. --extended-insert / --skip-extended-insert
20.2.7. --skip-lock-tables
20.2.8. --skip-add-locks
20.2.9. --where
20.2.10. 注释信息--comments /--skip-comments
20.2.11. 不导出注释信息
20.2.12. 字符集设置
20.3. mysqladmin - client for administering a MySQL server
20.3.1. reload
20.3.2. 更改密码
20.3.3. status
20.3.4. process list
20.4. myisamchk — MyISAM Table-Maintenance Utility
20.5. mysqlcheck — A Table Maintenance and Repair Program
20.6. mysqlslap - load emulation client
20.7. mysqldumpslow - Parse and summarize the MySQL slow query log.
20.8. mysql-shell
20.9.
20.10. MySQL慢查询日志(Slow Query Log)
20.10.1. MySQL 8.x
20.10.2. MySQL 5.x
20.11. mysql-admin
20.12. MySQL Workbench 数据库恢复

20.1. mysql - the MySQL command-line tool

20.1.1. ~/.my.cnf

# mysql_secure_installation config file
[mysql]

[mysqld]

[client]
user=root
password='chen'

[mysqldump]
quick

[mysqladmin]

[mysqlhotcopy]
			

20.1.2. 屏幕输出到文件

			
mysql>tee /home/neo/screen.txt
mysql>select * from member;
mysql>exit
			
			

20.1.3. 终端编码

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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
			

设置终端编码 set names utf8;

mysql> select * from category;
+----+------+-------------+--------+-----------+-----------+
| id | name | description | status | parent_id | path      |
+----+------+-------------+--------+-----------+-----------+
|  1 | ??   | ???????     | Y      |      NULL | 1/        |
|  4 | ???  | ???         | Y      |         1 | 1/4       |
|  5 | ???  | NULL        | Y      |         4 | 1/4/5     |
|  6 | ???  | NULL        | Y      |         5 | 1/4/5/6   |
|  7 | ???  | NULL        | Y      |         6 | 1/4/5/6/7 |
+----+------+-------------+--------+-----------+-----------+
5 rows in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from category;
+----+-----------+-----------------------+--------+-----------+-----------+
| id | name      | description           | status | parent_id | path      |
+----+-----------+-----------------------+--------+-----------+-----------+
|  1 | 中国    | 中华人民共和家                                    | Y      |      NULL | 1/        |
|  4 | 广东省 | 广东省                                                      | Y      |         1 | 1/4       |
|  5 | 深圳市 | NULL                      | Y      |         4 | 1/4/5     |
|  6 | 宝安区 | NULL                      | Y      |         5 | 1/4/5/6   |
|  7 | 龙华镇 | NULL                      | Y      |         6 | 1/4/5/6/7 |
+----+-----------+-----------------------+--------+-----------+-----------+
5 rows in set (0.00 sec)

			

20.1.4. Unix Socket

mysql -uroot -p -S /tmp/mysql.sock
			

20.1.5. 重定向巧用

			
echo "show databases;" | mysql -uroot -pneo

cat |mysql -uroot -pneo << EOF
show databases;
EOF
			
			

20.1.6. --sigint-ignore 忽略 Ctrl + C

使用该选项方式用户中途通过 Ctrl + C 推出,只能通过 quit 退出

$ mysql --sigint-ignore
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 71
Server version: 5.5.32-0ubuntu0.13.04.1 (Ubuntu)

Copyright (c) 2000, 2013, 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> quit
Bye
			

20.1.7. mysql log

/etc/my.cnf 配置文件

			
在服务器上的/etc/my.cnf中的[client]加入
tee =/tmp/mysql_history.log即可.
			
			

查看log设置

			
show VARIABLES like '%log%';
			
			

命令行

			
mysql -uroot --tee=/tmp/mysql_history.log
			
			
			
mysql> tee mysql_history.log
Logging to file 'mysql_history.log '

或者
mysql> \T mysql_history.log
Logging to file 'mysql_history.log '
			
			
			
mysql> notee
Outfile disabled.
或者
mysql> \t
Outfile disabled.