mysql5.7运维基础

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: mysql运维基础,mysql语法

1,mysql关闭的几种方法

mysqladmin -u root -p shutdown --mysqladmin关闭mysql
/etc/init.d/mysqld stop --mysqld脚本关闭

systemctl stop mysqld --mysql脚本关闭
kill -9 进程 --先查看mysqld进程,但不推荐方法

2,mysql修改密码
1

alter user root@'localhost' identified by '654321';

flush privileges;

2

mysqladmin -u root -p password 123456

3

mysql> use mysql

mysql> update mysql.user set authentication_string=password('6543216') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

4

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('7654321');

mysql> flush privileges;

忘记root密码,修root修改密码

/etc/init.d/mysqld stop --关闭mysql

mysqld_safe --skip-grant-tables & --安全模式启动
mysql> use mysql
mysql> update mysql.user set authentication_string=password('6543216') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
/etc/init.d/mysqld restart --重启mysql

mysql基础语法
1,mysql添加普通用户

mysql> create user 'test'@'localhost' identified by '123456';

mysql> flush privileges;

2

mysql> grant select on . to 'test2'@'localhost' identified by '654321';

mysql> flush privileges;

2 授权mysql用户和回收权限

mysql> grant select,insert on mydb.* to 'test'@'localhost';

mysql> show grants for 'test'@'localhost'; --查看权限
mysql> revoke select on mydb.* from 'test'@'localhost'; --回收select权限
mysql> flush privileges;

基础语句

create database mydb default charset utf8; --创建数据库

mysql> use mydb; --使用数据库
mysql> create table emm(id int); --创建表
mysql> insert into emm value(1); --插入数据
mysql> select * from emm; --查看表中数据
mysql> update emm set id=2 where id=1; --更新数据
mysql> show tables; --查看库中表
mysql> desc emm; --查看表结构
mysql> alter table emm add column age int after id; --添加一个字段
mysql> show engines; --查看数据库支持的引擎
mysql> show variables like '%char%'; --查看字符集
mysql> show variables like '%max_con%'; --查看默认连接数
mysql> show OPEN TABLES where In_use > 0; --查看正在使用的表
mysql> SHOW ENGINE INNODB STATUS; --查看innodb日志
mysql> drop table emm; --删除表
mysql> delete from emm; --清除表数据,不清楚表空间
mysql> truncate table emm; --清楚数据,清楚空间
mysql> drop database mydb; --删除数据库

运维基础语法
设置慢查询日志

mysql> show variables like 'long%';

Variable_name Value
long_query_time 10.000000

1 row in set (0.01 sec)

mysql> show variables like 'slow%';
Variable_name Value
slow_launch_time 2
slow_query_log OFF
slow_query_log_file /usr/local/src/mysql/data/iZwz9hzc7pd8k6u859n9rzZ-slow.log

3 rows in set (0.00 sec)

--查看慢查询日志是关闭的,如果是线上项目可以用语句设置开启,然后再my.cnf里面配置。

mysql> set global slow_query_log='ON';

Query OK, 0 rows affected (0.01 sec)
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
修改/etc/my.cnf
slow_query_log = ON
slow_query_log_file = /usr/local/src/mysql/data/iZwz9hzc7pd8k6u859n9rzZ-slow.log
long_query_time = 3
重启mysql,测试
mysql> select sleep(4);
[root@iZwz9hzc7pd8k6u859n9rzZ ~]# tail -100 /usr/local/src/mysql/data/iZwz9hzc7pd8k6u859n9rzZ-slow.log
/usr/local/src/mysql/bin/mysqld, Version: 5.7.23-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
/usr/local/src/mysql/bin/mysqld, Version: 5.7.23-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
Rows_examined: 0
SET timestamp=1536025164;
select sleep(4); --记录到了刚才的语句

二进制日志开启

cat /etc/my.cnf

log-bin=mysql-bin
binlog_format=mixed --混合模式
server-id = 1
sync-binlog = 1 --保证数据库的安全性,但影响一定的性能
expire_logs_days = 10 --日志自动删除10天以前的

mysql> show master logs;
Log_name File_size
mysql-bin.000001 177
mysql-bin.000002 177
mysql-bin.000003 177
mysql-bin.000004 177
mysql-bin.000005 177
mysql-bin.000006 177
mysql-bin.000007 573
mysql-bin.000008 177
mysql-bin.000009 177
mysql-bin.000010 1781
mysql-bin.000011 876
mysql-bin.000012 4132
mysql-bin.000013 154

13 rows in set (0.00 sec)
删除二进制日志
mysql> purge master logs to 'mysql-bin.000009';
Query OK, 0 rows affected (0.01 sec)
删除09以前的日志
mysql> purge master logs before '2018-09-04';
Query OK, 0 rows affected (0.01 sec)
删除9月4号之前的日志
还可以把expire_logs_days = 10 设置短也可以删除mysqlbinlog
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
删除所有mysqlbinlog
[root@iZwz9hzc7pd8k6u859n9rzZ data]# mysqlbinlog /usr/local/src/mysql/data/mysql-bin.000001
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;

at 4

180904 9:58:17 server id 1 end_log_pos 123 CRC32 0xec6dd750 Start: binlog v 4, server v 5.7.23-log created 180904 9:58:17 at startup

Warning: this binlog is either in use or was not closed properly.

ROLLBACK/!/;
BINLOG '
ueaNWw8BAAAAdwAAAHsAAAABAAQANS43LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAC55o1bEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AVDXbew=
'/!/;
查看二进制里面的内容

mysql> show binlog events in 'mysql-bin.000001';
Log_name Pos Event_type Server_id End_log_pos Info
mysql-bin.000001 4 Format_desc 1 123 Server ver: 5.7.23-log, Binlog ver: 4
mysql-bin.000001 123 Previous_gtids 1 154
mysql-bin.000001 154 Anonymous_Gtid 1 219 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql-bin.000001 219 Query 1 334 create database mydb default charset utf8
mysql-bin.000001 334 Anonymous_Gtid 1 399 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql-bin.000001 399 Query 1 498 use mydb; create table emm (id int)
mysql-bin.000001 498 Anonymous_Gtid 1 563 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql-bin.000001 563 Query 1 642 BEGIN
mysql-bin.000001 642 Query 1 741 use mydb; insert into emm values(1)
mysql-bin.000001 741 Xid 1 772 COMMIT / xid=20 /

10 rows in set (0.00 sec)
查看二进制日志里面的事件

数据库备份

[root@iZwz9hzc7pd8k6u859n9rzZ data]# mysqldump -u root -p mydb>~/mydb.sql

Enter password:
--备份整个库
[root@iZwz9hzc7pd8k6u859n9rzZ data]# mysqldump -u root -p -d mydb>~/mydb.sql
Enter password:
--备份表结构
[root@iZwz9hzc7pd8k6u859n9rzZ data]# mysqldump -u root -p mydb adds>~/adds.sql
Enter password:
--导出某一个或者多个表
[root@iZwz9hzc7pd8k6u859n9rzZ data]# mysqldump -u root -p -d mydb adds>~/adds.sql
Enter password:
--导出一个表或者多个表的表结构
[root@iZwz9hzc7pd8k6u859n9rzZ data]# mysqldump -uroot -p mydb --ignore-table=mydb.emm>~/ddd.sql
Enter password:
--忽略某个表导出数据

数据还原

mysql> drop database mydb;

Query OK, 2 rows affected (0.04 sec)
--先删除数据库,再创建一个空的mydb数据库
[root@iZwz9hzc7pd8k6u859n9rzZ data]# mysql -u root -p mydb .sql
Enter password
进去查看数据正常
但是我们这种备份只能定时去备份,但是现实工作中,数据库是一直运行的,所以当线上数据库被执行了drop操作的话,那怎么恢复过来呢?
首先需要定时备份的sql记录二进制的值
[root@iZwz9hzc7pd8k6u859n9rzZ data]# mysqldump -u root -p --databases mydb --lock-all-tables --flush-logs --master-data=2 > /opt/22.sql
Enter password:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;

--

-- Current Database: mydb

会记录日志为000002的154位置
现在再在数据库插入数据,然后删除数据库,我们还原步骤。
mysql>

mysql> show tables;
Tables_in_mydb
adds
emm

2 rows in set (0.00 sec)

mysql> insert into emm values(2)

-> ;

Query OK, 1 row affected (0.01 sec)

mysql> insert into emm values(3);
Query OK, 1 row affected (0.01 sec)

mysql>
--插入了两条数据

mysql> show master logs;
Log_name File_size
mysql-bin.000001 4080
mysql-bin.000002 702

2 rows in set (0.00 sec)
--02日志有变化了
mysql> drop database mydb;
Query OK, 2 rows affected (0.03 sec)
删除数据库,创建一个空的数据库
mysql> create database mydb default charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use mydb;
Database changed
mysql> source /opt/22.sql;
--导入定时备份的数据

mysql> show binlog events in 'mysql-bin.000002';
Log_name Pos Event_type Server_id End_log_pos Info
mysql-bin.000002 4 Format_desc 1 123 Server ver: 5.7.23-log, Binlog ver: 4
mysql-bin.000002 123 Previous_gtids 1 154
mysql-bin.000002 154 Anonymous_Gtid 1 219 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql-bin.000002 219 Query 1 298 BEGIN
mysql-bin.000002 298 Query 1 397 use mydb; insert into emm values(2)
mysql-bin.000002 397 Xid 1 428 COMMIT / xid=301 /
mysql-bin.000002 428 Anonymous_Gtid 1 493 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql-bin.000002 493 Query 1 572 BEGIN
mysql-bin.000002 572 Query 1 671 use mydb; insert into emm values(3)
mysql-bin.000002 671 Xid 1 702 COMMIT / xid=302 /
mysql-bin.000002 702 Anonymous_Gtid 1 767 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysql-bin.000002 767 Query 1 859 drop database mydb

--查看到drop database 在767位置
[root@iZwz9hzc7pd8k6u859n9rzZ data]# mysqlbinlog --no-defaults --stop-position="767" /usr/local/src/mysql/data/mysql-bin.000002| mysql -uroot -p6543216 mydb
mysql: [Warning] Using a password on the command line interface can be insecure.
--导入mysql差异日志
[root@iZwz9hzc7pd8k6u859n9rzZ data]# mysql -u root -p6543216 -e 'use mydb;select * from emm;'

mysql: [Warning] Using a password on the command line interface can be insecure.
id
1
2
3

数据库已经恢复完毕,只要弄清原理就很容易

数据库优化

一般mysql的使用的较多的是mysiam和innodb引擎
所以一般只要对这两种引擎优化就可以满足工作大多用途

max-allowed-packet = 16M 最大允许的包大小,太小会引起数据插入不到数据库
max-connections = 500 最大连接数,太小会线上客户端链接失败,too many concents等
innodb-buffer-pool-size = 2G innodb数据缓存池大小,一般设置内存的70%
myisam_sort_buffer_size = 8M myisam索引缓冲区
还有很多参数需要优化,包含xtrabackup热备等


本文来自 qq_34962337 的CSDN 博客 ,全文地址请点击:https://blog.csdn.net/qq_34962337/article/details/82356384?utm_source=copy
我的阿里云优惠券地址:https://promotion.aliyun.com/ntms/yunparter/invite.html?userCode=3ow2kbko

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5月前
|
存储 关系型数据库 MySQL
RDS MySQL 数据库运维简述
从运维的视角,汇总云数据库RDS MySQL使用的避坑指南。文章初版,维护更新,欢迎指点。
944 3
|
4月前
|
SQL 运维 关系型数据库
MySQL数据库运维第一篇(日志与主从复制)
MySQL数据库运维第一篇(日志与主从复制)
|
2月前
|
存储 SQL 运维
运维开发.MySQL.范式与反范式化
运维开发.MySQL.范式与反范式化
43 1
|
2月前
|
运维 关系型数据库 MySQL
在Linux中,MySQL数据库日常运维中涉及哪些关键任务?
在Linux中,MySQL数据库日常运维中涉及哪些关键任务?
|
2月前
|
运维 关系型数据库 MySQL
"MySQL运维精髓:深入解析数据库及表的高效创建、管理、优化与备份恢复策略"
【8月更文挑战第9天】MySQL是最流行的开源数据库之一,其运维对数据安全与性能至关重要。本文通过最佳实践介绍数据库及表的创建、管理与优化,包括示例代码。涵盖创建/删除数据库、表结构定义/调整、索引优化和查询分析,以及数据备份与恢复等关键操作,助您高效管理MySQL,确保数据完整性和系统稳定运行。
154 0
|
2月前
|
SQL 存储 关系型数据库
运维笔记.MySQL.基于mysqldump数据备份与恢复
运维笔记.MySQL.基于mysqldump数据备份与恢复
42 0
|
4月前
|
SQL 存储 关系型数据库
精通MySQL:从基础到高级运维实战
第一章:MySQL入门与基础 1.1 MySQL概述 简要介绍MySQL的历史、发展及其在数据库领域的地位
|
5月前
|
SQL 运维 关系型数据库
MySQL 运维篇
该文本涵盖了数据库管理的关键方面,包括DDL(CREATE, DROP, ALTER等)用于定义数据库结构,DML(INSERT, DELETE, UPDATE, SELECT)用于数据操作,DCL(GRANT, REVOKE, COMMIT, ROLLBACK)涉及权限管理和事务控制,以及DQL用于数据查询。还介绍了MySQL的安装过程,包括源码编译和二进制安装方法,以及启动服务、设置初始密码和修改用户密码的步骤。
55 1
|
5月前
|
缓存 运维 关系型数据库
运维角度浅谈MySQL数据库优化
运维角度浅谈MySQL数据库优化
|
10月前
|
运维 关系型数据库 MySQL
阿里大牛的595页MySQL笔记,透彻即系数据库、架构与运维
数据库运维的变革,经历从手工造到脚本化、系统化、平台化、智能化的转变,逐步实现DBA对数据库的规范化、自动化、自助化、可视化、智能化、服务化管理,从而保障数据库的安全、稳定、高效运行。