mysql：資料備份、pymysql模組

標籤：student   查詢   man   port   https   建表   需要   title   inf   

一 IDE工具介紹

生產環境還是推薦使用mysql命令列，但為了方便我們測試，可以使用IDE工具

下載連結：https://pan.baidu.com/s/1bpo5mqj

掌握：#1. 測試+連結資料庫#2. 建立庫#3. 建立表，新增欄位+類型+約束#4. 設計表：外鍵#5. 建立查詢#6. 備份庫/表#注意：批量加註釋：ctrl+？鍵批量去注釋：ctrl+shift+？鍵

二 MySQL資料備份

#1. 物理備份： 直接複製資料庫檔案，適用於大型資料庫環境。但不能恢複到異構系統中如Windows。#2. 邏輯備份： 備份的是建表、建庫、插入等操作所執行SQL語句，適用於中小型資料庫，效率相對較低。#3. 匯出表： 將表匯入到文字檔中。 

一、使用mysqldump實現邏輯備份

#文法：# 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 

二、恢複邏輯備份

#方法一：[[email protected] 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; 

三、備份/恢複案例

 View Code

Database Backup/恢複實驗一：資料庫損壞備份：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檔案#Database Backup/恢複實驗二：如果有誤刪除備份：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中

四、實現自動化備份

 View Code

備份計劃：1. 什麼時間 2:002. 對哪些Database Backup3. 備份檔案放的位置備份指令碼：[[email protected] ~]# 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 {} \;手動測試：[[email protected] ~]# chmod a+x /mysql_back.sql [[email protected] ~]# chattr +i /mysql_back.sql[[email protected] ~]# /mysql_back.sql配置cron：[[email protected] ~]# crontab -l2 * * * /mysql_back.sql

五、表的匯出和匯入

 View Code

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

一 連結、執行sql、關閉（遊標）

import pymysqluser=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查詢成功的記錄數目print(res)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()

 View Code

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，fetchall

 View Code

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

 View Code

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)conn.commit()print(cursor.lastrowid) #在commit之前和之後都可以查看cursor.close()conn.close()

mysql：資料備份、pymysql模組