MYSQL复习笔记2-自带工具介绍

浏览数：22 / 时间：2015年06月12日

Date: 20140102
Auth: Jin

一、mysql 命令行客户端
1）base
-h host
-P port
--socket=path，-S path用于连接的套接字文件替换使用IP PORT连接
-u username
-p password
---database=db_name，-D db_name 连接数据库
--default-character-set=charset client字符集
--execute=statement, -e statement 执行语句
--prompt=name Set the mysql prompt to this value.
--local-infile[={0|1}] 为LOAD DATA INFILE启用或禁用LOCAL功能。
--tee=file_name

2）进入命令行后
mysql> help
在mysql常用
(1)设置字符集
mysql> charset utf8
(2)调用shell命令，类似vim中：shell
system
Usage: \! shell-command
mysql> \! ls
(3)查看状态
mysql> status
(4)开启关闭警告
warnings (\W) Show warnings after every statement.
nowarning (\w) Don‘t show warnings after every statement.
(5)执行文件中的sql source
Usage: \. <filename> | source <filename>

3)设置提示符
--prompt=name
(1)可以用MYSQL_PS1环境变量来设置提示字符串
export MYSQL_PS1="(\u@\h) [\d]> "
(2)mysql配置文件中设置
[client]
prompt=\\u@\\h:[\\d] \\r:\\m:\\s>
显示效果
root@localhost:[test] 08:47:52>
(3)交互式
可以使用prompt(或\R)命令交互地更改提示
prompt (\u@\h) [\d]>

4）mysql技巧
(1) 垂直显示查询结果
mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
(2)使用--safe-updates选项
不允许你执行UPDATE或DELETE语句，除非在WHERE子句中指定一个键值约束或提供一个LIMIT子句(或二者皆使用)。
所有大的SELECT结果自动限制到1,000行，除非语句包括一个LIMIT子句
放弃可能需要检查1,000,000多行组合的多表SELECT语句

二、mysqladmin：用于管理MySQL服务器的客户端
基本连接数据的参数同mysql,
--default-character-set=name是设置数据库的默认字符集
# mysqladmin --help
分类记忆一下
1）操作数据库
mysqladmin create DataBase
创建数据库
mysqladmin drop DataBase
删除数据库
2）显示状态和变量
mysqladmin status
Uptime: 3263 Threads: 1 Questions: 107 Slow queries: 0 Opens: 34 Flush tables: 1 Open tables: 27 Queries per second avg: 0.032
已经运行的秒数：活动线程数目查询数目慢查询的数量打开的数据库表的数量打开的表的数量每个语句执行的时间
Flush tables flush refresh和reload命令的数量
Memory in use
mysqld代码直接分配的内存数量。只有用--with--debug=full编译了MySQL该值才显示。
Maximum memory used
mysqld代码直接分配的最大内存数量。只有用--with--debug=full编译了MySQL该值才显示。
简单状态
mysqladmin extended-status
详细状态
mysqladmin variable
详细变量
3）密码管理
mysqladmin password [new-password]
设置新密码
mysqladmin old-password [new-password]
类似password但使用旧的(pre-4.1)密码哈希格式保存密码
# mysqladmin variables -p163.com |grep password
| old_passwords | OFF
4）线程管理
mysqladmin processlist
查看线程
mysqladmin kill id,id,...
杀掉线程
5）监控服务是否存活
mysqladmin ping
6) 刷新
mysqladmin reload
mysqladmin flush-privileges
刷新权限
flush-hosts Flush all cached hosts
flush-logs Flush all logs #刷新LOG
flush-status Clear status variables
flush-tables Flush all tables
flush-threads Flush the thread cache
7）关闭服务器，启动主从
mysqladmin shutdown Take server down
mysqladmin start-slave Start slave 要作为slave才能运行
mysqladmin stop-slave Stop slave 要作为slave才能运行

三、mysqldump
1)、备份数据操作
(1)、导出所有数据库 --all--database，-A
mysqldump --all-database > 导出的文件名
mysqldump --all-database > /data/backup/db/alldb_bak.sql
(2)、导出单个数据库 --databases, -B
mysqldump 数据库名 > 导出的文件名
mysqldump -u root -p zabbix > /data/backup/db/zabbix_bak.sql
(3)、导出多个数据库 --databases, -B
mysqldump --database 数据库1 数据库2 > 导出的文件名
mysqldump --database zabbix mysql > /data/backup/db/two_db_bak.sql
(4)、压缩备份
直接将MySQL数据库压缩备份
mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

注意：在做主从dump数据加上--master-data，也就是--master-data=1

mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，
file和position记录的位置就是slave从master端复制文件的起始位置。SLAVE只需要start slave即可
man里有介绍

2)导出一个数据库结构 --no-data, -d
(1)、基本操作
mysqldump -–no-data -–databases databasename1 databasename2 > structurebackupfile.sql
(2)增加创建数据库结构前的删除操作
--add-drop-database 在每个CREATE DATABASE语句前添加DROP DATABASE语句。貌似没有
--add-drop-table 在每个CREATE TABLE语句前添加DROP TABLE语句。默认。反选项--skip-add-drop-table
--add-drop-trigger
mysqldump -u root -p -d --add-drop-table zabbix > /data/backup/db/zabbix_sche.sql

3)、不锁表备份
Mysqldump备份要锁表，不锁表备份的办法
加上--lock-tables=false参数，如果是innodb，则加上--single-transcation

4)、默认选项和反选项
默认选项 --opt
Use of --opt is the same as specifying --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert,
--lock-tables, --quick, and --set-charset. All of the options that --opt stands for also are on by default because --opt is on by
default
--compact 选项
Use of --compact is the same as specifying --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and
--skip-set-charset options.
产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项
--opt反选项 --skip-opt
To reverse --opt for all features except index disabling and table locking, use --skip-opt --disable-keys --lock-tables.
--tables
覆盖---database或-B选项。选项后面的所有参量被看作表名。

5) -R 导出存储过程

四、mysqlimport
load data infile的封装
将特定格式的文本数据导入指定的MYSQL，比如标准的CSV文件。
从来没用过，outfile和load data infile到用过。
没有分割符
mysql> select * from users into outfile ‘/tmp/zabbix_users_bak.txt‘;
mysql> load data infile ‘/tmp/zabbix_users_bak.txt‘ into table users;
有分割符
mysql> select * from users into outfile ‘/tmp/zabbix_users_bak1.txt‘ FIELDS TERMINATED BY ‘,‘;
mysql> load data infile ‘/tmp/zabbix_users_bak1.txt‘ into table users FIELDS TERMINATED BY ‘,‘;

五、mysqlcheck
1、基本描述
mysqlcheck的功能类似myisamchk，但其工作不同。主要差别是当mysqld服务器在运行时必须使用mysqlcheck，
而myisamchk应用于服务器没有运行时。使用mysqlcheck的好处是不需要停止服务器来检查或修复表。使用myisamchk修复失败是不可逆的。
Mysqlcheck为用户提供了一种方便的使用SQL语句CHECK TABLE、REPAIR TABLE、ANALYZE TABLE和OPTIMIZE TABLE的方式。
它确定在要执行的操作中使用使用哪个语句，然后将语句发送到要执行的服务器上。
并不是所有存储引擎都支持CHECK TABLE、REPAIR TABLE、ANALYZE TABLE和OPTIMIZE TABL，比如innoDB不支持REPAIR.
CHECK TABLE、检查
REPAIR TABLE、修复
ANALYZE TABLE、分析
OPTIMIZE TABL 优化

有3种方式来调用mysqlcheck：
shell> mysqlcheck[options] db_name [tables]
shell> mysqlcheck[options] ---database DB1 [DB2 DB3...]
shell> mysqlcheck[options] --all--database

如果没有指定任何表或使用---database或--all--database选项，则检查整个数据库。
下面的名可用来更改mysqlcheck的默认行为：
mysqlrepair
默认选项为--repair

mysqlanalyze
默认选项为--analyze

mysqloptimize
默认选项为--optimize
-B, --databases

2、自己实践
1) 检查表
-c, --check Check table for errors.
-C, --check-only-changed
Check only tables that have changed since last check or
haven‘t been closed properly.
[root@mnt ~]# mysqlcheck -c --database mysql
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log
Error : You can‘t use locks with log tables.
status : OK
mysql.help_category OK

[root@mnt ~]# mysqlcheck -C --database mysql
mysql.columns_priv Table is already up to date
mysql.db Table is already up to date
mysql.event Table is already up to date
mysql.func Table is already up to date
mysql.general_log
Error : You can‘t use locks with log tables.
status : OK
mysql.help_category Table is already up to date

2)分析表
-a, --analyze Analyze given tables.
[root@mnt ~]# mysqlcheck -a -B mysql
mysql.columns_priv Table is already up to date
mysql.db Table is already up to date
mysql.event Table is already up to date
mysql.func Table is already up to date
mysql.general_log
note : The storage engine for the table doesn‘t support analyze

3）优化
-o, --optimize Optimize table.
[root@mnt ~]# mysqlcheck -o -B mysql
mysql.columns_priv Table is already up to date
mysql.db Table is already up to date
mysql.event Table is already up to date
mysql.func Table is already up to date
mysql.general_log
note : The storage engine for the table doesn‘t support optimize
4）修复表
[root@mnt ~]# mysqlcheck -r -B mysql
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK

六、 mysqldumpslow
主要功能
1、打开slowlog
long_query_time = 2
slow-query-log-file = /data/logs/mysql/3306_slow.log
log-queries-not-using-indexes #log没有使用索引的query。

2、分析slowlog
# mysqldumpslow --help

-s ORDER what to sort by (al, at, ar, c, l, r, t), ‘at‘ is default 是表示按照何种方式排序，默认at

al: average lock time 平均锁定时间
ar: average rows sent 平均返回记录数
at: average query time 平均执行时间
c: count c 记录次数
l: lock time 锁时间
r: rows sent 返回的记录数来排序，
t: query time 时间
-r reverse the sort order (largest last instead of first) 倒序从最后开始显示
-t NUM just show the top n queries #显示头n条记录
-a don‘t abstract all numbers to N and strings to ‘S‘
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string #后边可以写一个正则匹配模式，大小写不敏感的。
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is ‘*‘, i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don‘t subtract lock time from total time

实例
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回记录集最多的10个查询。
/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
得到按照时间排序的前10条里面含有左连接的查询语句。

记忆部分

-s, 是表示按照何种方式排序，
c 记录次数
t 查询时间
l 锁时间
r 返回的记录数来排序，
at 平均执行时间
al 平均锁定时间
默认 at
-t, 是top n的意思，即为返回前面多少条的数据；

mysqldumpslow -s at -t 10 /database/mysql/slow-log

七、mysqlbinglog
主要功能分析MySQL Server所产生的二进制日志。当希望通过备份的binlog做一些指定时间之类的恢复时，mysqlbinlog可以帮助我们找出恢复操作要做哪些事情。
1、打开binlog
log-bin=mysql-bin
log-bin = binlogs/mysql-bin 文件存放目录前缀
binlog_format = ROW #格式
#binlog_format=mixed
expire_logs_day=1 #过期时间超过手动清除
max_binlog_size=700M #每个文件大小
log-bin-index[=file_name] #二进制日志索引文件的文件名。当mysqld在运行时，不应手动编辑该文件
binlog_cache_size=3M
对非事务表的更新执行完毕后立即保存到二进制日志中。对于事务表，例如BDB或InnoDB表，所有更改表的更新(UPDATE、DELETE或INSERT) 被缓存起来，直到服务器接收到COMMIT语句。

2、分析binlog
[root@mnt mysql]# mysqlbinlog mysql-bin.000003

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140101 17:40:02 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.71-log created 140101 17:40:02 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ‘
cuLDUg8BAAAAZgAAAGoAAAABAAQANS4xLjcxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABy4sNSEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
‘/*!*/;
# at 106
#140101 17:43:02 server id 1 end_log_pos 193 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1388569382/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database dbtest
/*!*/;
# at 193
#140101 17:44:39 server id 1 end_log_pos 356 Query thread_id=3 exec_time=0 error_code=0
use `dbtest`/*!*/;
SET TIMESTAMP=1388569479/*!*/;
create table tab1( id int(11) not null auto_increment, name varchar(25) not null,primary key(id) )
/*!*/;
# at 356
#140101 17:51:02 server id 1 end_log_pos 426 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1388569862/*!*/;
BEGIN
/*!*/;
# at 426
#140101 17:51:02 server id 1 end_log_pos 454 Intvar
SET INSERT_ID=1/*!*/;
# at 454
#140101 17:51:02 server id 1 end_log_pos 567 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1388569862/*!*/;
insert into tab1(name) values (‘diege‘),(‘lily‘)
/*!*/;
# at 567
#140101 17:51:02 server id 1 end_log_pos 594 Xid = 81
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;