MySQL相关命令与备份

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 不加任何参数直接备份 mysqldump -uroot zabbix >/opt/zabbix.bak.sql恢复,这样恢复时需要自已创建表mysql -uroot /opt/mysql.bak.

不加任何参数直接备份

 mysqldump -uroot  zabbix >/opt/zabbix.bak.sql

恢复,这样恢复时需要自已创建表

mysql -uroot < zabbix.bak.sql

加-B备份的时候增加建表语句

mysqldump -uroot -p -B zabbix > /opt/mysql.bak.B.sql
diff zabbix.bak.sql zabbix.B.bak.sql
mysql -uroot -p < /opt/zabbix.B.bak.sql 

备份时压缩

[root@zabbix opt]# mysqldump -uroot -p --compact -B zabbix |gzip > /opt/zabbix.B.bak.compact.sql.gz
Enter password: 
[root@zabbix opt]# ll -h
total 72M
  w-r--r-- 1 root root  22M May 29 21:27 zabbix.bak.sql
?w-r--r-- 1 root root  22M May 29 21:33 zabbix.B.bak.compact.sql
-rw-r--r-- 1 root root 6.9M May 29 21:35 zabbix.B.bak.compact.sql.gz
-rw-r--r-- 1 root root  22M May 29 21:27 zabbix.B.bak.sql

[root@zabbix opt]# ls
mysqldump.txt  zabbix.bak.sql  zabbix.B.bak.compact.sql  zabbix.B.bak.compact.sql.gz  zabbix.B.bak.sql

恢复压缩备份

[root@zabbix opt]# gzip -d zabbix.bak.sql.gz 

备份多个库

[root@zabbix opt]# for i in `mysql -e "show databases;" |egrep -v "infor|perf|Database"` ; do mysqldump -uroot -p -B $i > $i.bak.sql;done
Enter password: 
Enter password: 
[root@zabbix opt]# 
[root@zabbix opt]# ls
mysql.bak.sql  zabbix.bak.sql
[root@zabbix opt]# ll -h
total 23M
-rw-r--r-- 1 root root 504K May 29 22:23 mysql.bak.sql
-rw-r--r-- 1 root root  22M May 29 22:23 zabbix.bak.sql

--events可以在给出警告的时候不提示警告

用sed命令拼接的方法备份

[root@zabbix opt]# mysql -e "show databases;" |egrep -v "infor|perf|Database"|sed -r 's#(.*)#mysqldump -uroot -p -B & > &.bak.sql#g'|bash
Enter password: 
Enter password: 
[root@zabbix opt]# ls
mysql.bak.sql  zabbix.bak.sql

用awk命令拼接的方法备份

[root@zabbix opt]# mysql -e "show databases;" |egrep -v "infor|perf|Database"|awk '{print "mysqldump -uroot -p -B "$0" > "$0".bak.sql"}'|bash
Enter password: 
Enter password: 
[root@zabbix opt]# ls
mysql.bak.sql  zabbix.bak.sql

备份所有库数据的同时还刷新binlog

[root@zabbix opt]# mysqldump -uroot  -p -A -B -F > mysql.all.sql
Enter password: 
[root@zabbix opt]# ll -h
total 45M
-rw-r--r-- 1 root root  23M May 29 23:13 mysql.all.sql
-rw-r--r-- 1 root root 504K May 29 22:29 mysql.bak.sql
-rw-r--r-- 1 root root  22M May 29 22:29 zabbix.bak.sql

命令

mysql -e相关命令

        show processlist;
            查看数据库里正在执行的SQL语句,可能无法看全完整SQL语句
        show full processlist;
            查看正在执行的完整SQL语句,完整显示
        set global key_buffer_size = 1024*1024*16
            不重启数据库调整数据库参数,直接生效,重启后失效
        show variables;
            查看数据库的配置参数信息,例如:my.cnf里参数的生效情况
            show variables like '%log_bin%';
全局生效的方法,改/etc/my.cnf,然后在mysql命令行set设置
MariaDB [(none)]> show variables like 'key_buffer%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 134217728 |
+-----------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> set global key_buffer_size=1024*1024*32;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show variables like 'key_buffer%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 33554432 |
+-----------------+----------+
1 row in set (0.00 sec)
    kill ID
        杀掉SQL线程的命令,ID为线程号
    show session status;
        查看当前会话的数据库状态信息。
    show global status;
        查看整个数据库运行状态信息,很重要,要分析并要做好监控。
    show engine innodb status;
        显示innodb引擎的性能状态(早期版本show innodb status;)

```

mysqladmin

        设置密码
            mysqladmin password oldboy123
        修改密码
            mysqladmin -uroot -poldboy123 password oldboy
        查看状态
            相当于show status;  mysqladmin -uroot -poldboy123 status;
        每秒查看一次状态
            mysqladmin -uroot -poldboy123 -i 1 status;
        等同show global status;
            mysqladmin -uroot -poldboy123 extended-status;
        切割日志
            mysqladmin -uroot -poldboy123 flush-logs
        查看执行的SQL语句信息
            mysqladmin -uroot -poldboy123 processlist;
            mysqladmin -uroot -poldboy123 processlist -i 1;    每秒查看一次
        关闭mysql服务
            mysqladmin -uroot -poldboy123 shutdown
        相当于show variables
            mysqladmin -uroot -poldboy123 variables;

mysqldump

        -B,--databases:会在备份的数据中增加建库(create)及"use库"的语句,可以直接接多个库名,同时备份多个库
        -A,--all-databases:备份所有数据库。
        -d,--no-data:只备份库表结构(SQL语句形式),没有行数据
        -t,--no-create-info:只备份表内行数据(SQL语句形式)。没有表结构
        -T,--tab=name:将库表和数据分离成不同的文件,行数据是纯文本,表结构是SQL语句,5.6版本默认没有权限操作,需要修改my.cnf参数。
        -F,刷新binlog日志,生成新binlog文件,
        --master-data={1|2},在备份结果中增加binlog日志文件名及对应的binlog位置点,会打开--lock-all-tables功能,除非有--single-transaction在,使用该参数时会关闭--lock-tables功能
        -x,--lock-all-tables,备份时对所有数据库的表执行全局读锁,期间同时禁止--single-transaction和--lock-tables参数功能
        -l,--lock-tables:锁定所有的表为只读
        --single-transaction,在备份InoDB引擎数据表时,隔离级别,备份数据不会备份后来写入的数据,用户能正常写入数据 
        -R,--routines:备份存储过程和函数数据
        --triggers,备份触发器数据
        --compact,只显示很少的有用输出,适合学习和测试环境调试

mysqlbinlog

binlog是用来记录mysql内部增删改查等对mysql数据库更新的内容的记录

如果在mysqlbinlog里面有drop语句,需要把drop语句删除才能恢复,不然会重新drop表
        -d,--database=name
            根据指定库拆分binlog(拆分单表binlog可通过SQL关键字过滤)
        -r,--result-file=name
            指定解析binlog输出SQL语句的文件
        -R,--read-from-remote-server
            从mysql服务器读取binlog日志,是下面参数的别名,read-from-remote-master=BINLOG-DUMP-NON-GTIDS

#指定位置进行恢复
        -j,--start-position=#
            读取binlog的起始位置点,#号是具体的位置点
        --stop-position=#
            读取binlog的停止位置点,#号是具体的位置点

#指定开始和结束时间开始恢复
        --start-datetime=name
            读取binlog的起始位置点,name是具体的时间,格式为:2004-12-25 11:25:56
        --stop datetime=name
            读取binlog的停止位置点,name是具体的时间



        --base64-output=decode-rows
            解析ROW级别binlog日志的方法,例如:mysqlbinlog-base64-output=decode-rows -v mysql-bin.000016

mysql


        -u   指定数据库用户
        -p  指定数据库密码
        -S  指定数据库socket文件
        -h  指定数据库主机,默认localhost
        -P  指定数据库端口,默认3306
        --default-character-sets=name    指定字符集登录数据库或备份

备份脚本

#!/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
USER=root
PASS='pwd'
TIME=`date +%y%m%d`
FULL_BAKDIR=/backup/full_bak
BINLOG_DIR=/backup/binlog_bak

mkdir -p ${FULL_BAKDIR}/${TIME}
mkdir -p $BINLOG_DIR

function DB_NAME(){
    mysql -u$USER -p$PASS -e "show databases;" 2>/dev/null |egrep -v "Database|information_schema|mysql|performance_schema"
    }


function FULL_BAK(){
    for i in `DB_NAME` ;do
           mysqldump -u$USER -p$PASS -R -F --triggers --single-transaction --master-data=1 -B $i 2>/dev/null |gzip > ${FULL_BAKDIR}/${TIME}/${i}_${TIME}.sql.gz
    done
    }

function BINLOG_BAK(){
    cd / && \
    tar zcf binlog_${TIME}.tar.gz application/mysql/data/mysql-bin* && \
    mv binlog_${TIME}.tar.gz /$BINLOG_DIR/
        find /backup/binlog_bak/ -name "binlog*.tar.gz" -mtime +3 | xargs rm -f
    }

function main(){
    FULL_BAK
    BINLOG_BAK
    }
main
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
1月前
|
存储 关系型数据库 MySQL
在CentOS 8.x上安装Percona Xtrabackup工具备份MySQL数据步骤。
以上就是在CentOS8.x上通过Perconaxtabbackup工具对Mysql进行高效率、高可靠性、无锁定影响地实现在线快速全量及增加式数据库资料保存与恢复流程。通过以上流程可以有效地将Mysql相关资料按需求完成定期或不定期地保存与灾难恢复需求。
139 10
|
3月前
|
存储 关系型数据库 MySQL
【赵渝强老师】使用select...into outfile语句备份MySQL
本文介绍了MySQL中使用`SELECT...INTO OUTFILE`语句将表数据导出为文本文件的方法。通过示例演示了如何备份员工表(emp)的数据,包括创建存储目录、设置权限、配置参数`secure_file_priv`以及解决相关错误的过程。字段分隔符和行终止符可自定义,确保数据格式符合需求。最后展示了备份文件的内容,验证操作成功。
258 36
|
6月前
|
关系型数据库 MySQL Shell
MySQL 备份 Shell 脚本:支持远程同步与阿里云 OSS 备份
一款自动化 MySQL 备份 Shell 脚本,支持本地存储、远程服务器同步(SSH+rsync)、阿里云 OSS 备份,并自动清理过期备份。适用于数据库管理员和开发者,帮助确保数据安全。
|
3月前
|
存储 SQL 关系型数据库
【赵渝强老师】使用mysqldump备份MySQL
本文介绍了 MySQL 自带的逻辑备份工具 mysqldump 的使用方法。通过 mysqldump,可以将数据库中的数据转换为对应的 SQL 插入语句,便于备份和还原。文章详细说明了如何备份所有数据库、指定数据库及特定表,排除某些表不备份的操作,以及删除数据库后如何通过备份文件恢复数据。同时提供了视频讲解和具体命令示例,帮助用户更好地理解和应用该工具。
175 5
|
3月前
|
存储 SQL 关系型数据库
【赵渝强老师】使用mydumper备份MySQL
本文介绍了使用mydumper工具进行MySQL数据库备份与恢复的操作方法。相比单线程工作的mysqldump,mydumper支持多线程,速度提升可达10倍。其功能包括事务性表快照、快速压缩、导出binlog等,并提供详细的参数说明和操作步骤。文章通过实例演示了安装mydumper、创建存储目录、全库备份、指定数据库及表备份、删除数据库以及使用myloader恢复数据的完整流程,并附带视频讲解,帮助用户更好地理解和应用该工具。
109 0
|
5月前
|
关系型数据库 MySQL Linux
在Linux环境下备份Docker中的MySQL数据并传输到其他服务器以实现数据级别的容灾
以上就是在Linux环境下备份Docker中的MySQL数据并传输到其他服务器以实现数据级别的容灾的步骤。这个过程就像是一场接力赛,数据从MySQL数据库中接力棒一样传递到备份文件,再从备份文件传递到其他服务器,最后再传递回MySQL数据库。这样,即使在灾难发生时,我们也可以快速恢复数据,保证业务的正常运行。
273 28
|
5月前
|
存储 关系型数据库 MySQL
利用Cron表达式实现MySQL数据库的定时备份
以上就是如何使用Cron表达式和mysqldump命令实现MySQL数据库的定时备份。这种方法的优点是简单易用,而且可以根据需要定制备份的时间和频率。但是,它也有一些限制,例如,它不能备份MySQL服务器的配置文件和用户账户信息,也不能实现增量备份。如果需要更复杂的备份策略,可能需要使用专门的备份工具或服务。
131 15
|
9月前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
10月前
|
关系型数据库 MySQL Linux
Linux环境下MySQL数据库自动定时备份实践
数据库备份是确保数据安全的重要措施。在Linux环境下,实现MySQL数据库的自动定时备份可以通过多种方式完成。本文将介绍如何使用`cron`定时任务和`mysqldump`工具来实现MySQL数据库的每日自动备份。
616 3
|
10月前
|
监控 关系型数据库 MySQL
Linux环境下MySQL数据库自动定时备份策略
在Linux环境下,MySQL数据库的自动定时备份是确保数据安全和可靠性的重要措施。通过设置定时任务,我们可以每天自动执行数据库备份,从而减少人为错误和提高数据恢复的效率。本文将详细介绍如何在Linux下实现MySQL数据库的自动定时备份。
306 3

推荐镜像

更多