mysql学习笔记

一、mysql常用指令

1.1，mysqldump导出玩家数据

mysqldump -h127.0.0.1 -uroot -p66cMrHwUiMfB6kop vn129_db t_role --single-transaction --complete-insert --skip-add-drop-table --no-create-info --where="_id = 3449568978635732224" > _role_yxy.sql

参数说明

1、mysqldump 命令
2、-h 地址
3、-u 账号
4、-p 密码
5、vn129_db 库名
6、t_role 表名
7、--single-transaction参数的作用：设置事务的隔离级别为可重复读，即REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在dump期间，如果其他innodb引擎的线程修改了表的数据并提交，对该dump线程的数据并无影响，在这期间不会锁表。
8、--complete-insert ：使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
9、--skip-add-drop-table：  只create语句，没有delete
10、--no-create-info：只导出数据，而不添加CREATE TABLE 语句
11、--where：where条件
12、> _role_yxy.sql ： 导出到_role_yxy.sql文件

1.2，mysql清库操作

1、生成清空表的sql语句：
select CONCAT('TRUNCATE TABLE ',table_name,';') from information_schema.tables where TABLE_SCHEMA = ['库名'];
2、复制下生成的sql语句，粘贴到mysql命令行，回车执行。

1.3，复制表结构

create table [目标表名] like [源表名];         //复制表结构  
例如: create table test like user;

1.4，复制表数据，以及表结构

create table c select * from users;      //复制表的sql  

1.5，查询当前mysql任务，并杀掉某个任务：

show full processlist;
kill [id]

1.6，查看当前数据库引擎：

show variables like '%storage_engine%';

1.7，查看某个表使用的引擎：

show table status from 【库名】 where name='【表名】';
同一个库下，不同的表可以是不同的引擎，在create table中可以指定要使用的数据库引擎：MyIsAM或Innodb。

1.8，调查mysql是否死锁了：

show engine innodb STATUS;    查看innodb的状态，需要root权限;

1.9、查看历史执行的sql语句：

需要先配置：开启sql语句日志记录，注意该功能开启后会写磁盘，占用磁盘空间。如果功能是开启的话，那么执行sql语句：
SELECT * from mysql.general_log ORDER BY event_time DESC;
可以看到历史执行的sql语句。

1.10、sql语句优化，explain详解

跳转链接：https://www.cnblogs.com/tufujie/p/9413852.html

1.11、mysql.user表中Host为%的含义

原文介绍：https://blog.csdn.net/kikajack/article/details/79977108
Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1。这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。而%是个通配符，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果Host=%，表示所有IP都有连接权限。这也就是为什么在开启远程连接的时候，大部分人都直接把Host改成%的缘故，为了省事。
## 1.12、sysbench - 数据库功能及性能测试工具

1.13、修改密码

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

1.14、允许远程登录

1.登陆mysql数据库，修改表。
use mysql;
update user set host='%' where user='root';     # %表示允许任意地址
select host,user from user;                     # 查看一下是否成功
flush privileges;                               # 刷新一下

2.授权用户，允许从任何主机连接到mysql数据库。
grant all privileges on *.* to 'root'@'%' identified by 'pwd' with grant option;
flush privileges;

#如果只是允许用户从ip为192.168.1.8的主机连接到mysql服务器。
grant all privileges on *.* to 'root'@'192.168.1.8' identified by 'Fmty_888_!@#' with grant option;
flush privileges;

1.15、查询blob字段大小

select CONCAT(TRUNCATE(OCTET_LENGTH('blob列名')/1024,2)," KB") AS '任意命名' from '表名';
select _id,CONCAT(TRUNCATE(OCTET_LENGTH(monster)/1024,2)," KB") AS monster from t_map;

二、C++调用mysql常用接口

1、mysql_init()
2、mysql_real_connect()
3、mysql_query()
4、mysql_store_result()
5、mysql_fetch_row()
6、mysql_fetch_field()
7、mysql_num_rows()
8、mysql_num_fields()
9、mysql_free_result()
10、mysql_close()

三、常见错误

3.1、库的问题

今天想用C++写一套mysql的读写操作。
下载的是mysql_64版本的，然后在WriteSome工程中配置的是debug和win32。
在工程设置C/C++中，附加包含目录包含了mysql的include目录。
在工程设置连接器中，附加库目录中包含了mysql的lib目录。
编译通过，但是连接的时候，死活link报错。鼓捣了半天都没用，差点怀疑世界观了。
后来下过了一个mysql_32版本的，将include和lib目录指向win_32版本的目录后，问题解决。

四、参数

4.1、binlog太耗磁盘空间

1、测试环境下，项目组人员的db服务器都配置在同一台linux机器下，前期大概配置了20个人
2、世界地图存盘的设计，是将整个世界地图切割为100个存盘格子，并且每2分钟存盘1次
3、所以导致每2分钟至少会有100*20条update语句，而binlog，不管update语句是否修改数据，都会记录，所以导致内网linux机器的磁盘，很快就被消耗了。
4、redis+mysql+py都配置在同一台机器上，磁盘空间满了，redis就无法写入数据导致py脚本异常中断退出，game无法收到db的数据，导致client无法登陆

4.2、关闭binlog

1、linux环境下，一般mysql的配置文件都存放在/etc/my.cnf中
2、在[mysqld]节点下，增加 skip-log-bin
3、连接mysql后，show variables like ‘%log%’，可以看到变量log_bin处于OFF状态。

4.3、联合索引

CREATE TABLE IF NOT EXISTS t_league(
`_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`content` VARCHAR(512),
`league_id` BIGINT UNSIGNED,
`notdb_game_t11_1db_game_t11_1ice_id` BIGINT UNSIGNED, PRIMARY KEY (_id), KEY `league_notice`(`league_id`,`notice_id`)
) ENGINE = INNODB DEFAULT CHARSET=UTF8 COLLATE=UTF8_BIN;