MySQL VI: Data backup, Pymysql module

I. Introduction of IDE Tools

The production environment is also recommended to use the MySQL command line, but for our testing purposes, you can download the link using the IDE tool: HTTPS://PAN.BAIDU.COM/S/1BPO5MQJ

Master:

1. Test + link Database

2. Create a new library

3. New table, new field + Type + constraint

4. Design Table: FOREIGN key

5. New Query

6. Backup Library/Table

Attention:

Batch Add Comment: Ctrl +? Key

Batch to comment: ctrl+shift+? Key

Two MySQL data backup

1. Noun explanation

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.

2. Using mysqldump to implement logical backup

Syntax: mysqldump-h server-u user name-p password database name > backup file. sql

Example:

Library Backup

mysqldump-uroot-p123 db1 > Db1.sql

mysqldump-uroot-p123 db1 table1 table2 > Db1-table1-table2.sql

Docu backup

mysqldump-uroot-p123--databases db1 db2 mysql db3 > Db1_db2_mysql_db3.sql

Back Up all libraries

mysqldump-uroot-p123--all-databases > All.sql

3. Restore Logical Backup

Method One: 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;

4. Backup/Recovery case

Experiment One: Database corruption

Backup:

#mysqldump-uroot-p123--all-databases >/backup/' Date +%f ' _all.sql

#mysql-uroot-p123-e ' flush logs '//truncate and generate a new binlog

Insert data//Simulate server uptime

Mysql> set sql_log_bin=0; Impersonation server corruption

mysql> drop database db;

Recovery:

#mysqlbinlog last Binlog >/backup/last_bin.log

Mysql> set sql_log_bin=0;

mysql> source/backup/2014-02-13_all.sql//restore last full backup

Mysql> Source/backup/last_bin.log//Recover last Binlog file

Experiment two: If you delete the error

Backup:

mysqldump-uroot-p123--all-databases >/backup/' Date +%f ' _all.sql

MYSQL-UROOT-P123-E ' flush logs '//truncate and generate a new binlog

Insert data//Simulate server uptime

drop table DB1.T1//Simulated accidental deletion

Insert data//Simulate server uptime

Recovery:

#mysqlbinlog last Binlog--stop-position=260 >/tmp/1.sql

#mysqlbinlog last Binlog--start-position=900 >/tmp/2.sql

Mysql> set sql_log_bin=0;

mysql> source/backup/2014-02-13_all.sql//restore last full backup

Mysql> Source/tmp/1.log//Recover last Binlog file

Mysql> Source/tmp/2.log//Recover last Binlog file

Precautions:

1. Fully recover to a clean environment (such as a new database or delete an existing database)

2. All SQL statements should not be recorded in Binlog during recovery

5. Realize automated Backup

Backup schedule:

1) What time is 2:00

2) which database backups to

3) Where to put the backup files

Backup script:

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

#备份并截断日志

Mysqldump-u${user}-p${pass}--events--all-databases > ${back_dir}/${back_file}

Mysql-u${user}-p${pass}-e ' flush logs '

#只保留最近一周的备份

CD $back _dir

Find. -mtime +7-exec rm-rf {} \;

Manual test:

#chmod A+x/mysql_back.sql

#chattr +i/mysql_back.sql

#/mysql_back.sql

To configure Cron:

# CRONTAB-E

2 * * */mysql_back.sql

6. Export and import of tables

1) SELECT ... into OUTFILE exporting text files

Mysql> SELECT * from school.student1 to OUTFILE ' Student1.txt '

Fields TERMINATED by ', '//define field separators

Optionally enclosed by ' "'//define string with what symbols to enclose

LINES TERMINATED by ' \ n '; Define line break

2) MySQL command export text file:

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.html

3) LOAD DATA INFILE Import text file

Mysql> DELETE from Student1;

mysql> LOAD DATA INFILE '/tmp/student1.txt ' into TABLE school.student1

Fields TERMINATED by ', '

Optionally enclosed by ' "'

LINES TERMINATED by ' \ n ';

Note: May error error 1238 (HY000): Variable ' Secure_file_priv ' is a read only Variable

Database is the most critical is the data, once the database permissions leaked, then through the above statement can easily export data to a file and then download take away, so MySQL limit, can only export files to the specified directory

[Mysqld]

secure_file_priv= ' xxx '

Restart MySQL and re-execute the above statement

7. Database Migration

Be sure to migrate between the same versions

#mysqldump-H source ip-uroot-p123--databases db1 | Mysql-h Target ip-uroot-p456

MySQL VI: Data backup, Pymysql module