mysql常用sql

mysql常用sql

mysqlsql

日常优化分析sql
--得出运行总数前100个的sql统计

/opt/mysql/bin/mysqldumpslow -s c -t 100 /ssd0/logs/mysql/slow.log > 20190326_count.log

--得出执行时间前100个的sql统计

/opt/mysql/bin/mysqldumpslow -s at -t 100 /ssd0/logs/mysql/slow.log > 20190326_quest_time.log

--查看该数据库实例下所有库大小，得到的结果是以MB为单位

select table_schema,sum(data_length)/1024/1024 as data_length,sum(index_length)/1024/1024 as index_length,sum(data_length+index_length)/1024/1024 as sum from information_schema.tables;

--查看该实例下各个库大小

select table_schema, sum(data_length+index_length)/1024/1024 as total_mb,
sum(data_length)/1024/1024 as data_mb, sum(index_length)/1024/1024 as index_mb,count(*) as tables, curdate() as today from information_schema.tables group by table_schema order by 2

--导入导出（加入-R 导出存储过程及函数）
·本地导入导出

mysqldump -hlocalhost -udangjian -P3306 -p'***' -R party_build --log-error=/home/dump/party180815.log > /home/dump/party180815.sql

·压缩

tar -czf party180815.tar.gz party180815.sql

·移动
本地copy到远程目录

scp -P 1222 party180815.tar.gz root@***.net:/home/

·登录mysql，创建数据库party_build

CREATE DATABASE party DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

·导入数据库中
1:导入方法：

mysql -u root -p
mysql>use 数据库
然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
mysql>source d:/dbname.sql

2：命令行导入

mysql -hlocalhost -uroot -p'***' party_build < /home/dump/party180815.sql

修改字符集
centos7修改字符集配置文件

vim /etc/locale.conf
password for lisk:
LANG="zh_CN.UTF-8"
source /etc/locale.conf

#修改环境变量

sudo vim /etc/profile
LANG="zh_CN.UTF-8"
source /etc/profile

登录数据库查看mysql字符集,

show variables like 'character_set_%'
alter database 数据库名 character set utf8mb4;