正文 

当前位置： 首页 > 代码 > java教程

mysql数据库day04-数据库mysql的高级使用(代码片段)

攻城狮Chova 攻城狮Chova     2022-10-23     753

关键词：

数据库MySQL的高级使用

MySQL常用工具

mysql

  • 客户端工具mysql
  • 基本语法:
mysql [options] [database]
  • 选项options包括:
    • 连接选项
    • 执行选项
  • 连接选项:
连接选项含义说明
-u- -user指定用户名
-p- -password指定密码
-h- -host指定服务器IP或者域名
-P- -port指定连接的端口号
mysql -h 127.0.0.1 -P 3306 -u root -p root

mysql -h127.0.0.1 -P3306 -uroot -proot
  • 执行选项:
执行选项含义说明
-e- -execute执行SQL语句并退出
这个可以在MySQL客户端执行SQL语句,不需要连接MySQL数据库执行.便于对一些批处理脚本进行操作
mysql -uroot -proot database -e "SELECT name FROM table_name";

mysqladmin

  • mysqladmin:
    • 执行管理操作的客户端程序
    • 可以用来检查服务器的配置和当前状态,创建数据库和删除数据库等操作
    • 可以使用mysqladmin --help查看使用文档
# 创建数据库
mysqladmin -uroot -proot create 'database_name';

# 删除数据库
mysqladmin -uroot -proot drop 'database_name';

# 查看服务器版本
mysqladmin -uroot -proot version;

mysqlbinlog

  • mysqlbinlog:
    • MySQL数据库服务器生成的二进制日志文件以二进制的格式保存
    • 如果需要检查二进制格式的日志文件需要使用mysqlbinlog日志管理工具
  • 基本语法:
mysqlbinlog [options] log_file ....
  • 基本选项:
基本选项含义说明
-d- -database指定数据库名称
只列出指定的数据库相关操作
-o- -offset指定检查日志的偏移量
忽略掉日志中的前n行
-r- -result-file将输出的文本格式的日志保存到指定文件
-s- -short-form以简单格式显示文件
- -start-datetime
- -stop-datetime		- -start-datetime
- -stop-datetime		检查指定日期间隔内的所有日志
- -start-position
- -stop-position		- -start-position
- -stop-position		检查指定位置间隔内的所有日志

mysqldump

  • mysqldump:
    • 客户端工具mysqldump可以用来备份数据库以及在不同的数据库之间进行数据迁移
    • 客户端工具mysqldump的可以用来备份创建表以及数据,插入表以及数据的SQL语句
  • 基本语法:
mysqldump [options] db_name [table_names]

mysqldump [options] --database/-B db_name1 db_name2 ...

mysqldump [options] --all -database/-A
  • 基本选项:
基本选项含义说明
-u- -user指定用户名
-p- -password指定数据库服务器密码
-h- -host指定数据库服务器IP或者域名
-P–port指定连接的端口号
- -add-drop-database- -add-drop-database输出内容在每个创建数据库的语句前添加 Drop database 语句
- -add-drop-table- -add-drop-table输出内容在每个创建表的语句前添加 Drop table 语句
该选项默认开启,如果需要关闭,需要使用 - -skip-add-drop-table 选项
-n- -no-create-db输出内容不包含创建数据库的语句
-t- -no-create-info输出内容不包含创建表的语句
-d- -no-data输出内容不包含数据库表中的数据
-T- -table输出内容生成两个文件
一个是创建表结构的 .sql 文件.一个是数据的 .txt 文件,相当于select into outfile
nysqldump -uroot -proot db_name table_name --add-drop-database --add-drop-table > table_name.sql

mysqldump -uroot -proot -T /src db_name table_name

mysqlimport

  • mysqlimport:
    • 客户端工具mysqlimport是MySQL数据库服务器的数据导入工具
    • 客户端工具mysqlimport专门设计用来导入mysqldump -T导出的数据库数据的文本文件
  • 基本语法:
mysqlimport [options] db_name table_name.txt ...

mysqlimport -uroot -uroot db_name table_name.txt

source

  • source指令:
    • MySQL数据库服务器的数据导入指令
    • 可以用来执行SQL文件将数据导入数据库中
source table_name.sql

mysqlshow

  • mysqlshow:
    • 客户端对象查找工具
    • 查找MySQL数据库服务器中存在哪些数据库,数据库中存在哪些表,表中存在的列和索引信息
  • 基本语法:
mysqlshow [options] [db_name] [table_name] [col_name]
  • 基本参数:
基本参数说明
- -count显示数据库和表的统计信息
数据库和表均可以不指定
-i显示指定数据库和指定表的状态信息
# 查询数据库服务器中每一个数据库的表的数量以及表的数据记录的数量
mysqlshow -uroot -proot --count

# 查询指定数据库中每一个表的字段的数量以及行记录的数量
mysqlshow -uroot -proot db_name --count

# 查询指定数据库中指定表的详细信息 
mysqlshow -uroot -proot db_name table_name -i

MySQL日志

  • MySQL数据库服务器有4种不同的日志:
    • 错误日志
    • 二进制日志
    • 查询日志
    • 慢查询日志

错误日志

  • 错误日志:
    • MySQL数据库服务器中的错误日志记录MySQL数据库服务器启动,停止以及在运行过程中发生严重错误时的相关信息
    • 当MySQL数据库服务器出现错误问题导致无法正常使用时,可以首先查看错误日志
  • MySQL数据库服务器中的错误日志:
    • 错误日志默认是开启的
    • 错误日志默认存放的目录为MySQL数据库服务器的数据目录 var/lib/mysql
    • 错误日志默认的文件名称为 主机名.err
  • 查看错误日志文件位置的MySQL命令:
show variables like 'log_error%';
  • 查看错误日志文件内容的命令:
tail -f /var/lib/mysql/localhost.err

cat /var/lib/mysql/localhost.err

二进制日志

  • 二进制日志binlog:
    • 记录所有的数据定义语句DDL和数据操纵语句DML, 不记录数据查询语句DQL
    • 二进制日志可以用于MySQL数据库服务器的容灾恢复
    • MySQL数据库服务器的主从复制就是通过二进制日志实现的
  • 二进制日志默认情况下是没有开启的,需要到MySQL的配置文件 /usr/my.cnf 中配置二进制日志的开启以及二进制日志的格式
# 配置开启二进制日志,指定日志文件名前缀为mysqlbin
log_bin=mysqlbin
# 配置二进制日志的格式
binlog_format=MIXED

日志格式

  • STATEMENT :
    • 在日志文件中记录的都是SQL语句statement
    • 每一条对数据进行修改的SQL语句都会记录在日志文件中
    • 可以使用mysqlbinlog工具查看每一条SQL语句的文本
    • 主从复制时 ,Slave从库会将STATEMENT格式的日志解析为SQL语句文本,并在从库中重新执行一次
  • ROW :
    • 在日志文件中记录的不是SQL语句,而是每一行的数据变更
    • 如果MySQL数据库服务器的数据库中的表中有一行记录发生变更,就会在日志文件中记录这一行数据的变更
  • MIXED :
    • MySQL数据库服务器默认的日志格式,综合了STATEMENT和ROW两种数据库格式的优点
    • 默认情况下采用STATEMENT格式记录数据库日志,在一些特殊的情况下使用ROW格式记录数据库日志

日志读取

  • 二进制日志是以二进制格式存储的,所以不能直接读取,需要使用MySQL数据库服务器中的日志管理工具mybinlog来查看
  • 使用mysqlbinlog如下命令查看二进制日志:
mybinlog [options] 日志文件名称;
  • STATEMENT格式的日志会生成两种文件:
    • 日志索引文件mysqlbin.index, 在日志索引文件中记录日志的文件名称
    • 日志文件mysqlbin.000001
  • ROW格式的日志直接生成一个日志文件mysqlbin.000001. 使用mysqlbinlog的如下命令查看日志:
mysqlbinlog -vv mysqlbin.000001;

日志删除

  • 如果在磁盘中生成大量的日志文件,长时间不进行清除,会占用大量的磁盘空间,造成资源的浪费
  • reset master :
    • 使用reset master命令可以删除全部二进制binlog日志
    • 使用reset master命令删除后,二进制binlog日志文件的编号会从mysqlbin.000001重新开始
    reset master;
  • purge master logs to :
    • 删除指定编号之前的所有二进制binlog日志
purge master logs to 'mysqlbin.000001';
  • purge master logs before :
    • 删除指定日期之前的所有二进制binlog日志
purge master logs before '2022-12-22 18:18:18';
  • - -expire_logs_days :
    • 配置日志过期天数参数 - -expire_logs_days可以在指定的天数后将日志自动删除
--expire_logs_days=6

查询日志

  • 二进制日志不包含查询数据的SQL语句,在查询日志中记录了数据库服务器中的所有操作语句
  • 查询日志:
    • 记录MySQL数据库服务器中操作的SQL语句
    • 查询日志默认情况下是关闭的,需要在MySQL数据库服务器的配置文件 /usr/my.cnf 中配置开启
    • 生成的查询日志文件保存在MySQL的数据目录 /var/lib/mysql 中
# 开启查询日志的选项. 可选值包括 1-开启, 0-关闭
general_log=1
# 指定查询日志的文件名的选项. 如果未指定,默认文件名为 hostname.log
general_log_file=query_log.log
  • 使用cat命令查看MySQL数据库服务器的查询日志
cat query_log.log

慢查询日志

  • 慢查询日志:
    • 慢查询日志记录MySQL数据库服务器中执行时间超过参数long_query_time指定的时间并且扫描的记录数不小于min_examined_row_limited的SQL语句
    • 参数long_query_time的默认值为10s, 最小可以为0s, 可以精确到微秒
    • 慢查询默认是关闭的,可以在my.cnf中配置参数来开启MySQL数据库服务器的慢查询日志
# 开启慢查询日志的选项. 可选值包括 1-开启, 0-关闭
slow_query_log=1
# 指定慢查询日志的文件名选项
slow_query_log_file=slow_query_log.log
# 指定慢查询的时间限制的选项,默认为10s. 执行时间超过这个指定时间的SQL语句就认为是慢查询,这条SQL语句就会记录到慢查询日志中
long_query_time=10
  • 慢查询日志的读取:
    • 慢查询日志以纯文本文件格式记录的,可以使用cat命令直接读取
    cat slow_query_log.log
    • 慢查询日志以纯文本文件格式记录的,可以使用tail命令实时查看慢查询日志的记录
    tail -f slow_query_log.log
    • 如果存在大量的慢查询日志内容,可以使用mysqldumpslow对慢查询日志进行分类汇总查看
    mysqldumpslow slow_query_log.log

MySQL主从复制

  • 主从复制:
    • 将主数据库的数据定义语句DDL和数据操作语句DML通过二进制日志的形式传输到从数据库服务器中
    • 在从数据库服务器中重新执行这些二进制日志的数据定义语句DDL和数据操作语句DML
    • 这样使得从数据库服务器中的数据和主数据库服务器中的数据保持同步
  • MySQL主从复制:
  • MySQL数据库服务器支持一台主库同时向多台从库进行主从复制
  • MySQL数据库服务器中的从库同时也可以作为其余从库服务器的主库进行主从复制
  • 这样可以实现MySQL数据库服务器的链状复制
  • MySQL主从复制原理:
    • MySQL主数据库服务器在数据库中的事务提交时,会将主数据库中的数据变更作为时间事件记录在二进制日志Binary Log文件binlog中
    • MySQL主数据库服务器推送二进制日志Binary Log文件binlog到从数据库服务器中的中继日志Relay Log中
    • MySQL从数据库服务器重新执行中继日志Relay Log中的事件,变更从数据库服务器中的数据
  • MySQL主从复制解决的问题:
    • 主数据库服务器如果出现问题,可以立即切换到从数据库服务器提供服务,不会对运行的服务造成影响
    • 可以在主数据库服务器上执行更新操作,在从数据库服务器上执行查询操作,实现读写分离,减小主数据库服务器的访问压力
    • 可以在从数据库服务器上执行数据库中的数据备份,这样可以避免在数据备份期间影响主数据库服务器上的数据库操作

MySQL主从复制搭建

master

  • 在MySQL主节点master数据库服务器配置文件**/usr/my.cnf**中添加如下配置:
# MySQL的服务ID,要保证在整个集群中的服务ID唯一
server-id=0

# MySQL中二进制日志binlog的存储路径
log-bin=/var/lib/mysql/mysqlbin

# 是否为MySQL中只读的数据库. 可选值包括 0-读写, 1-只读
read-only=0

# MySQL中忽略主从同步的数据库
binlog-ignore-db=mysql

# 指定MySQL中主从同步的数据库
# binlog-do-db=db_name
  • 重启MySQL主节点master的MySQL服务:
service mysql restart
  • 对MySQL主从同步的服务器上数据库账号进行授权:
grant replication slave on *.* to 'root'@'192.168.136.101' identified by 'root';

flush privileges;
  • 使用命令查看主节点master中MySQL数据库服务器状态中的File和Position的值:
    • File : 主从同步时推送日志文件的起始文件的名称
    • Position : 主从同步时推送日志文件的起始的位置
    • Binlog_Do_DB : 指定主从同步的数据库
    • Binlog_Ignore_DB : 指定主从同步忽略的数据库
show master status;

slave

  • 在MySQL从节点slave数据库服务器配置文件 /usr/my.cnf中添加如下配置:
# MySQL的服务ID,要保证在整个集群中的服务ID唯一
server-id=1

# MySQL中二进制日志binlog的存储路径
log-bin=/var/lib/mysql/mysqlbin
  • 重启MySQL从节点slave的MySQL服务:
service mysql restart
  • 使用命令行指定当前从库对应的主库IP地址,用户名和密码.以及从指定的日志文件开始的位置推送同步日志:
change master to master_host='192.168.136.100',master_user='root',master_password='root',master_log_file='mysqlbin.000001',master_log_pos=666;
  • 执行命令开启主从复制的同步操作:
start slave;
  • 使用命令查看从节点slave的节点状态:
    • Slave_IO_Running: Yes
    • Slave_SQL_Running: Yes
show slave status\\G;
  • 可以使用命令在从节点slave上停止主从复制的同步操作:
stop slave;

mysql数据库day04-数据库mysql的高级使用(代码片段)

数据库MySQL的高级使用MySQL常用工具mysqlmysqladminmysqlbinlogmysqldumpmysqlimportsourcemysqlshowMySQL日志错误日志二进制日志日志格式日志读取日志删除查询日志慢查询日志MySQL主从复制MySQL主从复制搭建masterslaveMySQL常用工具mysql客户端工具mysq... 查看详情

mysql数据库day04-数据库mysql的高级使用(代码片段)

数据库MySQL的高级使用MySQL常用工具mysqlmysqladminmysqlbinlogmysqldumpmysqlimportsourcemysqlshowMySQL日志错误日志二进制日志日志格式日志读取日志删除查询日志慢查询日志MySQL主从复制MySQL主从复制搭建masterslaveMySQL常用工具mysql客户端工具mysq... 查看详情

mysql高级-day01(代码片段)

Mysql高级-day01MySQL高级课程简介序号Day01Day02Day03Day041Linux系统安装MySQL体系结构应用优化MySQL常用工具2索引存储引擎查询缓存优化MySQL日志3视图优化SQL步骤内存管理及优化MySQL主从复制4存储过程和函数索引使用MySQL锁问题综合案例5... 查看详情

day1-mysql

搭建数据库服务器数据库的基本使用表的基本操作表记录的基本操作用户授权与权限撤销数据备份与恢复Mysql主从同步Mysql读写分离Mysql高可用集群Mysql性能调优?Mysql数据库服务器????存储的数据?1搭建数据库服务器????商业软件????... 查看详情

mysql数据库day02-数据库mysql的体系结构(代码片段)

数据库体系结构MySQL的体系结构存储引擎基本概念存储引擎特性InnoDBMyISAMMEMORYMERGE存储引擎使用场景索引避免索引失效全值匹配最左前缀法则避免使用范围查询避免运算操作字符串要加单引号查询要覆盖索引多个条件使用inor的前... 查看详情

mysql数据库day02-数据库mysql的体系结构(代码片段)

数据库体系结构MySQL的体系结构存储引擎基本概念存储引擎特性InnoDBMyISAMMEMORYMERGE存储引擎使用场景索引避免索引失效全值匹配最左前缀法则避免使用范围查询避免运算操作字符串要加单引号查询要覆盖索引多个条件使用inor的前... 查看详情

外包杯ⅱ|任务进度day04

...制，初步实现了信息展示今天打算做什么：实现远程访问数据库遇到的问题：在确认开启远程访问数据库权限以及开启防火墙3306端口的前提下依旧无法实现远程访问数据库，以及同用MySQL后如何实现图片显示 查看详情

mysql数据库day03-数据库mysql的优化(代码片段)

数据库优化SQL优化SQL优化步骤查看SQL执行频率定位慢查询SQL分析SQL执行计划分析SQL执行耗费分析SQL优化器执行计划SQL优化实例批量插入大量数据优化主键顺序插入关闭唯一性校验关闭自动提交事务INSERT语句优化ORDERBY语句优化两... 查看详情

mysql数据库day03-数据库mysql的优化(代码片段)

数据库优化SQL优化SQL优化步骤查看SQL执行频率定位慢查询SQL分析SQL执行计划分析SQL执行耗费分析SQL优化器执行计划SQL优化实例批量插入大量数据优化主键顺序插入关闭唯一性校验关闭自动提交事务INSERT语句优化ORDERBY语句优化两... 查看详情

day18-mysql基础(代码片段)

MySQL什么是数据库数据库（DB，DataBase）概念：数据仓库，安装在操作系统之上的软件，可以存储大量的数据，超过500w时，需要做索引优 化数据库分类关系型数据库（SQL）：采用了关系模型来组织数据的数据库，其以行和列的... 查看详情

mysql怎么查询每个月有多少天详情如下：

表：order字段：id,time序号，时间数据：1，“2016-04-01”2，“2016-05-15”3，“2016-04-15”怎么求出数据中的月份每月有多少天，求助！最后答案是：4月5月3031参考技术A语句如下：--days 是time当月的天数Select id,time,Days=TIMESTAMPDIF... 查看详情

day37mysql安装配置及sql语句基础(代码片段)

内容大纲1、数据库概念2、MySQL介绍3、MySQL的安装4、安装后续处操作5、基本SQL语句6、存储引擎一、数据库概念数据库：DataBase，简称DB数据库就是存放数据的仓库，支持共享数据，增删改查数据的功能数据库管理系统：DataBaseManag... 查看详情

day41mysql

数据库索引1、索引是什么？索引一种数据数据结构，既结构数据，大白话说：索引就是一种组织数据的方式 那么索引到底如何组织数据的呢？​ 为表中的一条条记录创建建立索引就跟为书的一页页内容创建目录很类似... 查看详情

mysql数据库day03-数据库mysql的优化(代码片段)

数据库优化SQL优化SQL优化步骤查看SQL执行频率定位慢查询SQL分析SQL执行计划分析SQL执行耗费分析SQL优化器执行计划SQL优化实例批量插入大量数据优化主键顺序插入关闭唯一性校验关闭自动提交事务INSERT语句优化ORDERBY语句优化两... 查看详情

mysql高级--04--innodb数据存储结构---数据页结构

...自动生成，如何生成可参考右边的帮助文档文章目录数据库的存储结构:页==innoDB讲数据划分为若干个页,默认页的大小默认为16kb==行--页--区--段--表空间---数据库页的内部结构第一部分:文件头和文件尾1.1FileHeader... 查看详情

mysql-day01笔记(代码片段)

文章目录1、什么是数据库？什么是数据库管理系统？什么是SQL？他们之间的关系是什么？2、怎么在cmd里面使用命令启动和关闭服务？3、使用bin目录下的mysql.exe命令来连接mysql数据库服务器4、mysql常用命令࿱... 查看详情

mysql数据库day01-数据库mysql的基本概念(代码片段)

数据库MySQL的基本概念MySQ启动和登录MySQL索引基本概念索引特点索引结构索引分类索引语法索引原则MySQL视图基本概念视图语法存储过程和存储函数基本概念存储过程创建存储过程调用存储过程查看存储过程删除存储过程语法变... 查看详情

后端开发mysql基础知识-day4笔记(代码片段)

...1、索引（index）1.1、什么是索引？​索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。​一张表的一个字段可以添加一个索引， 查看详情