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
查看进程
查看进程
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';