MYSQL数据库Linux下实现MySQL数据备份和恢复的命令使用全攻略

MYSQL教程
[root@stu18 ~]
#mysqldump -uroot -pmypass --lock-all-tables --master-data=2 --events --routines--all-databases > /zhao/database_`date +%F`.sql

MYSQL教程
[root@stu18 zhao]
# less database_2013-08-13.sql
--   
#表示注释项
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=14203; 
#这里表示当前处于mysql-bin.000001这个二进制日志中,事件为14203这是通过--master-data=2产生的
--
-- Current Database: `hellodb`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */;

MYSQL教程
[root@stu18 data]
# mysqlbinlog mysql-bin.000001 >/zhao/binlog_`date +%F`.sql

MYSQL教程
mysql> flush logs; 
#滚动日志
[root@stu18 data]
# cp mysql-bin.000001 /zhao/mysql-bin.000001 #复制导出二进制文件

MYSQL教程
mysql> use hellodb;
mysql> INSERT INTO students(Name,Age,Gender,ClassID,TeacherID) values ('Yang kang',22,'M',3,3);

MYSQL教程
[root@stu18 data]
# mysqlbinlog --start-position=14203 --stop-position=14527 mysql-bin.000001 > /zhao/binlog_`date +%F_%H`.sql

MYSQL教程
mysql> DROP DATABASE hellodb;    
#删除数据库
############下面这些过程要在离线状态下执行############
mysql> SET sql_log_bin=0;     
#先关闭二进制日志
mysql> flush logs;      
#滚动日志
[root@stu18 ~]
# mysql -uroot -pmypass < /zhao/database_2013-08-13.sql #导入数据库备份文件
[root@stu18 ~]
# mysql -uroot -pmypass < /zhao/binlog_2013-08-13_19.sql #导入增量备份文件
[root@stu18 ~]
# mysql -uroot Cpmypass #登录查看,恢复完成
mysql> SET sql_log_bin=1;

MYSQL教程
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

MYSQL教程
mysqldump -Cadd-drop-table -uusername -ppassword databasename > backupfile.sql

MYSQL教程
mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

MYSQL教程
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

MYSQL教程
mysqldump -hhostname -uusername -ppassword Cdatabases databasename1 databasename2 databasename3 > multibackupfile.sql

MYSQL教程
mysqldump Cno-data Cdatabases databasename1 databasename2 databasename3 > structurebackupfile.sql

MYSQL教程
mysqldump Call-databases > allbackupfile.sql

MYSQL教程
mysql -hhostname -uusername -ppassword databasename < backupfile.sql

MYSQL教程
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

MYSQL教程
mysqldump -uusername -ppassword databasename | mysql Chost=*.*.*.* -C databasename

MYSQL教程
[root@stu18 ~]
# cd /usr/local/mysql/
[root@stu18 mysql]
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data

MYSQL教程
[root@stu18 mysql]
# vim /etc/my.cnf
datadir = /mydata/data 
#查看此项是否定义数据目录位置
sync_binlog=1 
#添加此项,每个事务提交时候,把事务日志从缓存区写到日志文件中,并且刷新日志文件的数据到磁盘上；
[root@stu18 mysql]
# service mysqld start

MYSQL教程
[root@stu18 ~]
# ls /mydata/data/
hellodb  myclass   mysql-bin.000003 stu18.magedu.com.err
ibdata1  mysql    mysql-bin.000004 stu18.magedu.com.pid
ib_logfile0 mysql-bin.000001 mysql-bin.index  student
ib_logfile1 mysql-bin.000002 performance_schema test

MYSQL教程
mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH LOGS;

MYSQL教程
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |  187 |    |     |
+------------------+----------+--------------+------------------+
[root@stu18 zhao]
# mysql -uroot -pmypass -e 'SHOW MASTER STATUS;' >/zhao/lvmback-2013-08-14/binlog.txt

MYSQL教程
[root@stu18 zhao]
# lvcreate -L 100M -s -p r -n mydata-lvm /dev/vg1/mydata

MYSQL教程
mysql> UNLOCK TABLES;

MYSQL教程
[root@stu18 data]
# cp -a * /zhao/lvmback-2013-08-14/

MYSQL教程
mysql> use hellodb;   
#指定默认数据库
Database changed
mysql> CREATE TABLE testtb (id int,name CHAR(10));  
#创建表
Query OK, 0 rows affected (0.35 sec)
mysql> INSERT INTO testtb VALUES (1,'tom');   
#添加数据
Query OK, 1 row affected (0.09 sec)
[root@stu18 data]
# mysqlbinlog --start-position=187 mysql-bin.000004 > /zhao/lvmlogbin_2013-08-14/binlog.sql   #日志实现增量备份

MYSQL教程
[root@stu18 ~]
# service mysqld stop
[root@stu18 ~]
# cd /mydata/data/
[root@stu18 data]
# rm -rf *

MYSQL教程
[root@stu18 ~]
# cp /zhao/lvmback-2013-08-14/* /mydata/data/ -a   #完全备份恢复
[root@stu18 ~]
# cd /mydata/data/    #查看恢复数据内容
[root@stu18 data]
# chown -R mysql.mysql * #更改属主属组
[root@stu18 data]
# service mysqld start  #启动服务
[root@stu18 data]
# mysql -uroot Cpmypass #登录测试

MYSQL教程
mysql> SHOW DATABASES;  
#查看数据完整性,无测试表testtd使用二进制恢复
mysql> SET sql_log_bin=0 
#关闭二进制日志
mysql> source /zhao/lvmlogbin_2013-08-14/binlog.sql; 
#二进制恢复
mysql> SHOW TABLES;   
#查看恢复结果
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes   |
| coc    |
| courses   |
| scores   |
| students   |
| teachers   |
| testtb   |
| toc    |
+-------------------+
mysql> SET sql_log_bin=1; 
#开启二进制日志

MYSQL教程
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file%';
+--------------------------+----------+
| Variable_name   | Value |
+--------------------------+----------+
| innodb_file_format  | Antelope |
| innodb_file_format_check | ON  |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON  |
+--------------------------+----------+

MYSQL教程
[root@stu18 hellodb]
# ls
classes.frm coc.MYD  courses.MYI scores.MYI teachers.frm testtb.ibd
classes.MYD coc.MYI  db.opt  students.frm teachers.MYD toc.frm
classes.MYI courses.frm scores.frm students.MYD teachers.MYI toc.MYD
coc.frm  courses.MYD scores.MYD students.MYI testtb.frm toc.MYI

MYSQL教程
[root@stu18 ~]
# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

MYSQL教程
[root@stu18 ~]
# yum -y install perl-DBD-mysql

MYSQL教程
# innobackupex --user=DBUSER--password=DBUSERPASS /path/to/BACKUP-DIR/

MYSQL教程
MYSQL教程[root@stu18 ~]
# mkdir /innobackup    #创建备份文件目录
[root@stu18 ~]
# innobackupex --user=root --password=mypass /innobackup/ #完全备份
################如果执行正确其后输出的几行信息通常如下###############
xtrabackup: Transaction log of lsn (1604655) to (1604655) was copied. 
#二进制日志的位置(lsn)
130814 07:04:55 innobackupex: All tables unlocked
innobackupex: Backup created in directory '/innobackup/2013-08-14_07-04-49'
#备份文件保存的位置
innobackupex: MySQL binlog position: filename 'mysql-bin.000003', position 538898
130814 07:04:55 innobackupex: Connection to database server closed
130814 07:04:55 innobackupex: completed

MYSQL教程
 
MYSQL教程[root@stu18 ~]
# cd /innobackup/2013-08-14_07-04-49/
[root@stu18 2013-08-14_07-04-49]
# ls
backup-my.cnf myclass    student   xtrabackup_binlog_info
hellodb  mysql    test    xtrabackup_checkpoints
ibdata1  performance_schema xtrabackup_binary xtrabackup_logfile

MYSQL教程
[root@stu18 ~]
# innobackupex -apply-log /innobackup/2013-08-14_07-04-49/
#############如果执行正确,其最后输出的几行信息通常如下################
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
130814 7:39:33 InnoDB: Starting shutdown...
130814 7:39:37 InnoDB: Shutdown completed; log sequence number 1606156
130814 07:39:37 innobackupex: completed OK!

MYSQL教程
[root@stu18 ~]
# service mysqld stop
[root@stu18 ~]
# cd /mydata/data/
[root@stu18 data]
# rm -rf *

MYSQL教程
[root@stu18 ~]
# innobackupex --copy-back /innobackup/2013-08-14_07-04-49/
#############如果执行正确,其最后输出的几行信息通常如下################
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/innobackup/2013-08-14_07-04-49'
innobackupex: back to original InnoDB log directory '/mydata/data'
innobackupex: Copying '/innobackup/2013-08-14_07-04-49/ib_logfile0' to '/mydata/data'
innobackupex: Copying '/innobackup/2013-08-14_07-04-49/ib_logfile1' to '/mydata/data'
innobackupex: Finished copying back files.
130814 07:58:22 innobackupex: completed OK!

MYSQL教程
# chown -R mysql:mysql /mydata/data/

MYSQL教程
[root@stu18 data]
# service mysqld start

MYSQL教程
[root@stu18 data]
# innobackupex --user=root --password=mypass --incremental /innobackup --incremental-basedir=/innobackup/2013-08-14_08-14-12/

MYSQL教程
[root@stu18 ~]
# innobackupex --user=root --password=mypass --incremental /innobackup --incremental-basedir=/innobackup/2013-08-14_08-29-05/

MYSQL教程
mysql> delete from coc where id=14;

MYSQL教程
mkdir /mybinlog 
#建立一目录用于存放二进制日志
chown mysql:mysql /mybinlog 
#更改权限
vim /etc/my.cnf 
#修改配置文件
log-bin=/mybinlog/mysql-bin 
#二进制日志目录及文件名前缀,添加之

MYSQL教程
[root@stu18 data]
# cp mysql-bin.000001/innobackup/

MYSQL教程
[root@stu18 ~]
# service mysqld stop
[root@stu18 ~]
# cd /mydata/data/
[root@stu18 data]
# rm -rf *

MYSQL教程
[root@stu18 ~]
# innobackupex --apply-log --redo-only/innobackup/2013-08-14_08-14-12/

MYSQL教程
[root@stu18 ~]
# innobackupex --apply-log--redo-only /innobackup/2013-08-14_08-14-12/--incremental-dir=/innobackup/2013-08-14_08-29-05/

MYSQL教程
[root@stu18 ~]
# innobackupex --apply-log--redo-only /innobackup/2013-08-14_08-14-12/ --incremental-dir=/innobackup/2013-08-14_09-08-39/

MYSQL教程
[root@stu18 ~]
# innobackupex --copy-back/innobackup/2013-08-14_08-14-12/

MYSQL教程
[root@stu18 ~]
# cd /mydata/data/
[root@stu18 data]
# chown -R mysql:mysql *

MYSQL教程
[root@stu18 ~]
# mysql -uroot -pmypas
mysql> select * from coc;
+----+---------+----------+
| ID | ClassID | CourseID |
+----+---------+----------+
| 1|  1 |  2 |
| 2|  1 |  5 |
| 3|  2 |  2 |
| 4|  2 |  6 |
| 5|  3 |  1 |
| 6|  3 |  7 |
| 7|  4 |  5 |
| 8|  4 |  2 |
| 9|  5 |  1 |
| 10 |  5 |  9 |
| 11 |  6 |  3 |
| 12 |  6 |  4 |
| 13 |  7 |  4 |
| 14 |  7 |  3 |
+----+---------+----------+
14 rows in set (0.00 sec)

MYSQL教程
[root@stu18 data]
# cd /innobackup/2013-08-14_09-08-39/
[root@stu18 2013-08-14_09-08-39]
# cat xtrabackup_binlog_info
mysql-bin.000001 780

MYSQL教程
[root@stu18 innobackup]
# mysqlbinlog mysql-bin.000001
# at 780
#130814 9:20:19 server id 1 end_log_pos 851 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1376443219/*!*/;
BEGIN
/*!*/;
# at 851
#130814 9:20:19 server id 1 end_log_pos 944 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1376443219/*!*/;
delete from coc where id=14
/*!*/;
# at 944
#130814 9:20:19 server id 1 end_log_pos 1016 Query thread_id=7 exec_time=0 error_code=0
SET TIMESTAMP=1376443219/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@stu18 innobackup]
# mysqlbinlog --start-position=780 mysql-bin.000001 > ./all.sql  #导出数据

MYSQL教程
[root@stu18 ~]
# mysql -uroot Cpmypass
mysql> SET SQL_LOG_BIN=0;   
#关闭二进制日志
mysql> source /innobackup/all.sql 
#导入数据
mysql> SET SQL_LOG_BIN=1;   
#开启二进制日志
mysql> select * from coc;   
#查看数据,恢复完成
+----+---------+----------+
| ID | ClassID | CourseID |
+----+---------+----------+
| 1 |  1 |  2 |
| 2 |  1 |  5 |
| 3 |  2 |  2 |
| 4 |  2 |  6 |
| 5 |  3 |  1 |
| 6 |  3 |  7 |
| 7 |  4 |  5 |
| 8 |  4 |  2 |
| 9 |  5 |  1 |
| 10 |  5 |  9 |
| 11 |  6 |  3 |
| 12 |  6 |  4 |
| 13 |  7 |  4 |
+----+---------+----------+
13 rows in set (0.00 sec)