MySQL Experiment Preparation (i)--environment preparation

MySQL Experimental script preparation (a) the experimental environment in which Python manipulates the database

• Install PIP
  1. download the script file

  [[email protected] .vnc]# wget https://bootstrap.pypa.io/get-pip.py

  2. View the PIP version

  [[email protected] .vnc]# pip --versionpip 10.0.1 from /usr/local/lib/python2.7/site-packages/pip (python 2.7)

  3. Install Pyton mysqldb

  [[email protected] data]# pip install MySQL-python

MySQL 8.0 User & database & table creation

• Creation of a database
  1. Database Creation

  createdatabase zdemo;use zdemo;

  2. statement view of the database

  createdatabase zdemo;+----------+----------------------------------------------------------------+DatabaseCreateDatabase                                                |+----------+----------------------------------------------------------------+ | zdemo    CREATEDATABASE/*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+----------------------------------------------------------------+1rowinset (0.00 sec)

  The character set of the database is given by default and is now generally utf8.

• Creation of tables
  1. Creating statements for tables

  CREATETABLE student (idintNOTNULL auto_increment,stu_id MEDIUMINT unsigned  NOTNULLCOMMENT‘学号id‘varchar(30NOTNULLCOMMENT‘姓名‘,PRIMARYKEY  (`id`));

  2. View of data table statements

  Mysql> ShowCreate Tablezdemo.student;+---------+------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------+|Table|Create Table|+---------+------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------+| Student |CREATE TABLE' Student ' (' ID ')int(Ten) unsigned not NULLAuto_increment, ' stu_id ' Mediumint (8) unsigned not NULL COMMENT ' Learning ID ', ' Stu_name 'varchar( -) not NULL COMMENT ' name ',PRIMARY KEY(' ID ')) Engine=innodbDEFAULTCharset=utf8 |+---------+------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------+  1 Row inch Set(0.00Sec

• Create user
  1. User-created

  createuser [email protected]‘%‘IDENTIFIEDWITHBY‘[email protected]‘PASSWORDEXPIREINTERVAL360DAY;

  2. empowering the Zdemo database to be increased, deleted, changed, checked

  grantselect,insert,update,deleteonto [email protected]‘%‘;

  where [email protected]'% ' is matching all IP addresses can access this database, rights management should be set to [email protected]‘192.168.35.%‘ match only one segment, or a specified IP address due to the experimental environment. Safety is not too much to be considered.
  3. View user ZSD permissions

  mysql>Show grants for [email protected]'% ';+----------------------------------------------------------------+ | Grantsfor [email protected]%|+----------------------------------------------------------------+ | GRANTUSAGE on *. * To' ZSD '@`%`                                | | GRANTSELECT, INSERT, UPDATE, DELETE on' Zdemo '. * To' ZSD '@`%` |+----------------------------------------------------------------+2Rows in Set (0.01 sec)

Python link MySQL db instance

• State of the experimental environment
  Client: 10.1.11.18
  MySQL server: 10.1.11.170

• Python link instance statement

   #!/usr/bin/python  #-*-coding:utf-8-*- import  mysqldbdb Span class= "OP" >=  mysqldb. connect  ( "10.1.11.170" , , ,  "Zdemo" , Charset=   ' UTF8 ' ) cursor Span class= "OP" >=  db.cursor () cursor.execute ( "select VERSION ()" ) data =  cursor.fetchone () print   "Database version:  %s   " %  datadb.close ()  

• Execution results

  [[email protected] data]# python test.pyDatabase version : 8.0.11

  Objective: To build the basic environment of client and database, the next section simply simulates the load by writing Python script, bulk inserting statements and query statements. The performance state variables of MySQL are collected through benchmark tests. Do analytical research

MySQL Experiment Preparation (i)--environment preparation