Using mysqldump to back up a database

Manager's request: Backup all MySQL databases 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 chose to use mysqldump for backup

[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 data for one week

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 (put binary log files in one directory alone)

4. To create a backup directory and a binary log directory:

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

   cd/opt/mysql/
   

   
   

   Chown-r mysql.mysql Backup Binlog (Modify permissions)

   Restart MySQL service:/etc/init.d/mysqld Reload

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

-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=;

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, a worker mistakenly operates, a drop operation is 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 Db_act db_ad db_admin db_as_s1 db_caiwu db_cm_s0 db_cm_s1 db_cm_s2 db_cm_s3 db_csw db_dabao db_device db_game db_game_i NFO db_he_s1 db_market db_opauth db_pay db_sdk_game db_sdk_market db_sdk_online db_sdk_pay db_sdk_snapshot Db_sdk_tongji Db_sdk_user db_site db_stat db_stat_log db_user db_webauth dss_gc sdk_stat_log Website/tmp/mysql/lishi

Locate the file for the full backup:

Cd/opt/mysql/backup

mysql-uroot-p123456 < Mysql_full_2015-04-15-13-36-50.sql

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

cd/opt/mysql/binlog/

[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= --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

This article is from the "See" blog, please be sure to keep this source http://732233048.blog.51cto.com/9323668/1633051

Using mysqldump to back up a database