tcpdump抓包常用笔记

2016-05-05 by wwek·0评论

查看HTTP GET请求

sudo tcpdump -s 0 -A 'tcp dst port 80 and tcp[((tcp[12:1] & 0xf0) >> 2):4] = 0x47455420'

1 2	sudo tcpdump -s 0 -A 'tcp dst port 80 and tcp[((tcp[12:1] & 0xf0) >> 2):4] = 0x47455420'

查看HTTP POST请求

sudo tcpdump -s 0 -A 'tcp dst port 80 and (tcp[((tcp[12:1] & 0xf0) >> 2):4] = 0x504f5354)'

1 2	sudo tcpdump -s 0 -A 'tcp dst port 80 and (tcp[((tcp[12:1] & 0xf0) >> 2):4] = 0x504f5354)'

查看HTTP请求响应头以及数据

sudo tcpdump -A -s 0 'tcp port 80 and (((ip[2:2] - ((ip[0]&0xf)<<2)) - ((tcp[12]&0xf0)>>2)) != 0)'
sudo tcpdump -X -s 0 'tcp port 80 and (((ip[2:2] - ((ip[0]&0xf)<<2)) - ((tcp[12]&0xf0)>>2)) != 0)'

sudo tcpdump -A -s 0 'tcp port 80 and (((ip[2:2] - ((ip[0]&0xf)<<2)) - ((tcp[12]&0xf0)>>2)) != 0)'

sudo tcpdump -X -s 0 'tcp port 80 and (((ip[2:2] - ((ip[0]&0xf)<<2)) - ((tcp[12]&0xf0)>>2)) != 0)'

抓取mysql执行的sql语句

tcpdump -i eth1 -s 0 -l -w - dst  port 3306 | strings

1 2	tcpdump -i eth1 -s 0 -l -w - dst port 3306 \| strings

抓取mysql通讯的网络包(cap用wireshark打开)

tcpdump -n -nn -tttt -i eth0 -s 65535 'port 3306' -w 20160505mysql.cap

1 2	tcpdump -n -nn -tttt -i eth0 -s 65535 'port 3306' -w 20160505mysql.cap

各种远程抓包方法

tcpdump -v -i <INTERFACE> -s 0 -w /tmp/sniff.pcap port <PORT> # On the remote side
mkfifo /tmp/fifo; ssh-keygen; ssh-copyid root@remotehostaddress; sudo ssh root@remotehost "tshark -i eth1 -f 'not tcp port 22' -w -" > /tmp/fifo &; sudo wireshark -k -i /tmp/fifo;
ssh user@server.com sudo tcpdump -i eth0  -w - 'port 80'| /Applications/Wireshark.app/Contents/Resources/bin/wireshark -k -i -
ssh root@HOST tcpdump -iany -U -s0 -w - 'not port 22' | wireshark -k -i -

tcpdump -v -i <INTERFACE> -s 0 -w /tmp/sniff.pcap port <PORT> # On the remote side

mkfifo /tmp/fifo; ssh-keygen; ssh-copyid root@remotehostaddress; sudo ssh root@remotehost "tshark -i eth1 -f 'not tcp port 22' -w -" > /tmp/fifo &; sudo wireshark -k -i /tmp/fifo;

ssh user@server.com sudo tcpdump -i eth0 -w - 'port 80'| /Applications/Wireshark.app/Contents/Resources/bin/wireshark -k -i -

ssh root@HOST tcpdump -iany -U -s0 -w - 'not port 22' | wireshark -k -i -

参考资料
analyze traffic remotely over ssh w/ wireshark

mysql中database库和tables表中字符集 utf8 和utf8mb4 有什么区别?

2015-12-04 by wwek·2条评论

mysql中databases库和tables表中字符集 utf8 和utf8mb4 有什么区别?

可以简单的理解 utf8mb4 是目前最大的一个字符编码,支持任意文字.

那么utf8mb4比utf8多了什么的呢?
多了emoji编码支持.
如果实际用途上来看,可以给要用到emoji的库或者说表,设置utf8mb4.
比如评论,文章什么的要支持emoji可以用到.

建议普通表使用utf8 如果这个表需要支持emoji就使用utf8mb4

新建mysql库或者表的时候还有一个排序规则
utf8_unicode_ci比较准确，utf8_general_ci速度比较快。通常情况下 utf8_general_ci的准确性就够我们用的了，在我看过很多程序源码后，发现它们大多数也用的是utf8_general_ci，所以新建数据库时一般选用utf8_general_ci就可以了
如果是utf8mb4那么对应的就是 utf8mb4_general_ci utf8mb4_unicode_ci

Centos使用MySQL工具Percona Toolkit

2015-09-19 by wwek·3条评论

Centos使用MySQL工具Percona Toolkit

安装Percona Toolkit 的Repo 得以支持直接用yum 安装二进制包

yum install -y https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-4.noarch.rpm
yum install -y percona-toolkit

1 2	yum install -y https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-4.noarch.rpm yum install -y percona-toolkit

改MySQL表结构DDL pt-online-schema-change

pt-online-schema-change -u root -h 10.8.8.8 -p password --alter='add column vid int ' --execute A=utf8,D=database,t=table

1	pt-online-schema-change -u root -h 10.8.8.8 -p password --alter='add column vid int ' --execute A=utf8,D=database,t=table

D=database 库名
t=table 表名
–alter=’add column vid int ‘ 要执行的语句

注意：
mysql版本5.5 数据库和表都是utf8编码,
表的字段注释维中文
在执行 pt-online-schema-change的时候一定的加参数 -A=utf-8

Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `wwek`.`wwek_users`...
Creating new table...
Created new table wwek._wwek_users_new OK.
Altering new table...
Altered `wwek`.`_wwek_users_new` OK.
2015-09-18T10:56:16 Creating triggers...
2015-09-18T10:56:16 Created triggers OK.
2015-09-18T10:56:16 Copying approximately 183066 rows...
Copying `wwek`.`wwek_users`: 30% 01:10 remain
Copying `wwek`.`wwek_users`: 49% 01:01 remain
Copying `wwek`.`wwek_users`: 63% 00:51 remain
Copying `wwek`.`wwek_users`: 76% 00:37 remain
Copying `wwek`.`wwek_users`: 88% 00:20 remain
2015-09-18T10:59:03 Copied rows OK.
2015-09-18T10:59:03 Swapping tables...
2015-09-18T10:59:03 Swapped original and new tables OK.
2015-09-18T10:59:03 Dropping old table...
2015-09-18T10:59:04 Dropped old table `wwek`.`_wwek_users_old` OK.
2015-09-18T10:59:04 Dropping triggers...
2015-09-18T10:59:04 Dropped triggers OK.
Successfully altered `wwek`.`wwek_users`.

Operation, tries, wait:

copy_rows, 10, 0.25

create_triggers, 10, 1

drop_triggers, 10, 1

swap_tables, 10, 1

update_foreign_keys, 10, 1

Altering `wwek`.`wwek_users`...

Creating new table...

Created new table wwek._wwek_users_new OK.

Altering new table...

Altered `wwek`.`_wwek_users_new` OK.

2015-09-18T10:56:16 Creating triggers...

2015-09-18T10:56:16 Created triggers OK.

2015-09-18T10:56:16 Copying approximately 183066 rows...

Copying `wwek`.`wwek_users`: 30% 01:10 remain

Copying `wwek`.`wwek_users`: 49% 01:01 remain

Copying `wwek`.`wwek_users`: 63% 00:51 remain

Copying `wwek`.`wwek_users`: 76% 00:37 remain

Copying `wwek`.`wwek_users`: 88% 00:20 remain

2015-09-18T10:59:03 Copied rows OK.

2015-09-18T10:59:03 Swapping tables...

2015-09-18T10:59:03 Swapped original and new tables OK.

2015-09-18T10:59:03 Dropping old table...

2015-09-18T10:59:04 Dropped old table `wwek`.`_wwek_users_old` OK.

2015-09-18T10:59:04 Dropping triggers...

2015-09-18T10:59:04 Dropped triggers OK.

Successfully altered `wwek`.`wwek_users`.

文档&&参考

官方文档Percona Toolkit Documentation

php5.3.x 5.4.x 5.5.x 连接mysql数据库的三种方式以及所用驱动

2014-03-30 by wwek·0评论

php连接mysql的驱动支持

请使用mysqlnd php官方原生驱动。编译参数中需要加如下

./configure --with-mysql=mysqlnd \
--with-mysqli=mysqlnd \
--with-pdo-mysql=mysqlnd \

./configure --with-mysql=mysqlnd \

--with-mysqli=mysqlnd \

--with-pdo-mysql=mysqlnd \

三种（mysql，mysqli，pdo-mysql）php连接 mysql api接口都采用mysqlnd驱动。

php连接mysql的三种api（mysql，mysqli，pdo-mysql）

php官方推荐使用 mysqli 和 PDO_MySQL

php连接mysql的三种api示例

<?php
// mysqli
$mysqli = new mysqli("example.com", "user", "password", "database");
$result = $mysqli->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $result->fetch_assoc();
echo htmlentities($row['_message']);

// PDO
$pdo = new PDO('mysql:host=example.com;dbname=database', 'user', 'password');
$statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = $statement->fetch(PDO::FETCH_ASSOC);
echo htmlentities($row['_message']);

// mysql
$c = mysql_connect("example.com", "user", "password");
mysql_select_db("database");
$result = mysql_query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");
$row = mysql_fetch_assoc($result);
echo htmlentities($row['_message']);
?>

<?php

// mysqli

$mysqli = new mysqli("example.com", "user", "password", "database");

$result = $mysqli->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");

$row = $result->fetch_assoc();

echo htmlentities($row['_message']);

// PDO

$pdo = new PDO('mysql:host=example.com;dbname=database', 'user', 'password');

$statement = $pdo->query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");

$row = $statement->fetch(PDO::FETCH_ASSOC);

echo htmlentities($row['_message']);

// mysql

$c = mysql_connect("example.com", "user", "password");

mysql_select_db("database");

$result = mysql_query("SELECT 'Hello, dear MySQL user!' AS _message FROM DUAL");

$row = mysql_fetch_assoc($result);

echo htmlentities($row['_message']);

PHP中三种主要的MySQL连接方式的功能：

	PHP的mysqli扩展	PDO (使用PDO MySQL驱动和MySQL Native驱动)	PHP的mysql扩展
引入的PHP版本	5.0	5.0	3.0之前
PHP5.x是否包含	是	是	是
MySQL开发状态	活跃	在PHP5.3中活跃	仅维护
在MySQL新项目中的建议使用程度	建议 – 首选	建议	不建议
API的字符集支持	是	是	否
服务端prepare语句的支持情况	是	是	否
客户端prepare语句的支持情况	否	是	否
存储过程支持情况	是	是	否
多语句执行支持情况	是	大多数	否
是否支持所有MySQL4.1以上功能	是	大多数	否

	ext/mysqli	PDO_MySQL	ext/mysql
PHP version introduced	5.0	5.1	2.0
Included with PHP 5.x	Yes	Yes	Yes
Development status	Active	Active	Maintenance only
Lifecycle	Active	Active	Deprecated
Recommended for new projects	Yes	Yes	No
OOP Interface	Yes	Yes	No
Procedural Interface	Yes	No	Yes
API supports non-blocking, asynchronous queries with mysqlnd	Yes	No	No
Persistent Connections	Yes	Yes	Yes
API supports Charsets	Yes	Yes	Yes
API supports server-side Prepared Statements	Yes	Yes	No
API supports client-side Prepared Statements	No	Yes	No
API supports Stored Procedures	Yes	Yes	No
API supports Multiple Statements	Yes	Most	No
API supports Transactions	Yes	Yes	No
Transactions can be controlled with SQL	Yes	Yes	Yes
Supports all MySQL 5.1+ functionality	Yes	Most	No

扩展阅读：

php连接mysql的驱动支持 http://www.php.net/manual/zh/mysqlnd.install.php

php连接mysql api mysqil http://php.net/manual/zh/mysqli.overview.php

php连接mysql 三种api的选择 https://php.net/manual/zh/mysqlinfo.api.choosing.php

MySQL(MyISAM)备份方案：AutoMySQLBackup

2013-05-12 by wwek·2条评论

AutoMySQLBackup是轻量级的mysql备份工具，数据量不大，MyISAM用用很好。

AutoMySQLBackup项目页 http://sourceforge.net/projects/automysqlbackup/

 mkdir AutoMySQLBackup
cd AutoMySQLBackup/
wget -c "http://nchc.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz"
tar zxvf automysqlbackup-v3.0_rc6.tar.gz
chmod +x install.sh
sh install.sh
#全部默认配置
cd /etc/automysqlbackup/
vi automysqlbackup.conf #修改里面的配置
crontab -e
#设定每日凌晨3:00执行
0 3 * * * /usr/local/bin/automysqlbackup

mkdir AutoMySQLBackup

cd AutoMySQLBackup/

wget -c "http://nchc.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz"

tar zxvf automysqlbackup-v3.0_rc6.tar.gz

chmod +x install.sh

sh install.sh

#全部默认配置

cd /etc/automysqlbackup/

vi automysqlbackup.conf #修改里面的配置

crontab -e

#设定每日凌晨3:00执行

0 3 * * * /usr/local/bin/automysqlbackup

btw:这货实际上是使用的mysqldump，如果你是innodb，那么推荐用Xtrabackup，当然还非常推荐，无痛使用Percona mysql。

mysql慢查询分析centos下mysqlsla的安装与使用

2013-04-23 by wwek·一条评论

yum -y install perl perl-DBI perl-CPAN

wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz

tar zxvf mysqlsla-2.03.tar.gz

cd mysqlsla-2.03

继续阅读

流水理鱼

流水理鱼（wwek）的博客

标签：mysql

tcpdump抓包常用笔记

查看HTTP GET请求

查看HTTP POST请求

查看HTTP请求响应头以及数据

抓取mysql执行的sql语句

抓取mysql通讯的网络包(cap用wireshark打开)

各种远程抓包方法

mysql中database库和tables表中字符集 utf8 和utf8mb4 有什么区别?

mysql中databases库和tables表中字符集 utf8 和utf8mb4 有什么区别?

Centos使用MySQL工具Percona Toolkit

Centos使用MySQL工具Percona Toolkit

安装Percona Toolkit 的Repo 得以支持直接用yum 安装二进制包

改MySQL表结构DDL pt-online-schema-change

文档&&参考

php5.3.x 5.4.x 5.5.x 连接mysql数据库的三种方式以及所用驱动

php连接mysql的驱动支持

php连接mysql的三种api（mysql，mysqli，pdo-mysql）

MySQL(MyISAM)备份方案：AutoMySQLBackup

mysql慢查询分析centos下mysqlsla的安装与使用