不要备份没有变化的表。有些存储引擎，例如，MyISAM，会记录每个表最后修改时间。可以通过查看磁盘上的文件或运行show table status 来查看这个时间。如果使用InnoDB，可以利用触发器记录修改时间到一个小的“最后修改时间”表中，帮助跟踪最新的修改操作。需要确保只对变更不频繁的表进行跟踪，这样才能降低开销。通过定制的备份脚本可以轻松获取到哪些表有变更。 例如，如果有包含不同语种各个月的名称列表，或者州或区域的简写之类的“查找”表，将它们放在一个单独的数据库是一个好主意，这样就不需要每次都备份这些表。
# 备份 [root@mastera0 ~]# systemctl stop mariadb [root@mastera0 ~]# tar -cf /tmp/mysql.all.tar /var/lib/mysql/ tar: Removing leading \`/\' from member names [root@mastera0 ~]# systemctl start mariadb [root@mastera0 ~]# ll /tmp total 29772 -rw-r--r--. 1 root root 30484480 Aug 30 11:28 mysql.all.tar # 还原 [root@mastera0 ~]# systemctl stop mariadb ot@mastera0 ~]# rm -rf /var/lib/mysql/* [root@mastera0 ~]# ll /var/lib/mysql total 0 [root@mastera0 ~]# tar -xf /tmp/mysql.all.tar -C / [root@mastera0 ~]# ll /var/lib/mysql total 28700 -rw-rw----. 1 mysql mysql 16384 Aug 30 11:27 aria_log.00000001 -rw-rw----. 1 mysql mysql 52 Aug 30 11:27 aria_log_control drwx------. 2 mysql mysql 32 Aug 30 11:24 db1 -rw-rw----. 1 mysql mysql 18874368 Aug 30 11:27 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Aug 30 11:27 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Aug 30 11:23 ib_logfile1 drwx------. 2 mysql mysql 4096 Aug 30 11:23 mysql drwx------. 2 mysql mysql 4096 Aug 30 11:23 performance_schema drwx------. 2 mysql mysql 6 Aug 30 11:23 test [root@mastera0 ~]# systemctl start mariadb [root@mastera0 ~]# echo \"select * from db1.t1\"|mysql -uroot -puplooking id 1 2
Changes will remain in memory only, until you decide to write them. Be careful before using the write command.
Device does not contain a recognized partition table Building a new DOS disklabel with disk identifier 0xb9ec589f.
Command (m for help): n Partition type: p primary (0 primary, 0 extended, 4 free) e extended Select (default p): p Partition number (1-4, default 1): 1 First sector (2048-41943039, default 2048): Using default value 2048 Last sector, +sectors or +size{K,M,G} (2048-41943039, default 41943039): +1G Partition 1 of type Linux and of size 1 GiB is set
Command (m for help): n Partition type: p primary (1 primary, 0 extended, 3 free) e extended Select (default p): p Partition number (2-4, default 2): 2 First sector (2099200-41943039, default 2099200): Using default value 2099200 Last sector, +sectors or +size{K,M,G} (2099200-41943039, default 41943039): +1G Partition 2 of type Linux and of size 1 GiB is set
Command (m for help): p
Disk /dev/vdb: 21.5 GB, 21474836480 bytes, 41943040 sectors Units = sectors of 1 * 512 = 512 bytes Sector size (logical/physical): 512 bytes / 512 bytes I/O size (minimum/optimal): 512 bytes / 512 bytes Disk label type: dos Disk identifier: 0xb9ec589f
Device Boot Start End Blocks Id System /dev/vdb1 2048 2099199 1048576 83 Linux /dev/vdb2 2099200 4196351 1048576 83 Linux
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks. [root@mastera0 mysql]# ls /dev/vdb* /dev/vdb /dev/vdb1 /dev/vdb2 [root@mastera0 mysql]# pvcreate /dev/vdb{1,2} Physical volume \"/dev/vdb1\" successfully created Physical volume \"/dev/vdb2\" successfully created [root@mastera0 mysql]# vgcreate vgmysql /dev/vdb{1,2} Volume group \"vgmysql\" successfully created [root@mastera0 mysql]# lvcreate -L 1G -n lv1 vgmysql Logical volume \"lv1\" created. [root@mastera0 mysql]# lvs LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert home rhel -wi-ao---- 500.00m root rhel -wi-ao---- 8.81g swap rhel -wi-ao---- 512.00m lv1 vgmysql -wi-a----- 1.00g [root@mastera0 mysql]# mkfs.ext4 /dev/vgmysql/lv1 mke2fs 1.42.9 (28-Dec-2013) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) Stride=0 blocks, Stripe width=0 blocks 65536 inodes, 262144 blocks 13107 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=268435456 8 block groups 32768 blocks per group, 32768 fragments per group 8192 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376
Allocating group tables: done Writing inode tables: done Creating journal (8192 blocks): done Writing superblocks and filesystem accounting information: done
[root@mastera0 mysql]# [root@mastera0 mysql]# systemctl stop mariadb [root@mastera0 mysql]# tar -cf /tmp/mysql.1.tar /var/lib/mysql/ tar: Removing leading \`/\' from member names [root@mastera0 mysql]# mount /dev/vgmysql/lv1 /var/lib/mysql^C [root@mastera0 mysql]# ll -dZ /var/lib/mysql drwxr-xr-x. mysql mysql system_u:object_r:mysqld_db_t:s0 /var/lib/mysql [root@mastera0 mysql]# mount /dev/vgmysql/lv1 /var/lib/mysql [root@mastera0 mysql]# ll -dZ /var/lib/mysql drwxr-xr-x. root root system_u:object_r:unlabeled_t:s0 /var/lib/mysql [root@mastera0 mysql]# ll /var/lib/mysql total 16 drwx------. 2 root root 16384 Aug 30 13:57 lost+found [root@mastera0 ~]# ll -d /var/lib/mysql drwxr-xr-x. 3 root root 4096 Aug 30 13:57 /var/lib/mysql [root@mastera0 ~]# tar -xf /tmp/mysql.1.tar -C / [root@mastera0 ~]# ll -d /var/lib/mysql drwxr-xr-x. 7 mysql mysql 4096 Aug 30 14:01 /var/lib/mysql [root@mastera0 ~]# ll /var/lib/mysql total 28724 -rw-rw----. 1 mysql mysql 16384 Aug 30 14:01 aria_log.00000001 -rw-rw----. 1 mysql mysql 52 Aug 30 14:01 aria_log_control drwx------. 2 mysql mysql 4096 Aug 30 11:24 db1 -rw-rw----. 1 mysql mysql 18874368 Aug 30 14:01 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Aug 30 14:01 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Aug 30 11:23 ib_logfile1 drwx------. 2 root root 16384 Aug 30 13:57 lost+found drwx------. 2 mysql mysql 4096 Aug 30 11:23 mysql drwx------. 2 mysql mysql 4096 Aug 30 11:23 performance_schema drwx------. 2 mysql mysql 4096 Aug 30 11:23 test [root@mastera0 ~]# systemctl start mariadb Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details. [root@mastera0 ~]# setenforce 0 [root@mastera0 ~]# systemctl start mariadb [root@mastera0 ~]# ll /var/lib/mysql -Z -rw-rw----. mysql mysql unconfined_u:object_r:unlabeled_t:s0 aria_log.00000001 -rw-rw----. mysql mysql unconfined_u:object_r:unlabeled_t:s0 aria_log_control drwx------. mysql mysql unconfined_u:object_r:unlabeled_t:s0 db1 -rw-rw----. mysql mysql unconfined_u:object_r:unlabeled_t:s0 ibdata1 -rw-rw----. mysql mysql unconfined_u:object_r:unlabeled_t:s0 ib_logfile0 -rw-rw----. mysql mysql unconfined_u:object_r:unlabeled_t:s0 ib_logfile1 drwx------. root root system_u:object_r:unlabeled_t:s0 lost+found drwx------. mysql mysql unconfined_u:object_r:unlabeled_t:s0 mysql srwxrwxrwx. mysql mysql system_u:object_r:unlabeled_t:s0 mysql.sock drwx------. mysql mysql unconfined_u:object_r:unlabeled_t:s0 performance_schema drwx------. mysql mysql unconfined_u:object_r:unlabeled_t:s0 test # lvm快照备份 MariaDB [(none)]> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) ## 数据库加上全局读锁后，立刻在新终端中创建快照 [root@mastera0 ~]# lvcreate -s -L 500M -n snap1 /dev/vgmysql/lv1 Logical volume "snap1" created. ## 快照创建之后，解锁，服务可以正常适用了 MariaDB [(none)]> unlock tables; Query OK, 0 rows affected (0.00 sec) ## 挂接快照使用 [root@mastera0 ~]# mount /dev/vgmysql/snap1 /mnt [root@mastera0 ~]# ls /mnt aria_log.00000001 db1 ib_logfile0 lost+found mysql.sock test aria_log_control ibdata1 ib_logfile1 mysql performance_schema [root@mastera0 ~]# cd /mnt [root@mastera0 mnt]# tar -cf /tmp/mysql.2.tar ./* tar: ./mysql.sock: socket ignored [root@mastera0 mnt]# ll /tmp total 89316 drwxr-xr-x. 2 root root 6 Aug 30 11:31 a -rw-r--r--. 1 root root 30484480 Aug 30 14:01 mysql.1.tar -rw-r--r--. 1 root root 30484480 Aug 30 14:58 mysql.2.tar -rw-r--r--. 1 root root 30484480 Aug 30 11:28 mysql.all.tar [root@mastera0 mnt]# cd [root@mastera0 ~]# umount /mnt [root@mastera0 ~]# lv lvchange lvdisplay lvmchange lvmdiskscan lvmpolld lvreduce lvresize lvconvert lvextend lvmconf lvmdump lvmsadc lvremove lvs lvcreate lvm lvmconfig lvmetad lvmsar lvrename lvscan [root@mastera0 ~]# lvremove /dev/vgmysql/snap1 Do you really want to remove active logical volume snap1? [y/n]: y Logical volume "snap1" successfully removed # 还原数据 [root@mastera0 ~]# systemctl stop mariadb [root@mastera0 ~]# rm -rf /var/lib/mysql/* [root@mastera0 ~]# tar -xf /tmp/mysql.2.tar -C /var/lib/mysql [root@mastera0 ~]# ll /var/lib/mysql total 28712 -rw-rw----. 1 mysql mysql 16384 Aug 30 14:01 aria_log.00000001 -rw-rw----. 1 mysql mysql 52 Aug 30 14:01 aria_log_control drwx------. 2 mysql mysql 4096 Aug 30 11:24 db1 -rw-rw----. 1 mysql mysql 18874368 Aug 30 14:42 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Aug 30 14:42 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Aug 30 11:23 ib_logfile1 drwx------. 2 root root 4096 Aug 30 13:57 lost+found drwx------. 2 mysql mysql 4096 Aug 30 11:23 mysql drwx------. 2 mysql mysql 4096 Aug 30 11:23 performance_schema drwx------. 2 mysql mysql 4096 Aug 30 11:23 test [root@mastera0 ~]# systemctl start mariadb [root@mastera0 ~]# echo "select * from db1.t1" | mysql -uroot -puplooking id 1 2
mysqldump 备份数据---逻辑备份sql语句 -u 用户名 -p 密码 -A 所有的库 --single-transaction INNODB存储引擎的表备份时能够做到数据一致，服务可用 mysqldump -uroot -puplooking -A --single-transaction > /tmp/mysql.201608301600.sql --lock-all-tables MYISAM存储引擎的表备份时能够做到数据一致，服务不可用 mysqldump -uroot -puplooking -A --lock-all-tables > /tmp/mysql.xxx.sql
mysqldump备份步骤
INNODB mysqldump -uroot -puplooking -A --single-transaction > /tmp/mysql.201608301600.sql
MYISAM mysqldump -uroot -puplooking -A --lock-all-tables > /tmp/mysql.xxx.sql
mysqldump还原步骤
1）停止服务 2）清空环境 3）启动服务 4）导入数据 5）刷新授权 6）测试
课堂实战3: 利用mysqldump实现逻辑备份并还原
[root@mastera0 ~]# mysqldump -uroot -puplooking -A --single-transaction > /tmp/mysql.all.1.sql [root@mastera0 ~]# ll /tmp total 89820 drwxr-xr-x. 2 root root 6 Aug 30 11:31 a -rw-r--r--. 1 root root 30484480 Aug 30 14:01 mysql.1.tar -rw-r--r--. 1 root root 30484480 Aug 30 14:58 mysql.2.tar -rw-r--r--. 1 root root 515980 Aug 30 16:02 mysql.all.1.sql -rw-r--r--. 1 root root 30484480 Aug 30 11:28 mysql.all.tar [root@mastera0 ~]# systemctl stop mariadb [root@mastera0 ~]# rm -rf /var/lib/mysql/* [root@mastera0 ~]# ll /var/lib/mysql total 0 [root@mastera0 ~]# systemctl start mariadb [root@mastera0 ~]# ll /var/lib/mysql total 28704 -rw-rw----. 1 mysql mysql 16384 Aug 30 16:10 aria_log.00000001 -rw-rw----. 1 mysql mysql 52 Aug 30 16:10 aria_log_control -rw-rw----. 1 mysql mysql 18874368 Aug 30 16:10 ibdata1 -rw-rw----. 1 mysql mysql 5242880 Aug 30 16:10 ib_logfile0 -rw-rw----. 1 mysql mysql 5242880 Aug 30 16:10 ib_logfile1 drwx------. 2 mysql mysql 4096 Aug 30 16:10 mysql srwxrwxrwx. 1 mysql mysql 0 Aug 30 16:10 mysql.sock drwx------. 2 mysql mysql 4096 Aug 30 16:10 performance_schema drwx------. 2 mysql mysql 4096 Aug 30 16:10 test [root@mastera0 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> \q Bye [root@mastera0 ~]#
[root@mastera0 ~]# mysql < /tmp/mysql.all.1.sql [root@mastera0 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> \q Bye [root@mastera0 ~]# mysql ERROR 1045 (28000): Access denied for user \'root\'@\'localhost\' (using password: NO) [root@mastera0 ~]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 6 Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type \'help;\' or \'\h\' for help. Type \'\c\' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec)
MariaDB [(none)]> show databases; +---------------------+ | Database | +---------------------+ | information_schema | | db1 | | db2 | | #mysql50#lost+found | | mysql | | performance_schema | | test | +---------------------+ 7 rows in set (0.00 sec)
FINISHED --2016-09-01 10:41:05-- Total wall clock time: 0.3s Downloaded: 1 files, 24M in 0.2s (122 MB/s) [root@mastera0 ~]# ls anaconda-ks.cfg Percona-XtraBackup-2.3.4-re80c779-el7-x86_64-bundle.tar [root@mastera0 ~]# tar -xf Percona-XtraBackup-2.3.4-re80c779-el7-x86_64-bundle.tar [root@mastera0 ~]# ls anaconda-ks.cfg percona-xtrabackup-2.3.4-1.el7.x86_64.rpm Percona-XtraBackup-2.3.4-re80c779-el7-x86_64-bundle.tar percona-xtrabackup-debuginfo-2.3.4-1.el7.x86_64.rpm percona-xtrabackup-test-2.3.4-1.el7.x86_64.rpm [root@mastera0 ~]# yum localinstall -y percona-xtrabackup Loaded plugins: product-id, search-disabled-repos, subscription-manager This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register. Skipping: percona-xtrabackup, filename does not end in .rpm. Nothing to do [root@mastera0 ~]# yum localinstall -y percona-xtrabackup-2.3.4-1.el7.x86_64.rpm Loaded plugins: product-id, search-disabled-repos, subscription-manager This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register. Examining percona-xtrabackup-2.3.4-1.el7.x86_64.rpm: percona-xtrabackup-2.3.4-1.el7.x86_64 Marking percona-xtrabackup-2.3.4-1.el7.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package percona-xtrabackup.x86_64 0:2.3.4-1.el7 will be installed --> Processing Dependency: rsync for package: percona-xtrabackup-2.3.4-1.el7.x86_64 rhel_dvd | 4.1 kB 00:00:00 --> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-2.3.4-1.el7.x86_64 --> Running transaction check ---> Package percona-xtrabackup.x86_64 0:2.3.4-1.el7 will be installed --> Processing Dependency: libev.so.4()(64bit) for package: percona-xtrabackup-2.3.4-1.el7.x86_64 ---> Package rsync.x86_64 0:3.0.9-17.el7 will be installed --> Finished Dependency Resolution Error: Package: percona-xtrabackup-2.3.4-1.el7.x86_64 (/percona-xtrabackup-2.3.4-1.el7.x86_64) Requires: libev.so.4()(64bit) You could try using --skip-broken to work around the problem You could try running: rpm -Va --nofiles --nodigest [root@mastera0 ~]# wget http://classroom.example.com/content/MYSQL/04-others/soft/libev-4.15-6.el7.x86_64.rpm --2016-09-01 10:43:37-- http://classroom.example.com/content/MYSQL/04-others/soft/libev-4.15-6.el7.x86_64.rpm Resolving classroom.example.com (classroom.example.com)... 172.25.254.254 Connecting to classroom.example.com (classroom.example.com)|172.25.254.254|:80... connected. HTTP request sent, awaiting response... 200 OK Length: 44964 (44K) [application/x-rpm] Saving to: ‘libev-4.15-6.el7.x86_64.rpm’ 100%[===========================================>] 44,964 --.-K/s in 0s
[root@mastera0 ~]# ls anaconda-ks.cfg libev-4.15-6.el7.x86_64.rpm percona-xtrabackup-2.3.4-1.el7.x86_64.rpm Percona-XtraBackup-2.3.4-re80c779-el7-x86_64-bundle.tar percona-xtrabackup-debuginfo-2.3.4-1.el7.x86_64.rpm percona-xtrabackup-test-2.3.4-1.el7.x86_64.rpm [root@mastera0 ~]# yum localinstall -y libev-4.15-6.el7.x86_64.rpm Loaded plugins: product-id, search-disabled-repos, subscription-manager This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register. Examining libev-4.15-6.el7.x86_64.rpm: libev-4.15-6.el7.x86_64 Marking libev-4.15-6.el7.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package libev.x86_64 0:4.15-6.el7 will be installed --> Finished Dependency Resolution
Dependencies Resolved
===================================================================================== Package Arch Version Repository Size ===================================================================================== Installing: libev x86_64 4.15-6.el7 /libev-4.15-6.el7.x86_64 86 k
Total size: 86 k Installed size: 86 k Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : libev-4.15-6.el7.x86_64 1/1 Verifying : libev-4.15-6.el7.x86_64 1/1
Installed: libev.x86_64 0:4.15-6.el7
Complete! [root@mastera0 ~]# yum localinstall -y percona-xtrabackup-2.3.4-1.el7.x86_64.rpm Loaded plugins: product-id, search-disabled-repos, subscription-manager This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register. Examining percona-xtrabackup-2.3.4-1.el7.x86_64.rpm: percona-xtrabackup-2.3.4-1.el7.x86_64 Marking percona-xtrabackup-2.3.4-1.el7.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package percona-xtrabackup.x86_64 0:2.3.4-1.el7 will be installed --> Processing Dependency: rsync for package: percona-xtrabackup-2.3.4-1.el7.x86_64 --> Running transaction check ---> Package rsync.x86_64 0:3.0.9-17.el7 will be installed --> Finished Dependency Resolution
Dependencies Resolved
===================================================================================== Package Arch Version Repository Size ===================================================================================== Installing: percona-xtrabackup x86_64 2.3.4-1.el7 /percona-xtrabackup-2.3.4-1.el7.x86_64 21 M Installing for dependencies: rsync x86_64 3.0.9-17.el7 rhel_dvd 359 k
IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackupex prints "completed OK!".
innobackupex version 2.3.4 based on MySQL server 5.6.24 Linux (x86_64) (revision id: e80c779) xtrabackup: cd to /tmp/backup/2016-09-01_11-32-43/ xtrabackup: This target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1607773) xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2097152 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2097152 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: Using atomics to ref count buffer pool pages InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Memory barrier is not used InnoDB: Compressed tables use zlib 1.2.7 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, size = 100.0M InnoDB: Completed initialization of buffer pool InnoDB: Highest supported file format is Barracuda. InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 1607773 in the ib_logfiles! InnoDB: Database was not shutdown normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages InnoDB: from the doublewrite buffer... InnoDB: 128 rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: 5.6.24 started; log sequence number 1607773 xtrabackup: Last MySQL binlog file position 426, file name /var/lib/mysql-log/mastera.000021
xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1607783 xtrabackup: using the following InnoDB configuration for recovery: xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 2 xtrabackup: innodb_log_file_size = 5242880 InnoDB: Using atomics to ref count buffer pool pages InnoDB: The InnoDB memory heap is disabled InnoDB: Mutexes and rw_locks use GCC atomic builtins InnoDB: Memory barrier is not used InnoDB: Compressed tables use zlib 1.2.7 InnoDB: Using CPU crc32 instructions InnoDB: Initializing buffer pool, size = 100.0M InnoDB: Completed initialization of buffer pool InnoDB: Setting log file ./ib_logfile101 size to 5 MB InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 InnoDB: New log files created, LSN=1607783 InnoDB: Highest supported file format is Barracuda. InnoDB: 128 rollback segment(s) are active. InnoDB: Waiting for purge to start InnoDB: 5.6.24 started; log sequence number 1608204 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1608214 160901 11:36:37 completed OK! [root@mastera0 mysql]# innobackupex --copy-back /tmp/backup/2016-09-01_11-32-43/ 160901 11:36:56 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully. At the end of a successful copy-back run innobackupex prints "completed OK!".
innobackupex version 2.3.4 based on MySQL server 5.6.24 Linux (x86_64) (revision id: e80c779) 160901 11:36:56 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0 160901 11:36:56 [01] ...done 160901 11:36:56 [01] Copying ib_logfile1 to /var/lib/mysql/ib_logfile1 160901 11:36:56 [01] ...done 160901 11:36:56 [01] Copying ibdata1 to /var/lib/mysql/ibdata1 160901 11:36:56 [01] ...done 160901 11:36:57 [01] Copying ./mysql/tables_priv.frm to /var/lib/mysql/mysql/tables_priv.frm 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/tables_priv.MYI to /var/lib/mysql/mysql/tables_priv.MYI 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/time_zone.MYD to /var/lib/mysql/mysql/time_zone.MYD 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/ndb_binlog_index.MYD to /var/lib/mysql/mysql/ndb_binlog_index.MYD 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/plugin.MYD to /var/lib/mysql/mysql/plugin.MYD 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/proc.MYI to /var/lib/mysql/mysql/proc.MYI 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/procs_priv.frm to /var/lib/mysql/mysql/procs_priv.frm 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/procs_priv.MYD to /var/lib/mysql/mysql/procs_priv.MYD 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/proxies_priv.MYI to /var/lib/mysql/mysql/proxies_priv.MYI 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/servers.frm to /var/lib/mysql/mysql/servers.frm 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/servers.MYD to /var/lib/mysql/mysql/servers.MYD 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/time_zone.MYI to /var/lib/mysql/mysql/time_zone.MYI 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/time_zone_name.frm to /var/lib/mysql/mysql/time_zone_name.frm 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/time_zone_name.MYI to /var/lib/mysql/mysql/time_zone_name.MYI 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/time_zone_name.MYD to /var/lib/mysql/mysql/time_zone_name.MYD 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/time_zone_transition.frm to /var/lib/mysql/mysql/time_zone_transition.frm 160901 11:36:57 [01] ...done 160901 11:36:57 [01] Copying ./mysql/time_zone_transition.MYI to /var/lib/mysql/mysql/time_zone_transition.MYI 160901 11:36:57 [01] ...done 160901 11:36:58 [01] Copying ./mysql/time_zone_transition.MYD to /var/lib/mysql/mysql/time_zone_transition.MYD 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/time_zone_transition_type.frm to /var/lib/mysql/mysql/time_zone_transition_type.frm 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/time_zone_transition_type.MYI to /var/lib/mysql/mysql/time_zone_transition_type.MYI 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/time_zone_transition_type.MYD to /var/lib/mysql/mysql/time_zone_transition_type.MYD 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/user.MYI to /var/lib/mysql/mysql/user.MYI 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/user.MYD to /var/lib/mysql/mysql/user.MYD 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/time_zone_leap_second.frm to /var/lib/mysql/mysql/time_zone_leap_second.frm 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/time_zone_leap_second.MYI to /var/lib/mysql/mysql/time_zone_leap_second.MYI 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/general_log.frm to /var/lib/mysql/mysql/general_log.frm 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/general_log.CSM to /var/lib/mysql/mysql/general_log.CSM 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/general_log.CSV to /var/lib/mysql/mysql/general_log.CSV 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/slow_log.frm to /var/lib/mysql/mysql/slow_log.frm 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/slow_log.CSM to /var/lib/mysql/mysql/slow_log.CSM 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/slow_log.CSV to /var/lib/mysql/mysql/slow_log.CSV 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/tables_priv.MYD to /var/lib/mysql/mysql/tables_priv.MYD 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/columns_priv.frm to /var/lib/mysql/mysql/columns_priv.frm 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/columns_priv.MYI to /var/lib/mysql/mysql/columns_priv.MYI 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/columns_priv.MYD to /var/lib/mysql/mysql/columns_priv.MYD 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/db.frm to /var/lib/mysql/mysql/db.frm 160901 11:36:58 [01] ...done 160901 11:36:58 [01] Copying ./mysql/db.MYI to /var/lib/mysql/mysql/db.MYI 160901 11:36:58 [01] ...done 160901 11:36:59 [01] Copying ./mysql/db.MYD to /var/lib/mysql/mysql/db.MYD 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/event.frm to /var/lib/mysql/mysql/event.frm 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/event.MYI to /var/lib/mysql/mysql/event.MYI 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/event.MYD to /var/lib/mysql/mysql/event.MYD 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/func.frm to /var/lib/mysql/mysql/func.frm 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/func.MYI to /var/lib/mysql/mysql/func.MYI 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/func.MYD to /var/lib/mysql/mysql/func.MYD 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_category.frm to /var/lib/mysql/mysql/help_category.frm 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_category.MYI to /var/lib/mysql/mysql/help_category.MYI 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_category.MYD to /var/lib/mysql/mysql/help_category.MYD 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_keyword.frm to /var/lib/mysql/mysql/help_keyword.frm 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_keyword.MYI to /var/lib/mysql/mysql/help_keyword.MYI 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_keyword.MYD to /var/lib/mysql/mysql/help_keyword.MYD 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_relation.frm to /var/lib/mysql/mysql/help_relation.frm 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_relation.MYI to /var/lib/mysql/mysql/help_relation.MYI 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_relation.MYD to /var/lib/mysql/mysql/help_relation.MYD 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_topic.frm to /var/lib/mysql/mysql/help_topic.frm 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_topic.MYI to /var/lib/mysql/mysql/help_topic.MYI 160901 11:36:59 [01] ...done 160901 11:36:59 [01] Copying ./mysql/help_topic.MYD to /var/lib/mysql/mysql/help_topic.MYD 160901 11:36:59 [01] ...done 160901 11:37:00 [01] Copying ./mysql/host.frm to /var/lib/mysql/mysql/host.frm 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/host.MYI to /var/lib/mysql/mysql/host.MYI 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/host.MYD to /var/lib/mysql/mysql/host.MYD 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/ndb_binlog_index.frm to /var/lib/mysql/mysql/ndb_binlog_index.frm 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/ndb_binlog_index.MYI to /var/lib/mysql/mysql/ndb_binlog_index.MYI 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/plugin.frm to /var/lib/mysql/mysql/plugin.frm 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/plugin.MYI to /var/lib/mysql/mysql/plugin.MYI 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/proc.frm to /var/lib/mysql/mysql/proc.frm 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/proc.MYD to /var/lib/mysql/mysql/proc.MYD 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/procs_priv.MYI to /var/lib/mysql/mysql/procs_priv.MYI 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/proxies_priv.frm to /var/lib/mysql/mysql/proxies_priv.frm 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/proxies_priv.MYD to /var/lib/mysql/mysql/proxies_priv.MYD 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/servers.MYI to /var/lib/mysql/mysql/servers.MYI 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/time_zone.frm to /var/lib/mysql/mysql/time_zone.frm 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./mysql/time_zone_leap_second.MYD to /var/lib/mysql/mysql/time_zone_leap_second.MYD 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./test/db.opt to /var/lib/mysql/test/db.opt 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./performance_schema/cond_instances.frm to /var/lib/mysql/performance_schema/cond_instances.frm 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./performance_schema/events_waits_current.frm to /var/lib/mysql/performance_schema/events_waits_current.frm 160901 11:37:00 [01] ...done 160901 11:37:00 [01] Copying ./performance_schema/events_waits_history.frm to /var/lib/mysql/performance_schema/events_waits_history.frm 160901 11:37:00 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/events_waits_history_long.frm to /var/lib/mysql/performance_schema/events_waits_history_long.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/events_waits_summary_by_instance.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_instance.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/events_waits_summary_by_thread_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_by_thread_by_event_name.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/events_waits_summary_global_by_event_name.frm to /var/lib/mysql/performance_schema/events_waits_summary_global_by_event_name.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/file_instances.frm to /var/lib/mysql/performance_schema/file_instances.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/file_summary_by_event_name.frm to /var/lib/mysql/performance_schema/file_summary_by_event_name.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/file_summary_by_instance.frm to /var/lib/mysql/performance_schema/file_summary_by_instance.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/mutex_instances.frm to /var/lib/mysql/performance_schema/mutex_instances.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/performance_timers.frm to /var/lib/mysql/performance_schema/performance_timers.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/rwlock_instances.frm to /var/lib/mysql/performance_schema/rwlock_instances.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/setup_consumers.frm to /var/lib/mysql/performance_schema/setup_consumers.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/setup_instruments.frm to /var/lib/mysql/performance_schema/setup_instruments.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/setup_timers.frm to /var/lib/mysql/performance_schema/setup_timers.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./performance_schema/threads.frm to /var/lib/mysql/performance_schema/threads.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./db1/db.opt to /var/lib/mysql/db1/db.opt 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./db1/t1.frm to /var/lib/mysql/db1/t1.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./db2/db.opt to /var/lib/mysql/db2/db.opt 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./db2/t1.frm to /var/lib/mysql/db2/t1.frm 160901 11:37:01 [01] ...done 160901 11:37:01 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info 160901 11:37:01 [01] ...done 160901 11:37:02 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb 160901 11:37:02 [01] ...done 160901 11:37:02 completed OK! [root@mastera0 mysql]# [root@mastera0 mysql]# ll /var/lib/mysql total 28688 drwx------. 2 root root 32 Sep 1 11:37 db1 drwx------. 2 root root 32 Sep 1 11:37 db2 -rw-r-----. 1 root root 18874368 Sep 1 11:36 ibdata1 -rw-r-----. 1 root root 5242880 Sep 1 11:36 ib_logfile0 -rw-r-----. 1 root root 5242880 Sep 1 11:36 ib_logfile1 drwx------. 2 root root 4096 Sep 1 11:37 mysql drwx------. 2 root root 4096 Sep 1 11:37 performance_schema drwx------. 2 root root 19 Sep 1 11:37 test -rw-r-----. 1 root root 38 Sep 1 11:37 xtrabackup_binlog_pos_innodb -rw-r-----. 1 root root 473 Sep 1 11:37 xtrabackup_info [root@mastera0 mysql]# ll /var/lib/mysql -d drwxr-xr-x. 7 mysql mysql 4096 Sep 1 11:37 /var/lib/mysql [root@mastera0 mysql]# chown mysql. /var/lib/mysql/ -R [root@mastera0 mysql]# ll /var/lib/mysql total 28688 drwx------. 2 mysql mysql 32 Sep 1 11:37 db1 drwx------. 2 mysql mysql 32 Sep 1 11:37 db2 -rw-r-----. 1 mysql mysql 18874368 Sep 1 11:36 ibdata1 -rw-r-----. 1 mysql mysql 5242880 Sep 1 11:36 ib_logfile0 -rw-r-----. 1 mysql mysql 5242880 Sep 1 11:36 ib_logfile1 drwx------. 2 mysql mysql 4096 Sep 1 11:37 mysql drwx------. 2 mysql mysql 4096 Sep 1 11:37 performance_schema drwx------. 2 mysql mysql 19 Sep 1 11:37 test -rw-r-----. 1 mysql mysql 38 Sep 1 11:37 xtrabackup_binlog_pos_innodb -rw-r-----. 1 mysql mysql 473 Sep 1 11:37 xtrabackup_info [root@mastera0 mysql]# getenforce Permissive [root@mastera0 mysql]#
[root@mastera0 mysql]# systemctl start maraidb Failed to start maraidb.service: Unit maraidb.service failed to load: No such file or directory. [root@mastera0 mysql]# systemctl start mariadb [root@mastera0 mysql]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> \q Bye =========================================== # 增量备份并还原 [root@mastera0 mysql]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# at 1017 #160831 15:09:29 server id 1 end_log_pos 1109 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; insert into db2.t1 values (4),(5) /*!*/;
1)14:00 mysqldump db1.t1 3 4 5 6 7 8 9 10 11 12 db2.t1 2)14:00-16:00 insert into db1.t1 values (13),(14); delete from db1.t1; insert into db2.t1 values (1),(2),(3); delete from db2.t1; insert into db2.t1 values (4),(5); 3)16:00 恢复 start at 245 at 598 at 953 stop at 430 at 785 at 1136
课堂实战5：基于二进制日志时间点和位置的数据库备份恢复模拟
# 打开二进制日志 open binlog [root@mastera0 ~]# setenforce 0 [root@mastera0 ~]# getenforce Permissive [root@mastera0 ~]# vim /etc/my.cnf [root@mastera0 ~]# mkdir /var/lib/mysql-log [root@mastera0 ~]# chown mysql. /var/lib/mysql-log [root@mastera0 ~]# ll -d /var/lib/mysql-log drwxr-xr-x. 2 mysql mysql 6 Aug 31 10:33 /var/lib/mysql-log [root@mastera0 ~]# systemctl start mariadb [root@mastera0 ~]# cd /var/lib/mysql-log [root@mastera0 mysql-log]# ll total 8 -rw-rw----. 1 mysql mysql 245 Aug 31 10:35 mastera.000001 -rw-rw----. 1 mysql mysql 34 Aug 31 10:35 mastera.index # 执行写操作 ddl dcl dml [root@mastera0 ~]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec)
MariaDB [(none)]> select * from db1.t1; Empty set (0.00 sec)
MariaDB [(none)]> \q ## 开始恢复数据
[root@mastera0 ~]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> desc db2.t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)
MariaDB [(none)]> select * from db2.t1; Empty set (0.00 sec)
--------------------------------------- # 数据库备份恢复演习2 ## 模拟场景 [root@mastera0 ~]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 9 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, 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 | | db1 | | db2 | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec)
MariaDB [(none)]> select * from db1.t1; Empty set (0.00 sec)
MariaDB [(none)]> select * from db2.t1; ERROR 1146 (42S02): Table \'db2.t1\' doesn\'t exist MariaDB [(none)]> \q Bye ## 数据还原 [root@mastera0 ~]# systemctl stop mariadb [root@mastera0 ~]# rm -rf /var/lib/mysql/* [root@mastera0 ~]# systemctl start mariadb [root@mastera0 ~]# mysql < /tmp/mysql.12.mysql [root@mastera0 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> \q Bye [root@mastera0 ~]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from t1; ERROR 1046 (3D000): No database selected MariaDB [(none)]> select * from db1.t1; +----+ | id | +----+ | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+ 8 rows in set (0.00 sec)
[root@mastera0 mysql-log]# mysqlbinlog --start-datetime='2016-08-31 13:45:10' --stop-datetime='2016-08-31 13:45:42' /var/lib/mysql-log/mastera.000006|mysql -uroot -puplooking ### 检测 [root@mastera0 mysql-log]# mysql -uroot -puplookingWelcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, 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 | | db1 | | db2 | | mysql | | performance_schema | | test | +--------------------+ 6 rows in set (0.00 sec)
MariaDB [(none)]> use db2; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed MariaDB [db2]> show tables; +---------------+ | Tables_in_db2 | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec)
MariaDB [db2]> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec)
[root@mastera0 mysql-log]# mysqldump -uroot -puplooking -A --master-data=2 --flush-logs > /tmp/mysql.14.mysql
---------------------------------- # 数据库备份恢复演习3 ## 模拟场景 [root@mastera0 mysql-log]# vim /tmp/mysql.test.sql [root@mastera0 mysql-log]# cat /tmp/mysql.test.sql insert into db1.t1 values (13),(14); delete from db1.t1; insert into db2.t1 values (1),(2),(3); delete from db2.t1; insert into db2.t1 values (4),(5); [root@mastera0 mysql-log]# mysql -uroot -puplooking < /tmp/mysql.test.sql [root@mastera0 mysql-log]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1; Empty set (0.00 sec)
MariaDB [(none)]> select * from db2.t1; +----+ | id | +----+ | 4 | | 5 | +----+ 2 rows in set (0.00 sec)
MariaDB [(none)]> \q Bye ## 数据还原 [root@mastera0 ~]# systemctl stop mariadb [root@mastera0 ~]# rm -rf /var/lib/mysql/* [root@mastera0 ~]# systemctl start mariadb [root@mastera0 ~]# mysql < /tmp/mysql.14.mysql [root@mastera0 ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> \q Bye [root@mastera0 ~]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 4 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db2.t1; Empty set (0.00 sec)
MariaDB [(none)]> \q Bye [root@mastera0 ~]# sed -n '22p' /tmp/mysql.14.mysql -- CHANGE MASTER TO MASTER_LOG_FILE='mastera.000015', MASTER_LOG_POS=245; [root@mastera0 ~]# mysqlbinlog /var/lib/mysql-log/mastera.000015 /*!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 #160831 15:06:16 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.44-MariaDB-log created 160831 15:06:16 BINLOG \' 6IHGVw8BAAAA8QAAAPUAAAAAAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAKeZqQ== \'/*!*/; # at 245 #160831 15:09:29 server id 1 end_log_pos 309 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; SET @@session.pseudo_thread_id=11/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=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 309 #160831 15:09:29 server id 1 end_log_pos 403 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; insert into db1.t1 values (13),(14) /*!*/; # at 403 #160831 15:09:29 server id 1 end_log_pos 430 Xid = 646 COMMIT/*!*/; # at 430 #160831 15:09:29 server id 1 end_log_pos 494 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; BEGIN /*!*/; # at 494 #160831 15:09:29 server id 1 end_log_pos 571 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; delete from db1.t1 /*!*/; # at 571 #160831 15:09:29 server id 1 end_log_pos 598 Xid = 647 COMMIT/*!*/; # at 598 #160831 15:09:29 server id 1 end_log_pos 662 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; BEGIN /*!*/; # at 662 #160831 15:09:29 server id 1 end_log_pos 758 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; insert into db2.t1 values (1),(2),(3) /*!*/; # at 758 #160831 15:09:29 server id 1 end_log_pos 785 Xid = 648 COMMIT/*!*/; # at 785 #160831 15:09:29 server id 1 end_log_pos 849 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; BEGIN /*!*/; # at 849 #160831 15:09:29 server id 1 end_log_pos 926 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; delete from db2.t1 /*!*/; # at 926 #160831 15:09:29 server id 1 end_log_pos 953 Xid = 649 COMMIT/*!*/; # at 953 #160831 15:09:29 server id 1 end_log_pos 1017 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; BEGIN /*!*/; # at 1017 #160831 15:09:29 server id 1 end_log_pos 1109 Query thread_id=11 exec_time=0 error_code=0 SET TIMESTAMP=1472627369/*!*/; insert into db2.t1 values (4),(5) /*!*/; # at 1109 #160831 15:09:29 server id 1 end_log_pos 1136 Xid = 650 COMMIT/*!*/; # at 1136 #160831 15:10:32 server id 1 end_log_pos 1155 Stop DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@mastera0 ~]# [root@mastera0 ~]# mysqlbinlog --start-position=245 --stop-position=430 /var/lib/mysql-log/mastera.000015|mysql -uroot -puplooking [root@mastera0 ~]# mysqlbinlog --start-position=598 --stop-position=785 /var/lib/mysql-log/mastera.000015|mysql -uroot -puplooking [root@mastera0 ~]# mysqlbinlog --start-position=953 --stop-position=1136 /var/lib/mysql-log/mastera.000015|mysql -uroot -puplooking [root@mastera0 ~]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 8 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db2.t1; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | +----+ 5 rows in set (0.00 sec)
MariaDB [(none)]> \q
[root@mastera0 mysql-log]# vim /tmp/mysql.test.sql [root@mastera0 mysql-log]# cat /tmp/mysql.test.sql insert into db1.t1 values (13),(14); delete from db1.t1; insert into db2.t1 values (1),(2),(3); delete from db2.t1; insert into db2.t1 values (4),(5); [root@mastera0 mysql-log]# mysql -uroot -puplooking < /tmp/mysql.test.sql [root@mastera0 mysql-log]# mysql -uroot -puplooking Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select * from db1.t1; Empty set (0.00 sec)
MariaDB [(none)]> select * from db2.t1; +----+ | id | +----+ | 4 | | 5 | +----+ 2 rows in set (0.00 sec)
