MySQL series 5--full backup and incremental backup

first, using the system's own tools to achieve full backup:mysqldump

1. Back Up all databases:

mysqldump-hlocalhost-uroot-p12345678 --all-databases >/opt/$ (Date +%f). SQL

//--all-databases--All database /opt/$ (date +%f). SQL redirection to a date-named file

2. Back up the specified database:

mysqldump-hlocalhost-uroot-p12345678 db1 DB2 >/opt/$ (Date +%f). SQL

3, Full recovery: first to ensure that the library exists, no first created

mysql-hlocalhost-uroot-p12345678 db88 </opt/123.sql

4. Full recovery can also be performed on the MySQL command line: source/opt/2018-01-22.sql

5. Full backup can be performed with scheduled tasks

if [!-d/data];then//First determine if the directory exists, does not exist then create

Mkdir/data

Fi

mysqldump-uroot-p12345678 Test >/data/$ (date +%f)-test.sql

Second, enable Binlog logs for real-time incremental backups

1. Binary logs, log SQL commands other than queries

Because MySQL does not enable the Binlog log by default, it needs to be modified in the configuration file

log file, cannot be greater than 500m,mysqlbinlog server51-bin.000001//view Log command

server_id=51//server number, unique

LOG-BIN=/MYSQLLOG/SERVER51//Enable Logbin log file, specify its path and file name, must give MySQL account permissions

binlog-format= "Mixed"//specify log file format with three formats

2. How log files Record SQL commands

Time: 180127 23:23:29 Server ID 51

Offset: at 313

3. Recovering data

A, offset recovery:

Mysqlbinlog--start-position=378--stop-position=534/mysqllog/server51.000001 | mysql-uroot-p12345678

Start offset termination offset Binlog log file login Account number and password

B, time recovery

mysqlbinlog--start-datetime= "2018-1-27 23:22:22"--stop-datetime= "2018-1-27 23:23:29"//Specify Start and end times

/mysqllog/server51.000001 | mysql-uroot-p12345678//Specify log file and login username and password

4. generate a new log file manually Binlog

Refresh log: Flush logs;

Created at login: mysql-uroot-p123456-e "flush Logs"

When exporting a backup database: mysqldump-uroot-p123456--flush-logs DB4 >/root/db4.sql

Generate a new log file when restarting the server

5. How to delete a log

mysql> Reset Master; Delete all logs, generate a new log file

Mysql> purge master logs to "log file name"; Delete a single log file

Mysql> purge master logs to "plj.000006"; Delete all log files before the specified log file

MySQL series 5--full backup and incremental backup