工作中Mysql常用命令记录

---

查询并写入结果到文件

select * from user into outfile '/tmp/data.txt' fields terminated by '|' lines terminated by '\n';
select * from sys_user where id='9' into outfile 'd:/data.txt' CHARACTER SET utf8 fields terminated by '|' lines terminated by '\n';
select * from volte_subs_sbc_day_16000000 into outfile '/tmp/volte_subs_sbc_day_16000000.txt' CHARACTER SET utf8 fields terminated by '|' lines terminated by '\n';

导入文件到数据库[load data]

load data infile 'd:/temp/volte_subs_sbc_day_16000000.txt' into table volte_subs_sbc_day_16000000 fields terminated by '|' lines terminated by '\n';

导出数据[mysqldump]

##导出数据表结构 -d表示导出表结构
mysqldump -d sendinmdb --default-character=utf8 sendinmdbwifigd acinfo>acinfo.sql;

##导出数据表数据, -t表示只导出数据
mysqldump -t sendinmdb acinfo>acinfo.sql;

##导出数据表结构及数据,不加-t -d表示导出结构及数据
mysqldump --default-character=utf8 sendinmdb host_template>host_template.sql;

##导出数据+条件
mysqldump -t -w"id in(180,181,182,183,184,185,186)" --default-character=utf8 sendinmdb module> module.sql;
mysqldump -t -w"id=186" --default-character=utf8 sendinmdb module> module.sql;
mysqldump -t -w"description='GZ-ZC-ZCSZXZPCS-0001'" --default-character=utf8 sendinmdb host> module.sql;

##导出的同时过滤部分表不导出
###导出语句中不包含完整的字段名的写法
mysqldump -uintone -p123456 -h192.168.0.160 -P3600 --ignore-table=ydvolteweb.tdr_data_new --ignore-table=ydvolteweb.tde_data_file --single-transaction ydvolteweb > ydvoltewebdata.sql
###导出语句中包含完整的字段名的写法
mysqldump -uintone -p123456 -h192.168.0.160 -P3600 --force --complete-insert --ignore-table=ydvolteweb.tdr_data_new --ignore-table=ydvolteweb.tde_data_file --single-transaction ydvolteweb > ydvoltewebdata.sql

##不能同时使用以上single-transaction及lock-all-tables2个参数
mysqldump -uintone -p123456 -h192.168.0.160 -P3600 --lock-all-tables dms > dms.sql
mysqldump -uintone -p123456 -h192.168.0.160 -P3600 --single-transaction dms > dms.sql

将mysqldump的文件导入数据库

mysql -uroot -p123456 -h127.0.0.1 -P3306 --default-character-set=utf8  dbName < /home/sendi/liu/db.sql &

mysql参数之innodb_buffer_pool_size大小设置

##查看
show global variables like 'innodb_buffer_pool_size';

##设置，单位：KB，以下设置为10GB
SET global innodb_buffer_pool_size = 10737418240; 

mysql validata_paasword插件

####安装mysql validata_paasword插件
use mysql;
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
show variables like 'validate_password%';
 
####卸载 validata_paasword插件
UNINSTALL PLUGIN validate_password;

mysql开启慢查询

set global slow_query_log='ON'; 
show variables like 'slow_query%';
show variables like 'long_query_time';
set global long_query_time=5;

命令行修改最大连接

set global max_connections=2000;

修复表

Table crashed and should be repaired

REPAIR TABLE mytable;

##表名如果有特殊字符，带上反斜杆,比如
repair table `dicdata-0607`;

显示慢Sql的查询语句

##慢Sql的显示方法
select ID,time,INFO from information_schema.processlist where COMMAND ='Query' order by time desc limit 10 \G;
##mysql终端显示方法,找出time比较长的，看Info内容.
show full processlist;

远程授权命令

grant all privileges on *.* to 'webuser'@'%' identified by '123456' with grant option;
flush privileges;

bin-log日志维护

##配置my.cnf
log-bin = mysql-bin
max_binlog_size = 1024M
expire_logs_days = 1
log_slave_updates = 1

##查看获取binlog文件列表
show binary logs;

##reset master：删除所有的二进制日志文件
mysql> reset master;

##purge master logs：删除指定日志文件
purge master logs to 'mysql-bin.000004'

##刷新日志
flush logs

关闭数据库

mysqladmin -u root -p shutdown
然后输入密码

查看库中有自增字段的表名和字段名

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_SCHEMA = 'dbName'  
AND COLUMN_KEY = 'PRI' AND EXTRA = 'auto_increment';