前言
- MySQL版本:5.7.17
- 本次主要演示Linux上mysql命令行工具的使用,Windows上的使用方式大同小异
MySQL常用命令行工具
MySQL常用的命令行工具主要分为客户端命令行工具和服务器命令行工具两种,分别只能运行在mysql客户端和mysql服务器上,即mysql中的客户端命令行工具使用时需要设置登录选项,而服务器命令行工具可以在服务器上直接使用
1. mysql客户端
语法:
mysql [options] [database]
常用登录选项:
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器ip或域名
-P, --port=port 指定连接端口
示例1:
- 使用mysql-root用户连接当前主机mysql服务器
mysql -u root -p
- 使用mysql-root用户连接远程mysql服务器
mysql -h 192.168.126.101 -P 3306 -u root -p
- 使用明文密码登录,并连接指定数据库db_test01
mysql -h 192.168.126.101 -P 3306 -u root -p"root" db_test01
常用执行选项:
-e --execute=name 建立连接后执行SQL语句返回结果后退出客户端,可以执行多条SQL语句
此选项常用于Shell批处理脚本
示例2:
- 查看mysql服务器上的数据库列表
mysql -h 192.168.126.101 -u root -p"root" -e "show databases;"
- 查看mysql服务器上的数据库mysql中的表信息
mysql -h 192.168.126.101 -u root -p"root" mysql -e "show tables;"
2. mysqladmin
mysqladmin
是一个执行管理操作的mysql客户端命令行工具,需要登录使用,可以用它来检查服务器的配置和当前状态、创建、以及删除数据库等
语法:
mysqladmin [options] database
示例:
- 查看mysqladmin帮助文档
mysqladmin --help
- 使用mysqladmin创建数据库
mysqladmin -h 192.168.126.101 -P 3306 -u root -p"root" create "db_test01"
- 使用mysqladmin删除数据库
mysqladmin -h 192.168.126.101 -P 3306 -u root -p"root" drop "db_test01"
3. mysqlbinlog
mysqlbinlog
主要用于查看和管理mysql服务器生成的二进制日志bin-log对应的文件,是mysql服务器命令行工具。启动bin-log日志后会在对应路径下生成日志索引文件(.index)和对应的日志文件,日志索引文件中记录有bin-log日志文件名。可以使用mysqlbinlog
配合二进制文件实现数据恢复,即在最近数据库备份的基础上将二进制文件中的指定范围内逻辑SQL语句再执行一次
语法:
mysqlbinlog [options] log-files1 log-files2...
常用选项:
-d, --database=name 指定数据库名称
-o, --offset=n 设置查看的偏移量,即忽略前n行
-r, --result-file=name 将显示内容输出到指定文件
-s, --short-from 按照简单格式显示,省略部分信息
--start-datatime=detel --stop-datatime=date2
指定日志显示条目的时间间隔
--start-position=pos1 --stop-position=pos2
指定日志显示条目的开始和结束位置
示例:
- 查看指定的bin-log日志文件
mysqlbinlog mysql-bin.000001
- 如果binlog的记录形式为
ROW
,则可以通过-v
参数或者-vv
参数来显示对应的SQL语句
mysqlbinlog -vv mysql-bin.000005
4. mysqldump
mysqldump
工具主要用于备份数据库或者指定的表数据,以及进行数据迁移。备份内容包含有创建表,及插入表的SQL语句,默认输出到命令行
语法:
mysqldump [options] database [tables]
mysqldump [options] -B, --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [options] -A, --all-databases [OPTIONS]
常用登录选项:
-u, --user=name 指定用户名
-p, --password[=name] 指定密码
-h, --host=name 指定服务器ip或域名
-P, --port=port 指定连接端口
常用执行选项:
--add-drop-database 在每个创建数据库的语句之前加上drop database if exists <database>,默认不添加
--add-drop-table 在每个表的创建语句之前加上drop table if exists <table>,默认是开启的,在导出数据时一定要小心,避免删除已有数据
--skip-add-drop-table 取消添加drop table语句
-n, --no-create-db 导出内容中不包含数据库的创建语句
-t, --no-create-info 导出内容中不包含数据库表的创建语句
-d, --no-data 导出内容中不包含表的数据插入语句
-T, --tab=name 自动生成表结构文件.sql,以及表数据文件.txt,其中在Linux中前者使用命令执行用户导出,后者使用mysql用户导出
--single-transaction 通过创建事务来实现数据的备份,保证数据恢复时能够到达某个一致性状态(只支持InnoDB表)
示例:
- 导出数据库
books
中的authors
表,包括创建、插入的SQL语句,并输出到指定文件中
mysqldump -uroot -proot books authors > books.authors.sql
- 导出数据库
books
中的所有表,并输出到指定文件中,同时设置取消添加drop table
语句
mysqldump -uroot -proot books --skip-add-drop-table > books.sql
- 导出整个数据库
books
,并将对应SQL语句输出到指定文件中
mysqldump -uroot -proot -B books > books.sql
- 导出数据库
books
中的表authors
的表结构和数据,分输出到指定路径下,注意:若secure_file_priv
参数不为空,则只能输出到此参数指定的文件夹,否则可以指定任意文件夹,但是需要命令执行用户和mysql用户都有写入权限,一般在Linux中导出到/tmp
路径下,此路径下任何用户都有完全权限
mysqldump -uroot -proot -T /tmp/ books authors
注意:
-
在Linux上使用
mysqldump -T
导出表结构和表数据时,表结构文件(.sql)是使用脚本执行用户创建和写入的,表数据文件(.txt)是使用mysql
用户创建和写入的,需要保证输出路径下两者至少都有读写入权限 -
在Windows上使用
mysqldump -T
导出表结构和表数据时,表结构文件(.sql)是使用脚本执行用户创建和写入的,表数据文件(.txt)默认是使用NETWORK SERVICE
用户创建和写入的,需要保证输出路径下两者至少都有读写入权限
5. mysqlimport
mysqlimport
是客户端导入数据工具,主要用于导入使用mysqldump
工具加上-T
参数后导出的表数据文本文件(.txt)
语法:
mysqlimport [options] database textfile
示例:
- 将表
books.authors
中的数据清空,重新导入之前导出的表数据文本文件authors.txt
,需要保证表数据文本文件名前缀和表名相同
mysql -uroot -proot -e "truncate books.authors"
mysqlimport -uroot -proot books authors /tmp/authors.txt
6. source
source
是属于mysql客户端的命令,主要是用于在mysql客户端命令行上批量执行sql语句使用的,可以配合mysqldump
工具实现数据库或者表的迁移
语法:
source [file.sql]
示例:
- 在数据库
books
上执行之前导出的表结构文件authors.sql
mysql -uroot -proot -e "ues books;source /tmp/authors.sql"
- 导入整个
books
数据库
mysql -uroot -proot -e "source /tmp/books.sql"
7. mysqlshow
mysqlshow
客户端工具主要用于显示数据库中的各种对象的信息
语法:
mysqlshow [options] [database [table [column]]]
常用选项:
--count 显示每张表的行数统计
-i, --status 显示每张表的额外信息
示例:
- 查询数据库
books
中所有表的行列数
mysqlshow -uroot -proot --count books
- 查询数据库
books
中表authors
的表结构信息
mysqlshow -uroot -proot books authors
或者直接在mysql客户端使用desc books.authors
命令
mysql -uroot -proot -e "desc books.authors"
- 查看数据库
books
中authors
表状态信息
mysqlshow -uroot -proot -i books authors id
或者直接在mysql客户端使用show table status
命令
mysql -uroot -proot -e "show table status from books like 'authors'"