MySQL实验准备(一)--环境准备

时间：2024-08-21 16:05:26

MySQL实验脚本准备(一)

python操纵数据库的实验环境

  • 安装pip

    1.下载脚本文件 
[root@db01 .vnc]# wget https://bootstrap.pypa.io/get-pip.py

2.查看pip版本

[root@db01 .vnc]# pip --version

pip 10.0.1 from /usr/local/lib/python2.7/site-packages/pip (python 2.7)

3.安装Pyton MySQLdb

[root@db01 data]# pip install MySQL-python

MySQL 8.0用户&数据库&表的创建

  • 数据库的创建

    1.数据库创建 
create database zdemo;

use zdemo;

2.数据库的语句查看

mysql> show create database zdemo;

+----------+----------------------------------------------------------------+

 | Database | Create Database                                                |

+----------+----------------------------------------------------------------+

 | zdemo    | CREATE DATABASE `zdemo` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+----------------------------------------------------------------+

1 row in set (0.00 sec)

数据库的字符集会默认给予，现在一般都是utf8。

  • 表的创建

    1.表的创建语句 
CREATE TABLE student (

id int unsigned NOT NULL auto_increment,

stu_id MEDIUMINT unsigned  NOT NULL COMMENT '学号id',

stu_name varchar(30) NOT NULL COMMENT '姓名',

PRIMARY KEY  (`id`)

);

2.数据表语句的查看

mysql> show create table zdemo.student;

+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 | Table   | Create Table                                                                                                                                                                                                                                          |

+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 | student | CREATE TABLE `student` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `stu_id` mediumint(8) unsigned NOT NULL COMMENT '学号id',

  `stu_name` varchar(30) NOT NULL COMMENT '姓名',

  PRIMARY KEY (`id`)

  ) ENGINE=InnoDB DEFAULT CHARSET=utf8     |

+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

  1 row in set (0.00 sec)
  • 创建用户

    1.用户创建 
create user zsd@'%' IDENTIFIED WITH sha256_password BY 'zsd@7101' PASSWORD EXPIRE INTERVAL 360 DAY;

2.赋予对zdemo数据库的增，删，改，查权限

grant select,insert,update,delete on zdemo.* to zsd@'%';

其中zsd@'%'是匹配所有IP地址都可以访问这个数据库，权限管理应该设置为zsd@'192.168.35.%'只匹配某一个网段，或者某个指定IP地址，由于实验环境。安全不做过多考虑。

3.查看用户zsd的权限

mysql> show grants for zsd@'%';

+----------------------------------------------------------------+

 | Grants for zsd@%                                               |

+----------------------------------------------------------------+

 | GRANT USAGE ON *.* TO `zsd`@`%`                                |

 | GRANT SELECT, INSERT, UPDATE, DELETE ON `zdemo`.* TO `zsd`@`%` |

+----------------------------------------------------------------+

2 rows in set (0.01 sec)

python 链接MySQL数据库实例

  • 实验环境状态

    客户端：10.1.11.18

    mysql服务端:10.1.11.170
  • python链接实例语句
#!/usr/bin/python

# -*- coding: UTF-8 -*-

import MySQLdb

db = MySQLdb.connect("10.1.11.170", "zsd", "zsd@7101", "zdemo", charset='utf8' )

cursor = db.cursor()

cursor.execute("SELECT VERSION()")

data = cursor.fetchone()

print "Database version : %s " % data

db.close()
  • 执行结果
[root@db01 data]# python test.py

Database version : 8.0.11

目的：搭建好客户端和数据库端的基础环境，下一节通过编写python脚本，批量插入语句和查询语句，简单模拟负载。并通过基准测试收集mysql的性能状态变量。做分析研究

相关文章