MySQL实验脚本准备(一)

python操纵数据库的实验环境

• 安装pip
  1.下载脚本文件

  [root@db01 .vnc]# wget https://bootstrap.pypa.io/get-pip.py

  2.查看pip版本

  [root@db01 .vnc]# pip --versionpip 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 MySQLdbdb = 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 " % datadb.close()

• 执行结果

  [root@db01 data]# python test.pyDatabase version : 8.0.11

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