MySQL: Data backup, Pymysql module

An introduction to IDE tools

The production environment is also recommended for MySQL command line, but for our testing purposes, you can use the IDE tools

Download Link: https://pan.baidu.com/s/1bpo5mqj

Master: #1. Test + link Database # #. Create a new library. New table, new field + type + constraint # #. Design table: Foreign Key # #. New query. Backup Library/Table # Note: Batch add Comment: Ctrl +? Key batch to comment: ctrl+shift+? Key

Two MySQL data backup

#1. Physical backup: Direct copy of database files for large database environments. However, you cannot restore to a heterogeneous system such as Windows. #2. 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. #3. 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

View Code

Database backup/Restore 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. Insert Data//Impersonate the server to function properly 4. Mysql> set sql_log_bin=0; Analog server corruption mysql> drop database db; restore: 1. # Mysqlbinlog last Binlog >/backup/last_bin.log2. Mysql> set sql_log_bin=0; Mysql> source/backup/2014-02-13_all.sql//Recover most recent full backup mysql> source/backup/last_bin.log//Recover last Binlog File # database backup/ 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. Insert Data//Impersonate the server to function properly 4. drop table DB1.T1//Analog Delete 5. Insert data//Emulation server uptime recovery: 1. # Mysqlbinlog last Binlog--stop-position=260 >/tmp/1.sql # mysqlbinlog last Binlog--start-position=900 >/tmp/2.sq L 2. Mysql> set sql_log_bin=0; mysql> source/backup/2014-02-13_all.sql//Restore last full backup mysql> source/tmp/1.log//restore final Binlog file mysql> source/ Tmp/2.log//Recover last Binlog file notes: 1. Fully recover to a clean environment (such as a new database or delete an existing database) 2. During recovery all sQL statements should not be recorded in the Binlog 

Iv. Implementing automated Backup

View Code

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-l2 * * */mysql_back.sql

V. Export and import of tables

View Code

SELECT ... into OUTFILE export text file Example:mysql> SELECT * from School.student1into OUTFILE ' student1.txt ' field TERMINATED by ', '//define fields delimiter Optionally enclosed by ' "'//definition string using what symbol is enclosed lines TERMINATED by ' \ n '; Define line breaks MySQL command export text file example: # mysql-u root-p123-e ' select * from Student1.school ' >/tmp/student1.txt# mysql-u root-p1 --xml-e ' select * from Student1.school ' >/tmp/student1.xml# mysql-u root-p123--html-e ' select * from Student1. School ' >/tmp/student1.htmlload Data INFILE Import text file mysql> DELETE from student1;mysql> LOAD DATA INFILE '/tmp/stud Ent1.txt ' into-TABLE school.student1fields TERMINATED by ', ' optionally-enclosed by ' ', ' LINES-TERMINATED by ' \ n ';

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

Three Pymysql modules

#安装pip3 Install Pymysql

A link, execute SQL, close (cursor)

Import Pymysqluser=input (' username: '). Strip () Pwd=input (' Password: '). Strip () #链接conn =pymysql.connect (host= ' localhost ', user= ' Root ', password= ' 123 ', database= ' Egon ', charset= ' UTF8 ') #游标cursor =conn.cursor () #执行完毕返回的结果集默认以元组显示 #cursor= Conn.cursor (cursor=pymysql.cursors.dictcursor) #执行sql语句sql = ' select * from UserInfo where name= '%s ' and password= '%s ' % (USER,PWD) #注意%s needs to be quoted in print (SQL) res=cursor.execute (SQL) #执行sql语句, returns the number of records for a successful SQL query print (RES) cursor.close () Conn.close () If res:    print (' login successful ') Else:    print (' Login failed ')

SQL injection of two execute ()

Note: The symbol--it will comment out the SQL after it, the correct syntax:--after at least one arbitrary character

The fundamental principle: based on the program string splicing name= '%s ', we enter a xxx '--haha, with our input xxx Plus ' in the program stitching into a judgment condition name= 'xxx '--haha'

The last space in a SQL statement if you encounter a select * from T1 where ID > 3--and Name= ' Egon '; then--After the condition is commented out # # #, SQL injection: User presence, bypassing the password Egon '--Any character # #, S QL injected: The user does not exist, bypassing the user with the password xxx ' or 1=1--any character

Workaround:

# It turns out we're string concatenation of SQL # sql= "SELECT * from UserInfo where name= '%s ' and password= '%s '"% (user,pwd) # Print (SQL) # Res=cursor.exe Cute (SQL) #改写为 (execute string concatenation for us, we do not need and must not quote the%s again) sql= "SELECT * from UserInfo where name=%s and password=%s" #!!! Note that%s needs to be stripped of the quotation marks, because Pymysql will automatically add Res=cursor.execute (Sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题, as long as we follow the rules of Pymysql.

Add, Delete, change: Conn.commit ()

View Code

Import pymysql# link conn=pymysql.connect (host= ' localhost ', user= ' root ', password= ' 123 ', database= ' egon ') #游标cursor = Conn.cursor () #执行sql语句 #part1# sql= ' insert into userinfo (Name,password) VALUES ("root", "123456"); ' # res=cursor.execute (SQL) #执行sql语句, returns the number of rows that SQL affects successfully # print (res) #part2 # sql= ' insert into userinfo (Name,password) VALUES (%s ,%s); ' # Res=cursor.execute (sql, ("root", "123456")) #执行sql语句, returns the number of rows that SQL affects successfully # print (res) #part3sql = ' INSERT into userinfo (name, Password) values (%s,%s); ' Res=cursor.executemany ("sql,[" ("Root", "123456"), ("LHF", "12356"), ("Eee", "156")]) #执行sql语句, returns the number of rows that SQL affects successfully print (RES) Conn.commit () #提交后才发现表中插入记录成功cursor. Close () Conn.close ()

Four check: Fetchone,fetchmany,fetchall

View Code

Import pymysql# link conn=pymysql.connect (host= ' localhost ', user= ' root ', password= ' 123 ', database= ' egon ') #游标cursor = Conn.cursor () #执行sql语句sql = ' select * from UserInfo; ' Rows=cursor.execute (SQL) #执行sql语句, returns the number of rows that SQL affects successfully rows, puts the result in a collection, waits for the query # cursor.scroll (3,mode= ' absolute ') # to move relative absolute position # Cursor.scroll (3,mode= ' relative ') # moves relative to the current position res1=cursor.fetchone () Res2=cursor.fetchone () Res3=cursor.fetchone () Res4=cursor.fetchmany (2) Res5=cursor.fetchall () print (res1) print (res2) print (RES3) print (res4) print (res5) print ('%s Rows in Set (0.00 sec) '%rows) Conn.commit () #提交后才发现表中插入记录成功cursor. Close () Conn.close () "(1, ' root ', ' 123456 ') (2, ' root ')  , ' 123456 ') (3, ' root ', ' 123456 ') ((4, ' root ', ' 123456 '), (5, ' root ', ' 123456 ')) ((6, ' root ', ' 123456 '), (7, ' LHF ', ' 12356 '), (8, ' eee ', ' 156 ')) Rows in Set (0.00 sec) "

Five gets the self-increment ID of the last piece of data inserted

View Code

Import pymysqlconn=pymysql.connect (host= ' localhost ', user= ' root ', password= ' 123 ', database= ' Egon ') cursor= Conn.cursor () sql= ' insert into userinfo (Name,password) VALUES ("xxx", "123"); ' Rows=cursor.execute (SQL) Conn.commit () print (Cursor.lastrowid) #在commit之前和之后都可以查看cursor. Close () Conn.close ()

MySQL: Data backup, Pymysql module