mysql5.7运维基础

2021-08-20 02:03:56

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

上一篇：lnmp安装，zabbix源码安装安装教程


下一篇：python 文件处理