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 default '',
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 -p123456 --single-transaction --all-databases > all.sql
# 备份指定的数据库, 多个库用空格分开
mysqldump -h 192.168.0.11 -uroot -p123456 --single-transaction test_db test_db2 > test_db.sql
# 备份指定的数据库忽略某些表
mysqldump -h 192.168.0.11 -uroot -p123456 --single-transaction test_db --ignore-table=test_db.t1 --ignore-table=test_db.t2 > test_db.sql
# 恢复数据
mysql -h 192.168.0.11 -uroot -p123456 test_db < test_db.sql
# 也可以连接到mysql, 使用 source 命令进行恢复
mysql -h 192.168.0.11 -uroot -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. 其他常用操作
# 查看进程
show processlist;
show full processlist;
# 查看变量
show variables like '%max_conn%';
show variables like '%char%';
show variables like '%commit%';
# 查看事务
select * from information_schema.INNODB_TRX;
# 查看锁
show status like '%lock%';