----------mysql数据备份-------------

 

 

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

 

一、使用mysql实现逻辑备份

#语法：# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql#示例：#单库备份mysqldump -uroot -p123 db1 > db1.sqlmysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql#多库备份mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql#备份所有库mysqldump -uroot -p123 --all-databases > all.sql

 

二、恢复逻辑备份

#方法一：[root@egon backup]# mysql -uroot -p123 < /backup/all.sql#方法二：mysql> use db1;mysql> SET SQL_LOG_BIN=0;mysql> source /root/db1.sql#注：如果备份/恢复单个库时，可以修改sql文件DROP database if exists school;create database school;use school;

 

 三、备份/恢复案例

#数据库备份/恢复实验一：数据库损坏备份：1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql2. # mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog3. 插入数据 //模拟服务器正常运行4. mysql> set sql_log_bin=0; //模拟服务器损坏mysql> drop database db;恢复：1. # mysqlbinlog 最后一个binlog > /backup/last_bin.log2. 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.sql2. mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog3. 插入数据 //模拟服务器正常运行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中

 

 四、实现自动化备份

备份计划：1. 什么时间 2:002. 对哪些数据库备份3. 备份文件放的位置备份脚本：[root@egon ~]# vim /mysql_back.sql#!/bin/bashback_dir=/backupback_file=`date +%F`_all.sqluser=rootpass=123if [ ! -d /backup ];thenmkdir -p /backupfi# 备份并截断日志mysqldump -u${user} -p${
    pass} --events --all-databases > ${back_dir}/${back_file}mysql -u${user} -p${
    pass} -e 'flush logs'# 只保留最近一周的备份cd $back_dirfind . -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 -l2 * * * /mysql_back.sql

 

五、表的导出和导入

SELECT... INTO OUTFILE 导出文本文件示例：mysql> SELECT * FROM school.student1INTO 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.htmlLOAD DATA INFILE 导入文本文件mysql> DELETE FROM student1;mysql> LOAD DATA INFILE '/tmp/student1.txt'INTO TABLE school.student1FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '”'LINES TERMINATED BY '\n';

 

 六、数据库迁移

 

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

 

 

 

              ------------------pymysql模块---------------------- 

#安装pip3 install pymysql

import pymysqltry:    # 1.conn是一个表示连接的对象    conn = pymysql.connect(        host="127.0.0.1",        port = 3306,        user = "root",        password = "123321",        database = "testDB")    print("连接服务器成功!")    #2. 查询数据 需要借助cursor类  游标   默认游标返回值类型为元组    # pymysql.cursors.DictCursor  可以将结果转为字典    cursor = conn.cursor(pymysql.cursors.DictCursor)    # 3.执行sql语句    sql = "select *from user;"    res = cursor.execute(sql) #res为本次查询得到的记录条数    print(res)    # 4.提取数据    print(cursor.fetchall())  # 提取本次查询所有结果    # print(cursor.fetchone())  # 提取本次查询的第一条记录    # print(cursor.fetchone())  # 提取本次查询的第一条记录    # print(cursor.fetchmany(2))  # 指定提取记录的条数    # 如果游标已经到达最后 将无法在读取到数据 可以使用scroll来移动游标位置    cursor.scroll(-1,mode="relative")    # mode参数表示 是相对位置relative 还是绝对位置 absolute    print(cursor.fetchall())  # 提取本次查询所有结果except Exception as e:    print(type(e),e)finally:    # 无论是否执行成功 最后都需要关闭连接    if cursor:cursor.close() # 关闭游标    if conn:conn.close() # 关闭连接

 

 

 一、excute()之sql注入

 

登录 客户端在网页输入用户名和密码  浏览器要把接收的数据传给后台服务器再由后台服务器 交给数据库服务器可以会遇到sql注入攻击什么sql注入攻击  一些不法分子 可能会在输入的数据中 添加一系列sql语句 来跳过认证环节 甚至直接删除数据解决方案    1.在客户端接收数据时 做一个re的判断  如果包含sql相关的字符 就直接报错    2.在服务器收到某一个客户端 发送的数据时 做一个判断        其实pymysql 已经封装好了相关的判断逻辑 只要将参数交给pymysql来拼接即可import pymysql# 1.conn是一个表示连接的对象conn = pymysql.connect(    host="127.0.0.1",    port = 3306,    user = "root",    password = "123321",    database = "testDB")print("连接服务器成功!")# 原来是我们对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)#part3sql='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, fetcall

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 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)print(cursor.lastrowid) #在插入语句后查看conn.commit()cursor.close()conn.close()