MySQL基本操作

MySQL 常用操作

---

1. 连接数据库

mysql -h 192.168.0.3 -uroot -p123456

2. 创建数据库/表

create database test_db default charset utf8mb4;

# 查看建库语句
show create database test_db;

use test_db;

create table article (
    id int auto_increment primary key,
    title varchar(64) default '',
    content text,
    create_at datetime default now(),
    update_at datetime default now()
) ENGINE InnoDB CHARACTER SET utf8mb4;

# 查看建表语句
show create table article;

# 查看表结构
desc article;

3. 修改表

# 增加字段
alter table article add column author varchar(64) default 'Alen' after content;

# 修改字段
alter table article change column author author varchar(128);

# 删除字段
alter table article drop column author;

# 增加索引
alter table article add index (title);

# 删除索引
alter table article drop index title;

4. 数据操作

# 插入数据
insert ignore into article (title, content, author) values ('Python', 'xxxx,oooo,pppp', 'john');
insert ignore into article values (5,'Go','aaaaa,cccc,dddd','Bob','2021-01-16 02:06:29','2021-01-16 02:06:29');

# 查询数据
select * from article;
select id,title,author from article;

# 修改数据
update article set author = 'Green' where id = 5;

# 删除数据
delete from article where id = 5;

# 清空表
delete from article; # 只删除数据, 不删除索引等, 不释放表空间
truncate article; # 删除表数据及索引, 释放表空间. 等于 drop table && create table

5. 备份恢复

# 备份所有的数据库
mysqldump -h 192.168.0.11 -uroot -p'123456' --set-gtid-purged=OFF --single-transaction --all-databases > all.sql

# 备份指定的数据库, 多个库用空格分开
mysqldump -h 192.168.0.11 -uroot -p'123456' --single-transaction test_db test_db2 > test_db.sql
mysqldump -h 192.168.0.11 -uroot -p'123456' --single-transaction --databases test_db --databases test_db2 -r backup.sql

# 备份指定的表
mysqldump -h 192.168.0.11 -uroot -p'123456' --single-transaction test_db test_table > test_table.sql

# 按照指定的条件备份
mysqldump -h 192.168.0.11 -uroot -p'p123456' --single-transaction test_db test_table -t --where="id>100" > id_gt_100.sql

# 备份指定的数据库忽略某些表
mysqldump -h 192.168.0.11 -uroot -p'p123456' --single-transaction test_db --ignore-table=test_db.t1 --ignore-table=test_db.t2 > test_db.sql

# 备份函数存储过程
mysqldump -h 192.168.0.11 -uroot -p'p123456' --no-create-db --no-create-info --no-data -R --triggers=false test_db > backup-routine.sql

# 恢复数据
mysql -h 192.168.0.11 -uroot -p'p123456' test_db < test_db.sql

# 也可以连接到mysql, 使用 source 命令进行恢复
mysql -h 192.168.0.11 -uroot -p'p123456'
use test_db
source test_db.sql;

选项说明

参数名	缩写	含义
--host	-h	服务器IP地址
--port	-P	服务器端口号
--user	-u	MySQL 用户名
--pasword	-p	MySQL 密码
--databases, -B		指定要备份的数据库
--all-databases		备份mysql服务器上的所有数据库
--compact		压缩模式，产生更少的输出
--comments		添加注释信息
--complete-insert		输出完成的插入语句
--lock-tables		备份前，锁定所有数据库表
--no-create-db, -n		禁止生成建库语句
--no-create-info, -t		禁止生成建表语句，即只导出数据
--no-data, -d		只导出表结构, 不导出任何数据
--routines, -R		导出存储过程及自定义函数
--force		当出现错误时仍然继续备份操作
--default-character-set		指定默认字符集
--add-drop-database		每个数据库创建之前添加drop数据库语句
--add-drop-table		每个数据表创建之前添加drop数据表语句(默认为打开状态，使用--skip-add-drop-table取消选项)
--add-locks		在每个表导出之前增加 LOCK TABLES 并且之后 UNLOCK TABLE (默认为打开状态, 使用--skip-add-locks取消选项)
--lock-all-tables, -x		提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁, 并且自动关闭--single-transaction 和 --lock-tables 选项

6. 其他常用操作

# 查看函数、存储过程
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='test_db' and ROUTINE_TYPE='FUNCTION'\G;
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='test_db' and ROUTINE_TYPE='PROCEDURE'\G;

# 查看表大小
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='mysql' \
    order by data_length desc, index_length desc;

# 统计单个数据库大小
SELECT ROUND(SUM(data_length + index_length)/1024/1024/1024, 2) AS database_size_GB \
  FROM information_schema.TABLES WHERE table_schema = 'DB_NAME';

# 统计整个实例大小
SELECT ROUND(SUM(data_length)/1024/1024/1024, 2) AS total_data_GB, \
  ROUND(SUM(index_length)/1024/1024/1024, 2) AS total_index_GB FROM information_schema.TABLES;

# 查看进程
show processlist;
show full processlist;

# 查看变量
show variables like '%max_conn%';
show variables like '%char%';
show variables like '%commit%';

# 查看事务
select * from information_schema.INNODB_TRX;

# 查看正在锁的事务
select * from infomation_schema.INNODB_LOCKS\G; -- mysql 5.7

# 查看等待锁的事务
show status like '%lock%';
select * from information_schema.INNODB_LOCK_WAITS\G; -- mysql 5.7
select * from sys.innodb_lock_waits\G;  -- mysql 8.0