Python MySQL 备份_Python--MySQL系列 数据备份 pymysql模块

MySQL数据备份

#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。

#2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。

#3. 导出表： 将表导入到文本文件中。

库的备份与恢复

1.逻辑备份：使用mysqldump实现

语法 :

mysqldump -h 服务器 -u用户名 -p密码 库名 > 路径\备份文件.sql

#单库备份

mysqldump -uroot -p --database db1 > E:\db1_20171030.sql

#多库备份

mysqldump -uroot -p --databases aaa bbb ccc > E:\aaa_bbb_ccc_20171030.sql

#备份所有库

mysqldump -uroot -p --all-databases > all.sql

2.恢复逻辑备份：

语法：

mysql -u用户名 -p密码 < 路径\备份文件.sql

#单库备份，多库备份，所有库备份

mysql -uroot -p < C:\\day45_bak_2017_10_30.sql

表的备份与恢复

备份单表

mysqldump -u用户 -p密码 库名 表名 > 路径\备份文件名.sql

备份多表

mysqldump -u用户 -p密码 库名 表名,表名2,表名3 > 路径\备份文件名.sql

恢复单表，恢复多表

mysql -u用户 -p密码 库名 < 路径\备份文件名.sql

#备份多个表 例子

mysqldump -uroot -p day45 employee t1 t2 > C:\\day45_t1_t2_employee_bak_2017_10_30.sql

恢复表

mysql -uroot -p day45 < C:\\day45_t1_t2_employee_bak_2017_10_30.sql

表的导入导出 (数据的导入导出)

SELECT... INTO OUTFILE 导出文本文件

示例：

mysql> SELECT * FROM school.student1

INTO OUTFILE 'student1.txt'

FIELDS TERMINATED BY ',' //定义字段分隔符

OPTIONALLY ENCLOSED BY '”' //定义字符串使用什么符号括起来

LINES TERMINATED BY '\n' ; //定义换行符

mysql 命令导出文本文件

示例:

# 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

LOAD DATA INFILE 导入文本文件

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';

数据库迁移

务必保证在相同版本之间迁移

# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456

pymysql模块

一 链接、执行sql、关闭(游标)

#安装

pip3 install pymysql

import pymysql

user=input('用户名: ').strip()

pwd=input('密码: ').strip()

#创建链接

conn=pymysql.connect(

host='localhost',

port=3306,

user='root',

password='',

database='zzz',

charset='utf8'

)

#游标

cursor=conn.cursor()

# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 结果是以字典形式呈现 {字段：数据,....}

#sql语句

sql='select * from userinfo where name=%s and password=%s ;'

#执行sql语句

res=cursor.execute(sql,(user,pwd)) #执行sql语句，返回sql查询成功的记录数目

cursor.close() #游标关闭

conn.close() #链接关闭

if res:

print('登录成功')

else:

print('登录失败')

二 execute()之sql注入

注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符

根本原理：就根据程序的字符串拼接name='%s'，我们输入一个xxx' -- haha,用我们输入的xxx加'在程序中拼接成一个判断条件name='xxx' -- haha'

最后那一个空格，在一条sql语句中如果遇到select * from t1 where id > 3 -- and name='egon';则--之后的条件被注释掉了

#1、sql注入之：用户存在，绕过密码

egon' -- 任意字符

#2、sql注入之：用户不存在，绕过用户与密码

xxx' or 1=1 -- 任意字符

解决方法：

# 原来是我们对sql进行字符串拼接

# sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)

# print(sql)

# res=cursor.execute(sql)

#改写为(execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了)

sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上

res=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

三 增、删、改：conn.commit()

import pymysql

#链接

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语句，返回sql影响成功的行数

#print(res)

#part2

# sql='insert into userinfo(name,password) values(%s,%s);'# res=cursor.execute(sql,("root","123456")) #执行sql语句，返回sql影响成功的行数

#print(res)

#part3

sql='insert into userinfo(name,password) values(%s,%s);'res=cursor.executemany(sql,[("root","123456"),("lhf","12356"),("eee","156")]) #执行sql语句，返回sql影响成功的行数print(res)

conn.commit() #提交后才发现表中插入记录成功cursor.close()

conn.close()

四 查：fetchone，fetchmany，fetchall

import pymysql

#链接

conn=pymysql.connect(host='localhost',user='root',password='123',database='egon')

#游标cursor=conn.cursor()

#执行sql语句

sql='select * from userinfo;'rows=cursor.execute(sql) #执行sql语句，返回sql影响成功的行数rows,将结果放入一个集合，等待被查询

#cursor.scroll(3,mode='absolute') # 相对绝对位置移动

#cursor.scroll(3,mode='relative') # 相对当前位置移动

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)'''

五 获取插入的最后一条数据的自增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) #在插入语句后查看

conn.commit()cursor.close()

conn.close()