MySQL中的常用工具实例汇总(推荐)
前言
本文主要给大家介绍了关于MySQL常用工具的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧。
一、mysql(客户端连接工具)
使用最频繁的连接数据库的客户端工具,使用语法如下:
mysql[options][database]
这里的options表示mysql的可用选项,可以一次写一个或者多个,甚至可以不写;database表示连接的数据库,一次只能写一个或者不写,如果不写,连接成功后需要用“usedatabase”命令来进入要操作的数据库。
1.连接选项
选项的表达方式有多种,例如:
#这三种方式都是可以的 shell>mysql-uroot 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 WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis19 ...
登录远程服务器,需要指定地址和端口:
shell>mysql-h192.168.10.10-P3306-uroot-p
注意:在正式的生产环境中,为了安全起见,一般需要创建应用账号并赋予适当权限,而不会用root直接操作数据库;默认端口(3306)一般不要使用,可以改为任意操作系统未占用的端口。
2.客户端字符集选项
--default-character-set=charset-name
作为服务器的字符集选项,这个选项也可以配置在my.cnf的[mysqld]组中。同样,作为客户端字符集选项,也可以配置在my.cnf的[mysql]组中,这样每次用mysql工具连接数据库的时候就会自动使用此客户端字符集。当然,也可以在mysql的命令行中手工指定客户端字符集:
shell>mysql-uuser-default-character-set=charset
相当于在mysql客户端连接成功后执行:
setnamescharset;
3.执行选项
-e,--execute=name//执行sql语句并退出
此选项可以直接在MySQL客户端执行sql语句,对于一些批处理脚本,这是方式尤其方便:
zj@bogon:~$mysqlmysql-e"selectuser,hostfromuser" +-----------+-----------+ |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]...
可以执行的命令如下:
createdatabasenameCreateanewdatabase新建数据库 debugInstructservertowritedebuginformationtolog把debug日志记录到日志文件中 dropdatabasenameDeleteadatabaseandallitstables删除数据库 extended-statusGivesanextendedstatusmessagefromtheserver查看MySQL服务器的状态信息 flush-hostsFlushallcachedhosts flush-logsFlushalllogs flush-statusClearstatusvariables flush-tablesFlushalltables flush-threadsFlushthethreadcache flush-privilegesReloadgranttables(sameasreload) killid,id,...Killmysqlthreads password[new-password]Changeoldpasswordtonew-passwordincurrentformat pingCheckifmysqldisalive processlistShowlistofactivethreadsinserver reloadReloadgranttables refreshFlushalltablesandcloseandopenlogfiles shutdownTakeserverdown statusGivesashortstatusmessagefromtheserver start-slaveStartslave stop-slaveStopslave variablesPrintsvariablesavailable versionGetversioninfofromserver
举例:
zj@bogon:/usr/local/mysql/bin$mysqladmin-uroot-pshutdown Enterpassword:
四、日志管理工具
由于服务器生成的二进制文件以二进制格式保存,所以如果想要检查这些文件的文本格式,就会用到mysqlbinlog日志管理工具。
用法如下:
shell>mysqlbinlog[option]log-file1log-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=#:指定位置间隔内的所有日志
1.示例准备:创建新日志,新建库t1和t2,以及分别新建表test1和test2
MySQL[(none)]>resetmaster; QueryOK,0rowsaffected(0.01sec) MySQL[(none)]>createtablet1(idint,namevarchar); ERROR1046(3D000):Nodatabaseselected MySQL[(none)]>resetmaster; QueryOK,0rowsaffected(0.01sec) MySQL[(none)]>createdatabaset1; QueryOK,1rowaffected(0.04sec) MySQL[(none)]>createdatabaset2; QueryOK,1rowaffected(0.02sec) MySQL[(none)]>uset1; Databasechanged MySQL[t1]>createtabletest1(idint,namevarchar(30)); QueryOK,0rowsaffected(0.11sec) MySQL[t1]>insertintotest1value(1,'zj'); QueryOK,1rowaffected(0.14sec) MySQL[t1]>insertintotest1value(2,'zj2'); QueryOK,1rowaffected(0.02sec) MySQL[t1]>uset2; Databasechanged MySQL[t2]>createtabletest2(idint,namevarchar(30)); QueryOK,0rowsaffected(0.02sec) MySQL[t2]>insertintotest2select*fromt1.test1; QueryOK,2rowsaffected(0.03sec) Records:2Duplicates:0Warnings:0 MySQL[t2]>select*fromt1.test1; +------+------+ |id|name| +------+------+ |1|zj| |2|zj2| +------+------+ 2rowsinset(0.02sec) MySQL[t2]>select*fromtest2; +------+------+ |id|name| +------+------+ |1|zj| |2|zj2| +------+------+ 2rowsinset(0.00sec)
2.不加任何参数,显示所有日志
注意:必须拥有访问目标文件的权限
zj@bogon:/usr/local/mysql/bin$sudo./mysqlbinlog--no-defaults/data/mysql/mysql-bin.000001 [sudo]passwordforzj: /*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER/*!*/; #at4 #17092020:44:49serverid1end_log_pos123CRC320x42fd5a4dStart:binlogv4,serverv5.7.18-logcreated17092020:44:49atstartup ...... createtabletest2(idint,namevarchar(30)) /*!*/; #at1366 #17092020:50:29serverid1end_log_pos1431CRC320x18a95938Anonymous_GTIDlast_committed=6sequence_number=7 SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/; #at1431 #17092020:50:29serverid1end_log_pos1509CRC320x2fa8bd6cQuerythread_id=4exec_time=0error_code=0 SETTIMESTAMP=1505911829/*!*/; BEGIN /*!*/; #at1509 #17092020:50:29serverid1end_log_pos1622CRC320x77ce6f3bQuerythread_id=4exec_time=0error_code=0 SETTIMESTAMP=1505911829/*!*/; insertintotest2select*fromt1.test1 /*!*/; #at1622 #17092020:50:29serverid1end_log_pos1653CRC320x41b7a45bXid=29 COMMIT/*!*/; SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/; DELIMITER; #Endoflogfile /*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;
3.加-d选项,将只显示t2数据库的操作日志
zj@bogon:/usr/local/mysql/bin$sudo./mysqlbinlog--no-defaults/data/mysql/mysql-bin.000001-dt2 /*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; ...... SETTIMESTAMP=1505911829/*!*/; insertintotest2select*fromt1.test1 /*!*/; #at1622 #17092020:50:29serverid1end_log_pos1653CRC320x41b7a45bXid=29 COMMIT/*!*/; SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/; DELIMITER; #Endoflogfile /*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;
4.加-o选项,忽略掉前20行命令
zj@bogon:/usr/local/mysql/bin$sudo./mysqlbinlog--no-defaults/data/mysql/mysql-bin.000001-o20 /*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER/*!*/; #at4 #17092020:44:49serverid1end_log_pos123CRC320x42fd5a4dStart:binlogv4,serverv5.7.18-logcreated17092020:44:49atstartup #Warning:thisbinlogiseitherinuseorwasnotclosedproperly. ROLLBACK/*!*/; BINLOG' wWLCWQ8BAAAAdwAAAHsAAAABAAQANS43LjE4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAADBYsJZEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AU1a/UI= '/*!*/; #at1509 #17092020:50:29serverid1end_log_pos1622CRC320x77ce6f3bQuerythread_id=4exec_time=0error_code=0 use`t2`/*!*/; SETTIMESTAMP=1505911829/*!*/; SET@@session.pseudo_thread_id=4/*!*/; SET@@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/; SET@@session.sql_mode=1436549152/*!*/; SET@@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/; /*!\Cutf8mb4*//*!*/; SET@@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/; SET@@session.lc_time_names=0/*!*/; SET@@session.collation_database=DEFAULT/*!*/; insertintotest2select*fromt1.test1 /*!*/; #at1622 #17092020:50:29serverid1end_log_pos1653CRC320x41b7a45bXid=29 COMMIT/*!*/; SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/; DELIMITER; #Endoflogfile /*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;
5.加-r选项,将上面的结果输出到文件resultfile中。
zj@bogon:/usr/local/mysql/bin$sudo./mysqlbinlog--no-defaults/data/mysql/mysql-bin.000001-o20-r./logfile zj@bogon:/usr/local/mysql/bin$sudomore./logfile /*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; ...
6.结果显示的内容较多,显得比较乱,加-s选项将上面的内容进行简单显示
zj@bogon:/usr/local/mysql/bin$sudo./mysqlbinlog--no-defaults/data/mysql/mysql-bin.000001-o20-s /*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER/*!*/; ROLLBACK/*!*/; use`t2`/*!*/; SETTIMESTAMP=1505911829/*!*/; SET@@session.pseudo_thread_id=999999999/*!*/; SET@@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/; SET@@session.sql_mode=1436549152/*!*/; SET@@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/; /*!\Cutf8mb4*//*!*/; SET@@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/; SET@@session.lc_time_names=0/*!*/; SET@@session.collation_database=DEFAULT/*!*/; insertintotest2select*fromt1.test1 /*!*/; COMMIT/*!*/; SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/; DELIMITER; #Endoflogfile /*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;
7.加“--start-datetime--stop-datetime”选项显示5:00:00~5:01:00之间的日志
zj@bogon:/usr/local/mysql/bin$sudo./mysqlbinlog--no-defaults/data/mysql/mysql-bin.000001--start-datetime="2017/09/3005:00:00"--stop-datetime='2017/09/3005:01:00'
开始日期和结束日期可以只写一个。如果只写开始日期,表示范围开始日期到日志结束;如果只写结束日期,表示日志开始到指定的结束日期。
8.--start-position=#和--stop-position=#,与日期范围类似,不过可以更精确的表示范围。
sudo./mysqlbinlog--no-defaults/data/mysql/mysql-bin.000001--start-position=4--stop-datetime=100
五、mysqlcheck(myisam表维护工具)
mysqlcheck工具可以检查和修复myisam表,还可以优化和分析表。实际上,它集成了mysql工具中的check、repair、analyze、optimize
有3种方式可以来调用mysqlcheck:
shell>mysqlcheck[options]db_name[tables] shell>mysqlcheck[options]--databaseDB1[DB2DB3...] shell>mysqlcheck[options]--all-databse
option中有以下常用选项:
- -c,--check(检查表)
- -r,--repair(修复表)
- -a,--analyze(分析表)
- -o,--optimize(优化表)
其中,默认选项是-c(检查表)
示例:
1.检查表
zj@bogon:/data/mysql$mysqlcheck-ct2 t2.test1OK t2.test2OK
2.修复表
zj@bogon:/data/mysql$mysqlcheck-rt2 t2.test1 note:Thestorageengineforthetabledoesn'tsupportrepair t2.test2OK
test1表的存储引擎为innodb,不支持repair。
3.分析表
zj@bogon:/data/mysql$mysqlcheck-at2 t2.test1OK t2.test2OK
4.优化表
zj@bogon:/data/mysql$mysqlcheck-ot2 t2.test1 note:Tabledoesnotsupportoptimize,doingrecreate+analyzeinstead status:OK t2.test2
六、mysqldump(数据导出工具)
mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表或装载表的sql语句。
有三中方式来调用mysqldump:
mysqldump[OPTIONS]database[tables]//备份单个数据库或者库中部分数据表 mysqldump[OPTIONS]--databases[OPTIONS]DB1[DB2DB3...]//备份指定的一个或者多个数据库 mysqldump[OPTIONS]--all-databases[OPTIONS]//备份所有数据库
1.连接选项
- -u,--user=name //指定用户名
- -p,--password[=name] //指定密码
- -h,--host=name //指定服务器IP或者域名
- -p,--port=# //指定连接端口
示例:
shell>mysqldump-h192.18.10.10-p3306-uroot-ptest>test.sql
2.输出内容选项
- --add-drop-database 每个数据库创建语句前加上dropdatabase语句
- --add-drop-table 在每个表创建语句前加上droptable语句
在默认情况下,这两个参数都自动加上。
- -n,--no-create-db 不包含数据库的创建语句
- -t,--no-create-info 不包含数据表的创建语句
- -d,--no-data 不包含数据
3.输出格式选项
--compact选项使得输出结果简洁,不包括默认选项中的各种注释。
root@bogon:/usr/local/mysql/bin#./mysqldump--compactt2emp>emp.sql root@bogon:/usr/local/mysql/bin#moreemp.sql /*!40101SET@saved_cs_client=@@character_set_client*/; /*!40101SETcharacter_set_client=utf8*/; CREATETABLE`emp`( `id`int(11)NOTNULLDEFAULT'0', `name`varchar(10)DEFAULTNULL, `context`text, PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8mb4; /*!40101SETcharacter_set_client=@saved_cs_client*/; INSERTINTO`emp`VALUES(1,'a','a'),(2,'b','b');
-c或者--complete-insert选项使得输出文件中的insert语句包括字段名称,默认是不包括字段名称的。
root@bogon:/usr/local/mysql/bin#./mysqldump-c--compactt2emp>emp.sql root@bogon:/usr/local/mysql/bin#moreemp.sql /*!40101SET@saved_cs_client=@@character_set_client*/; /*!40101SETcharacter_set_client=utf8*/; CREATETABLE`emp`( `id`int(11)NOTNULLDEFAULT'0', `name`varchar(10)DEFAULTNULL, `context`text, PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8mb4; /*!40101SETcharacter_set_client=@saved_cs_client*/; INSERTINTO`emp`(`id`,`name`,`context`)VALUES(1,'a','a'),(2,'b','b');
-T选项将指定数据表中的数据备份为单纯的数据文本和建表sql两个文件,经常和下面几个选项一起配合使用,将数据导出为指定格式显示。
- -T,--tab=name 备份数据和建表语句
- --fileds-terminated-by=name 域分隔符
- --fileds-enclosed-by=name 域引用符
- --fileds-optionally-enclosed-by=name 域可选引用符
- --fileds-escaped-by=name 转义字符
示例:将t2数据库中的表emp导出为单纯的数据文本和建表sql两个文件,并存放在当前路径下的bak目录下。
1.创建备份目录
root@bogon:/usr/local/mysql/bin#mkdirbak
2.将t2数据库下的表emp备份到bak目录下
root@bogon:/usr/local/mysql/bin#./mysqldumpt2emp-T./bak
3.查看bak目录,发现两个文件
root@bogon:/usr/local/mysql/bin#ls./bak emp.sqlemp.txt
4.查看两个文件的内容,.sql结尾的是建表及插入数据的sql,.txt结尾的是表数据
root@bogon:/usr/local/mysql/bin#more./bak/emp.sql --MySQLdump10.13Distrib5.7.18,forLinux(x86_64) -- --Host:localhostDatabase:t2 -------------------------------------------------------- --Serverversion5.7.18-log /*!40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/; /*!40101SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/; /*!40101SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION*/; /*!40101SETNAMESutf8mb4*/; /*!40103SET@OLD_TIME_ZONE=@@TIME_ZONE*/; /*!40103SETTIME_ZONE='+00:00'*/; /*!40101SET@OLD_SQL_MODE=@@SQL_MODE,SQL_MODE=''*/; /*!40111SET@OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0*/; -- --Tablestructurefortable`emp` -- DROPTABLEIFEXISTS`emp`; /*!40101SET@saved_cs_client=@@character_set_client*/; /*!40101SETcharacter_set_client=utf8*/; CREATETABLE`emp`( `id`int(11)NOTNULLDEFAULT'0', `name`varchar(10)DEFAULTNULL, `context`text, PRIMARYKEY(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8mb4; /*!40101SETcharacter_set_client=@saved_cs_client*/; /*!40103SETTIME_ZONE=@OLD_TIME_ZONE*/; /*!40101SETSQL_MODE=@OLD_SQL_MODE*/; /*!40101SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT*/; /*!40101SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS*/; /*!40101SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION*/; /*!40111SETSQL_NOTES=@OLD_SQL_NOTES*/; --Dumpcompletedon2017-09-2112:07:38
root@bogon:/usr/local/mysql/bin#more./bak/emp.txt 1aa 2bb
4.字符集选项
mysqldump导出的数据的字符集使用的是mysqld启动时的默认字符集,如果表的字符集用的不是默认字符集,导出的数据就有可能出现乱码。所以在导出时,应该先确定表的字符集,在导出时指定该字符集即可。
shell>mysqldump-uroot--compact--default-character-set=utf8t2emp>emp.sql
5.其他常用选项
-F--flush-logs(备份前刷新日志)
加上此选项后,备份前将关闭就日志,生成新日志。使得进行恢复的时候直接从新日志开始进行重做,大大方便了恢复过程。
-l--lock-tables(给所有表加读锁)
可以在备份期间使用,使得数据无法被更新,从而使备份的数据保持一致性,可以配合-F选项一起使用。
七、mysqlimport(数据导入工具)
mysqlimport是客户端数据导入工具,用来导入mysqldump加-T选项后导出的文本文件。
基本用法:
shell>mysqlimport[options]db_nametextfile1
八、mysqlshow(数据库对象查看工具)
mysqlshow客户端对象查找工具,用来很快的查找存在哪些数据库,数据库中的表、表中的列或索引,和mysql客户端工具很类似,不过有些特性是mysql客户端工具所不具备的。
使用方法:
shell>mysqlshow[option][db_name[tbl_name[col_name]]]
如果不加任何选项,默认情况下会显示所有数据库。
常用选项:
1.--count(显示数据库和表的统计信息)
如果不指定数据库,则显示每个数据库的名称、表数量、记录数量;
如果指定数据库,则显示指定数据库的每个表名、字段数量,记录数量;
如果指定具体数据库中的具体表,则显示表的字段信息。
2.-k或者--keys(显示指定表中的所有索引)
此选项显示了两部分内容,一部分是指定表的表结构,另一部分中是指定表的当前索引信息
3.-i或者--status(显示表的一些状态信息)
九、perror(错误代码查看工具)
在MySQL的使用过程中,可能会出现各种各样的error。这些error有些是由于操作系统引起的,比如文件或者目录不存在;有些则是由于存储引擎使用不当引起的。这些error一般都有一个代码,类似于“error:#”或者“Errcode:#”,“#”代表具体的错误号。perror的作用就是解释这些错误代码的详细含义:
perror[options][errorcode[errorcode]] zj@bogon:/usr/local/mysql/bin$perror30 OSerrorcode30:Read-onlyfilesystem zj@bogon:/usr/local/mysql/bin$perror60 OSerrorcode60:Devicenotastream zj@bogon:/usr/local/mysql/bin$perror3060 OSerrorcode30:Read-onlyfilesystem OSerrorcode60:Devicenotastream
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流,谢谢大家对毛票票的支持。