This article gives an example of Python Read write and backup database operation , as well as pymysql Module understanding and operation method .

One IDE Tool is introduced

The production environment is still recommended mysql Command line , But to make it easier for us to test , have access to IDE Tools

master :

#1. test + Linked database

#2. New library

#3. new table , New fields + type + constraint

#4. Design table : Foreign keys

#5. New query

#6. Backup Library / surface

# Be careful :

Batch annotation :ctrl+? key

Batch annotation :ctrl+shift+? key

Two MySQL The data backup

#1. The physical backup : Copy database files directly , It is suitable for large database environment . But it can't be recovered to heterogeneous systems, such as Windows.

#2. Logical backup : The backup is table creation 、 Building database 、 Insert, etc SQL sentence , Suitable for small and medium databases , Relatively inefficient .

#3. Export table : Import the table into a text file .

One 、 Use mysqldump Realize logical backup

# grammar :

mysqldump -h The server -u user name -p password Database name > Backup file .sql

# Example :

# Single database backup

mysqldump -uroot -p123 db1 > db1.sql

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

# Multi database 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

Two 、 Restore logical backup

# Method 1 :

[root@egon backup]# mysql -uroot -p123 < /backup/all.sql

# Method 2 :

mysql> use db1;

mysql> SET SQL_LOG_BIN=0;

mysql> source /root/db1.sql

# notes : If you back up / When restoring a single library , You can modify sql file

DROP database if exists school;

create database school;

use school;

3、 ... and 、 Backup / Recovery cases

# Database backup / Resuming experiment one : Database corruption

Backup :

  1. mysqldump -uroot -p123 --all-databases > /backup/date +%F_all.sql

  2. mysql -uroot -p123 -e ‘flush logs’ // Truncate and generate new binlog

  3. insert data // The simulation server is running normally

  4. mysql> set sql_log_bin=0; // Simulate server corruption

mysql> drop database db;

recovery :

  1. mysqlbinlog the last one binlog > /backup/last_bin.log

  2. mysql> set sql_log_bin=0;

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

mysql> source /backup/last_bin.log // Recover the last binlog file

# Database backup / Recovery experiment 2 : If there is a mistake, delete

Backup :

  1. mysqldump -uroot -p123 --all-databases > /backup/date +%F_all.sql

  2. mysql -uroot -p123 -e ‘flush logs’ // Truncate and generate new binlog

  3. insert data // The simulation server is running normally

  4. drop table db1.t1 // False deletion of simulation

  5. insert data // The simulation server is running normally

recovery :

  1. mysqlbinlog the last one binlog --stop-position=260 > /tmp/1.sql

mysqlbinlog the last one binlog --start-position=900 > /tmp/2.sql

  1. mysql> set sql_log_bin=0;

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

mysql> source /tmp/1.log // Recover the last binlog file

mysql> source /tmp/2.log // Recover the last binlog file

matters needing attention :

  1. Completely restored to a clean environment ( For example, new database or delete the original database )

  2. During the recovery period, all SQL Statement should not record binlog in

Four 、 Automated backup

Backup plan :

  1. What time? 2:00

  2. Which databases to back up

  3. The location of the backup file

Backup scripts :

[root@egon ~]# 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

Backup and truncate logs

mysqldump -u                                 u                         s                         e                         r                            −                    p                         {user} -p           user−p{pass} --events --all-databases >                                 b                         a                         c                                  k                          d                                 i                         r                            /                         {back_dir}/           backdir/{back_file}

mysql -u                                 u                         s                         e                         r                            −                    p                         {user} -p           user−p{pass} -e ‘flush logs’

Keep only the most recent week's backup

cd $back_dir

find . -mtime +7 -exec rm -rf {} ;

Manual testing :

[root@egon ~]# chmod a+x /mysql_back.sql

[root@egon ~]# chattr +i /mysql_back.sql

[root@egon ~]# /mysql_back.sql

To configure cron:

[root@egon ~]# crontab -l

2 * * * /mysql_back.sql

5、 ... and 、 Table export and import

SELECT… INTO OUTFILE Export text file

Example :

mysql> SELECT * FROM school.student1

INTO OUTFILE 'student1.txt’FIELDS TERMINATED BY ‘,’// Define the field separator

OPTIONALLY ENCLOSED BY ‘”’// Define what symbols a string should be enclosed with

LINES TERMINATED BY ‘\n’; // Define line breaks

mysql Command to export a 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 Import a 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’;

# There may be a mistake mysql> select *fromdb1.emp into outfile’C:\db1.emp.txt’fields terminated by’,‘lines terminated by’\r\n’;

ERROR 1238 (HY000): Variable’secure_file_priv’is a read only variable# The key to a database is data , Once the database permissions are compromised , Then you can easily export the data to a file and download it through the above statement , thus mysql There are restrictions on this , You can only export files to the specified directory in the configuration file

[mysqld]

secure_file_priv=‘C:\’# You can only export data to C:\ Next restart mysql

Re execute the above statement

6、 ... and 、 Database migration

Be sure to migrate between the same versions # mysqldump -h Source IP -uroot -p123 --databases db1 | mysql -h The goal is IP -uroot -p456

3、 ... and pymysql modular

# install pip3 install pymysql

One link 、 perform sql、 close ( The cursor )

import pymysql

user=input(' user name : ').strip()

pwd=input(' password : ').strip()

# link

conn=pymysql.connect(host=‘localhost’,user=‘root’,password=‘123’,database=‘egon’,charset=‘utf8’)

# The cursor

cursor=conn.cursor() # The result set returned after execution is displayed in tuples by default

#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

# perform sql sentence

sql=‘select * from userinfo where name="%s" and password="%s"’ %(user,pwd) # Be careful %s Quotes required

print(sql)

res=cursor.execute(sql) # perform sql sentence , return sql Number of successful records to query

print(res)

cursor.close()

conn.close()

if res:

print(' Login successful ')


else:

print(' Login failed ')


Two execute() And sql Inject

Be careful : Symbol – It'll be annotated after sql, The right grammar :– At least one arbitrary character after

Fundamental principles : According to the program string splicing name=’%s’, Let's enter a xxx’ – haha, With what we typed in xxx Add ’ In the process of splicing into a judgment condition name=‘xxx’ – haha’

The last space , In one sql If you encounter select *fromt1 where id > 3 --andname=‘egon’; be – The conditions after that have been commented out #1、sql Injected : Users exist , password bypass egon’ – Any character #2、sql Injected : The user doesn't exist , Bypass users and passwords xxx’ or 1=1 – Any character

resolvent :

It turns out that we are right sql String splicing # sql=“select * from userinfo where name=’%s’ and password=’%s’” %(user,pwd)# print(sql)# res=cursor.execute(sql)# to (execute Do string splicing for us , We don't need and must not do it any more %s In quotation marks )sql=“select * from userinfo where name=%s and password=%s”#!!! Be careful %s You need to get rid of the quotation marks , because pymysql It will automatically add res=cursor.execute(sql,[user,pwd])#pymysql The module automatically helps us solve sql Injection problem , As long as we follow pymysql The rules of .

3、 ... and increase 、 Delete 、 Change :conn.commit()

import pymysql# link conn=pymysql.connect(host=‘localhost’,user=‘root’,password=‘123’,database=‘egon’)# The cursor cursor=conn.cursor()# perform sql sentence #part1# sql=‘insert into userinfo(name,password) values(“root”,“123456”);’# res=cursor.execute(sql) # perform sql sentence , return sql The number of lines that affect success # print(res)#part2# sql=‘insert into userinfo(name,password) values(%s,%s);’# res=cursor.execute(sql,(“root”,“123456”)) # perform sql sentence , return sql The number of lines that affect success # print(res)#part3sql='insert into userinfo(name,password) values(%s,%s);'res=cursor.executemany(sql,[(“root”,“123456”),(“lhf”,“12356”),(“eee”,“156”)])# perform sql sentence , return sql The number of lines that affect success print(res)

conn.commit() # After submitting, it was found that the record in the table was inserted successfully cursor.close()

conn.close()

Four check :fetchone,fetchmany,fetchall

import pymysql# link conn=pymysql.connect(host=‘localhost’,user=‘root’,password=‘123’,database=‘egon’)# The cursor cursor=conn.cursor()# perform sql sentence sql='select * from userinfo;'rows=cursor.execute(sql)# perform sql sentence , return sql The number of lines that affect success rows, Put the results into a collection , Waiting to be queried # cursor.scroll(3,mode=‘absolute’) # Move relative to absolute position # cursor.scroll(3,mode=‘relative’) # Move relative to 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() # After submitting, it was found that the record in the table was inserted successfully 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)’’’

5、 ... and Get the auto increment of the last inserted data ID

import pymysql

conn=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)print(cursor.lastrowid)# Look at... After inserting the statement conn.commit()

cursor.close()

conn.close()