Powered by Typecho)))
Optimized by EAimTY
因为数据表没设计记录历史变更,业务需要查找某表前n月记录
You can try to use sed in order to extract only the table you want.
Let say the name of your table is mytable and the file mysql.dump is the file containing your huge dump:
$ sed -n -e '/CREATE TABLE.*mytable/,/UNLOCK TABLES/p' mysql.dump > mytable.dump
This will copy in the file mytable.dump what is located between CREATE TABLE mytable and the next CREATE TABLE corresponding to the next table.
You can then adjust the file mytable.dump which contains the structure of the table mytable, and the data (a list of INSERT).
打包备份指定的库:
innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=root --databases=wallpaper --stream=tar ./ > /backup/wallpaper.tar
解压:
tar -ixvf wallpaper.tar
或者:
innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=root --databases=wallpaper --stream=tar ./ | gzip - > /backup/wallpaper.tar.gz
tar -izxvf wallpaper.tar.gz
注:不能不压缩的单独备份某个库。
解压缩时必须加上 -i 参数。
在线制作 复制:
master 上:
innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=root --slave-info /mnt/backup
从其它 slave 制作:
innobackupex-1.5.1 --defaults-file=/usr/local/mysql/etc/my.cnf.3307 --user=root --slave-info /mnt
slave 上:
innobackupex-1.5.1 --apply-log --defaults-file=/usr/local/mysql/etc/my.cnf.3307 --no-lock --user=root /mnt/2011-03-04_02-05-39
停机拷贝:
innobackupex-1.5.1 --copy-back --defaults-file=/usr/local/mysql/etc/my.cnf.3307 --no-lock --user=root /mnt/2011-03-04_02-05-39
恢复目录权限:
chown -R mysql:mysql mysql
chown -R mysql:root mysql/mysql
查看 xtrabackup_slave_info 文件得到相关信息后:
change master to master_host='127.0.0.1', master_port=33007, master_user='slave', master_password='slave', master_log_file='mysql-bin.000013', master_log_pos=85329;
start slave;
压缩传输到远程主机:
在目标主机:
打包备份:
nc -l 12345 | cat - > /data/backups/backup.tar
解压到当前目录:
nc -l 12345 | tar -ix
在源主机:
innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=root --slave-info --stream=tar ./ | nc backuphost 12345
在目标主机:
innobackupex-1.5.1 --apply-log --defaults-file=/etc/mysql/my.cnf --no-lock --user=root /mysql/backup
service mysql stop
innobackupex-1.5.1 --copy-back --defaults-file=/etc/mysql/my.cnf --no-lock --user=root /mysql/backup
全备:
xtrabackup --defaults-file=/etc/mysql/my.cnf --backup --target-dir=/var/backups/zabbix/mysql/0
增量备份:
xtrabackup --defaults-file=/etc/mysql/my.cnf --backup --target-dir=/var/backups/zabbix/mysql/1 --incremental-basedir=/var/backups/zabbix/mysql/0
恢复:
xtrabackup --prepare --apply-log-only --target-dir=/var/backups/zabbix/mysql/0
xtrabackup --prepare --apply-log-only --target-dir=/var/backups/zabbix/mysql/0 --incremental-dir=/var/backups/zabbix/mysql/1
把全备目录下的 innodb 表空间文件复制回数据库目录就行了。
注意: xtrabackup 并不备份表结构文件 frm ,需要另行备份。