一、IDE工具介绍

生产环境还是推荐使用mysql命令行,但为了方便我们测试,可以使用IDE工具 下载链接:https://pan.baidu.com/s/1bpo5mqj

掌握:

1. 测试+链接数据库

2. 新建库

3. 新建表,新增字段+类型+约束

4. 设计表:外键

5. 新建查询

6. 备份库/表

注意:

    批量加注释:ctrl+?键

    批量去注释:ctrl+shift+?键

二 MySQL数据备份

1、名词解释

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

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

    导出表: 将表导入到文本文件中。 

2、使用mysqldump实现逻辑备份

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

示例:

  单库备份

    mysqldump -uroot -p123 db1 > db1.sql

    mysqldump -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 

3、恢复逻辑备份

方法一: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; 

4、备份/恢复案例

实验一:数据库损坏

  备份:

    #mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql

    #mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog

    插入数据 //模拟服务器正常运行

    mysql> set sql_log_bin=0; //模拟服务器损坏

    mysql> drop database db;

  恢复:

    #mysqlbinlog 最后一个binlog > /backup/last_bin.log

    mysql> set sql_log_bin=0; 

    mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份 

    mysql> source /backup/last_bin.log //恢复最后个binlog文件

实验二:如果有误删除

  备份:

    mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql

    mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog

    插入数据 //模拟服务器正常运行

    drop table db1.t1 //模拟误删除

    插入数据 //模拟服务器正常运行

  恢复:

    #mysqlbinlog 最后一个binlog --stop-position=260 > /tmp/1.sql 

    #mysqlbinlog 最后一个binlog --start-position=900 > /tmp/2.sql 

    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中

5、实现自动化备份

备份计划:

    1)什么时间 2:00

    2)对哪些数据库备份

    3)备份文件放的位置

备份脚本:

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 {} \;

手动测试:

    #chmod a+x /mysql_back.sql 

    #chattr +i /mysql_back.sql

    #/mysql_back.sql

配置cron:

    # crontab -e

    2 * * * /mysql_back.sql

6、表的导出和导入

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

    mysql> SELECT * FROM school.student1 INTO OUTFILE 'student1.txt'

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

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

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

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

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

注意:可能会报错 ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable

数据库最关键的是数据,一旦数据库权限泄露,那么通过上述语句就可以轻松将数据导出到文件中然后下载拿走,因而mysql对此作了限制,只能将文件导出到指定目录

    [mysqld]

    secure_file_priv='xxx' 

重启mysql,重新执行上述语句

7、数据库迁移

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

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

三、pymysql模块

1、安装

pip3 install pymysql

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

    import pymysql

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

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

    #链接

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

    #游标

    cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示

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

    #执行sql语句

    sql='select * from tablename 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('登录失败')

3、 execute()之sql注入

4、增、删、改:conn.commit()

5、查:fetchone,fetchmany,fetchall

6、获取插入的最后一条数据的自增ID