In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-02-21 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
01mysql backup and recovery
1. Mysqldump backup database (usage: databases below 1G)
1) single database
[root@www1] # mysqldump-uroot-- databases hellodb > / root/helldb01.sql
2) multiple databases
[root@www1] # mysqldump-uroot-- databases hellodb mydb > / root/hellodb_mydb.sql
3) back up all databases
[root@www1] # mysqldump-uroot-- all-databases-- lock-all-tables-- master-data=2 > / root/all.sql
2. Disaster recovery case: (this example is wrong and needs to be sorted out)
Original server: 192.168.1.73
Recovery server: 192.168.1.72
Original server: perform insert and delete operation
MariaDB [(none)] > use hellodb
MariaDB [hellodb] > desc students
# insert a record
MariaDB [hellodb] > insert into students (Name,Age,Gender,ClassID,TeacherID) VALUES ('Chao Cai',47,'M',3,7)
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 | Chao Cai | 47 | M | 3 | 7 | |
+-+ +
# Delete a record
MariaDB [hellodb] > DELETE FROM students WHERE StuID=3
Restore server: first restore using mysqldump backup files, then restore using binary log files
[root@source ~] # scp all.sql 192.168.1.72:/root
[root@restore ~] # yum-y install mariadb-server
[root@restore ~] # systemctl start mariadb.service
[root@restore ~] # mysql
MariaDB [(none)] > SHOW DATABASES
+-+
| | Database |
+-+
| | information_schema |
| | mysql |
| | performance_schema |
| | test |
+-+
4 rows in set (0.00 sec)
MariaDB [(none)] >\ Q
[root@restore ~] # mysql
< all.sql [root@restore ~]# mysql MariaDB [(none)]>SHOW DATABASES
+-+
| | Database |
+-+
| | information_schema |
| | hellodb |
| | mydb |
| | mysql |
| | performance_schema |
| | test |
+-+
6 rows in set (0.00 sec)
MariaDB [(none)] > use hellodb
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 | |
+-+ +
25 rows in set (0.00 sec)
Check the backup file, find the value of MASTER_LOG_POS, and make a note
[root@restore ~] # less all.sql
-- MySQL dump 10.14 Distrib 5.5.50-MariaDB, for Linux (x86 / 64)
--
-Host: localhost Database:
-
-- Server version 5.5.50-MariaDB
/ * 40101 SET @ OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT * /
/ * 40101 SET @ OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS * /
/ * 40101 SET @ OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION * /
/ * 40101 SET NAMES utf8 * /
/ * 40103 SET @ OLD_TIME_ZONE=@@TIME_ZONE * /
/ * 40103 SET TIME_ZONE='+00:00' * /
/ * 40014 SET @ OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 * /
/ * 40014 SET @ OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 * /
/ * 40101 SET @ OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' * /
/ * 40111 SET @ OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 * /
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245
--
-- Current Database: `hellodb`
--
CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `hellodb` / *! 40100 DEFAULT CHARACTER SET utf8 * /
USE `hellodb`
--
The value here is: MASTER_LOG_POS=245
[root@source ~] # mysqlbinlog-- start-position=245 / var/lib/mysql/mysql-bin.000001 > incre.sql
[root@source ~] # scp incre.sql 192.168.1.72:/root
[root@restore ~] # mysql
< incre.sql [root@restore ~]# mysql MariaDB [(none)]>Use hellodb
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 | Chao Cai | 47 | M | 3 | 7 | |
+-+ +
25 rows in set (0.00 sec)
Data recovery complete
3. Implement hot backup based on lvm2 (this example needs to be verified)
Configure lvm2 partitions for mariadb Stora
[root@source ~] # fdisk / dev/sda
Welcome to fdisk (util-linux 2.23.2).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.
Command (m for help): n
Partition type:
P primary (2 primary, 0 extended, 2 free)
E extended
Select (default p):
Using default response p
Partition number (3pr 4, default 3):
First sector (51374080-251658239, default 51374080):
Using default value 51374080
Last sector, + sectors or + size {KMagne Mpeng} (51374080-251658239, default 251658239): + 10G
Partition 3 of type Linux and of size 10 GiB is set
Command (m for help): W
The partition table has been altered!
Calling ioctl () to re-read partition table.
WARNING: Re-reading the partition table failed with error 16: Device or resource busy.
The kernel still uses the old table. The new table will be used at
The next reboot or after you run partprobe (8) or kpartx (8)
Syncing disks.
[root@source] # partx-a / dev/sda
[root@source ~] # pvcreate / dev/sda3
Physical volume "/ dev/sda3" successfully created
[root@source ~] # vgcreate myvg / dev/sda3
Volume group "myvg" successfully created
[root@source] # lvcreate-L + 5G-n mydata myvg
Logical volume "mydata" created.
[root@source ~] # mkfs.xfs / dev/myvg/mydata
[root@source ~] # systemctl stop mariadb.service
[root@source ~] # mount / dev/myvg/mydata / data/
[root@source] # mkdir-p / data/mysql
[root@source] # chown-R mysql.mysql / data/mysql/
[root@source ~] # vim / etc/my.cnf
Modify
Datadir=/var/lib/mysql
For
Datadir=/data/mysql
[root@source ~] # systemctl start mariadb.service
[root@source ~] # mysql
< all.sql [root@source ~]# mysql -e 'SHOW MASTER STATUS' >/ root/ pos.`date +% F`
[root@source] # cat pos.2016-11-02
File Position Binlog_Do_DB Binlog_Ignore_DB
Mysql-bin.000002 245
# lvm2 backup of the database
[root@source] # lvcreate-L 500m-s-n mydata-snap-p r / dev/myvg/mydata
Logical volume "mydata-snap" created.
# modify the database
MariaDB [(none)] > UNLOCK TABLES
MariaDB [(none)] > use hellodb
MariaDB [hellodb] > DELETE FROM students WHERE StuID=5
MariaDB [hellodb] > DELETE FROM students WHERE StuID=11
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 |
| | 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 |
| | 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 | |
+-+ +
23 rows in set (0.00 sec)
[root@source ~] # systemctl stop mariadb.service
[root@source ~] # lvremove / dev/myvg/mydata-snap
[root@source ~] # umount / dev/myvg/mydata
[root@source] # mke2fs-t ext4 / dev/myvg/mydata
[root@source ~] # mount / dev/myvg/mydata / data/
[root@source ~] # mkdir / data/ {mysql,binlogs}
[root@source] # chown-R mysql.mysql / data/*
[root@source ~] # vim / etc/my.cnf.d/server.cnf
Add in [server] line (about 9 lines)
Log_bin=/data/binlogs/mysql-bin
[root@source ~] # systemctl start mariadb.service
MariaDB [(none)] > SET sql_log_bin=0
MariaDB [(none)] > source / root/all.sql
MariaDB [test] > SHOW DATABASES
+-+
| | Database |
+-+
| | information_schema |
| | hellodb |
| | mydb |
| | mysql |
| | performance_schema |
| | test |
+-+
6 rows in set (0.00 sec)
MariaDB [test] > SHOW MASTER STATUS
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |
+-+
| | mysql-bin.000003 | 245 |
+-+
1 row in set (0.00 sec)
MariaDB [(none)] > SET sql_log_bin=1
MariaDB [test] > FLUSH TABLES WITH READ LOCK
[root@source] # mysql-e 'flush logs;'
[root@source ~] # mysql-e "SHOW MASTER STATUS;"
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |
+-+
| | mysql-bin.000004 | 245 |
+-+
[root@source ~] # mysql-e "SHOW MASTER STATUS;" > / root/pos- `date +% F`
[root@source] # cat pos-2016-11-03
File Position Binlog_Do_DB Binlog_Ignore_DB
Mysql-bin.000004 245
[root@source] # lvcreate-L 2G-n mydata-snap-s-pr / dev/myvg/mydata Logical volume "mydata-snap" created.
MariaDB [test] > UNLOCK TABLES
MariaDB [hellodb] > DELETE FROM students WHERE StuID=18
Query OK, 1 row affected (0.01sec)
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 |
| | 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 |
| | 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 | |
+-+ +
23 rows in set (0.01 sec)
[root@source] # mount-r / dev/myvg/mydata-snap / mnt
[root@source ~] # ls / mnt/
Binlogs lost+found mysql
[root@source ~] # cd / mnt/
[root@source mnt] # ls mysql/
Aria_log.00000001 hellodb ib_logfile0 mydb performance_schema
Aria_log_control ibdata1 ib_logfile1 mysql test
[root@source mnt] # ls binlogs/
Mysql-bin.000001 mysql-bin.000003 mysql-bin.index
Mysql-bin.000002 mysql-bin.000004
[root@source mnt] # cp-a mysql/ / tmp/
[root@source mnt] # cp-a / data/binlogs/ / tmp/
[root@source mnt] # systemctl stop mariadb.service
# simulate erroneous deletion of data in the database
[root@source mnt] # rm-rf / data/mysql/*
[root@source mnt] # rm-rf / data/binlogs/*
# restore
[root@source mnt] # cp-a / tmp/mysql/* / data/mysql/
[root@source mnt] # cp-a / tmp/binlogs/* / data/binlogs/
[root@source mnt] # systemctl start mariadb.service
[root@source mnt] # mysql
MariaDB [(none)] > SHOW BINARY LOGS
+-+ +
| | Log_name | File_size |
+-+ +
| | mysql-bin.000001 | 30334 | |
| | mysql-bin.000002 | 1038814 | |
| | mysql-bin.000003 | 288 |
| | mysql-bin.000004 | 245 |
| | mysql-bin.000005 | 245 |
+-+ +
MariaDB [(none)] > use hellodb
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 | |
+-+ +
25 rows in set (0.00 sec)
# data restored to backup
MariaDB [hellodb] >\ Q
Bye
[root@source] # cat pos-2016-11-03
File Position Binlog_Do_DB Binlog_Ignore_DB
Mysql-bin.000004 245
[root@source ~] # cd / tmp/
[root@source ~] # cd / tmp/binlogs/
[root@source binlogs] # ls
Mysql-bin.000001 mysql-bin.000003 mysql-bin.index
Mysql-bin.000002 mysql-bin.000004
[root@source binlogs] # mysqlbinlog-- start-position=245 mysql-bin.000004 / *! 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
# 161103 13:08:44 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.50-MariaDB created 161103 13:08:44
# Warning: this binlog is either in use or was not closed properly.
BINLOG'
XMYaWA8BAAAA8QAAAPUAAAABAAQANS41LjUwLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAA9VR4dg==
'/ *! * /
DELIMITER
# End of log file
ROLLBACK / * added by mysqlbinlog * /
/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.
12
Report
