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; | 清理包括内存中的数据在内的所有的连接参数信息 |