Using mysqldump to back up a database

XXX Request: Backup all databases of MySQL on the online test environment

[Email protected] data]# cd/app/mysql/data/

[Email protected] data]# Du-sh

2.9G.

Only 2.9G of data, data capacity is not very large, so I choose to use mysqldump for backup

The backup time only takes 1-2s time.

Backup file size is only 280M, 1/10 of the original file

General situation: The original file size is dozens of times times the backup file

[Email protected] ftp.scj.com]# Ps-ef | grep MySQL

Root 5642 1211 11:16 pts/0 00:00:00 grep mysql

Root 29558 1 0 Apr14 pts/1 00:00:00/bin/sh/usr/local/mysql/bin/mysqld_safe--datadir=/opt/mysql/data--pid-f Ile=/opt/mysql/data/www.scj.com.pid

MySQL 29758 29558 0 Apr14 pts/1 00:00:18/usr/local/mysql/bin/mysqld--basedir=/usr/local/mysql--datadir=/opt/mysql /data--plugin-dir=/usr/local/mysql/lib/plugin--user=mysql--log-error=/var/log/mysqld.log--pid-file=/opt/mysql/ Data/www.scj.com.pid--socket=/var/lib/mysql/mysql.sock

Requirement: Full backup once daily 1 o'clock in the morning

Keep backup file data for three days

Keep binary log files for three days

Recovery of data after backup using binary log files

Backup steps:

2. To modify a MySQL configuration file:

   Open Binary log file

   Vi/usr/local/mysql/my.cnf

   Add a line below [mysqld]:

   Log-bin=/opt/mysql/binlog/mysql-binlog (place binary log files separately in one directory)

4. Create backup directory and binary log directory:

   mkdir-p/opt/mysql/{backup,binlog}

   cd/opt/mysql/

    

   Chown-r Mysql.mysql backup Binlog                 (Modify permissions)

   Restart the MySQL service:  /etc/ Init.d/mysqld Reload

   [[email protected] ~]# ls/opt/mysql/binlog/           & nbsp;  (see if binaries are generated)

   mysql-binlog.000001  mysql-binlog.index

   Note: The binary log file is only executed after it is generated mysqldump , otherwise execution will fail; if there is no binary log file generation after reload, then only try: Stop the MySQL first and start again

6. Use mysqldump for full backups:

   To back up all libraries except Database|information_schema|mysql|test|performance_schema, use the --databases parameter, remember

   Mysql-u root-p123456-e "Show Databases" | Grep-ev "Database|information_schema|mysql|test|performance_schema" |xargs mysqldump-uroot-p123456-- Lock-all-tables--routines--events--triggers--master-data=2--flush-logs--add-drop-database--add-drop-table-- Databases >/opt/mysql/backup/mysql_full_ ' Date +%f-%h:%m:%s '. sql

   Note: Executing this command screen will output one line:

   Warning:using a password on the command line interface can is insecure.

   It is not safe to enter a password at the command line, it is completely possible to ignore this line, no tube (grep itself ignores this sentence)

-U #指定用户名

-P #指定用户密码

-H #指定主机地址

-a|--all-databases #备份所有数据库

--databases #备份指定数据库

--single-transcation #基于此项可以实现对InnoDB表做热备份, but do not need to use

--lock-all-tables #执行备份时为所有表请求加锁

-e|--events #备份事件调度器代码

--opt #同时启动各种高级选项

-r|--routines #备份存储过程和存储函数

--flush-logs #备份之前刷新日志

--triggers #备份触发器

--master-data=2 #该选项将会记录binlog的日志位置与文件名并追加到备份文件中, (if 1 will output the Change Master command, which is useful under Master and slave)

As follows:

[email protected] backup]# less Mysql_full_2015-04-15-11-54-07.sql

There is a line inside:

--Change MASTER to master_log_file= ' mysql-binlog.000002 ', master_log_pos=120;

Binary log file name and POS signal generated after backup, all operations will be recorded to mysql-binlog.000002

Recovery steps:

If, at some point, the OPS operator mistakenly operated, a drop operation was performed:

Restore start:

First, use the full backup file to restore the data to the backup:

Cd/opt/mysql/data

All libraries except Information_schema|mysql|test|performance_schema are temporarily moved to a directory:

MV $ (mysql-u root-p123456-e "Show Databases" | Grep-ev "Database|information_schema|mysql|test|performance_schema" |x args)/tmp/mysql/linshi

Find the last full backup file:

Cd/opt/mysql/backup

mysql-uroot-p123456 < Mysql_full_2015-04-15-13-36-50.sql (revert to data as backup)

Then use the binary log file to revert to the data when the drop was executed:

cd/opt/mysql/binlog/

Based on the records in the last backup file, locate the binary log file name and the starting POS signal:

[email protected] backup]# less Mysql_full_2015-04-15-11-54-07.sql

There is a line inside:

--Change MASTER to master_log_file= ' mysql-binlog.000002 ', master_log_pos=120;

The POS signal at the time of the drop is found based on the binary log file (i.e., the POS signal at the end):

[Email protected] binlog]# Mysqlbinlog mysql-binlog.000002 |less

# at 965

#150415 12:04:27 Server ID 1 end_log_pos 1060 CRC32 0xd681bd27 Query thread_id=2 exec_time=1 error_code =0

SET timestamp=1429113867/*!*/;

Drop Database Allen

/*!*/;

DELIMITER;

Locate the location where the drop was executed, as above, and find that the POS signal before the drop was executed is 965

Start recovery with Mysqlbinlog:

Mysqlbinlog--start-position=120--stop-position=965 mysql-binlog.000002 |mysql-uroot-p123456

NOTE: POS signal 120 and log file name 000002 are recorded in the backup file, this needs to be noted

Note: The following error may be reported when using the Mysqlbinlog command to read the binary log file:

Mysqlbinlog:unknown variable ' Default-character-set=utf8 '

Workaround: Simply add the--no-defaults parameter, i.e.:

Mysqlbinlog--no-defaults mysql-binlog.000002 |less

Automate backups using scripts and Scheduled tasks:

vi/usr/local/mysql/run/mysql_backup_full.sh

#!/bin/bash

#这个脚本用来对数据库作全备份

User=root

password=123456

backupdir=/opt/mysql/backup/#备份目录

#取一天前备份时生成的那个二进制日志文件

val_binlog=$ (Less/opt/mysql/binlog/mysql-binlog.index | awk-f "/" ' {print $} ' |tail-n 2|head-n 1)

#创建备份目录

[!-D $backupdir] && mkdir-p $backupdir

#备份除Database |information_schema|mysql|test|performance_schema All libraries outside of these databases

Mysql-u $user-p$password-e "Show Databases" | Grep-ev "Database|information_schema|mysql|test|performance_schema" |xargs mysqldump-u $user-p$password-- Lock-all-tables--routines--events--triggers--master-data=2--flush-logs--databases > ${backupdir}mysql_full_ ' Date +%f-%h:%m:%s '. sql

#删除两天前的备份文件, which is to keep three backup files

Find $backupdir-type f-mtime +2-exec rm-f {} \;

#删除两天前的二进制日志文件, which preserves three binary log files

Mysql-u $user-p$password-e "PURGE MASTER LOGS to ' $val _binlog '"

[[email protected] run] #chmod 700/usr/local/mysql/run/mysql_backup_full.sh (plus x execute permission)

Note: This script contains the root password of MySQL, for security, set 700 permissions, no one else can access

[[email protected] run] #crontab-E (Create a scheduled Task)

#分 The Sun and Moon Week user name command

0 1 * * * cd/usr/local/mysql/run;. /mysql_backup_full.sh

Using mysqldump to back up a database