MariaDB备份还原修复

MariaDB备份还原修复

mysqldump

语法：mysqldump [-u username] [-p] database_name [table_name]

如：mysqldump -u root -p zabbix >zabbix.sql

如果没有指定table_name,会备份指定的database中的所有表

mysql还原：

mysql -u root -p zabbix

tab-delimited file(导入，导出)

借助--tab选项可以将数据库或表导出为tab-delimited file,以便excel能成功读取绘图分析等，也方便从excel导入到数据库，会在指定的目录生成两个表名.{sql,txt}的文件

root@jlive:~#mysqldump --tab /tmp/ -u root -p zabbix users

Enter password: 

root@jlive:~#ls /tmp/users.*

/tmp/users.sql  /tmp/users.txt

恢复，导入数据库--mysqlimport

语法：mysqlimport [--local] [-u username] [-p] database_name filename

--local #从本地文件系统读取而非数据库读取

root@jlive:~#mysqlimport --local -uroot -p zabbix /tmp/users.txt 

Enter password: 

zabbix.users: Records: 3  Deleted: 0  Skipped: 2  Warnings: 2

任何不能被导入的记录都会被忽略并给出提示

mysqlhotcopy

只能快速备份基于MyISAM,ARCHIVE存储引擎的表，实际上是一个perl脚本

语法：mysqlhotcopy db_name [/path/to/new_directory]

如果备份目录没有指定，默认将备份到MariaDB的data目录

cold backups

直接copy整个MariaDB data目录，数据库迁移时也可以采用，在新数据库主机上直接放到对应的data目录后修改文件系统权限即可。

/opt/mairadb/data/{zabbix,mysql}

检查和优化表--mysqlcheck

语法：mysqlcheck [options] [-u username] [-p] database_name [table_name]

root@jlive:~#mysqlcheck -uroot -p zabbix users groups

Enter password: 

zabbix.users                                       OK 

zabbix.groups                                      OK

check所有的数据库--all-databases

mysqlcheck -uroot -p --all-databases

注意,optimiz,repair并不适用所有的存储引擎，如InnoDB,XtraDB表就不支持,但MyISAM,Aria存储引擎能很好支持

--analyze #分析

--optimize #优化

--repair #修复

当然，InnoDB,XtraDB也提供了非常安全的纠错机制

[mysqld]

innodb_force_recovery = 3 #0或者不存在该条目为禁用，1-6值越大越倾向自我repair

https://mariadb.com/kb/en/xtradbinnodb-recovery-modes/

XtraBackup

官网喧称唯一开源的热备企业级解决方案

https://www.percona.com/doc/percona-xtrabackup/2.4/index.html

说明：innobackupex是xtrabackup的一个封装，默认情况下，xtrabackup只能备份InnoDB and XtraDB, 而innobackupex还可以备份MyISAM, Aria, and other non-InnoDB tables

数据库版本MariaDB-10.1.10

 XtraBackup-2.4.1

1.安装

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.1/binary/tarball/percona-xtrabackup-2.4.1-Linux-x86_64.tar.gz

tar -xvf percona-xtrabackup-2.4.1-Linux-x86_64.tar.gz -C /opt/

ln -s /opt/percona-xtrabackup-2.4.1-Linux-x86_64/ /opt/percona-xtrabackup

vim /etc/profile

PATH=$PATH:/opt/percona-xtrabackup/bin

source /etc/profile

vim /etc/man.conf

MANPATH /opt/percona-xtrabackup/man

2.备份

innobackupex --user=backupuser --password=password --parallel=4 --default-file=/etc/my.cnf /root/mariadb_backup/

160325 00:25:19 Executing UNLOCK TABLES

160325 00:25:19 All tables unlocked

160325 00:25:19 Backup created in directory '/root/mariadb_backup/2016-03-25_00-25-18'

MySQL binlog position: filename 'mysql-bin.000004', position '7533157', GTID of the last change '0-1-1390'

160325 00:25:19 [00] Writing backup-my.cnf

160325 00:25:19 [00]        ...done

160325 00:25:19 [00] Writing xtrabackup_info

160325 00:25:19 [00]        ...done

xtrabackup: Transaction log of lsn (26735665) to (26736838) was copied.

 

160325 00:25:19 completed OK!

提示：备份时可以启用压缩，更多帮助请man innobackupex

innobackupex --user=backupuser --password=password --parallel=4 --stream=xbstream --compress --compress-threads=4 /root/mariadb_bakcup/ >/root/mariadb_backup/full.xbstream

或

innobackupex --user=backupuser --password=password --parallel=4 --stream=tar ./|bzip2 - >full.tbz2

3.还原

第一步：

root@jlive:~#innobackupex --user=backupuser --password=password --default-file=/etc/my.cnf  --use-memory=4G --apply-log /root/mariadb_backup/2016-03-25_00-25-18/

160325 00:26:47 innobackupex: Starting the apply-log operation

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.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)

xtrabackup: cd to /root/mariadb_backup/2016-03-25_00-25-18

xtrabackup: This target seems to be not prepared yet.

InnoDB: Number of pools: 1

xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(26735665)

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup:   innodb_data_home_dir = .

xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup:   innodb_log_group_home_dir = .

xtrabackup:   innodb_log_files_in_group = 1

xtrabackup:   innodb_log_file_size = 8388608

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup:   innodb_data_home_dir = .

xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup:   innodb_log_group_home_dir = .

xtrabackup:   innodb_log_files_in_group = 1

xtrabackup:   innodb_log_file_size = 8388608

xtrabackup: Starting InnoDB instance for recovery.

xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)

InnoDB: PUNCH HOLE support not available

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Uses event mutexes

InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.3

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority: -20

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 26735665

InnoDB: Doing recovery: scanned up to log sequence number 26736838 (0%)

InnoDB: Doing recovery: scanned up to log sequence number 26736838 (0%)

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: Doing recovery: scanned up to log sequence number 26736838 (0%)

InnoDB: Starting an apply batch of log records to the database...

InnoDB: Progress in percent: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 

InnoDB: Apply batch completed

InnoDB: xtrabackup: Last MySQL binlog file position 7533157, file name ./mysql-bin.000004

InnoDB: Creating shared tablespace for temporary tables

InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

InnoDB: File './ibtmp1' size is now 12 MB.

InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.

InnoDB: 32 non-redo rollback segment(s) are active.

InnoDB: 5.7.10 started; log sequence number 26736838

InnoDB: not started

InnoDB: xtrabackup: Last MySQL binlog file position 7533157, file name ./mysql-bin.000004

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 26736857

InnoDB: Number of pools: 1

xtrabackup: using the following InnoDB configuration for recovery:

xtrabackup:   innodb_data_home_dir = .

xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend

xtrabackup:   innodb_log_group_home_dir = .

xtrabackup:   innodb_log_files_in_group = 2

xtrabackup:   innodb_log_file_size = 50331648

InnoDB: PUNCH HOLE support not available

InnoDB: Mutexes and rw_locks use GCC atomic builtins

InnoDB: Uses event mutexes

InnoDB: GCC builtin __sync_synchronize() is used for memory barrier

InnoDB: Compressed tables use zlib 1.2.3

InnoDB: Number of pools: 1

InnoDB: Using CPU crc32 instructions

InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M

InnoDB: Completed initialization of buffer pool

InnoDB: page_cleaner coordinator priority: -20

InnoDB: Setting log file ./ib_logfile101 size to 48 MB

InnoDB: Setting log file ./ib_logfile1 size to 48 MB

InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

InnoDB: New log files created, LSN=26736857

InnoDB: Highest supported file format is Barracuda.

InnoDB: Log scan progressed past the checkpoint lsn 26737164

InnoDB: Doing recovery: scanned up to log sequence number 26737173 (0%)

InnoDB: Doing recovery: scanned up to log sequence number 26737173 (0%)

InnoDB: Database was not shutdown normally!

InnoDB: Starting crash recovery.

InnoDB: xtrabackup: Last MySQL binlog file position 7533157, file name ./mysql-bin.000004

InnoDB: Removed temporary tablespace data file: "ibtmp1"

InnoDB: Creating shared tablespace for temporary tables

InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

InnoDB: File './ibtmp1' size is now 12 MB.

InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.

InnoDB: 32 non-redo rollback segment(s) are active.

InnoDB: 5.7.10 started; log sequence number 26737173

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: not started

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 26738761

160325 00:26:53 completed OK!

第二步：

Note: The datadir must be empty; Percona XtraBackup innobackupex --copy-back option will not copy over existing files unlessinnobackupex --force-non-empty-directories option is specified. Also it’s important to note that MySQL server needs to be shut down before restore is performed. You can’t restore to a datadir of a running mysqld instance (except when importing a partial backup). 

简言之，就是数据库data目录要为空，否则会报如下错误

160325 00:37:05 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.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)

Original data directory . is not empty!

root@jlive:~#mv /opt/mariadb/data/ /opt/

root@jlive:~#mkdir /opt/mariadb/data

这里特别说明，

我测试的xtrabackup版本2.3.4,2.4.1都有这个问题，即使data目录为空了仍然报Original data directory . is not empty!

解决办法：在配置文件/etc/my.cnf的mysqld章节指明datadir

[mysqld]

datadir = /opt/mariadb/data

service mysql stop #先停数据库再还原

root@jlive:~#innobackupex --user=backupuser --password=password --default-file=/etc/my.cnf  --copy-back/root/mariadb_backup/2016-03-23_23-58-49/

160325 01:07:19 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.4.1 based on MySQL server 5.7.10 Linux (x86_64) (revision id: a2dc9d4)

160325 01:07:19 [01] Copying ib_logfile0 to /opt/mariadb/data/ib_logfile0

160325 01:07:19 [01]        ...done

160325 01:07:19 [01] Copying ib_logfile1 to /opt/mariadb/data/ib_logfile1

160325 01:07:19 [01]        ...done

160325 01:07:19 [01] Copying ibdata1 to /opt/mariadb/data/ibdata1

 

160325 01:07:19 [01]        ...done

... ...

160325 01:08:10 [01]        ...done

160325 01:08:10 [01] Copying ./xtrabackup_info to /opt/mariadb/data/xtrabackup_info

160325 01:08:10 [01]        ...done

160325 01:08:10 [01] Copying ./xtrabackup_binlog_pos_innodb to /opt/mariadb/data/xtrabackup_binlog_pos_innodb

160325 01:08:10 [01]        ...done

160325 01:08:10 [01] Copying ./ibtmp1 to /opt/mariadb/data/ibtmp1

160325 01:08:10 [01]        ...done

 

160325 01:08:10 completed OK!

第三步：

还原完成后修改权限后启动

chown -R mysql: /opt/mariadb/data

service mysql start