linux下mysql备份数据入门

一、MySQL介绍：

MySQL为C/S模型

#并发型 大多数遇到的服务
>主进程派生子进程进行处理

#重复型 如TFTP
*启动进程（端口）
*发送请求
*处理请求
*同时第二个客户发送请求时出现问题
使用主进程进行处理
>主进程派生子进程进行处理

taobao 迁移出 IOE
Mysql5.6 新版本 功能有所减少
Mysql5.5

MySQL特性：
1.存在依赖关系的表
2.可以在多种操作系统上运行

                          |————client 与mysql出自同一处
mysql<-SQL<-|————php
                          |————perl
                          |————python

存储引擎：数据库读写文件的方式
MyISAM为mysql的存储引擎
读多写少，读较快，内存消耗打，并发量上不来，不支持回滚
可用innodb存储引擎

二、安装mysql
yum -y install mysql-server mysql mysql-connector-odbc
port:3306
/var/lib/mysql/
/etc/my.cnf
service mysqld restart//在其他平台环境下，在启动服务之前要先初始化数据库，REDHAT下已经包含
/usr/bin/mysqladmin -u root password 'new-password' //设定密码，初始化
/usr/bin/mysqladmin -u root password 'new-password' -p //后续修改密码，需要原始密码

mysql -uroot -p //登录

三、基本命令
#mysql
>show databases;
【
information_schema //tmple file
mysql //root
test //anyone
这几个库不用
】
>create database db1;

#mysqladmin create db2 //有密码的话加 -p

#mysqlbinlog //读取用二进制方式记录的日志文件

#mysqlcheck //检查、修复、分析以及优化表的表维护客户程序。不用!

#mysqldump //10G以下可以使用其备份，并且跨版本的。重要

#mysqlimport //不用，试用其他方法恢复数据。

四、库，表的基本操作与授权
> create database bookshop;
> use bookshop;
> drop database bookshop;

> create table reserve(
-> id int primary key,
-> bookname text,
-> writer text,
-> bookdate date not null,
-> price float,
-> amount int
-> );
> show tables;
> desc reserve;//查看表结构
#ls .mysql_history
> insert into reserve (id,bookname,writer,bookdate,price,amount) values(1,"linux","linus","2001-01-01",100,50),(2,"web-apache","aparch.og","2010-01-01",50,50),(3,"web-nginx","nginx.org","2010-01-01",50,50);
> select * from reserve;
> select bookname,amount from reserve; //只查询指定列
> select bookname,amount from reserve where id=1;
> select * from reserve where bookname like "web%";
...

索引:快速查找数据，有用到二叉树，暂不详讲。
使用grant语句添加用户名授权
1.授权192.168.0网段的可以以用户user2，密码redhat1的身份访问192.168.0.112这台主机下的bookshop数据库
> grant all privileges on bookshop.* to 'user2'@'192.168.0.%' identified by 'redhat1';
2.授权192.168.0.143的主机可以以用户user2，密码redhat2的身份访问192.168.0.112这台主机下的bookshop数据库
> grant all privileges on bookshop.* to 'user2'@'192.168.0.143' identified by 'redhat2' with grant option; //这里的with grant option 表示143这个主机还可有再授权给别人，没有就只能他自己能访问bookshop数据库。
注：如果1跟2条都做了的话，第2条生效。
3.修改用户密码
> grant usage on bookshop.* to user2@192.168.0.143 identified by '123456';
4.在赋予权限之后，要执行下flush privileges命令刷新权限缓存来让你的设置立即生效。> flush privileges;
5.找回root密码
#service mysqld stop //先停掉mysql服务
#mysqld_safe --skip-grant-tables &//后台 重新启动mysql，不过这个会使任何人都可以访问mysql服务器，故请断掉网络连接来操作。
#mysql
> use mysql;
> update user set password=password('new_pass') where user="root" and host=localhost;//select * from user\G;//\G表示格式化下，更好地查看user表
> exit
# service mysqld restart
//修改密码成功了为new_pass
五、备份
mysqldump 可以跨版本，可以备份某些库，也可以备份所有的库。

mysqldump --database bookshop
mysqldump bookshop //备份bookshop库中所有表
mysqldump --all-databases -uroot -predhat > /tmp/mysql.all.sql
//查看备份文件，为sql脚本文件

mysql < /tmp/mysql.all.sql //将sql脚本文件导入，恢复数据
mysqladmin flush-privileges // 使用户密码生效

2个冲突问题:
1.服务可用性； 假设1点到5点备份中服务可用的，会造成数据的一致性问题
2.数据的一致性；锁表，则一些写服务就延迟了。
根据实际生产环境均衡。

*假设保证数据一致性：在备份时加入参数 --lock-all-tables,整个过程中表只可读不可写。
mysqldump --all-databases -uroot -predhat --lock-all-tables > /tmp/mysql.all.sql

*二进制日志的使用

vi /etc/my.cnf
在[mysqld]中加入 log-bin=mylog
/etc/init.d/mysqld restart
//这样如果改动就会记录到这个里面去
ls /var/lib/mysql/
会出现的日志为mylog.00001，还多了个mylog.index文件
mysqlbinlog mylog.00001 | mysql 恢复改动数据
以后可以通过下面命令做完整备份
mysqldump --all-databases -uroot -predhat --lock-all-tables --flush-logs > /tmp/mysql.all.sql

//当你加入--flush-logs参数将会新增mylog.xxx的文件，service mysqld restart也会新增mylog.xxx 来记录数据库的改动

==============

更新：今天又学了新的东西哦，主要三部分，以====分割线分割了，第一部分接上面的，

关于如何跳过误操作进行重演日志；第二部分为搭建单向主从数据库以及双向

主从服务器；第三部分为逻辑卷snapshot备份。

1.假设3点做的完全备份，3点到10点间有10，11，12 3条插入记录；之后在10点做了个delete
的误操作，前面3条都删掉了，又插入了13，14 2条记录；我们在12
点发现这个问题，要如何恢复数据库呢。
停止服务，清空环境，日志不能清掉！；#for i in `ls | grep ^mylog -v`;do rm -rf /var/lib/mysql/$i;done
导入完全备份；
不能重演全部，要跳过delete操作；
查看log中的时间，时间是用来分隔操作的，重演delete上面的和下面的操作命令，可通过
时间指定；
#mysqlbinlog mylog.000014 | grep ^delete -C 3 //查找delete前后的时间
#mysqlbinlog mylog.000014 --stop-date="2013-05-17 10:21:50" | mysql
#mysqlbinlog mylog.000014 --start-date="2013-05-17 10:22:00" | mysql
//可以先不管道到mysql，先回车查看重演是否正确。


2.假设10点insert 15，16；delete15，16（误删除）；insert17，18；都在同一时刻的操作。
问题复原（创造同一时间点的5个操作）：
vim /tmp/sql1
【
use db1;
insert into t1 (id) values (15);
insert into t1 (id) values (16);
delete from t1;
insert into t1 (id) values (17);
insert into t1 (id) values (18);
】
mysql < /tmp/sql1
恢复：
导入完全备份；
重演：
方法一，
#mysqlbinlog mylog.000014 | grep ^delete -C 3 //查找delete前后的时间，发现前后时间点都一样
//不能通过时间点恢复，可以通过position号进行重演
#mysqlbinlog mylog.000014 --stop-position="292" | mysql
#mysqlbinlog mylog.000014 --start-position="368" | mysql
方法二，
#mysqlbinlog mylog.000014 > /tmp/sql1.log
#vi /tmp/sql1.log //删除掉delete那一行，再将该文件导入到数据库去


===================
MYSQL的AB复制：
主从服务器只能解决硬件故障
从库不提供服务，当主服务器出现问题时，可以指向从服务器进行数据的查询。当主服务器有变化时，
从服务器做数据同步。
*双方初始化一致
*主服务器开启日志
*从服务器读取主日志
*从服务器自动重演日志

假设master的ip地址为192.168.0.102，slave 的ip地址为192.168.0.112

---单向主从---

master端：
先停服务，清空环境，还原配置，在开启服务
开启日志，修改server-id，重启服务
mysqldump --flush-logs//备份
scp 给slave
授权给从服务器 grant replication slave on *.* to slave@'192.168.0.112'  //从ip地址
刷新授权表 flush privileges;


slave端：
修改server-id
初始化并导入备份 mysql< 
mysql> change master to master_host='192.168.0.13',master_user='',master_password='',
master_log_file="xx.000001",master_log_pos=106;
mysql> slave start; //在此之前一定要保证2边数据一致
mysql> show slave status\G;
//查看Slave_IO_Running Slave_SQL_Running
从服务器的日志是从下一个日志备份的


---双向主从---
双master环境下的问题：网络延迟会造成2个写操作分别到达主/辅服务器中，会有冲突出现。主从断开。
搭建双master，使slave也能同步自己的数据给master，授权语句仍然需要做master上做！

master下:
grant replication slave on *.* to slave@'192.168.0.102'         //主ip地址


flush previleges


change master to master_host='192.168.0.112',master_user='',master_password='',   //从ip地址
master_log_file="xx.000001",master_log_pos=106;


slave:
开启日志
重启服务后要查看下主辅同步还在否，不在就slave start
添加授权//在server端操作，因为要保证user表一致性
show master status; //查询自己的log号告诉对方


==================


使用LVM快照方式进行备份
之前我们讲到
1.数据一致性
2.服务可用性
要想解决上述兼得问题，可以使用以下2个方案：
1）master提供服务，slave提供备份
2）lvm snapshot 如果要备份就读snapshot，服务仍然读mysql


缺点:
需要你的系统支持快照
使用快照需要系统root权限
有进程在写造成snapshot延迟
/var/lib/mysql


lvcreate -s -L 1G -n snap0 /dev/vg0/lv0 
//快照大小最好不要超过lv大小，系统本身限制，大了会认识不出来。
ls /dev/vg0/snap0
mount /dev/vg0/snap0 /var/snap/

1.数据迁移（因为快照要做逻辑卷上做，而本来的数据库是在物理卷上的，故要迁移）
假设/var/lib/mysql上的数据在物理设备上/dev/sda3 上的，那么就需要额外创建lv来做快照备份
创建lv，比如/dev/vg0/lv0
service mysqld stop  //将mysql服务停止
cd /var/lib/mysql/   //这边记得查看下content值，方便解决下面selinux问题
tar cf - . | tar xif - -C /var/lv/     //要使用相对路径 备份/var/lib/mysql下的数据
ls /var/lv/
挂载：
mount /dev/vg0/lv0 /var/lib/mysql/    //将lv挂载为mysql数据目录，此时内容为空，相当于清空环境了
tail /var/log/mysql.log //mysql的启动日志
权限三部分：服务内部，selinux，普通权限
sealert -a /var/log/audit.log //记录了selinux的报错信息
此处要递归修改/var/lib/mysql的context值，restore

2.数据备份（不停止服务）
需要查看日志点来重演
#/etc/init.d/mysqld start
#mysql
mysql> flush tables with read lock; //先锁表，使写延迟
mysql> show master status; //查看position号，比如log12的457位置
lv create -s -L 1G -n snap0 /dev/vg0/lv0 //做快照，snapshot 跟修改的大小有关
unlock tables;//解锁
mount /dev/vg0/snap0 /var/snap
cd /var/snap
用tar指令备份snapshot //日志和数据库不要备份在一起！备份 目录（即数据库）和ib_log*
umount snap/
lvremove /dev/vg0/snap0

3数据恢复还原：
假设数据库损坏，则先/etc/init.d/mysqld stop
清空/var/lib/mysql下的环境
将步骤2中备份数据snapshot解压缩到/var/lib/mysql下，再重演log12的457位置后东西