mysql 备份、添加修改表名字段、锁表

-- 备份数据
create table `admin_20200312` like `admin`;
insert into `admin_20200312` select * from `admin`;

-- 添加字段
ALTER TABLE `admin`
ADD COLUMN `status` tinyint(4) DEFAULT 1 COMMENT '状态 1:启用 2:禁用' AFTER `id`;

-- 修改字段
ALTER TABLE `admin`
MODIFY COLUMN `status` tinyint(4) DEFAULT 1 COMMENT '状态 1:启用 2:禁用';

-- 修改字段
ALTER TABLE `admin` change `delete_time` `delete_time1` datetime DEFAULT NULL;

-- 删除字段
ALTER TABLE `admin` DROP COLUMN `id`;

-- 添加索引
ALTER TABLE `admin`
ADD INDEX `idx_urid` (`urid`) USING BTREE;

-- 删除索引
DROP index `idx_urid` ON  `admin`;

-- 清空表
truncate `admin`;

-- 查看表的详细结构
show create table `admin` \G

-- 修改表名
ALTER TABLE 旧表名 RENAME TO 新表名 ;

-- 修改表的注释
ALTER TABLE `admin` `tel` '新注释--手机号';

-- 查看表大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='xingluo'
order by data_length desc, index_length desc;





1、查询是否锁表
show OPEN TABLES where In_use > 0;

2、查询进程
show processlist;

查询到相对应的进程===然后 kill id

select * from  information_schema.processlist where COMMAND != 'Sleep'\G


批量kill mysql进程
mysql> SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root';
+------------------------+
| concat('KILL ',id,';') 
+------------------------+
| KILL 3101;             
| KILL 2946;             
+------------------------+
2 rows IN SET (0.00 sec)
 
mysql> SELECT concat('KILL ',id,';') FROM information_schema.processlist WHERE user='root' INTO OUTFILE '/root/kill.txt';
Query OK, 2 rows affected (0.00 sec)
 
mysql> source /root/kill.txt;
Query OK, 0 rows affected (0.00 sec)