Shulou(Shulou.com)06/01 Report--

1. Use the tools that come with the system to achieve complete backup: mysqldump

Data backup method:

Physical backup: directly copy the files corresponding to the library or table. Myisam only, cross-platform sex is poor

Logical backup: when performing backup, generate corresponding sql command according to existing data, and save sql to specified file

Backup all databases:

mysqldump -hlocalhost -uroot -p12345678 --all-databases > /opt/$(date +%F).sql

//--all-databases--all databases/opt/$(date +%F).sql redirect to date-named files

2. Backup the specified database:

mysqldump -hlocalhost -uroot -p12345678 db1 db2 > /opt/$(date +%F).sql

3, complete recovery: first to ensure that the inventory is not created first

mysql -hlocalhost -uroot -p12345678 db88

/data/$(date +%F)-test.sql

Second, enable binlog log to achieve real-time incremental backup

Differential backup: backup all data generated since a full backup

Incremental backup: Backs up all newly generated data since the last backup

Binary log, records sql commands other than queries

Because mysql does not enable binlog by default, it needs to be modified in the configuration file.

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

server_id=51 //Server ID, unique

log-bin=/mysqllog/server51//Enable logbin log file, specify its path and file name, must give mysql account permissions

binlog-format="mixed" //Specifies the log file format, there are three formats

2, log file record sql command way

Time: 180127 23:23:29 server id 51

Offset: at 313

3. Recovery of data

A. Offset recovery:

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

Start offset End offset binlog Log file Login account 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 the log file and login username and password

Manually generate a new log file binlog

Refresh logs: flush logs;

Create on login: mysql -uroot -p123456 -e "flush logs"

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

Generate new log files when restarting server

5 How to delete the log

mysql> reset master; //Delete all logs and create a new log file

mysql> purge master logs to "log file name"; //Delete individual log files

mysql> purge master logs to "plj.00006"; //Delete all log files before the specified log file

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.