Mysql使用小技巧

1.忘记密码

1、停掉mysql服务
2、在mysql.ini下，添加“skip-grant-tables”配置（mysqld这样可以去掉登录时的密码验证）
3、重启mysql
4、在cmd下输入mysql -uroot -P3306 ，无密码登录
5、use mysql;
6、set password=password（‘123’） --error
update user set password=password(‘admin’) where user=‘root’ and host=‘localhost’;
7、重启，或直接flush privileges;

2.备份数据

①手动备份数据库(表的)方法
cmd控制台:
mysqldump –u root –proot 数据库 [表名1 表名2…] > 文件路径
比如: 把temp数据库备份到 d:\temp.bak
mysqldump –u root –proot temp > d:\temp.bak
如果你希望备份是，数据库的某几张表
mysqldump –u root –prot temp dept > d:\temp.dept.bak

如何使用备份文件恢复我们的数据.
mysql控制台
source d:\temp.dept.bak
恢复：
进入数据库 选择数据库 source 备份sql的路径
②使用定时器来自定完成

JAVA代码实现，quaz

3.rand函数(随机获取行)

//随机获取一行
order by rand() limit 1，5

4.rollup子句

查询某个时间，某部分的付款金额总和
select DATE_FORMAT(payment_date,’%Y-%m’),staff_id ,sum(amount) from payment GROUP BY DATE_FORMAT(payment_date,’%Y-%m’),staff_id;

聚合的再次(归纳)
select DATE_FORMAT(payment_date,’%Y-%m’),ifnull(staff_id,’’) ,sum(amount) from payment GROUP BY DATE_FORMAT(payment_date,’%Y-%m’),staff_id WITH ROLLUP;

5.show profile(详细执行过程)

select @@have_profiling 查看是否支持

select @@profiling 查看是否开启
set profiling=1 通过set语句，在session级别开启

测试innodb表
select count(*) from payment;
show profiles; 查看最近执行的语句

show profile for query QUERY_ID 查看执行过程线程中每个状态和消耗时间，找出最耗时的操作

测试myisam
create table payment_myisam like payment;
alter table payment_myisam engine=myisam;
insert into payment_myisam select * from payment;
再次分析查询，发现myisam在executing后就结束了

6.定期分析表&检查表

analyze table payment
check table payment_myisam
检查一个或多个表是否有错误
CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。
create view a_view as select * from a;
check table a_view;
drop table a;

7.碎片整理

如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理(垃圾最多，效果最好)
create table test100(id int unsigned ,name varchar(32))engine=myisam;

insert into test100 values(1,‘aaaaa’);
insert into test100 values(2,‘bbbb’);
insert into test100 values(3,‘ccccc’);

insert into test100 select id,name from test100;

Delete from test1 id=3;
我们应该定义对myisam进行整理
optimize table test100;
问题1：我怎么知道是那些表呢？
注意
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更改的表进行整理即可。
3.在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
4.默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。