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 Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. 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;
+----+------+----------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+------+---------+------+-------+------------------+
| 28 | root | 192.168.10.101:58498 | NULL | Sleep | 88 | | NULL |
| 33 | root | 192.168.10.108:50105 | NULL | Sleep | 68 | | NULL |
| 34 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+----------------------+------+---------+------+-------+------------------+
3 rows in set (0.00 sec)
如下所示，有一个tcp/ip,两个本地unix socket,其中36510是通过ssh tunnel转发过来的
MySQL [test]> show processlist;
+----+------+----------------------+-------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+----------------------+-------+---------+------+-------+------------------+
| 28 | root | 192.168.10.101:58498 | test | Query | 0 | NULL | show processlist |
| 34 | root | localhost | mysql | Sleep | 112 | | NULL |
| 35 | root | localhost:36510 | NULL | Sleep | 4 | | NULL |
+----+------+----------------------+-------+---------+------+-------+------------------+
这个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:programPuTTYplink.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 '1iuse basedata;' basedata.sql sed -i '1icreate database basedata;' basedata.sql sed -i '1idrop 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 +---------+------------+-----------+------+-----+---------+-------+---------------------------------+--------------------------------------------------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +---------+------------+-----------+------+-----+---------+-------+---------------------------------+--------------------------------------------------+ | display | tinyint(1) | | NO | | 1 | | select,insert,update,references | True if the vpc can be displayed to the end user | +---------+------------+-----------+------+-----+---------+-------+---------------------------------+--------------------------------------------------+
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
+----+-------+-----------------+-------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------------+-------------+---------+------+-------+------------------+
| 4 | cloud | localhost:46162 | cloud | Sleep | 24 | | |
| 5 | cloud | localhost:46163 | cloud_usage | Sleep | 32 | | |
| 30 | root | localhost | | Query | 0 | | show processlist |
+----+-------+-----------------+-------------+---------+------+-------+------------------+
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
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
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
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.
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