mysql冷备份，转存sql，物理备份，xtrabackup

在用windows时，转存mysql我都是用navicat，登录远程数据库，

然后用图形界面转存sql。

在linux下，如何做呢？

1.linux下转存sql文件

表级别备份

mysqldump [OPTIONS] [database] [tables]

库级别备份

mysqldump [OPTIONS] --databases DB1 [DB2 DB3...]

全库级别备份

mysqldump [OPTIONS] --all-databases [OPTIONS]

# mysqldump -u root -p tb_test tb_order > /sql/tb_order.sql
# mysqldump -u root -p --databases tb_test > /sql/tb_test.sql

# 全库备份有特别的参数要填写
# mysqldump -u root -p --all-databases --master-data --single-transaction > all.sql
# 有可能会提示
# mysqldump: Error: Binlogging on server not active
# 这是说要开启binlog功能，在配置文件my.cnf中（路径需自己找到）

# vi my.cnf
# 开启log-bin的注释，并修改
# log-bin=/usr/local/mysql/data/binlog
# 路径可以自定义，但必须要格式正确。
# 保存，重启服务
# service mysql restart

常用参数：
--flush-logs, -F   		开始备份前刷新日志(二进制日志)
--flush-privileges  	备份包含mysql数据库时刷新授权表
--lock-all-tables, -x   MyISAM一致性，服务可用性（针对所有库所有表）
--lock-tables, -l       备份前锁表（针对要备份的库）
--single-transaction    适用InnoDB引擎，保证一致性，服务可用性
--master-data=2
表示将二进制日志位置和文件名写入到备份文件并在dump文件中注释掉这一行；
--master-data=1
表示将二进制日志位置和文件名写入到备份文件,在dump文件中不注释这一行；
--master-data参数其他说明：
1）恢复时会执行，默认是1
2）需要RELOAD privilege并必须打开二进制文件
3）这个选项会自动打开--lock-all-tables，关闭--lock-tables

 2.导入sql，恢复sql文件

若仅是一个表的sql则
# mysql -u root -p 库名 < *.sql

若是库级sql，或全库sql则
# mysql -u root -p < *.sql

3.逻辑导出

mysql> select  字段  into outfile '路径' from 库名.表名; 
例如
mysql> select * into outfile '/sql/tb_order' from tb_test.tb_order;

若提示
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
或
ERROR 1 (HY000): Can't create/write to file '/tmp/backup/emp.bak' (Errcode: 13 - Permission denied)

则需要在my.cnf中增加

secure_file_priv=上面路径的目录
例 secure_file_priv=/sql/

保存重启mysql服务，然后给sql目录授权
# chown -P mysql.mysql /sql


神奇的导出文件长这样

# vi /sql/tb_order.text

1       安安    馅饼    10      food    5       2016-10-01 22:21:40
2       宝贝    馅饼    223     food    5       2016-09-30 18:35:00
3       长城    饺子    154     food    10      2016-08-16 15:15:00
4       弟弟    NIKE    26      shoe    500     2015-03-02 01:41:40
5       吴王    李宁    41      shoe    250     2015-06-25 19:28:20


如果在语句中加入更多选项，比如
mysql> select  字段  into outfile '路径' 
mysql> fields terminated by ':' lines terminated by '\n'
mysql> from 库名.表名; 
意思表示以‘:’分割各个字段，以\n分割各行数据

这东西可以做导出excel啊~

逻辑导入

# 把/etc/passwd文件导入数据库，先建表
mysql> CREATE TABLE `passwd` (
  `uname` varchar(20) DEFAULT NULL,
  `pass` char(2) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  `gid` int(11) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `home` varchar(50) DEFAULT NULL,
  `shell` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 再执行
# mysqlimport db_itheima --fields-terminated-by=':' --lines-terminated-by='\n' /etc/passwd -p

# 例子里说的是，导入的文件必须格式规范，像/etc/passwd这样格式化的。
# --fields-terminated-by=':' 说字段之间用:分割
# --lines-terminated-by='\n' 行之间用回车\n分割

 4.使用mysqldump + binlog日志实现增备

先备份主备份文件sql文件，再备份binlog.00000x的增加部分。

# 确保二进制文件开着
# vim /usr/local/mysql/my.cnf
# log-bin = /usr/local/mysql/data/binlog

# 保存sql文件，全量备份
# mysqldump -uroot --all-databases --flush-logs --master-data=2 --single-transaction > /sql/all.sql -p
# 注：--master-data 选项的作用就是将二进制的信息写入到输出文件中，在这里是写入到备份的sql文件中


然后进入mysql终端中对数据库进行任意操作的写入或修改（不是查）
需要备份就执行
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      234 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)
记录File和Position的信息，下面使用

# mysqlbinlog --start-position=234 /usr/local/mysql/data/binlog.000002 > /sql/binlog.sql
# 恢复
# mysql -u root -p < *.sql

5.物理备份

使用软件 xtrabackup

只能对innodb表增量备份，myisam表增量备份时是全备，且myisam备份时会锁表。

1.安装pxb软件

# 本机配了yum的aliyun基础源和epel源
# yum install percona-xtrabackup
# rm -rf /etc/my.cnf

2.使用pxb软件实现全量备份

mysql> grant 权限 on 数据库.数据表 to '用户名'@'主机名称或IP' identified by '密码';
# 创建一个admin账号
mysql> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';
mysql> flush privileges;

开始进行全量备份
# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=admin --password=123 /full_xtrabackup

# 如若报错，基本上是my.cnf中某条目未定义，比如sock文件位置。

3.把备份期间产生的新数据由日志整合到全量备份中

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=admin --password=123 --apply-log /full_xtrabackup/最晚的一个日期文件夹

4.增量备份

innobackupex --user=admin --password=123 --incremental --redo-only /incre_backup --incremental-basedir=/full_xtrabackup/日期/

5.删库跑路

# rm -rf /usr/local/mysql/data/*
# pkill mysqld

# 或者给data文件夹改名。

6.增备全备整合

# 先执行全备整理
# innobackupex --user=admin --password=123 --apply-log --redo-only /full_xtrabackup/日期/
# 再按顺序将所有增备整合到全被中，顺序从最早到最近，全部整合一遍
# innobackupex --user=admin --password=123 --apply-log --redo-only /full_xtrabackup/日期/ --incremental-dir=/incre_backup/日期/

7.恢复

# 请确保my.cnf中有定义datadir参数
# innobackupex --defaults-file=/usr/local/mysql/my.cnf --copy-back /full_xtrabackup/最后一个日期文件夹

191029 21:22:54 completed OK!
表示成功
查看data文件夹

# ll /usr/local/mysql/data
# rm掉的文件都回来了。
# 但是权限都回到root了
# chown -R mysql.mysql /usr/local/mysql
# service mysql restart

8.总结

第一次全备，之后都是增备，可以使用定时任务进行。

数据库坏了之后整合需要用shell脚本进行循环整合。

数据库启动不来，基本问题如下

1.删除/etc/my.cnf

2.pkill mysqld

3.没赋权限chown -R mysql.mysql /usr/local/mysql