MySQL日常笔记

2015-12-07

两张结构相同的表联合查询,返回至同一个结构中

SELECT * FROM hack_site1 WHERE name = 'query'
UNION ALL
SELECT * FROM hack_site2 WHERE name = 'query'

优化

查询慢查日志是否开启,以及日志路径:

mysql> show variables like 'slow%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_launch_time    | 2                                |
| slow_query_log      | OFF                              |
| slow_query_log_file | /app/mysql/c836212589df-slow.log |
+---------------------+----------------------------------+
3 rows in set

查询慢查日志超时时间:

show variables like 'long_query_time';

开启慢查日志:

set global slow_query_log=on; 

开启没使用索引的查询日志:

set global log_queries_not_using_indexes=on;

导入导出

把表t1里的100w-1000w之间的数据导出:

导出

select * from t1 limit 1000000,9000000 into outfile "d:/t1_1000000-9000000.txt";

在服务器上导入该文件进表t1:

导入

load data infile "/app/sql_tmp/t1_1000000-9000000.txt" into table t1;

远程导入本地的数据 (需要先开启远程导入):

set global local_infile=on;  
load data local infile  "d:/t1_1000000-9000000.txt" into table t1;

查看

MySQL中看看表是如何创建的:

show create table user;
show create table user\G;

查看默认储存引擎:

show engines;

show variables like '%storage_engine%';

查看一个数据库的所有表:

select table_name from information_schema.tables where table_schema='dbname' and table_type='base table';

查看一张表的所有字段信息:

desc tablename

查看数据库状态(包含注释):

SHOW TABLE STATUS FROM 'dbname';

其他操作

存在则更新,不存在则插入 (表必须有主键或者是唯一索引)

replace into t_test set ikey=1,value='a';

mysql生成当前时间戳

unix_timestamp(now())

shell中执行mysql命令

mysql -uroot -e "show slave status \G"   #加-e参数

Windows Mysql修改默认储存引擎,my.ini添加以下行:

[mysqld]
default-storage-engine=MYISAM
default-tmp-storage-engine=MYISAM

提高mysql备份效率,建议用mysql自带的mysqldump导出,它会把条目批量写入一条语句,而navicat导出是一条一条的语句(巨型数据量时,生成的文件会非常大)。

windows mysql导出数据库,进目录 wamp\bin\mysql\mysql5.6.17\bin\ 执行:

mysqldump.exe -u root -p user --default-character-set=utf8 > user.sql

其中user为数据库名

接下来可以用替换的方法把,把里面ENGINE=InnoDB改为ENGINE=MyISAM

导入:

mysql -u root -p aaa --default-character-set=utf8 < user.sql

innodb转myisam慢:

在超大数据量的时候(亿级数据量)innodb如果有索引,那么在转换的时候会很慢,建议先把索引删除再去转换。

use database_name
alter table xxx engine=myisam;

索引

myisam建立索引慢

数据量大的时候,建立索引会非常慢,可以通过以下办法去快速建立索引

1、进入mysql界面。use dbname;

2、导出相应表的数据。 select * from tab into outfile 'tab.txt'; 此处tab.txt文件在mysql的data目录里

3、删除相应表的数据,并置第一条记录为0。 truncate table tab;

4、创建索引。create index IDX_NAME using BTREE on tab (col);

   或:alter table tab add index name1 (name);  括号里name是表名,name1是索引名可以不写

   索引的方式有:BTREE、RTREE、HASH、FULLTEXT、SPATIAL

5、导入文件到相应表。load data infile 'tab.txt' into table tab;

优化索引大小

建立前缀索引

语法为:

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

比如添加键值:

alter table test add key (city(7));

另一种前缀索引

ALTER TABLE `hack_site11` ADD INDEX `name` (`name`(7)) USING BTREE ,

命令行

登录:

mysql -uroot -h db.server.io -P 3306 -D temp_db -p
输入root用户的mysql密码

1、显示数据库列表:
show databases;
2、显示库中的数据表: 
use mysql;
show tables;
3、显示数据表的结构: 
describe 表名; 
4、建库: 
create database 库名; 
5、建表: 
use 库名; 
create table 表名 (字段设定列表); 
6、删库和删表: 
drop database 库名; 
drop table 表名; 
7、将表中记录清空: 
delete from 表名; 
8、显示表中的记录: 
select * from 表名

锁库

全局锁库(只读):

FLUSH TABLES WITH READ LOCK;

表级别的锁定:

lock tables test read;

解锁:

UNLOCK tables;

注:在退出mysql终端的时候都会隐式的执行unlock tables。也就是如果要让表锁定生效就必须一直保持对话。

用户操作

给指定数据库添加用户

GRANT ALL PRIVILEGES ON dbname.* to malu@'%' identified by 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

dbname:表示数据库名

删除用户

DROP USER username@hostname;

修改用户密码

方法一:

(适用于管理员或者有全局权限的用户重设其它用户的密码)
进入命令行模式
mysql -u root -p
 
mysql>use mysql;
mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='username';
mysql> FLUSH PRIVILEGES;
mysql> quit;

方法二:

mysql -u root -p 
mysql>use mysql; 
mysql> SET PASSWORD FOR   username=PASSWORD('new password');
mysql> QUIT

方法三:

mysqladmin -u root "old password" "new password"

注:new password请输入你想要设置的密码。

方法四:

重置mysql里root用户密码, 解决报错:Access denied for user ‘root’@’localhost’ (using password: YES)

# /etc/init.d/mysqld stop 
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking & 
# mysql -u root mysql 
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root'; 
mysql> FLUSH PRIVILEGES; 
mysql> quit 
# /etc/init.d/mysqld restart 

错误解决

解决报错: table is marked as crashed and should be repaired

意思是表被标记为损坏,应进行修复

找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:

myisamchk -c -r ../data/tablename/xxx.MYI

然后重启mysql (记得要重启mysql!)

解决报错: [ERROR] Can’t open the mysql.plugin table. Please run mysql_upgrade to create it.

可以先初始化datadir目录,处理办法,shell下执行:

$ mysql_install_db --user=mysql --datadir=/app/mysql/