常见的 MySQL 管工具
mysql 命令行 跨平台 MySQL 官方 bundle 包自带
MySQL-Workbench 图形 跨平台 MySQL 官方提供
MySQL-Front 图形 Windows 开源, 轻量级客户端软件
phpMyAdmin 浏览器 跨平台 开源, 需 LAMP 平台
Navicat 图形 Windows 专业功能强大商业版
PhpMyAdmin 部署思路:
1. 安装 httpdmysqlphp-mysql 及相关包
2. 启动 httpd 服务程序
3. 解压 phpMyAdmin 包, 部署到网站目录
4. 配置 config.inc.php, 指定 MySQL 主机地址
5. 创建授权用户
6. 浏览器访问登录使用
- yum -y install httpd php php-mysql
- tar -zxf phpMyAdmin-2.11.11-all-languages.tar.gz -C /var/www/html/
- cd /var/www/html/
- mv phpMyAdmin-2.11.11-all-languages/ phpmyadmin
- chown -R apache:apache phpmyadmin/
- cp phpmyadmin/config.sample.inc.php phpmysdmin/config.inc.php
- vim /var/www/html/phpmyadmin/config.inc.php
- $cfg[Servers][$i][host] = localhost;
- $cfg[blowfish_secret] = 123456;
- mysql>create database bbsdb;
- mysql>grant all on bbsdb.* to admin@localhost identified by 654321;
- systemclt start httpd
- firefox http://localhost/phpmyadmin
MySQL 数据备份与恢复
数据备份方式:
1. 物理备份, 冷备份: cp tar ....
例: 物理备份与恢复
备份操作格式:
cp -rp /var/lib/mysql / 数据库 备份目录 / 文件名
tar -zcvf xxx.tar.gz /var/lib/mysql / 数据库 /*
恢复操作格式:
cp -rp 备份目录 / 文件名 /var/lib/mysql/(默认存储路径)
tar -zxvf xxx.tar.gz -C /var/lib/mysql / 数据库名 /
2. 逻辑备份, mysqldump mysql
原理: 执行备份时, 根据已有的库和表生成对应 SQL 命令, 把生成的 SQL 命令存储到指定的备份文件里
备份策略:
完全备份: 备份所有数据(一台服务器, 一个库, 一张表)
增量备份: 备份自上一次备份 (包含完全备份差异备份增量备份) 之后有变化的数据
差异备份: 备份自上一次完全备份之后有变化的数据
例: 逻辑备份及恢复
完全备份操作格式:
mysqldump -u 用户名 -p 密码 源库名 > 路径 / xxx.sql
完全备份的恢复操作格式:
mysql -u 用户名 -p 密码 目标库名 < 路径 / xxx.sql
库名的表示方式:
--all-databases 所有库
库名 指定的单个库
库名. 表名 指定库的指定表
-B 库名 1 库名 2 .. 备份多个库
注意事项:
无论备份还是恢复, 都要验证用户及权限
例:
将所有的库备份为 mysql-all.sql 文件
mysqldump -u root -p 123456 --all-databases > alldb.sql
将 userdb 库备份为 userdb.sql 文件
mysqldump -u root -p 123456 userdb > userdb.sql
将备份文件 userdb.sql 恢复到 userdb3 库
- mysql > create databases userdb3;
- mysql - u roo - p 123456 userdb3 < userdb.sql
实时增量备份
binlog 日志
类型: 二进制日志, 用途: 记录所有更改数据的操作,
配置:
log_bin[=dir/name]
server_id = 数字
max_binlog_size = 数字 m
启用 binlog 日志
采用 binlog 日志的好处
1. 记录除查询之外的所有 SQL 命令
2. 用于数据恢复
3. 配置 mysql 主从同步的必要条件
例:
- vim /etc/my.cnf
- [mysqld]
- ....
- log_bin // 启用 binlog 日志
- server_id=100 // 指定 id 值, id 号不能重复 (1-255) 范围
- systemctl restart mysqld
binlog 相关文件
默认日志文件名:
主机名 - bin.index // 记录已有的 binlog 日志文件名
主机名 - bin.000001 // 第 1 个二进制日志(达到 500M 存去下一条纪录)
主机名 - bin.000002 // 第 2 份二进制日志
....
手动生成新的日志文件
1. 重启 mysql 服务
2. 执行 SQL 操作 mysql > flush logs;(flush logs, 切换到下一个 binlog 日志文件)
3.mysqldump --flush-logs
4.mysql -uroot -p 密码 -e flush logs
清理 binlog 日志
删除早于指定版本的 binlog 日志:
purge master logs to binlog 文件名;
删除所有 binlog 日志, 重建新日志:
reset master;
例:
- mysql > purge master logs to mysql - bin.000003;
- mysql > reset master;
分析 binlog 日志
查看日志当前记录格式:
mysql>show variables like binlog_format;
修改日志记录格式:
- vim /etc/my.cnf
- [mysqld]
- ....
binlog_format="mixed" 设置日志文件的记录格式
systemctl restart mysqld
三种记录格式:
1.statement: 每一条修改数据的 sql 命令都会记录在 binlog 日志中
2.row: 不记录 sql 语句上下文相关信息, 仅保存哪条记录被修改
3.mixed: 是以上两种格式的混合使用
binlog 日志文件记录 sql 命令的方式:
1. 时间点
2.pos 点(偏移量)
使用 mysqlbinlog 工具
格式: mysqlbinlog [选项] binlog 日志文件名
常用选项:
1. 时间点:
- --start-datetime="yyyy-mm-dd hh:mm:ss"
- --stop-datetime="yyyy-mm-dd hh:mm:ss"
2.pos 点:
--start-position = 数字
--stop-position = 数字
例:
查看从 2017 年 1 月 2 日 15:30 开始的更改操作
- mysqlbinlob --start-datetime="2017-01-01 15:30" /var/lib/mysql-bin.000001
- ....
- at 318
binlog 恢复数据
基本思路:
1. 使用 mysqlbinlog 提取历史 SQL 操作
2. 通过管道交给 mysql 命令执行
例:
恢复第 1 份 binlog 日志的部分信息
mysqlbinlog --start-position=296 --start-position=1073 /var/lib/mysql-bin.000001 |mysql -uroot -p123456
MySQL 备份工具
物理备份缺点:
1. 跨平台性差
2. 备份时间长冗余备份浪费存储空间
mysqldump 备份缺点:
1. 效率较低, 备份和还原速度慢
2. 备份过程中, 数据插入和更新操作会被挂起
XtraBackup 备份工具:
1. 备份过程中不锁库表, 适合生产环境
2. 由专业组织 Percona 提供(改进 MySQL 分支)
主要含两个组件:
1.xtrabackup:C 程序, 支持 InnoDB/XtraDB
2.innobackupex: 以 Perl 脚本封装 xtrabackup, 还支持 MyISAM
支持事务和事务回滚, 要求存储引擎为 innodb
事务日志文件:
ibdata
LSN 日志序列号
- ib_logfile0 //SQL 命令
- ib_loggile1
- ....
安装 XtraBackup
- yum -y install per-Digest-MD5.x86_64 rsync perl-DBD-MySQL
- rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
- rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
- rpm -ql percona-xtrabackup-24
- /usr/bin/innobackupex // 备份 innodbxtrdbmyisam 引擎的表
- /usr/bin/xbcloud
- ....
- /usr/bin/xtrabackup // 备份 innodbxtrdb 引擎的表
- ....
innobackupex 基本选项 --host // 主机名
- --user // 用户名
- --port // 端口号
- --password // 密码
- --databases // 数据库名(单个库: databases="库名", 多个库: databases="库 1 库 2", 单个表: databases="库. 表")
- --no - timestamp // 不用日期命名备份文件存储的子目录名
- --redo - only // 日志回滚合并(最后一次增量备份日志回滚不需要此选项)
- --apply - log // 准备还原(回滚日志)
- --copy - back // 恢复数据
--incremental 目录名 // 增量备份
--incremental - basedir = 目录名 // 增量备份时, 指定上一次备份数据存储的目录名
--incremental - dir = 目录名 // 准备恢复数据时, 指定增量备份数据存储的目录名
- --export // 导出表信息
- import // 导出表空间
XtraBackup 完全备份
格式: innobackupex -user 用户名 --password 密码 --databases="系统库列表和存储数据库" 备份目录名 --no-timestamp
例: 将所有库完全备份到 /backup
innobackupex --user root --password 123456 /backup --no-timestamp
XtraBackup 完全恢复
完全恢复时要求空的库目录
- rm -rf /var/lib/mysql
- mkdir /var/lib/mysql
- chown -R mysql:mysql /var/lib/mysql
格式: innobackupex -user 用户名 --password 密码 --databases="系统库列表和存储数据库" --copy-back 备份目录名
例: 恢复所有数据
innobackupex --user root --password 123456 --copy-back /backup
XtraBackup 增量备份
必须先有一次完全备份
格式: innobackupex --user 用户名 --password 密码 databases="系统库列表和存储数据库" --incremental 目录名 --incremental-basedir="完全备份目录名" -no-timestamp
例: 完全备份到 / allbak 第一次增量备份到 / new1 第二次增量备份到 / new2
- cp -rp /var/lib/mysql/mysql /root/mysql.bak // 备份授权库
- innobackupex --user root --password 123456 --databases="gamedb" /fullbak --no-timestamp // 完全备份
- innobackupex --user root --password 123456 --databases="gamedb" --incremental /new1 --incremental-basedir="/fullbak" --no-timestamp // 第一次增量备份
- innobackupex --user root --password 123456 --databases="gamedb" --incremental /new2 --incremental-basedir="/new1" --no-timestamp // 第二次增量备份
XtraBackup 增量恢复
- rm -rf /var/lib/mysql
- mkdir /var/lib/mysql
- chown -R mysql:mysql /var/lib/mysql
格式:
1.innobackupex --user 用户名 --password 密码 --databases="系统库列表和存储数据库" --apply-log --redo-only 完全备份目录名
2.innobackupex --user 用户名 --password 密码 --databases="系统库列表和存储数据库" --apply-log --redo-only 完全备份目录名 --incremental-dir = 增量备份目录名
3.innobackupex --user 用户名 --psssword 密码 --databases="系统库列表和存储数据库" --copy-back 完全备份目录名
例: 恢复第一次增量备份到 / new1 第二次增量备份到 / new2 的数据 rm - rf /
- var / lib / mysql mkdir /
- var / lib / mysql innobackupex--user root--password 123456--databases = "gamedb"--apply - log--redo - only / fullbak // 恢复完全备份
- innobackupex--user root--password 123456--databases = "gamedb"--apply - log--redo - only / fullbak--incremental - dir = "/new1" // 恢复增量
- innobackupex--user root--password 123456--databases = "gamedb"--apply - log / fullbak--incremental - dir = "new2" // 恢复增量
- innobackupex--user root--password 123456--databases = "gamedb"--copy - back / fullbak // 拷贝文件
- cp - r / root / mysql.bak /
- var / lib / mysql / mysql chown - R mysql: mysql /
- var / lib / mysql
恢复完全备份文件中的单个表
格式: innobackupex innobackupex --user 用户名 --password 密码 --databases="系统库列表和存储数据库" --apply-log --export 完全备份目录名
例: 完全备份数据库到 / allbak 目录
- innobackupex --user root --password 123456 --databases="gamedb" /allbak --no-timestamp // 完全备份
- mysql>drop table gamedb.a;
- innobackupex --user root --password 123456 --databases="gamedb" --apply-log --export /allbak // 导出表信息
- mysql>create table gamedb.a(id int); // 创建表
- mysql>alter table gamedb.a discard tablespace; // 删除表空间
- cp /allbak/gamedb/a.{ibd,cfg,exp} /var/lib/mysql/gamedb // 拷贝表信息文件
- chown mysql:mysql /var/lib/mysql/gamedb/a.* // 修改所有者
- mysql>alter table gamedb.a import tablespace; // 导入表空间
来源: http://www.bubuko.com/infodetail-2493610.html