时间:2021-07-01 10:21:17 帮助过:3人阅读
mysqldump进行mysqll数据库的备份与恢复
命令说明: Schema和数据存储一起、巨大的SQL语句、单个巨大的备份文件
mysqldump: 客户端,通过mysql协议连接至mysqld; mysqldump [options] [db_name [tbl_name ...]] shell> mysqldump [options] db_name [tbl_name ...] shell> mysqldump [options] --databases db_name ... shell> mysqldump [options] --all-databases -A, --all-databases MyISAM, InnoDB: 温备 -x, --lock-all-tables:锁定所有库的所有表; -l, --lock-tables:对每个单独的数据库,在启动备份之前锁定其所有表; InnoDB: --single-transaction:启动一个大的单一事务实现备份 -B, --databases db_name1 db_name2 ...:备份指定的数据库 -C, --compress:压缩传输; 命令的语法格式: mysqldump [OPTIONS] database [tables]:备份单个库,或库指定的一个或多个表 mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]:备份一个或多个库 mysqldump [OPTIONS] --all-databases [OPTIONS]:备份所有库 其它选项: -E, --events:备份指定库的事件调度器event scheuler; -R, --routines:备份存储过程和存储函数; --triggers:备份触发器 --master-data[=#]: 1:记录CHANGE MASTER TO语句;此语句未被注释; 2:记录为注释语句; --flush-logs, -F:锁定表之后执行flush logs命令;
实验及目的:
故障数据库:192.168.150.138 恢复至数据库:192.168.150.137 目的:故障数据库将备份恢复还原至新建数据库,并保证数据一致性
故障服务器进行的备份操作
1、为保证数据的一致性,故障服务器需先开启二进制日志功能 MariaDB [(none)]> SHOW GLOBAL VARIABLES like ‘%log% 查看二进制日志功能是否开启 | log_bin | OFF 修改配置文件进行全局修改 可以修改的地方: /etc/my.conf /etc/my.cnf.d/ 此次修改为/etc/my.cnf.d/server.cnf [server] log_bin=mysql-bin #mysql-bin即为二进制日志的名称 修改配置文件需对数据库进行重启操作 ~]# systemctl restart mariadb.service 再次确认二进制日志功能是否已经开启: MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%log%‘; | log_bin | ON 2、进行mysqldump操作 ~]# mysqldump -uroot --all-databases --lock-all-tables --master-data=2 > /root/all.sql 说明--lock-all-tables表示锁表操作,保证备份期间无数据变动 --master-data=2将备份时的二进制日志进行表示为注释语句添加至all.sql中 具体内容为:-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000001‘, MASTER_LOG_POS=245; 3、为了模拟备份点之后还出现了数据修改,对原数据库进行数据变更操作 ~]# mysql MariaDB [(none)]> use hellodb; MariaDB [hellodb]> DESC students; +-----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------+----------------+ | StuID | int(10) unsigned | NO | PRI | NULL | auto_increment | | Name | varchar(50) | NO | MUL | NULL | | | Age | tinyint(3) unsigned | NO | MUL | NULL | | | Gender | enum(‘F‘,‘M‘) | NO | | NULL | | | ClassID | tinyint(3) unsigned | YES | | NULL | | | TeacherID | int(10) unsigned | YES | | NULL | | +-----------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) MariaDB [hellodb]> INSERT INTO students (Name,Age,Gender,ClassID,TeacherID) VALUES(‘void‘,11,‘M‘,3,6); Query OK, 1 row affected (0.01 sec) MariaDB [hellodb]> select * from studnets; ERROR 1146 (42S02): Table ‘hellodb.studnets‘ doesn‘t exist MariaDB [hellodb]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 26 | JinJiao King | 100 | M | NULL | 1 | | 27 | YinJiao King | 98 | M | NULL | 2 | | 28 | void | 11 | M | 3 | 6 | +-------+---------------+-----+--------+---------+-----------+ 28 rows in set (0.00 sec) MariaDB [hellodb]> DELETE FROM students WHERE StuID=3; Query OK, 1 row affected (0.00 sec) MariaDB [hellodb]> quit Bye
新数据库进行还原操作:
1、将备份的.sql文件拷贝至新数据库 2、注意点:在数据库进行还原操作是,为了减少IO负担,可以将二进制日志文件在session层面进行暂时的关闭 SET sql_log_bin=OFF; 3、首先我查看新数据,并无任何hellodb的数据 root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.54-MariaDB Source distribution Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | myda | | mysql | | performance_schema | | test | +--------------------+ 4、进行还原操作:需要注意mysql用户对all.sql有读取权限 source /tmp/all.sql: 5、数据确认:此时的数据仅仅为我备份点时的数据,顾还需要进行数据的一个前滚操作 MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | myda | | mydb | | mysql | | performance_schema | | test | | testdb | +--------------------+ MariaDB [(none)]> use hellodb; Database changed MariaDB [hellodb]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 26 | JinJiao King | 100 | M | NULL | 1 | | 27 | YinJiao King | 98 | M | NULL | 2 | +-------+---------------+-----+--------+---------+-----------+ 27 rows in set (0.00 sec)
数据的一个前滚的操作:
为了保证数据的一致性,通过mysqldump进行备份时,一定要定期对二进制日志进行备份 故障数据库的操作: 1、~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000001 可以查看到在备份点之后我做过的所有sql操作 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #170119 15:39:40 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.52-MariaDB created 170119 15:39:40 at startup# Warning: this binlog is either in use or was not closed properly. ROLLBACK/*!*/; BINLOG ‘ PG2AWA8BAAAA8QAAAPUAAAABAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAA8bYBYEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAW+9+9w== ‘/*!*/; # at 245 #170119 15:44:08 server id 1 end_log_pos 316 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1484811848/*!*/; SET @@session.pseudo_thread_id=4/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.au tocommit=1/*!*/;SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/ ;SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 316 #170119 15:44:08 server id 1 end_log_pos 344 Intvar SET INSERT_ID=28/*!*/; # at 344 #170119 15:44:08 server id 1 end_log_pos 492 Query thread_id=4 exec_time=0 error_code=0 use `hellodb`/*!*/; SET TIMESTAMP=1484811848/*!*/; INSERT INTO students (Name,Age,Gender,ClassID,TeacherID) VALUES(‘void‘,11,‘M‘,3,6) /*!*/; # at 492 #170119 15:44:08 server id 1 end_log_pos 519 Xid = 431 COMMIT/*!*/; # at 519 #170119 15:45:05 server id 1 end_log_pos 590 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1484811905/*!*/; BEGIN /*!*/; # at 590 #170119 15:45:05 server id 1 end_log_pos 690 Query thread_id=4 exec_time=0 error_code=0 SET TIMESTAMP=1484811905/*!*/; DELETE FROM students WHERE StuID=3 /*!*/; # at 690 #170119 15:45:05 server id 1 end_log_pos 717 Xid = 434 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 2、将这些sql操作导出至增量文件,并拷贝至新数据库 ~]# mysqlbinlog --start-position=245 /var/lib/mysql/mysql-bin.000001 >incre.sql 3、新数据库进行前滚操作: MariaDB [hellodb]> source /tmp/incre.sql 4、数据确认:此时我备份点之后的数据修改也全部还原 MariaDB [hellodb]> SELECT * FROM students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | | 26 | JinJiao King | 100 | M | NULL | 1 | | 27 | YinJiao King | 98 | M | NULL | 2 | | 28 | void | 11 | M | 3 | 6 | +-------+---------------+-----+--------+---------+-----------+ 27 rows in set (0.00 sec)
本文出自 “Open World” 博客,请务必保留此出处http://voidyao000.blog.51cto.com/12458042/1906271
mysql备份与恢复
标签:mysql 数据库 备份