MySQL常用命令

MySQL常用命令

启动MySQL服务：service mysqld start

停止MySQL服务：service mysqld stop

重启MySQL服务：service mysqld restart

进入MySQL数据库：mysql -uroot -p

选择一个库：use mydatabase;

退出MySQL数据库：Exit

修改MySQL用户密码：ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘new password’;

开通MySQL远程用户 以下2行 ：
grant all privileges on . to ‘root’@’%’ identified by ‘password’ with grant option

flush privileges;

查看MySQL状态：status

查看root 默认密码: grep “password” /var/log/mysqld.log

查看mysql所有表记录及数据大小（在information_schema下执行）

SELECT TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, TABLE_ROWS*AVG_ROW_LENGTH, 
DATA_LENGTH, ROUND(DATA_LENGTH/1024/1024,0) MB FROM TABLES 
WHERE TABLE_SCHEMA = 'osp' ORDER BY DATA_LENGTH DESC

mysql无法重启：可能进程里已经存在mysql进程
解决方法：用命令“ps -ef|grep mysqld ”查看是否有mysqld进程，如果有使用“kill -9 进程号”杀死，然后重新启动mysqld！

查看进程

查看进程

show PROCESSLIST

#1：查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

#2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

#3：查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

杀掉进程

KILL 420821;

查看进程信息

show status like 'Threads%';

查看最大连接数

show variables like "max_connections";

设置最大连接数

 set GLOBAL max_connections=30000

最大连接数还可以在my.ini 中设置

#最大连接数
max_connections=30000
#最大允许错误连接数
max_connect_errors=30000

查看只读信息

show global variables like "%read_only%";

设置成只读

 set global read_only=0;

注：这个只是设置普通用户只读，非超级用户（root），设置只读不影响 slave

设置超级用户成只读（一般不建议使用）

 set global super_read_only=0;

查看同步信息

 show slave STATUS

索引空间查看

查看索引空间
select table_schema,table_name,
(data_length+index_length) length,index_length,
data_free,
(data_length+index_length)/1024/1024 length1,
data_free /1024/1024 data_free1,
table_rows,
DATA_FREE/(data_length+index_length) freeRate
from information_schema.tables
where table_schema in (‘pos_gls’)
and data_free !=0
AND data_free > 20 * 1024 * 1024 – 大于20M
– AND DATA_FREE/(data_length+index_length) > 0.5 – 空闲率 > 0.5
order by data_free DESC ;

重置索引（指定表名）

alter table membermst engine=InnoDB

引擎设置

ALTER TABLE `test_date_2` ENGINE = MyISAM;
ALTER TABLE `test_date_2` ENGINE = InnoDB;

MyIASM锁的粒度是表级的，而InnoDB支持行级锁
MyIASM支持全文类型索引，而InnoDB不支持全文索引
MyIASM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyIASM
MyIASM表保存成文件形式，跨平台使用更加方便

应用场景：
1、MyIASM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyIASM
2、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

时间处理
set @dt = now();
select date_add(@dt, interval 1 day); - 加1天
select date_add(@dt, interval 1 hour); -加1小时
select date_add(@dt, interval 1 minute); - 加1分钟
select date_add(@dt, interval 1 second); -加1秒
select date_add(@dt, interval 1 microsecond);-加1毫秒
select date_add(@dt, interval 1 week);-加1周
select date_add(@dt, interval 1 month);-加1月
select date_add(@dt, interval 1 quarter);-加1季
select date_add(@dt, interval 1 year);-加1年

MySql异常处理

1、错误连接数过多

MySQL--is blocked because of many connection error

解决方法

修改服务器上面max_connect_errors参数的值，默认10

set global max_connect_errors=1000;

执行FLUSH HOSTS

FLUSH HOSTS 

重启mysql，重新连接数据库

2、模式错误

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在 /etc/my.cnf 文件里加上如下：

sql_mode='NO_ENGINE_SUBSTITUTION

查看模式：

SELECT @@sql_mode;

设置模式

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';