MySQL实验脚本准备(一)

python操纵数据库的实验环境

• 安装pip
  1.下载脚本文件
  bash [root@db01 .vnc]# wget https://bootstrap.pypa.io/get-pip.py
  2.查看pip版本
  bash [root@db01 .vnc]# pip --version pip 10.0.1 from /usr/local/lib/python2.7/site-packages/pip (python 2.7)

• 安装Pyton MySQLdb
  bash [root@db01 data]# pip install MySQL-python Collecting MySQL-python Downloading https://files.pythonhosted.org/packages/a5/e9/51b544da85a36a68debe7a7091f068d802fc515a3a202652828c73453cad/MySQL-python-1.2.5.zip (108kB) 100% |████████████████████████████████| 112kB 488kB/s Building wheels for collected packages: MySQL-python Running setup.py bdist_wheel for MySQL-python ... done Stored in directory: /root/.cache/pip/wheels/07/d2/5f/314860e4cb53a44bf0ee0d051d4b34465e4b4fbe9de6d42f42 Successfully built MySQL-python Installing collected packages: MySQL-python Successfully installed MySQL-python-1.2.5

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

• 数据库的创建
  1.数据库创建
  sql create database zdemo; use zdemo;
  2.数据库的语句查看
  sql 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.表的创建语句
  sql 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.数据表语句的查看
  sql 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.用户创建
  sql create user zsd@'%' IDENTIFIED WITH sha256_password BY 'zsd@7101' PASSWORD EXPIRE INTERVAL 360 DAY;
  2.赋予对zdemo数据库的增，删，改，查权限
  sql grant select,insert,update,delete on zdemo.* to zsd@'%';
  其中zsd@’%’是匹配所有IP地址都可以访问这个数据库，权限管理应该设置为zsd@'192.168.35.%'只匹配某一个网段，或者某个指定IP地址，由于实验环境。安全不做过多考虑。

3.查看用户zsd的权限
bash 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链接实例语句
  “`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()
+ **执行结果**bash
[root@db01 data]# python test.py
Database version : 8.0.11
“`
目的：搭建好客户端和数据库端的基础环境，下一节通过编写python脚本，批量插入语句和查询语句，简单模拟负载。并通过基准测试收集mysql的性能状态变量。做分析研究