Mysql基础(五)日常维护工具备份

MySQL字符集

字符集介绍:

字符集就是一套文字符号及其编码、比较规则的集合,第一个计算机字符集ASC2!
MySQL数据库字符集包括字符集(CHARACTER)和校对规则(COLLATION)两个概念,其中字符集用来定义MySQL数据字符串的存储方式而校对规则定义比较字符串的方式.

mysql常见的字符集

字符的类型 格式 相关解释
GBK 2 不是国际标准
utf-8 3 中英文混合环境,国际标准
latin1 1 MYsql默认字符集
utf8mb4 4 UTF8 Uniconde

查看常用的字符集

show character set\G;" |egrep "gbk|utf8|latin1"|awk '{print $0};
查看mysql当前字符集设置情况
show variables like 'character_set%';

设置默认字符集

vim /etc/my.cnf
        写文件
        default_character_set=uft8
        [mysql] 【客户端】
        default_character_set=utf8
        保存退出

数据备份的目的

数据误删除或在数据或设备损坏的时候,导致数据的丢失,使用备份恢复数据。

数据库的备份方式

  1. 物理备份:指定备份库和表对应的文件。

    cp -r /var/lib/mysql /要备份到的路径
    换一台新的服务器拷贝回去
    chown -R mysql:mysql /var/lib/mysql/文件
    systemctl restart mysqld

  2. 逻辑备份:在执行备份命令时候,根据备份的库和表以及数据生成对应的SQL命令,把sql存储到指定的文件中。

    mysql -uroot -p密码 test > test.sql

数据备份策略

  • 完全备份:备份所有数据(一张表的所有数据 一个库的所有数据 一台数据库的所有数据)
  • 差异备份:备份自完全备份后,所有新产生的数据
  • 增量备份:备份自上一次备份后,所有新产生的数据

选择备份的策略

  • 完全备份+差异备份
  • 完全备份+增量备份
  1. 数据备份的时间:数据访问量少的时候
  2. 数据备份的频率:根据数据产生的量来设置备份的频率
  3. 备份文件的命名:库名-日期.sql
  4. 备份文件的存储设置:准备独立的存储设备存储备份文件
  5. 如何执行备份:使用周期性计划任务执行本机器的脚本

完全备份的缺点

  • 备份和恢复数据会给表加上写锁
  • 数据量大时候,备份或者恢复数据都会受到磁盘I/O的影响

增量备份与增量恢复

  1. 启动mysql数据库服务的binlog日志文件实现实时的增量备份
  2. binlog日志
  3. 启用binlog方法
  4. 查看binlog方法
  5. 手动生成新的binlog日志的方法
  6. 删除以经有的binlog日志
  7. binlog日志记录sql命令的方式
  8. 使用binlog日志恢复数据

实际中的操作:

1.开启binlog日志

vim /etc/my.cnf
        [mysqld]
        log-bin                         #开启binlog日志
        #log-bin=绝对路径           #指定一个存储位置
        server_id=51                    #指定主机名
        binlog-format='mixed'       #设置记录模式
        #模式一:只记录sql
        #模式二:只记录数据改变(默认)
        #模式三:两者都记录
systemctl restart mysqld
ls /var/lib/mysql/*-bin.*       #文件大于500M会自动生成第二个日志文件
如果需要单独指定binlog日志文件位置:
        1.mkdir /路径
        2.chown mysql:mysql /路径 
        3.修改相应的配置文件
刷新日志:
        mysql> flush logs       #刷新binlog日志生成新的日志文件

2.使用binlog日志恢复数据

mysqlbinlog [选项] /日志的指定位置 | mysql -uroot -p密码 
在日常工作中使用单个binlog日志记录某一个数据库的操作有利于后期对数据库的恢复

3.删除已经有的binlog日志文件

mysql > reset master;       #删除所有的日志文件重新生成第一个日志文件
mysql> purge master logs to "binlog文件名字(000005)";       删除指定日志文件之前的日志文件
系统命令删除日志但是索引文件不会同步删除,需要单独进index文件中删除

binlog日志记录sql命令的方式

记录的方式2种

  • 偏移量
  • 记录SQL命令执行的时间

    mysqlbinlog [选项] /mylog/plj.000008 | mysql -uroot -p654321
    【选项】指定时间范围的选项
    –start-datetime=”yyyy-mm-dd hh:mm:ss”
    –stop-position=”yyyy-mm-dd hh:mm:ss”

        【选项】指定偏移量范围选项
        --start-position=偏移量的值 --stop-position=偏移量的值
    

实战案例:

公司平时的例子(完全备份和增量备份)
        步骤一:[root@pc01]#mysqldump -uroot -p123456 –flush-logs db3.user3 > /root/user3.sql       #首先完全备份,然后重新生成一个binlog日志 
        步骤二:对数据库db3的数据表user3进行数据插入操作
        步骤三:mysql> delete from db3.users;                   #模拟删除该表的数据
        步骤四:[root@pc01]# mysql -uroot -p123456 db3 < /root/use3.sql             #首先恢复完全备份的数据
        步骤五:[root@pc01]#mysqlbinlog –start-potition=229 –stop-position=1490 /mylog/plj.000008 | mysql -uroot -p123456           #根据binlog日志恢复增量的数据

实战迁移数据

背景:

公司业务数据book,由于之前建表没注意字符集的问题,导致之前写入的数据出现乱码。
现在要将之前的数据和现在数据的字符集一致,不出现乱码情况
【将字符集为latin1已有记录的数据转成utf8,并且已经存在的记录不乱码】

步骤:

  • 1 -> 建库及建表的语句导出,sed批量修改为utf8
  • 2 -> 导出之前所有的数据
  • 3 -> 修改mysql服务端和客户端编码为utf8
  • 4 -> 删除原有的库表及数据
  • 5 -> 导入新的建库及建表语句
  • 6 -> 导入之前的数据

执行命令:

1.导出表结构

mysqldump -uroot --default-character-set=结构名 -d 表名>导出位置及其名称 --default-chararcter-set=utf8

2.编辑文件修改字符集

:%s/latin1/utf8/g 修改后保存退出

3.确保数据库不再更新,导出所有数据

mysqldump --quick --no-create-info --extended-insert --default-charaacter-set=latin1 表名>保存路径及其文件名
参数说明:
        --quick:用于转储大的表,强制mysqldump从服务器一次一行的检索数据而不是检索所有行,并输出当前cache到内存中
        --no-create-info:不要创建create table语句
        --extended-insert:使用包括几个values列表的多行insert语法,这样文件更小,I/O也小,导入数据时会很快
        --default-chararcter-set:按照原有的字符集导出数据,这样导出的文件中,所有文件都是可见的,不会保存成乱码

4.创建新的表

mysql -e 'create table 表名' -r -p

5.导入表结构和数据

mysql -r -p 创建的表名<导入的表结构/导入的表数据

MYSQL日常维护

MYSQLCHECH修复工具

mysqlcheck客户端工具可以检查和修复MyISAM表,还可以优化和分析表。
实际上,它集成了mysql工具中check、repair、analyze、optimize的功能。

功能

/usr/local/mysql/bin/mysqlcheck   #源码编译安装位置
rpm -qf `which mysqlcheck`  yum安装查看

mysqlcheck的参数

mysqlchech --help 参数:
    -c,--check (检查表);
    -r,--repair(修复表);
    -a,--analyze (分析表);
    -o,--optimize(优化表);//其中,默认选项是-c(检查表)
    -u, 使用mysql中哪个用户进行操作

mysqlcheck语法

mysqlcheck[options]  db_name [tables]
mysqlcheck[options] ---database DB1 [DB2 DB3...]
mysqlcheck[options] --all--database
例子:
    1.检查表
            mysqlcheck -uroot -p123456 -c book books
    2.修复表
            mysqlcheck -uroot -p123456 -r book books
    3.修复指定的数据库
            mysqlcheck -uroot -p  -r --database book
    4.扩展: 修复文件系统。 容易掉失数据
            fsck -y -f /dev/sdaX
    5.每天定时对mysql数据库进行优化(使用周期任务)一般不需要每天
            crontab –e
    0 1 * * * mysqlcheck -A -o -r  -u你的用户名 -p你的密码 > /dev/null 2>&1
    0 3 * * * mysqlcheck -uroot -p123456 -r -o -A > /dev/null 2>&1 每天3点优化

MySQL备份恢复

mysql的备份类型:

  • Hot backup(热备份):指在数据库运行中直接备份,对正在运行的数据库没有任何影响。(Online Backup)官方手册为在线备份
  • Cold backup(冷备份):指在数据库停止的情况下进行备份(OfflineBackup) 官方手册称为离线备份
  • warm backup(温备份):备份同样在数据库运行时进行,但是会对当前数据库的操作有所影响,例如加一个全局读锁以保证备份数据的一致性

根据备份的内容:

逻辑备份:

指备份后的文件内容是可读的,通常为文本文件,内容一般是SQL语句,或者是表内的实际数据。
如mysqldump和SELECT * INTO OUTFILE的方法,一般适用于数据库的升级和迁移,恢复时间较长

裸文件备份:

拷贝数据库的物理文件,数据库既可以处于运行状态(mysqlhotcopy、ibbackup、xtrabackup这类工具)
也可以处于停止状态,恢复时间较短。

按照备份数据库的内容来分,又可以分为:

  • 完全备份:对数据库完整的备份
  • 增量备份:在上一次完全备份基础上,对更新的数据进行备份(xtrabackup)
  • 日志备份:二进制日志备份,主从复制

逻辑备份工具mysqldump

介绍:使用的时候MySQL当要导入或者导出数据量大的库的时候,用PHPMYADMIN甚至MySQLAdministrator这些工具都会力不从心.
这时只能使用MySQL所提供的命令行工具mysqldump进行备份恢复。数据量大的时候不推荐使用,可支持MyISAM,InnoDB

数据库库名的标示方式?

  • 库名 表名 备份一张表的所有数据
  • 库名 备份一个库的所有数据
  • –all-databases 或 -A
  • -B 库名1 库名2 库名N 把多个库的所有数据备份到一个文件里面

导出数据:

语法: mysqldump [OPTIONS]database [tables] >导出的文件名.sql
实际操作导出:
        1.导出所有数据库
            mysqldump -uroot -p123456 -A >all.sql
            参数-A代表所有,等同于--all-databases
        2.导出某个数据库
            mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql       # 注意是-p空格后是数据库名,不是密码。
        3.导出单张表
            mysqldump -uroot -p123456 库名 表名 >导出的文件名     #导出book库books表
        4.导出库的表结构
            mysqldump -uroot -p123456 -d 库名>导出的文件名          #只导出book库的表结构
        5.只导出数据
            mysqldump -uroot -p123456 -t 库名>出的文件名               #只导出book库中的数据
        6.导出数据库,并自动生成库的创建语句
            mysqldump -uroot -p123456 -B book2 >book2.sql
            mysql -uroot -p123456 < book2.sql                           #导入不用指定数据名

实际操作导入:

    1.导入数据库source交互界面:
            mysql> create database book;
            mysql> use book;
            mysql> source /root/book.sql
    2.导入表:
            mysql> drop table books;
            mysql> source /root/books.sql;   #导入表时,不需要重新,创建表。要先进到相应的数据库中
                mysql> select * from books;
    3.导入表结构和数据:
            mysql> create database book;
            mysql -uroot -p123456 book<booktable.sql
            mysql -uroot -p123456 book<bookdata.sql

mysqlhotcopy备份

介绍:mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,
但只能用于备份MyISAM存储引擎和运行在数据库目录所在的机器上.与mysqldump备份不同,mysqldump属于逻辑备份,备份时是执行的sql语句.

  • 本质:Mysqlhotcopy本质是使用锁表语句后再使用cp或scp拷贝数据库

1.安装mysqlhotcopy所依赖的软件包(perl-DBD)

yum install perl-DBD* -y

2.查看mysqlhotcopy帮助信息:

mysqlhotcopy –help
        --allowold   don't abort if targetdir already exists (rename it _old)       #不覆盖以前备份的文件
        --addtodest  don't rename targetdir if it exists, just add files to it       #属于增量备份
        --noindices   don't include full index files in copy                            #不备份索引文件
        --debug     enable debug                                                          #启用调试输出
        --regexp=#   copy all databaseswith names matching regexp             #使用正则表达式
        --checkpoint=#  insert checkpointentry into specified db.table           #插入检查点条目
        --flushlog      flush logs once all tables are locked                         #所有表锁定后刷新日志
        --resetmaster    reset the binlog once all tables are locked                  #一旦锁表重置binlog文件
        --resetslave  reset themaster.info once all tables are locked               #一旦锁表重置master.info文件

举例说明:

备份一个数据库到一个目录中

mysqlhotcopy -u root -p qaz520133 book /tmp
        ->Locked 2 tables in 1 seconds.
        ->Flushed tables (`book`.`books`, `book`.`category`) in 0 seconds.
        ->Copying 7 files...
        ->Copying indices for 0 files...
        ->Unlocked tables.

对比文件是否一致

du -h /tmp/book /var/lib/mysql/book
        ->48K   /tmp/book
        ->48K   /var/lib/mysql/book

备份多个数据库到一个目录中

mkdir /opt/book-mysql 
mysqlhotcopy -u用户名 -p密码 数据库名 数据库名 要备份到的路径

备份数据库中的某个表

语法:mysqlhotcopy -u用户名 -p密码 数据库名./要备份的表名/ 要备份到的路径
本质:事实上就是把mysql的文件复制粘贴
    例子:
            (1)找到本地数据的地址:cat /etc/my.cnf 找到datadir就是数据所在的本地位置
            (2)破坏数据库:rm -rf /数据库位置/book/
            (3)查询一下:mysql -e 'show databases'或者是查询表mysql -e 'use 库名;show tables'
            (4)恢复数据(-a 权限和属性):cp -ra /备份出来的文件绝对路径/ /datadir的文件路径/
            (5)查询是否恢复:mysql -e 'show databases'或者是查询表mysql -e 'use 库名;show tables'

总结:

mysqldump和mysqlhotcopy的比较:
        1、mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,数据库大时,占用系统资源较多,支持常用的MyISAM,innodb
        2、mysqlhotcopy只是简单的缓存写入和文件复制的过程,占用资源和备份速度比mysqldump快很多很多。特别适合大的数据库,但需要注意的是:mysqlhotcopy只支持MyISAM 引擎
        3、mysqlhotcopy只能运行在数据库目录所在的机器上,mysqldump可以用在远程客户端。
        4、相同的地方都是在线执行LOCK TABLES 以及UNLOCK TABLES
        5、mysqlhotcopy恢复只需要COPY备份文件到源目录覆盖即可,mysqldump需要导入SQL文件到原来库中。

xtrabackup备份工具使用

1、xtrabackup简介

Percona开源软件,不锁表生产环境中使用比较多, 针对InnoDB存储引擎,MySQL本身没有提供合适的热备工具,ibbackup虽是一款高效的首选热备方式,但它是是收费的。
好在Percona公司给大家提供了一个开源、免费的Xtrabackup热备工具,它可实现ibbackup的所有功能,并且还扩展支持真正的增量备份功能,是商业备份工具InnoDBHotbackup的一个很好的替代品。

Xtrabackup包括两个主要工具

  • Xtrabackup只能备份InnoDB和XtraDB两种引擎表,而不能备份MyISAM数据表。
  • innobackupex则封装了xtrabackup,同时可以备份MyISAM数据表。
  • Xtrabackup做备份的时候不能备份表结构、触发器等等,智能区分.idb数据文件。另外innobackupex还不能完全支持增量备份,需要和xtrabackup结合起来实现全备的功能

2、xtrbackup 安装

方法一:yum安装
     rpm -Uvh https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-3.noarch.rpm
     yum list | grep percona
     yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL 安装依赖
     yum install percona-xtrabackup –y

方法二:rpm安装
     依赖解决:rpm -Uvh ftp://rpmfind.net/linux/epel/6/x86_64/libev-4.03-3.el6.x86_64.rpm  安装这个库解决依赖
     rpm -Uvh https://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.8/RPM/rhel6/x86_64/percona-xtrabackup-20-2.0.8-587.rhel6.x86_64.rpm

方法三:源码安装
     wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.2/binary/tarball/percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz
      tar -xf percona-xtrabackup-2.3.2-Linux-x86_64.tar.gz
     cp percona-xtrabackup-2.3.2-Linux-x86_64/bin/* /usr/bin/

注意:

安装完成以后记得更改你的/etc/my.cnf配置文件制定数据目录,因为Xtrabackup是根据你的/etc/my.cnf配置文件来获取你备份的文件
比如在/etc/my.cnf的[mysqld] 下添加datadir=/usr/local/mysql/data,然后重启mysql
vim /etc/my.cnf
        [mysqld]
        datadir=/usr/local/mysql/data
        basedir=/usr/local/mysql
        [root@xuegod63 ~]# service mysqld restart

3、xtrbackup使用

我们一般使用innobackupex脚本
innobackupex是perl脚本对xtrabackup的封装,和功能扩展。

备份工作:

权限和链接
    trabackup需要连接到数据库和datadir操作权限。
    xtrabackup或者innobackupex在使用过程中涉及到2类用户权限:

系统用户,用来调用innobackupex或者xtrabackup
    连接到服务:innobackupex或者xtrabackup通过—user和—password连接到数据库服务
     innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/      innobackupex --user=LUKE  --password=US3TH3F0RC3 --stream=tar ./ | bzip2 -   压缩
    $ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/

其他连接选项:

Option  Description
    –port        要连接的指定端口
    –socket       socket文科
    –host        指定要连接的IP
    一般时候可以单独创建用来备份数据库的用户,安全,并赋予对应的权限

发表评论