MySQL Data backup and operation

Physical backup: Direct copy of database files for large database environments. However, you cannot restore to a heterogeneous system such as Windows. Logical Backup: Backup is the SQL statement executed by the operation of the table, build, insert, etc., for small and medium sized databases, the efficiency is relatively low. Export table: Imports a table into a text file.

First, using mysqldump to implement logical backup

#语法: # mysqldump-h Server-u user name-p password database name > backup file. sql# Example: #单库备份mysqldump-uroot-p123 db1 > db1.sqlmysqldump-uroot-p123 DB1 table1 table2 > db1-table1-table2.sql# multi-Library backup mysqldump-uroot-p123--databases db1 DB2 mysql db3 > Db1_db2_mysql_ db3.sql# backing up all libraries mysqldump-uroot-p123--all-databases > All.sql

Second, restore the logical backup

#方法一: [[email protected] backup]# mysql-uroot-p123 </backup/all.sql# method Two:mysql> use db1;mysql> SET sql_log_bin= 0;mysql> source/root/db1.sql# Note: If you back up/restore a single library, you can modify the SQL file drop database if exists school;create database School;use School

Third, backup/recovery case

#数据库备份/Recovery Experiment One: Database corruption backup:1. # mysqldump-uroot-p123--all-databases >/backup/' Date +%F ' _all.sql2. # MYSQL-UROOT-P123-E'Flush Logs' //truncate and generate a new Binlog3. Inserting data//Simulate server uptime4. Mysql>Setsql_log_bin=0;//Impersonation Server CorruptionMysql>drop database db; restore:1. # Mysqlbinlog Last Binlog >/backup/Last_bin.log2. Mysql>Setsql_log_bin=0; MySQL> source/backup/ the- Geneva-13_all.sql//Restore the most recent full backupMysql> Source/backup/last_bin.log//Restore last Binlog file#数据库备份/Recovery Experiment Two: If you delete the backup incorrectly:1. mysqldump-uroot-p123--all-databases >/backup/' Date +%F ' _all.sql2. Mysql-uroot-p123-e'Flush Logs' //truncate and generate a new Binlog3. Inserting data//Simulate server uptime4. drop table Db1.t1//Analog Accidental deletion5. Inserting data//Simulate server uptimeRecovery:1. # Mysqlbinlog Last Binlog--stop-position=260>/tmp/1. SQL # Mysqlbinlog last Binlog--start-position= the>/tmp/2. SQL2. Mysql>Setsql_log_bin=0; MySQL> source/backup/ the- Geneva-13_all.sql//Restore the most recent full backupMysql> source/tmp/1. log//Restore last Binlog fileMysql> source/tmp/2. log//Restore last Binlog filePrecautions :1completely recover to a clean environment (such as a new database or delete an existing database)2. All SQL statements should not be logged to Binlog during recovery

View Code

Iv. Implementing automated Backup

Backup schedule:
1. What time is 2:00
2. What database backups to
3. Where to put the backup files

Backup script:
[Email protected] ~]# Vim/mysql_back.sql
#!/bin/bash
Back_dir=/backup
back_file= ' Date +%f ' _all.sql
User=root
Pass=123

if [!-d/backup];then
Mkdir-p/backup
Fi

# Back up and truncate logs
Mysqldump-u${user}-p${pass}--events--all-databases > ${back_dir}/${back_file}
Mysql-u${user}-p${pass}-e ' flush logs '

# Keep backups for the last week only
CD $back _dir
Find. -mtime +7-exec rm-rf {} \;

Manual test:
[Email protected] ~]# chmod a+x/mysql_back.sql
[Email protected] ~]# chattr +i/mysql_back.sql
[Email protected] ~]#/mysql_back.sql

To configure Cron:
[Email protected] ~]# crontab-l
2 * * */mysql_back.sql

Backup schedule: 1. What time 2:002. Which database backups to 3. Backup Files Place backup script: [[email protected] ~]# vim/mysql_back.sql#!/bin/bashback_dir=/backupback_file= ' Date +%f ' _all.sqluser=  rootpass=123if [!-d/backup];thenmkdir-p/backupfi# Backup and truncate log Mysqldump-u${user}-p${pass}--events--all-databases > ${back_dir}/${back_file}mysql-u${user}-p${pass}-e ' flush logs ' # retains only the last week's backup CD $back _dirfind. -mtime +7-exec rm-rf {} \; manual test: [[email protected] ~]# chmod a+x/mysql_back.sql [[email protected] ~]# chattr +i/mysql_ Back.sql[[email protected] ~]#/mysql_back.sql configuration cron:[[email protected] ~]# crontab-l0 2 * * */mysql_back.sql

V. Export and import of tables

SELECT ... into OUTFILE export text file example: MySQL> SELECT *From School.student1into OUTFILE'Student1.txt'Fields TERMINATED by',' //defining field SeparatorsOptionally enclosed by'"' //define what symbols to use in the string.LINES TERMINATED by'\ n';//Define line breakmysql command export text file example: # mysql-U root-p123-e'SELECT * from Student1.school'>/tmp/student1.txt# MySQL-U root-p123--xml-e'SELECT * from Student1.school'>/tmp/student1.xml# MySQL-U root-p123--html-e'SELECT * from Student1.school'>/tmp/student1.htmlload DATA INFILE importing text files MySQL>DELETE from Student1;mysql> LOAD DATA INFILE'/tmp/student1.txt'Into TABLE school.student1fields TERMINATED by','optionally enclosed by'"'LINES TERMINATED by'\ n';

View Code

Vi. Database Migration

Be sure to migrate between the same version # Mysqldump-h source ip-uroot-p123--databases DB1 | Mysql-h Target ip-uroot-p456

MySQL Data backup and operation