There are 2 different ways to complete Mysql backups
1 using system commands to back up MySQL data copy directly
For example
/etc/init.d/mysqld stop
Tar cvfz/var/lib/mysql/mydb_backup.tar.gz/tmp
/etc/init.d/mysqld start
This method must first shut down the MySQL service, which is also a major disadvantage, and this backup out of the data does not support different versions of MySQL to restore!
So this method is rarely used in production environments.
2 Using Mysqldump for backup
The rationale for mysqldump backups is to save the SQL statements that are generated by modifying the content in the database to the specified location, and restore the data by simply exporting the SQL statement file to the new MySQL for execution.
For example
Mysqldump-u root-p 123 >/var/test/123.sql
If you need to back up all the databases in MySQL, use the-all-databases
Mysqldump-u root-p--all-databases >/var/test/123.sql
The command to recover data is as follows:
Mysql-u Root-p 123 </var/test/123.sql
Incremental backup of MySQL
An incremental backup is a much more frequent operation than a full backup.
MySQL's incremental backup uses a MySQL binary log to implement the method;
MySQL's binary log holds all SQL statements for MySQL operations;
MySQL binary log function is closed by default, we need to manually open;
VIM/ETC/MY.CNF Edit Add
Log-bin=noah (header) default to Binlog if not added;
After restarting the service, the Binlog binaries are found in the/var/lib/mysql;
Binary log files We need to use the command mysqlbinlog to view;
So how do you use binary files to recover data?
1 Use time to recover
For example
Mysqlbinlog--start-date= "2009-05-20 10:51:01"--stop-date= "2009-05-20 11:52:12"/var/lib/mysql/binlog.000001 | mysql-uroot-p123456
The SQL statements that operate between these 2 periods of time for MySQL are exported to MySQL for execution; this restores the data during this time;
But the time here is only accurate to seconds, then there are many SQL statements in the same second, there will be problems; therefore, we introduce a more accurate method of recovery, as follows;
2 Use location number to restore
For example
Mysqlbinlog--start-position= "457"--stop-position= "1009"/var/lib/mysql/binlog.000001 | mysql-uroot-p123456
A unique Log_pos is set in the MySQL binary log; we use this unique position to recover the data; This method does not ignore any data;