Procedures and procedures for MySQL backup and AB replication

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;