Mysqldump and snapshot-based backups

<title>Mysqldump and snapshot-based backups</title> Mysqldump and snapshot-based backup table of Contents

• Mysqldump: only for small datasets
• Lvm-snapshot: LVM Snapshot-based backup
• Related reading

Mysqldump: only for small datasets

Mysqldump db_name [tbname1] [tbname2]

# Mysqldump-uroot-pyour_password db_name > test.sqlmysql> CREATE database Test2;mysqldump-uroot-pyour_password t Est2 < Test.sql

Back up all the libraries

Mysqldump--all-databases > Test.sql

Backing up a specified number of libraries

--databases tb1 tb2 .....

Recovery

MySQL < test.mysqlmysql database_name < Test.sql

Lock before backup

Mysqldump--lock-all-tables: Request lock All tables after backup, to MyISAM, InnoDB, Aria do Win Bei mysqldump--lock-tables tables_name [table1] [table2 ]

If it's InnoDB,

Mysqldump--single-transaction: Be able to InnoDB storage engine to achieve hot standby, this time do not have to lock-all-tables

Backup code

--events: Backup Event Scheduler Code--routines: Backup stored procedure and storage function--triggers: Backup trigger

Scroll logs when backing up:

--flush-logs: Before a backup, the log is requested to scroll after the lock

Sync location Tag when copying

--master-data={0|1|2}0 means no record (default) 1 indicates record as change Master Statement 2 indicates record as comment change master statement dump a master replication server to PR Oduce A dump file Thatcan is used to set up another server as a slave of the master. It causes the dump output to include a change MASTER tostatement that indicates the binary log coordinates (file name and P Osition) of the dumped server. These is themaster server coordinates from which the slave should startreplicating after your load the dump file into the Slave

Using mysqldump Backup
Request Lock: –lock-all-tables or using –single-transaction for InnoDB hot standby
Scrolling log: –flush-logs
Select the library you want to back up –databases
Record binary files and location –master-data

To request a global lock manually

> Flush tables with read lock> flush logs; (Under Shell: Mysqladmin flush-logs) > Unlock Tables

Recovery:
Recommendation: Turn off binary logging and turn off write operations for other users set sql_bin_log = 0;
Should use source Test.sql

Backup strategy: Based on mysqldump
Backup:
mysqldump+ binary log files
Sunday make a full backup, back up the simultaneous scrolling log
Monday to Saturday: Backing up binary logs

Recovery: Full backup + events from each binary file to the moment
The MySQL configuration file and the MySQL-related OS profile should be backed up after each modification

Lvm-snapshot: LVM Snapshot-based backup

1. The transaction log must be on the same volume as the data file (also note that there is no commit transaction)
   
2. To request a global lock on MySQL before creating a snapshot volume
   
3. Log scrolling Once the global lock is completed, binary log files and location tags (manual)
   
4. Release lock
   

Steps:

1. Request a global lock and scroll the log
   Flush tables with read lock;
   Flush logs;
   
2. Binary log files and location tags (manual)
   Mysql-e ' Show Master Status ' >/path/to/somefile (then perform corresponding action on slave machine)
   
3. To create a snapshot volume
   Lvcreate-l size-s-N data_back-p r/dev/data/mysqldata
   
4. Releasing a global lock
   Unlock tables
   
5. Mount a snapshot and back up
   Cp
   
6. After the backup is complete, delete the snapshot volume
   Lvremove
   

Related reading

• MySQL Backup
  
• Lvm
  

Mysqldump and snapshot-based backups