MySQL运维基础

MySQL命令与操作

运维对MySQL的命令要求不高，掌握基础的DDL和DML即可，命令执行的核心思路就是先DDL再DML

DDL、DML

我们知道，MySQL的逻辑结构是

数据库 (DATABASE)
|__ 表 (TABLE)
|__ 字段 (COLUMN)
|__ 数据 (DATA)

通俗来讲，DDL就是对数据库、表、字段进行增删改查，而DML就是对具体的数据进行增删改查

# ——DDL——

show databases; # 查看所有数据库

use mysql; # 选中mysql数据库，后续的操作针对mysql数据库

show tables; #

create database TEST; # 创建数据库

create table student{ # 创建表
id int,
name varchar(5)
}

alter table student add gender VARCHAR(10); # 增加表字段

alter table student change name studet_name varchar(5); # 修改字段名称

alter table student drop column gender; # 删除表字段

drop table student; # 删除表

drop database TEST; # 删除数据库

# ——DML——

insert into student values(1,'Tom'); # 插入数据

update student set name='张三' where id=1; # 更新字段数据

delete from student where id=1; # 删除行数据

select * from student; # 查询数据

总的来说 ，mysql命令的执行顺序是 DDL -> DML，先选中数据库，选中表，再进行具体数据的增删改查

权限管理

创建用户

# 创建用户
create user '用户名'@'IP地址 ' identified by '密码';

# 创建本地用户
create user 'dev'@'localhost' identified by '123456';

# 创建远程用户（任意地址）
create user 'admin'@'%' identified by '123456';

# 修改密码
alter user '用户名'@'主机地址' identified by '密码';

# 刷新权限
flush privileges;

授权

授权分为：全局、数据库级、表级、列级权限

# 格式：grant 权限列表 on 数据库.表 to '用户名'@'主机地址';

# 全局权限(*表示通配符)
grant all privileges on *.* to 'admin'@'%';

# 数据库级权限，表示只能使用 select 和 insert 两种命令
grant select ,insert on mysql.* to 'admin'@'192.168.181.%';

# 表级权限
grant update,delete on mysql.student to 'admin'@'192.168.181.%';

# 列级权限
grant select(id,name),update(age) on mysql.student to 'admin'@'192.168.181.%';

授权以后记得要刷新权限

撤销权限

撤销权限的语法和授权类似，只是将grant···to···换成revoke···from···

revoke select ,insert on mysql.* from 'admin'@'192.168.181.%';

删除用户

drop user 'dev'@'localhost';

# 习惯性刷新权限
flush privileges;

查看权限

# 查看当前用户权限
show grants;

# 查看指定用户权限
show grants for '用户名'@'主机地址';

数据备份与恢复

数据备份的本质其实就是将数据库中的数据转换成SQL语句再重新执行

比如说：

MySQL 中有一个数据库 School ，这个数据库中有一个表 student 的结构为:

create table student{
id int,
name varchar(50)
};

表中存在两条数据{1,'张三'},{2,'李四'}

将数据备份以后生成 N 条 SQL 语句：

CREATE DATABASE School;

USE testdb;

CREATE TABLE student(
id INT,
name VARCHAR(50)
);

INSERT INTO student VALUES(1,'张三');
INSERT INTO student VALUES(2,'李四');

数据备份

MySQL官方有提供备份工具 mysqldump

# 语法格式
mysqldump -u[用户名] -p [数据库] [表名] > backup_test.sql

# 建议添加不锁表参数
mysqldump -u root -p –single-transaction my_db users > /home/桌面/users_backup.sql

# 备份全部数据库
mysqldump -u root -p –all-databases > all_databases_backup.sql

数据恢复

执行数据恢复前需要先暂停 MySQL 服务再恢复数据

# 语法格式
mysql -u [用户] -p [数据库名] < [备份文件]

mysql -u root -p my_db users < users.sql

注意：数据恢复一般会出现多种情况

再介绍第二个备份工具 XtraBackup

XtraBackup 是企业级的备份工具，速度快并且支持热备份

# XtraBackup的本质：

复制数据文件
+
记录备份期间产生的事务日志
+
恢复时重放日志

# 创建备份目录
mkdir -p /backup/full
# 创建最小权限用户
create user 'backup'@'localhost' identified by 'Password';
grant reload, lock tables, replication client on *.* TO 'backup'@'localhost';
flush privileges;

xtrabackup –backup –user=backup –password=StrongPassword –target-dir=/backup/full

再进行恢复数据操作前需要先暂停 MySQL 服务

xtrabackup –prepare –target-dir=/data/backup/full