MySQL数据库Day04-数据库MySQL的高级使用

MySQL数据库Day04-数据库MySQL的高级使用

数据库MySQL的高级使用

​​MySQL常用工具​​

​​mysql​​​​mysqladmin​​​​mysqlbinlog​​​​mysqldump​​​​mysqlimport​​​​source​​​​mysqlshow​​

​​MySQL日志​​

​​错误日志​​​​二进制日志​​

​​日志格式​​​​日志读取​​​​日志删除​​

​​查询日志​​​​慢查询日志​​

​​MySQL主从复制​​

​​MySQL主从复制搭建​​

​​master​​​​slave​​

MySQL常用工具

mysql

客户端工具mysql基本语法:

mysql [options] [database]

选项options包括:

连接选项执行选项

连接选项:

mysql -h 127.0.0.1 -P 3306

执行选项:

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 ....

基本选项:

mysqldump

mysqldump:

客户端工具mysqldump可以用来备份数据库以及在不同的数据库之间进行数据迁移客户端工具mysqldump的可以用来备份创建表以及数据,插入表以及数据的SQL语句

基本语法:

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

基本选项:

nysqldump -uroot -proot db_name table_name --add-drop-database --add-drop-table >

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]

基本参数:

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

MySQL日志

MySQL数据库服务器有4种不同的日志:

错误日志二进制日志查询日志慢查询日志

错误日志

错误日志:

MySQL数据库服务器中的错误日志记录MySQL数据库服务器启动,停止以及在运行过程中发生严重错误时的相关信息当MySQL数据库服务器出现错误问题导致无法正常使用时,可以首先查看错误日志

MySQL数据库服务器中的错误日志:

错误日志默认是开启的错误日志默认存放的目录为MySQL数据库服务器的数据目录var/lib/mysql错误日志默认的文件名称为主机名.err

查看错误日志文件位置的MySQL命令:

show variables like 'log_error%';

查看错误日志文件内容的命令:

tail -f /var/lib/mysql/localhost.errcat

二进制日志

二进制日志binlog:

记录所有的数据定义语句DDL和数据操纵语句DML,不记录数据查询语句DQL二进制日志可以用于MySQL数据库服务器的容灾恢复MySQL数据库服务器的主从复制就是通过二进制日志实现的

二进制日志默认情况下是没有开启的,需要到MySQL的配置文件/usr/my-f中配置二进制日志的开启以及二进制日志的格式

# 配置开启二进制日志,指定日志文件名前缀为mysqlbinlog_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-f中配置开启生成的查询日志文件保存在MySQL的数据目录/var/lib/mysql中

# 开启查询日志的选项. 可选值包括 1-开启, 0-关闭general_log=1# 指定查询日志的文件名的选项. 如果未指定,默认文件名为 hostname.loggeneral_log_file=query_log.log

使用cat命令查看MySQL数据库服务器的查询日志

cat

慢查询日志

慢查询日志:

慢查询日志记录MySQL数据库服务器中执行时间超过参数long_query_time指定的时间并且扫描的记录数不小于min_examined_row_limited的SQL语句参数long_query_time的默认值为10s,最小可以为0s,可以精确到微秒慢查询默认是关闭的,可以在my-f中配置参数来开启MySQL数据库服务器的慢查询日志

# 开启慢查询日志的选项. 可选值包括 1-开启, 0-关闭slow_query_log=1# 指定慢查询日志的文件名选项slow_query_log_file=slow_query_log.log# 指定慢查询的时间限制的选项,默认为10s. 执行时间超过这个指定时间的SQL语句就认为是慢查询,这条SQL语句就会记录到慢查询日志中long_query_time=10

慢查询日志的读取:

慢查询日志以纯文本文件格式记录的,可以使用cat命令直接读取

cat

慢查询日志以纯文本文件格式记录的,可以使用tail命令实时查看慢查询日志的记录

tail

如果存在大量的慢查询日志内容,可以使用mysqldumpslow对慢查询日志进行分类汇总查看

mysqldumpslow slow_query_log.log

MySQL主从复制

主从复制:

将主数据库的数据定义语句DDL和数据操作语句DML通过二进制日志的形式传输到从数据库服务器中在从数据库服务器中重新执行这些二进制日志的数据定义语句DDL和数据操作语句DML这样使得从数据库服务器中的数据和主数据库服务器中的数据保持同步

MySQL主从复制:MySQL数据库服务器支持一台主库同时向多台从库进行主从复制MySQL数据库服务器中的从库同时也可以作为其余从库服务器的主库进行主从复制这样可以实现MySQL数据库服务器的链状复制MySQL主从复制原理:

MySQL主从复制解决的问题:

主数据库服务器如果出现问题,可以立即切换到从数据库服务器提供服务,不会对运行的服务造成影响可以在主数据库服务器上执行更新操作,在从数据库服务器上执行查询操作,实现读写分离,减小主数据库服务器的访问压力可以在从数据库服务器上执行数据库中的数据备份,这样可以避免在数据备份期间影响主数据库服务器上的数据库操作

MySQL主从复制搭建

master

在MySQL主节点master数据库服务器配置文件**/usr/my-f**中添加如下配置:

# 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主从同步的服务器上数据库账号进行授权:

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-f中添加如下配置:

# MySQL的服务ID,要保证在整个集群中的服务ID唯一server-id=1# MySQL中二进制日志binlog的存储路径log-bin=/var/lib/mysql/mysqlbin

重启MySQL从节点slave的MySQL服务:

service

使用命令行指定当前从库对应的主库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: YesSlave_SQL_Running: Yes

show slave status\G;

可以使用命令在从节点slave上停止主从复制的同步操作:

stop slave;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。