一、写在前

  作为一名运维工程师，我们需要运维的内容可能非常广泛，可能是路由器、交换机；肯能有服务器、操作系统；可能有nginx、中间件等；也有可能是数据库等等。这几天可能倒腾网络配置，过几天可能研究如何优化linux性能，再过几天又可能化身DBA，总之就是领导需要啥运维就得会啥！常在河边站哪有不湿鞋，好记性不如烂笔头。为了临阵磨枪，我们需要把各类知识领域常用命令收纳规整，再需要的时候可以翻出笔记，找到那些我们需要的知识！作为一个混合型运维工程师，如果说数据库需要学习和了解的，首先考虑的一定是mysql。mysql因为开源免费、性能和稳定性各方面也非常不错，所以是很多中小型企业关系数据库的首选，自然而然也成了混合型运维工程师在数据库领域首先也要学习的target！
  环境说明：

• 操作系统：centos7.6
• mysql5.7.32

二、mysql基础操作命令

1、数据库启停和状态查看

#systemctl start mysqld
#systemctl stop mysqld
#systemctl status mysqld

2、连接数据库

##连接默认地址127.0.0.1，端口3306的mysql实例
#mysql -uroot -p
##连接指定地址和端口服务器
#mysql -udba -ppassword -h 192.168.0.124 -P 3306

3、用户创建及改密

##创建用户
mysql> CREATE USER ‘test’@’%’ IDENTIFIED BY ‘test123’;
##用户密码修改方式一
mysql> use mysql;
mysql> update user set authentication_string=password(‘654321’) where user=‘test’ and host=’%’;
##用户密码修改方式二
mysql> set password for test@’%’= password(‘123456’);
##用户改名或者更新授权地址
mysql> rename user test@’%’ to test@‘192.168.0.%’;

4、用户授权及回收

##用户授权，授权testdb库的全部权限
mysql> grant all privileges on testdb.* to test@’%’;
##用户授权，授权test1.t1表的查询、更新权限
mysql> grant select,update on testdb.t1 to test@’%’;
##回收用户在testdb库上的drop表的权限
mysql> revoke drop on mysql.* from test@’%’;
##回收用户在testdb库上的所有权限
mysql> revoke all privileges on testdb.* from test@’%’;
##查看用户的授权
mysql> show grants for test@’%’;
##刷新授权
mysql> flush privileges;

5、数据库版本查询

[wuhs@test1 ~]$ mysqld -V
mysqld Ver 5.7.32 for el7 on x86_64 (MySQL Community Server (GPL))
mysql> select version();
±-----------+
| version() |
±-----------+
| 5.7.32-log |
±-----------+
1 row in set (0.00 sec)

6、查看mysql引擎

##查看实例支持的引擎和默认引擎
mysql> show engines;
##查看某表的引擎
mysql> show table status from dbname where name=‘table_name’;

7、查询正在执行的语句

mysql> select * from information_schema.`PROCESSLIST` where info is not null;
mysql> show full processlist;

8、查询数据库大小

##查询有哪些库
mysql> show databases;
##查询某个库大小
mysql> select concat(round(sum(data_length/1024/1024),2),‘MB’) as data from information_schema.tables where table_schema=‘dbname’;
##查询各数据库的大小，并按照降序排列
mysql> select table_schema ,round(sum(data_length/1024/1024),2) as size from information_schema.tables group by table_schema order by size desc ;

9、查询数据表大小

##查询有哪些表
mysql> show tables;
##查询数据库下各表的大小
mysql> select table_name,table_rows,data_length+index_length,
concat(round((data_length+index_length)/1024/1024,2),‘MB’)
data from information_schema.tables where table_schema=‘dbname’;
##查询某数据某表的大小
mysql> select table_name,table_rows,data_length+index_length,
concat(round((data_length+index_length)/1024/1024,2),‘MB’)
data from information_schema.tables where table_schema=‘dbname’
and table_name=‘tb_name’;

10、查询数据库表结构

mysql> SELECT column_name FROM information_schema.columns WHERE table_name=‘tb_name’;
show create table ‘tb_name’ \G

11、查询数据库是否有触发器

mysql> SHOW TRIGGERS\G;

12、查询某表的索引

mysql> show index from tb_name

13、账户锁定和解锁

mysql> ALTER USER ‘test’@’%’ ACCOUNT LOCK;
mysql> ALTER USER ‘test’@’%’ ACCOUNT UNLOCK;

14、锁库与解锁

mysql> FLUSH TABLES WITH READ LOCK;
mysql> UNLOCK TABLES;

三、库表操作常用命令

1、库的创建、删除和查看

##创建库
mysql> create database test;
##删除库
mysql> drop database test;
##查看库
mysql> show create database test;
##切换库
mysql> use test;

2、表的创建、删除、和查看

##创建表
mysql> create table tb_test( id int not null, data float not null,
-> birth datetime not null,
-> primary key(id)
-> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
##删除表
mysql> drop table tb_test;
##查看表
mysql> show create table tb_test;

3、往表中插入数据

##插入单条数据
mysql> insert into tb_test (id,data,birth) values (‘1’,‘100’,‘2017-05-05’);
##插入多条数据
mysql> insert into tb_test (id,data,birth) values (‘2’,‘200’,‘2017-05-05’), (‘3’,‘300’,‘2017-05-07’);

4、更新表数据

mysql> update tb_test set birth=‘2021-01-01’ where id=‘3’;

5、删除表数据

##删除行
mysql> delete from tb_test where id=‘5’;
##删除表，保留表结构
mysql> truncate table tb_test;
##删除表，完整删除
mysql> drop table tb_test;

6、修改表

##增加一列
mysql> alter table tb_test add column sex varchar(2) not null;
##修改列属性，使用modify
mysql> alter table tb_test modify 性别 varchar(2) default ‘男’;
##修改列属性，使用alter
mysql> alter table tb_test alter 性别 set default ‘女’;
##修改列，使用change，将列名sex改为列名性别
mysql> alter table tb_test CHANGE sex 性别 varchar(4);
##修改表引擎
mysql> alter table tb_test ENGINE = MYISAM;
##重命名表
mysql> alter table tb_test rename TO alter_tbl;
##删除表外键
alter table tb_test drop foreign key keyName;
##删除列
mysql> alter table tb_test drop tz;

7、查询表数据

##查询表所有数据
mysql> select * from tb_test;
##查询指定条件数据
mysql> select * from tb_test where id=1;
##查询指定行数
mysql> select * from tb_test limit 2;
##查询结果降序排序
mysql> select * from tb_test order by birth DESC;

四、参数相关常用命令

1、查看参数

##通过包含指定字符串，模糊匹配查找相关参数
##查看系统变量参数
mysql> show variables like ‘%timeout%’;
##查看会话状态变量参数
mysql> show session status like ‘%time%’;
##查看全局状态变量参数
mysql> show session status like ‘%count%’;

2、修改参数

##此种方式只适合修改动态参数，静态参数需要修改my.cnf配置文件后重启生效，状态变量都是静态参数。
##修改会话参数
mysql> set session innodb_lock_wait_timeout=50;
##修改全局参数
mysql> set global innodb_lock_wait_timeout=50;

五、主从复制相关常用命令

1、配置主从

change master to
MASTER_HOST=‘192.168.0.125’,
MASTER_USER=‘replbak’,
MASTER_PASSWORD=‘Test!123’,
MASTER_PORT=3306,
master_auto_position=1;

2、主从启停及查看

##启动主从
mysql> start slave;
##关闭主从同步
mysql> stop slave;
##清空主从配置，清空前需要停止主从服务
mysql> reset slave all;
##查看主从状态
mysql> show master status\G
mysql> show slave status\G

六、其他命令

1、查看、修改存储过程和函数的DEFINER

mysql> select db,name,type,definer from mysql.proc where db=‘amcsh’；
mysql> update mysql.proc set definer=‘user@localhost’ where db = ‘amcsh’ and name = ‘extract_schema_from_file_name’;

2、查看、修改事件的DEFINER

mysql> select definer from mysql.event;
mysql> update mysql.event set definer=‘user@localhost’ name = ‘xxxxxx’;

3、查看触发器的DEFINER

mysql> select * from information_schema.triggers where TRIGGER_SCHEMA = ‘amcsh’;
只能删除重建，或者通过工具修改，修改时建议锁表。

4、查看、修改视图的DEFINER

mysql> select * from information_schema.views where table_schema = ‘xxxxxx’;
mysql> select concat(“alter definer=`developer`@`%` sql security definer view “,table_schema,”.”,table_name," as “,view_definition,”;") as ‘view’ from information_schema.views where definer<>‘developer@%’ and table_schema = ‘xxxxxx’;