澳门皇冠金沙网站-澳门皇冠844网站

热门关键词: 澳门皇冠金沙网站,澳门皇冠844网站

MySQL中的常用工具实例汇总,mysql连接工具与MyI

一.mysql 连接工具  

  在mysq提供的工具中,DBA使用最频繁的莫过于mysql。这里的mysql是指连接数据库的客户端工具。

  1.1 连接选项

-u, -- user=name 指定用户名
-p ,--password[=name] 指定密码
-h ,--host=name 指定服务器IP或者域名
-p, --port=# 指定连接端口

在实际连接中命令如下:mysql -u 用户名 -p 密码
[root@hsr ~]#  mysql -u root -p

-- 完整的连接命令如下
[root@hsr ~]#  mysql -u root -p -h 172.168.18.201 -P 3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.

  1.2 执行选项
    通过 "-e,--execute=name 执行sql语句并退出" 此选项不需要连接到mysql数据库后再执行,对于一些批处理脚本很方便,下面是从客户端直接查询mysql数据库中user表的User和Host字段:

[root@hsr ~]#  mysql -u root -p  mysql -e "select User,Host from user"
Enter password: 

    澳门皇冠金沙网站 1
  连接多个sql 语句用英文分号隔开如   select User,Host from user; select xxx。

前言

mysql学习记录(二十四)--mysql相关工具

澳门皇冠844网站,澳门皇冠金沙网站,一、理论:
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:文本替换工具

二、实践:

 

[email protected]:~$ 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
[email protected]:~$ 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
[email protected]:~$ 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

[email protected]:~$ 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 |
 ------------ ------------- ---------- ----------- -------- --------------------- --------------------- 
[email protected]:~$ 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
[email protected]:~$ cd ~/Downloads/
[email protected]:~/Downloads$ mkdir mysql
[email protected]:~/Downloads$ cd mysql/
[email protected]:~/Downloads/mysql$ vi a.sql;
(在此步添加了一些内容)
[email protected]:~/Downloads/mysql$ more a.sql;
insert into t2 values (1);
insert into t2 values (2222222222222222222222222);
insert into t2 values (3);
[email protected]:~/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
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test < a.sql;
ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';
 ------ 
| id   |
 ------ 
|    1 |
 ------ 
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -f < a.sql 
ERROR 1054 (42S22) at line 2: Unknown column '2a' in 'field list'
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -e 'select * from t2';
 ------ 
| id   |
 ------ 
|    1 |
|    3 |
 ------ 
[email protected]:~/Downloads/mysql$ mysql -uroot -p(密码) test -f -v ~/test.txt;
[email protected]:/usr/local/mysql/bin$ cd ~/
[email protected]:~$ more ~/test.txt 
-- MySQL dump 10.13  Distrib 5.5.44, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version 5.5.44-log

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t2`
--

DROP TABLE IF EXISTS `t2`;
/*!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 */;

--
-- Dumping data for table `t2`
--

LOCK TABLES `t2` WRITE;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
INSERT INTO `t2` VALUES (1),(3),(1),(3),(1),(2147483647),(3),(1),(2147483647),(3),(1),(2147483647),(3);
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET [email protected]_TIME_ZONE */;

/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;

-- Dump completed on 2015-11-06  5:15:01
[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact -d test t2 > ~/Downloads//t2.txt
[email protected]:/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 */;
[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact -c test t2 > ~/Downloads/t2c.txt
[email protected]:/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);

[email protected]:/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

[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact test t2 > ~/Downloads/testA.txt
[email protected]:/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);
[email protected]:/usr/local/mysql/bin$ mysqldump -uroot -p(密码) --compact --default-character-set=latin1 test t2 > ~/Downloads/testA.txt
[email protected]:/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);

[email protected]:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码);
 -------------------- 
|     Databases      |
 -------------------- 
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| test               |
| test1              |
 -------------------- 
[email protected]:/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.
[email protected]:/usr/local/mysql/bin$ mysqlshow -uroot -p(密码) test --count;
Database: test
 -------- ---------- ------------ 
| Tables | Columns  | Total Rows |
 -------- ---------- ------------ 
| t2     |        1 |         13 |
 -------- ---------- ------------ 
1 row in set.

[email protected]:/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
[email protected]:~/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);
[email protected]:~/Downloads$ mysqlshow
 -------------------- 
|     Databases      |
 -------------------- 
| information_schema |
| test               |
 -------------------- 
[email protected]:~/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 |         |
 ------- --------- ----------- ------ ----- --------- ------- --------------------------------- --------- 
[email protected]:~/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
[email protected]:~/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
[email protected]:~/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      |         |               |
 ------- ------------ --------------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 

一、理论: 1.mysql:客户端连接工具 a.-u:指定用户名 b.-p:指定密码 c.-host:指定服务器IP或者域名 d.-P:指...

二. myisampack(MyISAM表压缩工具)

  myisampack是一个表压缩工具。在压缩之前需要确认mysqld已关闭或者要压缩的表不会有其他的sql操作;而且压缩过程会很占用cpu资源,建议在服务器空闲的状态进行。

  可以将数据文件压缩到40%-70%,当以后使用表时,解压缩列需要的信息被读入内存。当访问具体的记录时性能会更好,因为你只需要解压缩一个记录。压缩后的表也将成为一个只读表,不能进行DML操作。

--压缩前,查看test库中表的数据文件大小,其中有个a表是myisam类型, 将压缩MYD文件

    [root@hsr ~]# cd /usr/local/mysql/data

    [root@hsr data]# cd test
    [root@hsr test]# ls -ltr
    总用量 24
    -rw-r----- 1 mysql mysql   61 8月  24 16:43 db.opt
    -rw-r----- 1 mysql mysql 8566 8月  28 14:29 a.frm
    -rw-r----- 1 mysql mysql 1024 8月  28 14:32 a.MYI
    -rw-r----- 1 mysql mysql  456 8月  28 14:32 a.MYD

-- myisampack先到基地址里面找到该命令(用rpm安装可以在根目录下直接用该命令)
[root@hsr ~]# cd /usr/local/mysql/bin

澳门皇冠金沙网站 2

-- 压缩a表,提示太小无法压缩
[root@hsr bin]# ./myisampack /usr/local/mysql/data/test/a
/usr/local/mysql/data/test/a is too small to compress
如果压缩成功,该表就只能读,不能写。可以在查看MYD文件大小。

本文主要给大家介绍了关于MySQL常用工具的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧。

一、mysql (客户端连接工具)

使用最频繁的连接数据库的客户端工具,使用语法如下:

mysql [options] [database]

这里的 options 表示 mysql 的可用选项,可以一次写一个或者多个,甚至可以不写;database 表示连接的数据库,一次只能写一个或者不写,如果不写,连接成功后需要用 “use database”命令来进入要操作的数据库。

1. 连接选项

选项的表达方式有多种,例如:

# 这三种方式都是可以的
shell> mysql -u root
shell> mysql -uroot
shell> mysql -user=root
  1. 连接选项
  • -u, --user=name    指定用户名
  • -p, --password[=name]    指定密码 
  • -h, --host=name    指定服务器 IP 或者域名 
  • -P, --port=#    指定连接端口

一般在在本地环境,为了方便,可以在 配置文件 my.cnf 中配置当前用户和密码,配置好后,直接执行 mysql 就可以连接到数据库:

[client]
user=root
password=000000
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

配置好后,直接执行 mysql 即可:

zj@bogon:~$ mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 19
...

登录远程服务器,需要指定地址和端口:

shell> mysql -h 192.168.10.10 -P 3306 -uroot -p

注意: 在正式的生产环境中,为了安全起见,一般需要创建应用账号并赋予适当权限,而不会用 root 直接操作数据库;默认端口(3306)一般不要使用,可以改为任意操作系统未占用的端口。

2. 客户端字符集选项

--default-character-set=charset-name

作为服务器的字符集选项,这个选项也可以配置在 my.cnf 的 [mysqld] 组中。同样,作为客户端字符集选项,也可以配置在 my.cnf 的 [mysql]组中,这样每次用 mysql 工具连接数据库的时候就会自动使用此客户端字符集。当然,也可以在 mysql 的命令行中手工指定客户端字符集:

shell> mysql -u user -default-character-set=charset

相当于在 mysql 客户端连接成功后执行:

set names charset;

3. 执行选项

-e, --execute=name // 执行 sql 语句并退出

此选项可以直接在 MySQL 客户端执行 sql 语句,对于一些批处理脚本,这是方式尤其方便:

zj@bogon:~$ mysql mysql -e "select user,host from user"
 ----------- ----------- 
| user | host |
 ----------- ----------- 
| root | 127.0.0.1 |
| mysql.sys | localhost |
| root | localhost |
 ----------- ----------- 

可以按这种方式连续执行多个 sql 语句,用英文分号(;)隔开。

4. 格式化选项

  • -E, --vertical    将输出方式按照字段顺序竖着显示 
  • -s, --silent    去掉 mysql 中的线条框显示

“-E” 选项类似于 mysql 里面执行 sql 语句后加 “G”, 经常和 -e 一起使用。

二、myisampack (myisam 表压缩工具)

myisampack 是一个表压缩工具,可以使用很高的压缩率来对 myisam 存储引擎的表进行压缩,使得压缩后的表占用比压缩前小得多的空间。但是压缩后的表将成为一个只读表,不能进行 DML 操作。

三、mysqladmin(MySQL 管理工具)

mysqladmin 是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。它的功能与 mysql 客户端非常类似,主要区别在于它更侧重于一些管理方面的功能。

使用语法:

shell> mysqladmin [options] command [command-options]...

可以执行的命令如下:

 create databasename Create a new database 新建数据库
 debug  Instruct server to write debug information to log 把 debug 日志记录到日志文件中
 drop databasename Delete a database and all its tables 删除数据库
 extended-status Gives an extended status message from the server 查看 MySQL 服务器的状态信息
 flush-hosts Flush all cached hosts
 flush-logs Flush all logs
 flush-status Clear status variables
 flush-tables Flush all tables
 flush-threads Flush the thread cache
 flush-privileges Reload grant tables (same as reload)
 kill id,id,... Kill mysql threads
 password [new-password] Change old password to new-password in current format
 ping  Check if mysqld is alive
 processlist Show list of active threads in server
 reload  Reload grant tables
 refresh  Flush all tables and close and open logfiles
 shutdown  Take server down
 status  Gives a short status message from the server
 start-slave Start slave
 stop-slave Stop slave
 variables  Prints variables available
 version  Get version info from server

举例:

zj@bogon:/usr/local/mysql/bin$ mysqladmin -uroot -p shutdown
Enter password: 

四、日志管理工具

由于服务器生成的二进制文件以二进制格式保存,所以如果想要检查这些文件的文本格式,就会用到 mysqlbinlog 日志管理工具。

用法如下:

shell> mysqlbinlog [option] log-file1 log-file2...

option 有很多选项:

  • -d,--database=name: 指定数据库名称,只列出指定的数据库相关操作。
  • -o, --offset=#: 忽略日志中的前 n 行命令。
  • -r, --result-file=name: 将输出的文本格式日志输出到指定文件
  • -s, --short-form: 显示简单格式,省略掉一些信息。
  • --start-datetime=name --stop-datetime=name: 指定日期间隔内的所有日志。
  • --start-position=# --stop-position=#: 指定位置间隔内的所有日志

本文由澳门皇冠金沙网站发布于数据库研究,转载请注明出处:MySQL中的常用工具实例汇总,mysql连接工具与MyI