Knowledge Point: Linux database backup

Server-side Enable binary logging

If the log does not start, you must enable Binlog, to restart MySQL, first, turn off MySQL, open/etc/my.cnf, add the following lines:

[Mysqld]log-bin

And then restart MySQL, will produce hostname-bin.000001 and Hostname-bin.index, the previous log file is to record all updates to the data operation, the subsequent file is to store all the binary index, cannot be easily deleted.

Restart

[[Email protected]]# service mysqld Start (5. 0 version is mysqld) [[email protected]]# service MySQL start (
   
    5.5. 7 version is MySQL)
   

View Log Status

mysqldump Backup Execution Scripts

# name:backup.sh# This isa shellscript for Auto DB backup and Delete old backup# back up address Backupdir=/var/lib/mysql-files/mysqlbackup# backup file suffix time=_ ' Date +%y_%m_%d_%h_%m_%S ' #需要备份的数据库名称db_name=bike_system#mysql user name Db_user=root#mysql Password Db_pass=the Bicycle#mysqldump command uses an absolute path,-flush-logs Refresh Biglog log mysqldump-flush-logs-u$db_user-p$db_pass--databases $db _name | gzip > $backupdir/$db _name$time.sql.gz# Delete the backup file 7 days ago Find $backupdir-name $db _name"*.sql.gz"-type F-mtime +7-exec RM-RF {} \; >/dev/NULL 2>&1Echo"Success"

Set the script to execute at timed

Input: CRONTAB-E

Select an editor

Add at the end of the file:

 - 1 * * */var/lib/mysql-files/

Mysqldump Usage Reference

usage: mysqldump[options][db_name[tbl_name ...] Main options explained:--all-databases,-A: Back up all databases--databases,-B: The database to be backed up, can be backed up multiple at the same time, separated by spaces--flush-logs,-F: Scroll the log before and after the request to the lock to log the binary log when copying--flush-Privileges: Notify database reread Authorization form--host=host_name,-Hhost_name: Host name of the database to be backed up, which can be based on network backup--Lock-all-tables,-x: Request lock all tables before backing up, do Win Bei to Myisam,innodb,aria--single-transaction: Enables hot provisioning of INNODB storage engines-uusename The user name of the backup-ppassword password for login database--Events : Backing up event Scheduler Code--routines: Backing up stored procedures and storage functions--triggers: Backup trigger--master-date={0|1|2},0 means no logging, 1 means the distance is a changemaster statement, and 2 represents a Changemaster statement that is recorded as a comment

• 1. Back up the data and structure of all databases mysqldump-uroot-p123456-a >f:\all.sql
• 2. Back up the structure of all databases (plus-d parameter) mysqldump-uroot-p123456-a-d>f:\all_struct.sql
• 3. Back up data for all databases (plus-t parameter) mysqldump-uroot-p123456-a-t>f:\all_data.sql
• 4. Backing up the data and structure of a single database (, database name MyDB) mysqldump-uroot-p123456 Mydb>f:\mydb.sql
• 5. Backing up the structure of a single database mysqldump-uroot-p123456 Mydb-d>f:\mydb.sql
• 6. Backing up data from a single database mysqldump-uroot-p123456 Mydb-t>f:\mydb.sql
• 7. Backup multiple tables of data and structure (data, structure of a separate backup method with the same) mysqldump-uroot-p123456 MyDB T1 T2>f:\multables.sql
• 8. Back up multiple databases at once mysqldump-uroot-p123456--databases db1 db2>f:\muldbs.sql

Restore partial (1) MySQL command line source method and (2) system command line method

1. Restore all databases:

(1) MySQL command line: Mysql>source f:\all.sql

(2) System command line: mysql-uroot-p123456

(2) mysql-uroot-p123456 MyDB

(2) mysql-uroot-p123456 MyDB

(2) System command line: Mysql-uroot-p123456<f:\muldbs.sql

Biglog Restore Reference

Mysqlbinlog--start-datetime= "Time" Log file path | Mysql-u User-P restore from the specified start time to the present

Mysqlbinlog--stop-datetime= "Time" Log file path | Mysql-u User-P restore from the beginning to the specified end time

Mysqlbinlog--start-datetime= "Time"--stop-datetime= "time" Log file path | Mysql-u User-P restore from the specified start time to the specified end time

Restore by Location:

--start-position= "Starting location for restoring data"

--stop-position= "Ending location of restore data"

Mysqlbinlog--start-position= "Location" Log file path | Mysql-u User-P restore from the specified starting position to the present

Mysqlbinlog--stop-position= "Location" Log file path | Mysql-u User-P restore from the beginning to the specified end position

Mysqlbinlog--start-position= "Location"--stop-position= "Location" Log file path | Mysql-u User-P restore from the specified starting position to the specified end position

Knowledge Point: Linux database backup