mysql使用命令行工具_mysql命令行工具

最新推荐文章于 2022-11-08 19:21:45 发布

尽心则无余

最新推荐文章于 2022-11-08 19:21:45 发布

阅读量500

点赞数

文章标签： mysql使用命令行工具

本文链接：https://blog.csdn.net/weixin_32365777/article/details/113151998

版权

mysql包相关命令行工具

[root@manage ~]# rpm -qa|grep mysql

mysql-server-5.1.73-5.el6_7.1.x86_64

mysql-5.1.73-5.el6_7.1.x86_64

mysql-connector-java-5.1.17-6.el6.noarch

mysql-libs-5.1.73-5.el6_7.1.x86_64

[root@manage ~]# rpm -ql mysql

/usr/bin/msql2mysql　　没多大用

/usr/bin/my_print_defaults　　没多大用

/usr/bin/mysql　　有用

/usr/bin/mysql_config

/usr/bin/mysql_find_rows　　比grep能更近一步，将有关系的块组织在一起，显示出来，而grep只是将匹配行过滤出来而已

/usr/bin/mysql_waitpid　　没多大用

/usr/bin/mysqlaccess　　没多大用

/usr/bin/mysqladmin　　非常有用

/usr/bin/mysqlbinlog　　有用

/usr/bin/mysqlcheck　　没多大用

/usr/bin/mysqldump　　有用

/usr/bin/mysqlimport　　有用

/usr/bin/mysqlshow　　不太用

/usr/bin/mysqlslap　　不太用

连接方式与客户端的不一样，在相关输出中会有明确的表示，此时深刻理解了客服模型。

服务器唯一，但不同版本也可多样。客户端多种多样。连接方式socket,tcp/ip,tunnel，请看下面的输出，可以看的更清楚一点。

centos7 远程连接tcp/ip

[root@kvm1 ~]# rpm -qa|grep mari

mariadb-libs-5.5.47-1.el7_2.x86_64

marisa-0.2.4-3.el7.x86_64

mariadb-5.5.47-1.el7_2.x86_64

[root@kvm1 ~]# rpm -ql mariadb

[root@kvm1 ~]# mysql -p -h 192.168.10.110

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 21

Server version: 5.1.73 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> status

--------------

mysql Ver 15.1 Distrib 5.5.47-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 20

Current database:

Current user: root@192.168.10.101

centos6.5,本地连接unix socket连接

[root@vm1 ~]# mysql -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 24

Server version: 5.1.73 Source distribution

mysql> status

--------------

mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: 24

Current database:

Current user: root@localhost

如下所示，说明有三个数据库连接，其中一个本地连接unix socket，两个远程tcp/ip连接，time以秒计算

mysql> show processlist;

+----+------+----------------------+------+---------+------+-------+------------------+

+----+------+----------------------+------+---------+------+-------+------------------+

| 28 | root | 192.168.10.101:58498 | NULL | Sleep | 88 | | NULL |

| 33 | root | 192.168.10.108:50105 | NULL | Sleep | 68 | | NULL |

+----+------+----------------------+------+---------+------+-------+------------------+

3 rows in set (0.00 sec)

如下所示，有一个tcp/ip,两个本地unix socket,其中36510是通过ssh tunnel转发过来的

MySQL [test]> show processlist;

+----+------+----------------------+-------+---------+------+-------+------------------+

+----+------+----------------------+-------+---------+------+-------+------------------+

+----+------+----------------------+-------+---------+------+-------+------------------+

这个id(即connection id)的数量是一直在增加的

[root@10-9-71-105 ~]# mysql -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 17135

Server version: 5.7.10-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

mysql> status

--------------

mysql Ver 14.14 Distrib 5.7.10, for Linux (x86_64) using EditLine wrapper

Connection id: 17130

Current database:

Current user: root@localhost

[root@10-9-71-105 ~]# mysqladmin status -p

Enter password:

Uptime: 3129296 Threads: 50 Questions: 58828358 Slow queries: 0 Opens: 77917 Flush tables: 1 Open tables: 400 Queries per second avg: 18.799

这一个字段代表当前共有50条连接

Threads: 50

其实就是通过下面两条命令显示的详细，二者是等价的

[root@10-9-71-105 ~]# mysqladmin proc stat -p

mysql> show processlist;

win7 heidisql 远程连接，通过 ssh tunnel连接

/* 连接到 127.0.0.1 (经由 MySQL (SSH tunnel))，用户名 root，密码：Yes ... */

/* 尝试创建plink.exe进程，响应等待 4 秒... */

/* D:\program\PuTTY\plink.exe -ssh root@192.168.10.110 -pw "******" -P 22 -N -L 3307:127.0.0.1:3306 */

SELECT CONNECTION_ID();

/* 已连接。线程ID：26 */

/* Unknown character set: 'utf8mb4' */

/* 字符集: utf8 */

mysql与mysqlimport工具配合使用

[root@manage ~]# mysql -e 'CREATE TABLE imptest(id INT, n VARCHAR(30))' fgy -p

Enter password:

在fgy库中创建表imptest,两列

[root@manage ~]# cat imptest.txt

100 hello world

200 ni hao

[root@manage ~]# mysqlimport --local fgy imptest.txt -p

Enter password:

fgy.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 4

[root@manage ~]# mysql -e 'select * from imptest' fgy -p

Enter password:

+------+------+

| id | n |

+------+------+

| 100 | NULL |

| 200 | NULL |

+------+------+

之所以产生警告是因为imptest.txt里分隔符不是tab,而是空格，改成tab就可以了

#man mysql中的解释

When used interactively, query results are presented in an ASCII-table format.

When used noninteractively (for example, as a filter), the result is presented in tab-separated format.

[root@manage ~]# mysqlimport --local fgy imptest.txt -p

Enter password:

fgy.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

[root@manage ~]# mysql -e 'select * from imptest' fgy -p

Enter password:

+------+-------------+

| id | n |

+------+-------------+

| 200 | ni hao |

| 100 | hello world |

+------+-------------+

The following example demonstrates tabular versus nontabular output and the use of raw mode to disable escaping:

mysql

mysql -s -r

上面两条命令是不一样的

Then type an SQL statement, end it with “;”, \g, or \G and press Enter.

ego, \G

Send the current statement to the server to be executed and display the result using vertical format.

my_print_defaults groups(groups代表多个/etc/my.cnf中的[]),非常方便，不用去看文件，但是好像用处也不是太大，如果配置项不多的话，直接vi /etc/my.cnf一眼就看完了，也就不需要在命令行敲入那么多字母了。

[root@manage ~]# my_print_defaults mysqld

--datadir=/var/lib/mysql

--socket=/var/lib/mysql/mysql.sock

--user=mysql

--symbolic-links=0

--innodb_rollback_on_timeout=1

--innodb_lock_wait_timeout=600

--max_connections=350

--log-bin=mysql-bin

--binlog-format=ROW

--character_set_server=utf8

mysqldump

这一段代码太不优化了，多余-重复

不需要sed来添加行，只需要加入 --add-drop-database就可以了

有--database或-A,添加--add-drop-database才有效。

还是要多看看 #man mysqldump手册页。了解具体需求，才有针对性。

drop database if exists spauth;

create database spauth;

use spauth;

mysqldump-u root -d -R --add-drop-table basedata >basedata.sql

sed -i '1i\use basedata;'basedata.sql

sed-i '1i\create database basedata;'basedata.sql

sed-i '1i\drop database if exists basedata;'basedata.sql

mysqldump-u root --add-drop-table basedata industry>>basedata.sql

mysqldump -u root --add-drop-table basedata data_dictionary>>basedata.sql

mysqldump -u root --add-drop-table --extended-insert=false basedata tb_sequence>>basedata.sql

-e, --extended-insert，长INSERT，多row在一起批量INSERT，提高导入效率，和没有开启 -e 的备份导入耗时至少相差3、4倍，默认开启；用--extended-insert=false关闭。强烈建议开启，通过下面的测试比较就会明白为什么了。

(1)默认方式导出，也即--extended-insert=true

-d只有表结构即列信息，无内容即行信息

· --no-data, -d

Do not write any table row information (that is, do not dump table contents). This is

useful if you want to dump only the CREATE TABLE statement for the table (for example,

to create an empty copy of the table by loading the dump file).

· --routines, -R

Include stored routines (procedures and functions) for the dumped databases in the

output. Use of this option requires the SELECT privilege for the mysql.proc table. The

output generated by using --routines contains CREATE PROCEDURE and CREATE FUNCTION

statements to re-create the routines. However, these statements do not include

attributes such as the routine creation and modification timestamps. This means that

when the routines are reloaded, they will be created with the timestamps equal to the

reload time.

mysqlbinlog

因为每次操作的时间和“位置”都会被记录下来。所以要想还原数据有两种途径通过“时间”或“位置”。

[root@manage mysql]# pwd

/var/lib/mysql

[root@manage mysql]# ls

cloud ibdata1 mysql-bin.000001 mysql-bin.000005 mysql-bin.000009 mysql-bin.000013

cloudbridge ib_logfile0 mysql-bin.000002 mysql-bin.000006 mysql-bin.000010 mysql-bin.index

cloud_usage ib_logfile1 mysql-bin.000003 mysql-bin.000007 mysql-bin.000011 mysql.sock

fss mysql mysql-bin.000004 mysql-bin.000008 mysql-bin.000012 spauth

[root@manage mysql]# mysqlbinlog mysql-bin.000013 |more

mysqlshow

mysqlshow - display database, table, and column information

mysqlshow supports the following options, which can be specified on the command line or in

the [mysqlshow] and [client] groups of an option file.

[root@manage ~]# mysqlshow -p123456 cloud vpc display

Database: cloud Table: vpc Wildcard: display

+---------+------------+-----------+------+-----+---------+-------+---------------------------------+--------------------------------------------------+

+---------+------------+-----------+------+-----+---------+-------+---------------------------------+--------------------------------------------------+

+---------+------------+-----------+------+-----+---------+-------+---------------------------------+--------------------------------------------------+

mysqlcheck

[root@manage ~]# mysqlcheck -p cloud

Enter password:

cloud.account OK

cloud.account_details OK

cloud.account_network_ref OK

cloud.op_lock

note : The storage engine for the table doesn't support check

cloud.op_networks OK

cloud.op_nwgrp_work

note : The storage engine for the table doesn't support check

cloud.op_pod_vlan_alloc OK

mysqlcheck is similar in function to myisamchk, but works differently. The main operational

difference is that mysqlcheck must be used when the mysqld server is running, whereas

myisamchk should be used when it is not.

[root@manage ~]# mysqladmin extended-status|more

+-----------------------------------+----------+

| Variable_name | Value |

+-----------------------------------+----------+

| Aborted_clients | 0 |

| Aborted_connects | 1 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 7 |

| Bytes_received | 17954 |

| Bytes_sent | 83686 |

| Com_admin_commands | 2 |

fgy3是库名

[root@manage ~]# mysqladmin create fgy3 create fgy4

[root@manage ~]# mysqlshow

[root@manage ~]# mysqladmin drop fgy4

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

Do you really want to drop the 'fgy4' database [y/N] y

Database "fgy4" dropped

[root@manage ~]# mysqlshow

[root@manage ~]# mysqladmin proc stat

+----+-------+-----------------+-------------+---------+------+-------+------------------+

+----+-------+-----------------+-------------+---------+------+-------+------------------+

+----+-------+-----------------+-------------+---------+------+-------+------------------+

Uptime: 16972 Threads: 3 Questions: 8410 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 10 Queries per second avg: 0.495

[root@manage ~]# mysqladmin version

[root@manage ~]# mysqladmin variables|more

[root@manage ~]# mysqladmin extended-status|more

远程连接经由tcp/ip

[root@kvm1 ~]# mysqladmin version -h 192.168.10.110 -p

Enter password:

mysqladmin Ver 9.0 Distrib 5.5.47-MariaDB, for Linux on x86_64

Server version 5.1.73

Protocol version 10

Connection 192.168.10.110 via TCP/IP

TCP port 3306

Uptime: 51 min 31 sec

Threads: 1 Questions: 59 Slow queries: 0 Opens: 30 Flush tables: 1 Open tables: 23 Queries per second avg: 0.19

本地连接经由unix socket

[root@vm1 ~]# mysqladmin version -p

Enter password:

mysqladmin Ver 8.42 Distrib 5.1.73, for redhat-linux-gnu on x86_64

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Server version 5.1.73

Protocol version 10

Connection Localhost via UNIX socket

UNIX socket /var/lib/mysql/mysql.sock

Uptime: 47 min 31 sec

Threads: 1 Questions: 57 Slow queries: 0 Opens: 30 Flush tables: 1 Open tables: 23 Queries per second avg: 0.19

尽心则无余

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql使用命令行工具_mysql命令行工具

mysql包相关命令行工具[root@manage ~]# rpm -qa|grep mysqlmysql-server-5.1.73-5.el6_7.1.x86_64mysql-5.1.73-5.el6_7.1.x86_64mysql-connector-java-5.1.17-6.el6.noarchmysql-libs-5.1.73-5.el6_7.1.x86_64[root@manage ...
复制链接

扫一扫