【Linux】MySQL 运维常用脚本

目录

操作规范

1、数据操作，必须谨慎，线上尤甚！！！
2、线上数据操作，必须备份！备份需完整、可用，备份使用自己最容易操作回滚的方式。
3、数据操作，能修改数据解决的决不删除
4、不确定的操作不做，不确定的命令不敲
5、如果出现问题，例如数据丢失、数据文件损坏，必须首先完整备份当前环境，保证后续操作不会造成更大的影响，以便留给能解决问题的人解决。
6、不允许移动、修改、删除 运行中的数据库的任意数据文件。
7、不允许删除近期生成的、未备份的binlog
8、不允许在 os 命令行 键入 密码

常用命令

以下脚本在 linux命令行执行

1、启动 mysql

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

参数解释：
==--defaults-file #ff0000== 指定配置文件路径
==--user #ff0000== 指定 mysql server 进程运行用户

2、关闭 mysql

/usr/local/mysql/bin/mysqladmin -uroot -p -s /tmp/mysql.sock shutdown

参数解释：
==-s #ff0000== sock文件地址
==shutdown #ff0000== 表示关闭数据库

3、进入 mysql client

/usr/local/mysql/bin/mysql -uroot -p -h192.168.0.1 -p3306 -s /tmp/mysql.sock

参数解释：
==-u #ff0000== 用户名
==-p #ff0000== 密码
==-h #ff0000== 地址
==-p #ff0000== 端口

4、导出 sql 查询的数据

/usr/local/mysql/bin/mysql -uroot -p -h192.168.0.1 -p3306 -s /tmp/mysql.sock -e "use db_name;select * from test1;"

参数解释：
==-e #ff0000== 后面接sql语句，必须放在最后一个参数，并紧跟双引号包裹的 sql 语句。相当于进入 mysql client 执行该 sql

5、数据备份
全库备份（gtid 开启需使用全备）

mysqldump -uroot -p -s /tmp/mysql.sock --single-transaction --master-data=2 -r -e --triggers --opt -a > all_db.sql

导出指定数据库

mysqldump -uroot -p -s /tmp/mysql.sock --single-transaction -b db_1 db_2 > db_1_2.sql

导出指定表

mysqldump -uroot -p -s /tmp/mysql.sock --single-transaction db_1 table_1 table_2 > table_1_2.sql

根据 where 条件导出

mysqldump -uroot -p -s /tmp/mysql.sock --single-transaction --where="status=1" db_1 table_1 > table_1.sql

参数说明：
==--single-transaction #ff0000== 以事务的方式导出,保证数据完整性
==--master-data=2 #ff0000== 导出当前 binlog position
==-r #ff0000== 导出存储过程 routines
==-e #ff0000== 导出事件 event
==--triggers #ff0000== 导出触发器 triggers
==--opt #ff0000== 同--add-drop-table, --add-locks, --create-options,--quick, --extended-insert, --lock-tables, --set-charset, --disable-keys
==-b #ff0000== 指定需导出的数据库，多个使用逗号隔开
==--where #ff0000== 导出条件，同 sql 中的 where 子句
==--no-create-db #ff0000== 不打印 create database 语句
==--no-create-info #ff0000== 不打印 create table 语句
==--no-data #ff0000== 不导出 insert 语句，即不导出表内数据，用于只导出表结构

常用 sql

以下脚本在 mysql 客户端/命令行执行

-- 查看数据库
show databases;

-- 查看建库语句
show create database db_name;

进入数据库
use database

-- 查看当前进入数据库的所有表
show tables;

-- 模糊匹配
show tables like '%%';

-- 查看指定数据库的所有表
show tables from db_name;

-- 查看建表语句
show create table table_name;

-- 查看表结构
show columns from table_name;

-- 修改表名
rename table table_name to new_table_name [, table_name2 to new_table_name2] … ;

-- 修改表存储引擎
alter table table_name engine=innodb;

-- 删除表
drop table table_name;

-- 添加列
alter table table_name add column_1 varchar(40);

-- 删除列
alter table table_name drop column_1;

-- 修改列
alter table table_name modify column_1 varchar(40) not null;

-- 修改列名
alter table table_name change birth_date date_of_birth date;

-- 添加索引
alter table table_name add index idx_test(column_1,[column_2]...);

-- 删除索引
drop index idx_test on table_name;

-- 查看索引
show index from table_name; 

-- 添加外键约束
alter table table_name add constraint fk_name foreign key(column_1) references t2(column_1); 

-- 查询前10 行数据，limit 关键字必须放在 sql 语句的最后
select * from test1 limit 10;

-- 查看第 6-10 行数据 
select * from test1 limit 5,5;

-- 按照 create_time 正序排序，asc 关键字默认可不写
select * from test1 order by create_time asc;

-- 按照 create_time 倒序排序
select * from test1 order by create_time desc;


-- 修改语句
update table_name set column_1=23,column_2='joe' where id=3;

-- 删除语句
delete from test1 where id=3;

权限管理

-- 创建用户
create user user1@'192.168.1.1';
create user user1@'192.168.1.1' password expire interval 30 day;

-- 授权、初始密码
grant select,insert,delete,update,create on db_name.* to user1@'192.168.1.1' identified by 'password';

-- 修改密码
alter user user1@'192.168.1.1' identified by 'password';

-- 修改用户密码过期时间
alter user user1@'192.168.1.1' password expire interval 30 day;

-- 锁定用户
alter user user1@'192.168.1.1' account lock;

-- 解锁用户
alter user user1@'192.168.1.1' account unlock;

-- 撤回权限
revoke update,create on db_name.* from user1@'192.168.1.1';

-- 刷新权限 (修改权限之后必须执行)
flush privileges;

-- 查看权限
show grants for user1@'192.168.1.1';

管理命令

-- 查看全部连接
show full processlist;

-- 查看 mgr 节点状态
select * from performance_schema.replication_group_members;

-- 查看参数
show [global|session] variables like '%buffer_pool%';

-- 修改参数 (仅限于可以在线修改的参数)
set [global|session] autocommit=1;

-- 查看状态
show [global|session] status like '%com_%';

-- 查看从库状态
show slave status\g

-- 启动从库复制线程
start slave;

-- 启动从库 io 线程
start slave io_thread;

-- 启动从库 sql 线程
start slave sql_thread;

-- 启动指定 channel 从库线程
start slave for channel "moshi";

-- 停止从库复制线程
stop slave

-- 清除从库配置：在从库执行，用于重建主从关系，不可恢复，谨慎操作
reset slave all;