MySQL Backup and Pymysql

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

Back to top 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

Iv. Implementing automated Backup

View Code

V. Export and import of tables

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

Seven 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 ') #游标cursor =conn.cursor () #执行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.execute (SQL) #改写为 (exe Cute help us with string concatenation, we don't have to and must not quote%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

Four check: Fetchone,fetchmany,fetchall

View Code

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

View Code

MySQL Backup and Pymysql