2018-9-29 直播课堂笔记

最新推荐文章于 2023-02-03 11:47:35 发布

a1779078902

最新推荐文章于 2023-02-03 11:47:35 发布

阅读量291

点赞数 1

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/a1779078902/article/details/82904619

3.2 show full processlist详解

4.mysql用户管理

5.mysql数据库备份恢复

5.1 利用mysqldump备份与还原

5.2 Percona Xtrabackup实现数据的备份与恢复

6.MySQL配置文件my.cnf详解

7.MySQL存储引擎MyISAM与InnoDB的优劣

1.设置更改root密码

1.1 知道原来的root密码，或者密码为空时的设置方法：

1.1 .1方法一：使用mysqladmin来完成。 mysqladmin -u root password "newpwd" ，password后面的双引号不是必须的，不过如果密码包含空格或者一些特殊的符号，需要用引号。

1..1.2 方法二：进入MYSQL,直接通过SET 或者 UPDATE user表来设置密码：

[root@knightlai ~]# mysql -u root -p
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd') -> WHERE User = 'root'; mysql> FLUSH PRIVILEGES;

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

1.2 不知道原来的root密码

1.2.1 方法一：编辑MySQL配置文件my.cnf：

在[mysqld]中加入下面这条语句：skip-grant-tables，重启MySQL服务。

进入mysql执行：

mysql>UPDATE mysql.user SET Password=PASSWORD('newpwd') -> WHERE User='root';
mysql> FLUSH PRIVILEGES;

1.2.2 方法二：

1。kill掉所有mysql的进程
2。使用--skip-grant-tables的参数启动mysql
[root@knightlai ~]# mysqld_safe --skip-grant-tables &
3.登入Mysql执行mysql> UPDATE user SET password=password("新密码") WHERE user='root';      【密码注意大小写】

mysql> flush privileges;

mysql> exit;

重新启动mysql服务

这样新的root密码就设置成功了。

2.连接mysql

2.1mysql客户端和mysql服务器部署在同一台机器上面时，用mysql命令连接本地mysql服务器指定localhost这个地址，不需要指定mysql服务器端口，读取[mysql]区块的配置信息，并且通过mysql.sock方法连接mysql服务器；

 错误代码：Can't connect to local MySQL server through socket '/tmp/mysql.sock '

有时候如果默认的sock连接方式不是在/tmp/mysql.sock,而是在/var/lib/mysql/mysql.sock
就会报错。我们可以直接做一个软链接：ln -s /tmp/mysql.sock /var/lib//mysql/mysql.sock,就可以解决这个问题

3.mysql常用命令

3.1 基础命令

1.显示已有的databases: mysql> show database;

2.创建数据库：  
mysql> create database [数据库名];   
如：mysql> create database crc;
3.操作数据库：mysql> use [数据库名]；

4.删除数据库：   
 mysql> drop database [数据库名];    
如： mysql> drop database crc; 

5.查看进程：
mysql>show processlist;

6.  查看表，应该先指定一个数据库为当前数据库；比如是名为linux的数据库；show tables;  
mysql>use mysql;  
mysql> show tables;  
Empty set (0.00 sec)

7.创建新用户并授权
mysql> grant all privileges on . to 'root'@'localhost' identified by'123456';
mysql> flush privileges;

8.查看某个表全部字段
desc slow_log;
show create table slow_log\G; （不仅可以显示表信息，还可以显示建表语句）

9.查看当前用户
select user();

10.查看当前所在数据库
select database();

11.查看数据库版本
select version();

12.查看数据库状态
show status; 当前会话状态
show global status; 全局数据库状态
show slave status\G; 查看主从数据库状态信息

13.查询数据库参数
show variables;

14.修改数据库参数
show variables like 'max_connect%';
set global max_connect_errors = 1000;（重启数据库会失效，要在配置文件中修改）

15.查询表数据
select * from mysql.db; //查询该表中的所有字段
select count() from mysql.user; //count()表示表中有多少行
select db,user from mysql.db; //查询表中的多个字段
select * from mysql.db where host like '10.0.%';在查询语句中可以使用万能匹配 “%”

16.插入一行数据
insert into db1.t1 values (1, 'abc');

17.更改表的某一行数据
update db1.t1 set name='aaa' where id=1;

18.清空表数据
truncate table db1.t1;

19.删除表
drop table db1.t1;

20.清空数据库中的所有表（数据库名是eab12）
mysql -N -s information_schema -e "SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM TABLES WHERE TABLE_SCHEMA='eab12'" | mysql -f eab12

3.2 show full processlist详解

//这个可以看到更全的信息
mysql> show full processlist;
+----+------+--------------------+------+---------+-------+-------+-----------------------+
| Id | User | Host               | db   | Command | Time  | State | Info                  |
+----+------+--------------------+------+---------+-------+-------+-----------------------+
|  1 | root | localhost          | NULL | Sleep   |    19 |       | NULL                  |
|  2 | root | 192.168.100.1:7437 | test | Sleep   |  8042 |       | NULL                  |
|  3 | root | 192.168.100.1:7438 | NULL | Sleep   | 24355 |       | NULL                  |
|  5 | root | 192.168.100.1:7443 | NULL | Sleep   | 24324 |       | NULL                  |
|  7 | root | 192.168.100.1:7450 | test | Sleep   | 24279 |       | NULL                  |
|  9 | root | 192.168.100.1:5152 | test | Query   |     0 | init  | show full processlist |
+----+------+--------------------+------+---------+-------+-------+-----------------------+
6 rows in set
各个列的含义：

①.id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
②.user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
③.host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
④.db列，显示这个进程目前连接的是哪个数据库
⑤.command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
⑥.time列，显示这个状态持续的时间，单位是秒
⑦.state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
⑧.info列，显示这个sql语句，是判断问题语句的一个重要依据

4.mysql用户管理

4.1 一般我们是使用root授权，如果要想其它用户也有这个权限需要加上这个参数 with grant option

mysql> grant all privileges on *.* to 'test'@'%' identified by '123456' with grant option;

all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。
on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。
如果我要指定将权限应用到test库的user表中，可以这么写：test.userto：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”test”@”192.168.0.%”，表示test这个用户只能在192.168.0IP段登录
identified by：指定用户的登录密码
with grant option：表示允许用户将自己的权限授权给其它用户


可以使用GRANT给用户添加权限，权限会自动叠加，不会覆盖之前授予的权限，
比如你先给用户添加一个SELECT权限，后来又给用户添加了一个INSERT权限，
那么该用户就同时拥有了SELECT和INSERT权限。


//回收相关权限
mysql> revoke create on *.* from 'test@localhost';
mysql> flush privileges;

4.2 用户权限管理主要有以下作用：
1. 可以限制用户访问哪些库、哪些表
2. 可以限制用户对哪些表执行SELECT、CREATE、DELETE、DELETE、ALTER等操作
3. 可以限制用户登录的IP或域名
4. 可以限制用户自己的权限是否可以授权给别的用户

5.mysql数据库备份恢复

做为一个运维人员来说，各种备份是非常重要的。不管是配置文件，还是数据库备份。

5.1 利用mysqldump备份与还原

mysqldump -u 用户名 -p 数据库 > 数据库.sql
例：mysqldump -u root -p db1 > db1.sql(将数据库db1备份到db1.sql中)
//将数据库还原    
mysqldump -u root -p db < db1.sql

5.2 Percona Xtrabackup实现数据的备份与恢复

5.2.1 Percona XtraBackup是一个开源、免费的MySQL热备份软件,能够为InnoDB和XtraDB数据库执行非阻塞备份，特点如下：

1、快速、可靠的完成备份
2、备份期间不间断事务处理
3、节省磁盘空间和网络带宽
4、自动对备份文件进行验证
5、恢复快，保障在线运行时间持久性

5.2.2 XtraBackup 有两个工具：xtrabackup 和 innobackupex：
xtrabackup 本身只能备份 InnoDB 和 XtraDB ，不能备份 MyISAM；
innobackupex 本身是 Hot Backup 脚本修改而来，同时可以备份 MyISAM 和 InnoDB，但是备份 MyISAM 需要加读锁。

具体的原理可以参考这个链接：

http://wangxin123.com/2017/01/20/%E9%80%9A%E8%BF%87Percona%20Xtrabackup%E5%AE%9E%E7%8E%B0%E6%95%B0%E6%8D%AE%E7%9A%84%E5%A4%87%E4%BB%BD%E4%B8%8E%E6%81%A2%E5%A4%8D/

innobackex工具备份mysql数据：

http://zhangguangzhi.top/2017/08/23/innobackex%E5%B7%A5%E5%85%B7%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE/#%E4%B8%89%E3%80%81%E5%BC%80%E5%A7%8B%E6%81%A2%E5%A4%8Dmysql

6.MySQL配置文件my.cnf详解

[mysqld]
port = 3306
socket = /tmp/mysql.sock

basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1 #表示是本机的序号为1,一般来讲就是master的意思

skip-name-resolve
# 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，
# 则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求

#skip-networking

max_connections = 1000
# MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

binlog_cache_size = 1M
# 一个事务，在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size大小32K


详细的参数可以看：https://www.jb51.net/article/48082.htm

7.MySQL存储引擎MyISAM与InnoDB的优劣

	MyISAM	InnoDB
存储结构	每张表被存放在三个文件： frm-表格定义 MYD(MYData)-数据文件 MYI(MYIndex)-索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
可移植性、备份及恢复	由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
锁	只支持表锁	支持表锁、行锁行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的
外键	不支持	支持
SELECT	MyISAM更优
INSERT UPDATE DELETE		InnoDB更优它不会重新建立表，而是一行一行的删除

但是InnoDB的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。

8. 什么是事务以及事务的特点

8.1概念

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。

例如：在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。

8.2 特性

　　事务是恢复和并发控制的基本单位。

　　事务应该具有4个属性：原子性、一致性、隔离性、持续性。这四个属性通常称为ACID特性。

　　原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。

　　一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

　　隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

　　持久性（durability）。持续性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

9.binlog日志功能进行数据恢复

9.1、binlog 介绍

　　服务器的二进制日志记录着该数据库的所有增删改的操作日志(前提是要在自己的服务器上开启binlog)，还包括了这些操作的执行时间。为了显示这些二进制内容，我们可以使用mysqlbinlog命令来查看。

　　用途1：主从同步

　　用途2：恢复数据库

9.2用法：mysqlbinlog命令用法：shell> mysqlbinlog [options] log_file ...

常见的选项有以下几个：

--start-datetime

从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间。取值如：="1470733768" 或者="2016-08-09  5:09:28"
示例: 

[root@hcloud ~]# mysqlbinlog --start-datetime="2016-08-09 5:05:27" /var/lib/mysql/mysql-bin.000001
 
--stop-datetime

从二进制日志中读取指定小于时间戳或者等于本地计算机的时间  取值和上述一样

--start-position        

从二进制日志中读取指定position 事件位置作为开始。取值：="2698"

示例：

[root@hcloud ~]# mysqlbinlog --start-position="2698" /var/lib/mysql/mysql-bin.000001
 
--stop-position

从二进制日志中读取指定position 事件位置作为事件截至。取值：="2698"

扩展链接：https://note.youdao.com/share/?token=70F9416173B94FDCBE1E6B3D2A9CF271&gid=31981530#/