数据备份,pymysql模块使用

 阅读目录

1. IDE工具
2. 数据备份/恢复
3. pymysql模块

 

 

 

推荐工具 : mysql IDE 工具

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

 

 

一: 数据备份

 

三种方法

#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。
#2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
#3. 导出表： 将表导入到文本文件中。 

 

1. 使用mysqldump实现逻辑备份

#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

#示例：

#单库备份
mysqldump -uroot -p123 db1 > C:\\db1_bak_20171030_.sql   

#库下多表备份
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql 

#多库备份
mysqldump -uroot -p123 --databases day43 day44 day45 > C:\\day43_day45_day44_bak_2017_10_30.sql

#备份所有库
mysqldump -u用户名 -p密码  --all-databases > C:\\all.sql

 

 

2. 恢复逻辑备份

#方法一：

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

#恢复表
mysql -uroot -p day45 <  C:\\day45_t1_t2_employee_bak_2017_10_30.sql

#方法二,mysql语句实现：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;
mysql> source 文件路径

#注：如果备份/恢复单个库时，可以修改sql文件
DROP database if exists school; #如果存在school这个库就删除
create database school;  #创建school库
use school;

 

 

 3.备份/恢复案例

#数据库备份/恢复实验一：数据库损坏
备份：
1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. # mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog
3. 插入数据 //模拟服务器正常运行
4. mysql> set sql_log_bin=0; //模拟服务器损坏
mysql> drop database db;

恢复：
1. # mysqlbinlog 最后一个binlog > /backup/last_bin.log
2. mysql> set sql_log_bin=0; 
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份 
mysql> source /backup/last_bin.log //恢复最后个binlog文件


#数据库备份/恢复实验二：如果有误删除
备份：
1. mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql
2. mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog
3. 插入数据 //模拟服务器正常运行
4. drop table db1.t1 //模拟误删除
5. 插入数据 //模拟服务器正常运行

恢复：
1. # mysqlbinlog 最后一个binlog --stop-position=260 > /tmp/1.sql 
# mysqlbinlog 最后一个binlog --start-position=900 > /tmp/2.sql 
2. mysql> set sql_log_bin=0; 
mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份
mysql> source /tmp/1.log //恢复最后个binlog文件
mysql> source /tmp/2.log //恢复最后个binlog文件

注意事项：
1. 完全恢复到一个干净的环境（例如新的数据库或删除原有的数据库）
2. 恢复期间所有SQL语句不应该记录到binlog操作日志中  #set sql_log_bin=0;

 

 

4.实现自动化备份

备份计划：
1. 什么时间 2:00
2. 对哪些数据库备份
3. 备份文件放的位置

备份脚本：
[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

# 备份并截断日志
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 {} \;

手动测试：
[root@egon ~]# chmod a+x /mysql_back.sql 
[root@egon ~]# chattr +i /mysql_back.sql
[root@egon ~]# /mysql_back.sql

配置cron：
[root@egon ~]# crontab -l
* * * /mysql_back.sql

 

 

5.表的导出和导入

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

#可能会报错
mysql> select * from db1.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


#数据库最关键的是数据，一旦数据库权限泄露，那么通过上述语句就可以轻松将数据导出到文件中然后下载拿走，因而mysql对此作了限制，只能将文件导出到指定目录
在配置文件中
[mysqld]
secure_file_priv='C:\\' #只能将数据导出到C:\\下

重启mysql
重新执行上述语句

 

 

6.数据库迁移

务必保证在相同版本之间迁移
# mysqldump -h 源IP -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456

 

 

 

 

二: pymysql 模块

 

#安装
pip3 install pymysql  
python -m pip install pymysql

 

 

1.基本使用方法

import pymysql
user=input('用户名: ').strip()
pwd=input('密码: ').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需要加引号
print(sql)
res=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目

#cursor.executemany(sql,[(),(),()])　　传多个值用列表,列表内用元组

print(res)

cursor.close()　　#关闭游标
conn.close()　　#关闭链接

if res:
    print('登录成功')
else:
    print('登录失败')

 

 

 

2.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的规矩来。

 

 

3. 增、删、改：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()

 

 

4. 查：fetchone，fetchmany，fetchall

import pymysql

conn = pymysql.connect(host = '127.0.0.1',
                       port = 3306,
                       user = 'root',
                       password = '123',
                       database = 'day46',
                       charset = 'utf8')

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

sql = 'select * from student'
res = cur.execute(sql)

print(res)  #查询到多少条数据

print(cur.fetchone())    #拿出一条
cur.scroll(3,mode='absolute')   # 游标相对绝对位置移动3个  从第四条开始取
cur.scroll(1,mode='relative')   #游标相对当前位置移动1个
print(cur.fetchmany(2)) #取两条
# print(cur.fetchall()) #全部取出

conn.commit()   #提交后才成功

cur.close()
conn.close()

"""
16
{'sid': 1, 'gender': '男', 'class_id': 1, 'sname': '理解'}
[{'sid': 5, 'gender': '女', 'class_id': 1, 'sname': '张二'}, {'sid': 6, 'gender': '男', 'class_id': 1, 'sname': '张四'}]
"""

 

 

5. 获取插入的最后一条数据的自增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()