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

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的性能状态变量。做分析研究