Skip to main content

Mysqldump & Mysqlbinlog

Mysqldump

Mysqldump一般用于导出数据库备份。

导出数据库

采用事务,此时不会锁住表

mysqldump -u root -p --master-data=2 --all-databases --triggers --routines --events --single-transaction --opt -S ~/mysql/3306/my3306.sock > ~/mysql/3306/tmp/bak.sql  

直接执行,此时会锁住导出的表

mysqldump -u root -p --all-databases --triggers --routines --events --opt -S ~/mysql/3306/my3306.sock > ~/mysql/3306/tmp/bak.sql  

--single-transaction属性会采用事务快照,从而保证数据的一致性,同时不会锁住表让其他进程无法操作数据。

常用参数

Parameter Description
--single-transaction 基于Innodb数据不锁定数据库
--master-data=[value] 导出主服务器信息,这样子导出的数据可以直接部署在从数据库上。传递value=2会将change master命令写在sql语句中,而value=1则不会,需要手动进行操作。此命令必须开启binlog,并且具有reload权限
--quick / -q 直接读取表中的记录,而不走缓存
--no-data 不导出数据,可以用在导出表结构上
--no-create-info 仅导出数据,不导出表创建语句
--no-create-db 不创建数据库
--extended-insert 使用多行插入的语法来加载数据,可以提升数据载入速度重要
--complete-insert 使每一个sql插入语句包含完整的字段列表
-R / --routines 导出存储过程和自定义函数
--triggers 导出触发器
--where=[value] 使用查询条件进行导出
--add-locks 每张表导出之前锁定表,导出结束之后解锁表
--opt 参数集合,--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

详细参数可以 man mysqldump

Mysqlbinlog

Mysqlbinlog 可以用来进行数据恢复,其中详细记录了所有的SQL操作。同时也可以用来详细分析数据库的SQL运行状况。

直接在Mysql中查询binlog日志

show binlog events;  

导出Mysqlbinlog为可读格式

mysqlbinlog binlog.000002 --database=cloak_server --set-charset=UTF8 --base64-output=decode-rows -v --start-position="53020" > ~/mysql/3306/genlog/gen.log  

常用参数

Parameter Description
--base64-output=[value] 格式化输出日志。AUTO or UNSPEC 默认值,当有需要的时候自动处理。NEVER 从不处理。DECODE-ROWS 解码row格式的事件。
-v 详细格式显示
--start-position=[value] 指定日志的起始位置(偏移量)
--stop-position=[value] 指定日志的停止位置(偏移量)
--start-datetime=[value] 指定日志的起始时间(偏移量)
--stop-datetime=[value] 指定日志的停止时间(偏移量)
--read-from-remote-server / -R 从远程读取数据
--stop-never 从远程读取数据时,保持连接

详细参数可以 man mysqlbinlog

日志解析

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES UTF8 */;
DELIMITER /*!*/;  
/*事件开始*/
# at 53020
/*这个地方会生成下一次事件记录的postition id,并记录在end_log_pos属性中,160728  1:01:41说明了事件的时间,并标记了数据库ID*/
#160728  1:01:41 server id 71493306  end_log_pos 55983 CRC32 0x6bfddfb3     Write_rows: table id 123 flags: STMT_END_F
/*执行SQL语句,我们可以看到这里是一个插入的过程*/
### INSERT INTO `cloak_server`.`trafficcampaign_16`
### SET
###   @1=119
###   @2='Whatsapp-IT'
###   @3='http://9lp.co/clock/it/index.php'
###   @4='http://www.empty.com'
###   @5=0
###   @6='["{\"name\":\"cb\",\"value\":\"{callback}\",\"desc\":\"callback\"}"]'
###   @7='["{\"url\":\"http://oloooo.com/it/whatsapp/index.html\",\"lpurl\":\"http://www.empty.com\",\"cpc\":\"0.01\",\"payout\":\"0.01\",\"rulename\":\"whastappit\",\"detectgoogle\":false,\"detectbaidu\":false,\"detectbing\":false,\"detectamazon\":false,\"detectfacebook\":false,\"detectblacklist\":false,\"LoadingHtml\":false,\"TransferData\":false,\"AdwordsFilter\":false,\"detectcookie\":false,\"existcookie\":false,\"detectcountry\":true,\"countryname\":[\"IT\"],\"detectdevicetype\":true,\"devicetype\":[\"mobile\"],\"detectdeviceos\":true,\"deviceos\":[\"IOS\",\"Android\"],\"detectparameter\":false,\"parameter\":[],\"detectdevicemodel\":false,\"devicemodel\":[],\"detectcarrier\":false,\"carrier\":[],\"detectorg\":false,\"orglist\":[],\"detectipaddr\":false,\"ipaddr\":[],\"detectuseragent\":false,\"useragent\":[],\"detectref\":false,\"reflist\":[],\"detectcity\":false,\"citylist\":[],\"weblang\":[],\"detectlang\":false}","{\"url\":\"http://www.whatsapp.com\",\"lpurl\":\"http://www.empty.com\",\"cpc\":\"0.01\",\"payout\":\"0.01\",\"rulename\":\"all\",\"detectgoogle\":false,\"detectbaidu\":false,\"detectbing\":false,\"detectamazon\":false,\"detectfacebook\":false,\"detectblacklist\":false,\"LoadingHtml\":false,\"TransferData\":false,\"AdwordsFilter\":false,\"detectcookie\":false,\"existcookie\":false,\"detectcountry\":false,\"countryname\":[],\"detectdevicetype\":false,\"devicetype\":[],\"detectdeviceos\":false,\"deviceos\":[],\"detectparameter\":false,\"parameter\":[],\"detectdevicemodel\":false,\"devicemodel\":[],\"detectcarrier\":false,\"carrier\":[],\"detectorg\":false,\"orglist\":[],\"detectipaddr\":false,\"ipaddr\":[],\"detectuseragent\":false,\"useragent\":[],\"detectref\":false,\"reflist\":[],\"detectcity\":false,\"citylist\":[],\"weblang\":[],\"detectlang\":false}"]'
/*事件结束*/
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;  
DELIMITER ;  
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Innodb事务隔离级别

Level Description
READ UNCOMMITTED 幻像读、不可重复读和脏读都允许。
READ COMMITTED 允许幻像读、不可重复读,但不允许脏读。
REPEATABLE READ 允许幻像读,但不允许不可重复读和脏读。InnoDB默认级别
SERIALIZABLE 幻像读、不可重复读和脏读都不允许。

幻象读,脏读,不可重复读

脏读

事务A插入了一条数据,但是没有提交。此时事务B读取了这一条数据,而之后A回滚了这一次操作。于是B就取出了不存在的数据,即脏读。

不可重复读

事务A读取了一条数据,事务B修改了这一条数据并提交。事务A再次去读这条数据,就会出现数据不一致的问题,即不可重复读。

幻象读

事务A使用条件检索读取了一个数据集合,事务B插入了新的数据,而新的数据符合事务A的查询条件。事务A使用相同条件再次查询的时候,就会出现额外的新的数据,即幻象读。

Mysql General Log

Mysql的General Log日志会详细记录每个用户执行的每个操作的语句,方便进行SQL分析等操作。比如PDO开发的时候,方便知道实际执行的Sql代码。

开启General Log

set global general_log_file="/Users/fangyuan/mysql/3306/genlog/gen.log";  
set global general_log=on;  

关闭Genral Log

set global general_log=off;  

Mysql导入SQL语句

可以使用mysql连接来直接向数据库中导入mysqldump导出的数据

mysql -S ~/mysql/3306/my3306.sock < ~/mysql/3306/tmp/bak.sql  

Mysqladmin

关闭mysql

mysqladmin -S ~/mysql/3306/my3306.sock -u root -p shutdown  

显示mysql的进程列表

mysqladmin -u root -p processlist -S ~/mysql/3306/my3306.sock  

监视Mysql的运行状态

mysqladmin -S ~/mysql/3306/my3306.sock -r -i 1 extended-status -u root -p  

Mysql核心命令

在使用mysql进行连接数据库的时候,有以下核心命令会被经常使用或比较重要:

Command Description
help
show privileges; 显示当前用户的权限
show processlist; 显示正在运行的进程,只会执行sql的前100个字符Referrence
show full processlist; 显示正在运行的进程,并显示完整的SQL语句。
show master status \G; 显示主库状态
show slave status \G; 显示从库状态
show index from cloak_server.traffic_campaign16 \G; 显示user表的索引信息
show grants for root@'localhost'; 显示授权语句
show create table cloak_server.traffic_campaign16; 显示表的创建语句
flush logs; 刷新binlog日志,更换为新的日志
flush hosts; 重置error计数器
flush tables; 关闭所有打开的表,清空查询缓存
flush status; 重置mysql状态
flush master; 重置binlog索引文件为空
flush query cache; 重置query cache
reset master; 删除所有二进制及索引文件
reset slave; 删除从库的主从关系配置
reset slave all; 清理包括内存中的数据在内的所有的连接参数信息