MYSQL learning summary (4): MYSQL recovery and backup

#Use mysqldump to back up all libraries
Mysqldump –opt –master-data –single-transaction –u root –ppassword –all –database > all.sql
Parameter description: --opt seems to be the default option, it can be omitted. The main function is to prevent the library from being too large. Otherwise, the results will be put into the memory first, which will cause problems.
--master-data This is mainly to display the exported position (pos of binary log) on ​​the results.
--single- transaction This works relative to the innoDB engine, similar to flush table with read lock;
#Use mysqldump to back up a database
Mysqldump –u root –ppassword databasename > databasename.sql
#Use mysqldump to back up a table (i.e. export One table data)
Mysqldump –u root –ppassword –databases databasename –tables tablename > tablename.sql
#Use mysqldump to compress the backup
Mysqldump –u root –ppassword databasename | gzip > databasename.sql.gz
#mysqldump recovery
Gunzip < databasename.sql.gz | mysql –u root –ppassword databasename
Mysql –u root –ppassword databasename < databasename.sql
You can also log in to mysql and run source /tmp/databasename.sql


# Use mysqlhotcopy to backup the library
Mysqlhotcopy –uroot –p=password databasename /bakup/backup directory
#Use mysqlhotcopy to back up a certain table
Mysqlhotcopy –u root –p=password databasename./tablename/ /backup/backup directory
#Use mysqlhotcopy to back up library and add backup records
Mysqlhotcopy –u root –p=password –checkpoint=hotcopy.checkpoint databasename /bakup/backup record
Parameter description: Save the record of each backup execution in the hotcopy library-checkpoint table, which needs to be created in advance Libraries and tables
For example: create databases hotcopy;
Use hotcopy;
Create table checkpoint (time_stamp timestamp not null,src varchar(32),dest varchar(32),msg varcher(255));
#Restore hotcopy backup library
Copy the database directory directly to the data directory of the database. Pay attention to the permissions of the directory.
The difference and personal understanding between #mysqldump and mysqlhotcopy
Mysqlhotcopy is only applicable to tables in the MYISAM engine;
To use mysqlhotcopy, you must install the perl-DBI and perl-DBD-mysql modules
Both will automatically lock the table when they are executed
Mysqlhotcopy’s during recovery The backup file needs to be copied to the target directory for overwriting, and mysqldump needs to import the backup file into the original database
#Recovery based on binary logs
Mysqlbinlog mysql-bin.XXXXX | mysql –u root –ppassword // Restore the entire mysql-bin.XXXX binary Log file
Mysqlbinlog –start-datatime="2014-02-28 10:00:00" –stop-datatime="2014-02-28 12:00:00" mysql-bin.XXXX | mysql –u root –ppassword //Restore some operations from 10 o'clock to 12 o'clock in mysql-bin.XXXX
Mysqlbinlog –start-position='3000' –stop-position='5000' mysql-bin.XXXX | mysql –u root –ppassword // Restore the part from 3000-5000 in mysql-bin.XXXX. You can use the mysqlbinlog tool to export the binary log and find the location. For example: mysqlbinlog /var/mysql-bin.XXXX >/tmp/binlogXXXX.txt

The above is the MYSQL learning summary (4): MYSQL recovery and backup content. For more related content, please pay attention to the PHP Chinese website ( www.php.cn)!