Shell环境下MySQL数据库常用操作指南

mysql -u root -p -e "load data local infile '/path/to/file.csv' into table stu.students fields terminated by ',' lines terminated by '\n' ignore 1 rows;"

show variables like 'local_infile'; -- 查看
set global local_infile = 'on';     -- 临时开启（需权限）

mysqldump -u root -p123456 stu students > /root/data/mydb.sql

mysqldump -u root -p --no-create-info stu students > data_only.sql

mysqldump -u root -p --no-data stu students > schema_only.sql

alias db_backup='mysqldump -u root -p --single-transaction --quick --lock-tables=false stu students | gzip > /root/data/mydb_$(date +%y%m%d).sql.gz'

-- 查看建表语句（修正拼写错误：show create table）
show create table students\g

-- 查看当前数据库
select database();
-- 或
status;  -- 显示更多信息，包括当前库

-- 前10条
select * from students limit 10;

-- 分页：跳过前20条，取10条（第3页）
select * from students limit 20, 10;
-- 或
select * from students limit 10 offset 20;

-- 推荐指定位置（如 first / after）
alter table students add column enroll_date date default '2025-12-12' after name;

-- modify：只改类型和注释
alter table students modify datet datetime comment '入学时间';

-- change：改名 + 改类型
alter table students change stu_id student_id varchar(50) not null;

alter table students drop column length;

-- 修改表字符集（不影响已有数据编码！）
alter table students convert to character set utf8mb4 collate utf8mb4_unicode_ci;

-- 更彻底（同时转数据）：
alter table students convert to character set utf8mb4;

rename table students to student_info;
-- 或
alter table students rename to student_info;

-- 删除表（结构+数据全丢）
drop table students;

-- 安全写法：先检查是否存在
drop table if exists students;

#!/bin/bash
date=$(date +%y%m%d_%h%m)
backup_dir="/backup/mysql"
db="stu"
table="students"
user="root"

mkdir -p $backup_dir

mysqldump --single-transaction -u$user $db $table | gzip > $backup_dir/${db}_${table}_$date.sql.gz

echo "$(date): backup completed: ${db}_${table}_$date.sql.gz" >> /var/log/db_backup.log

# 每天凌晨2点备份
0 2 * * * /root/scripts/db_backup.sh