MYSQL DATABASE INSTALLATION AND ADMINISTRATION
I. Downloading MySQL:
The official download site for MySQL is: https://dev.mysql.com/downloads. Please click on the
links down below to download MySQL Installer 8.0.22:
https://dev.mysql.com/downloads/installer/
Once downloaded, please use the MD5 checksums and GnuPG signatures to verify the integrity of
the packages you download.
II. Installing and configuring:
1. Installing and configuring on Windows:
Step 1: In “Choosing a Setup Type”, choose “Custom”, then click next.
Step 2: Add MySQL Server, MySQL Workbench, MySQL Shell, ODBC and MySQL
Documentation. Then click next.
*Notes: After this step, if there is any missing requirement for your desired products, click
“Execute” to resolve automatically.
Step 3: After all required packages have been installed, click “Execute”.
Step 4: After the installation is finished, click next.
Step 5: In “Product Configuration”, click next.
Step 6: In “High Availability”, choose “Standalone MySQL Server / Classic MySQL
Replication” and click next.
Step 7: In “Type and Networking”, click next.
Step 8: Choose “Use Strong Password Encryption for Authentication” as recommendation in
“Authentication Method”, then click next.
Step 9: Enter password for root account, then click next.
Step 10: In “Windows Service”, click on next.
Step 11: In “Apply Configuration”, click execute.
Step 12: Click finish after the configuration is applied.
Step 13: Click on next.
Step 14: Click on finish as the final step of the installation.
2. Installing and configuring on Linux (Debian-based):
Step 1: Download the .deb package from MySQL and run using dpkg:
‘dpkg -i package-name.deb’
Choose ‘ubuntu-bionic’, then ‘OK’
Step 2: Customize what to install.
Choose MySQL Server & Cluster (MySQL server and client).
Step 3: Choose MySQL version.
Choose version 8.0
Step 4: Confirm to create MySQL directory.
Step 5: Enter root password.
Step 6: Re-enter root password.
Step 7: Confirm authentication method.
Step 8: Choose authentication plugin.
Step 9: Exit and run ‘apt-get update’ to update Kali’s repository.
Step 10: Run ‘apt install mysql-community-server’ to install MySQL.
Step 11: Validate installation and MySQL’s version.
Run ‘mysql --help’ or ‘mysql --version’
Step 12: Validate user:mysql and group:mysql have been created.
Run ‘groups:mysql’
III. Administrating:
1. Write a query script to create new users, new databases, new tables and grant
privileges for each user in MySQL Workbench:
Step 1: In MySQL Workbench interface, login as root user with your password:
Step 2: Type the following commands to create new users:
/*Create new users*/
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password1';
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'password2';
CREATE USER 'user3'@'localhost' IDENTIFIED BY 'password3';
Click the lightning button to execute these commands, then check if they are successfully
executed.
*Notes: If there are errors, especially error code 1396, try typing these commands:
FLUSH PRIVILEGES;
DROP USER ‘user1’@’localhost’;
DROP USER ‘user2’@’localhost’;
DROP USER ‘user3’@’localhost’;
Then, re-create 3 users.
Step 3: Type the following commands to create new databases:
/*Create new databases*/
DROP DATABASE IF EXISTS example1;
CREATE DATABASE example1;
DROP DATABASE IF EXISTS example2;
CREATE DATABASE example2;
Check if they are successfully executed. There are warnings if you query to drop a non-
existed database.
Step 4: Create new tables for each databases and insert data for them.
Type the following commands to create new data for ‘example1’ database:
USE example1;
CREATE TABLE ex (
id smallint unsigned not null auto_increment,
name varchar(20) not null,
constraint pk_ex primary key(id)
);
INSERT INTO ex(id, name) VALUES(null, 'Sample data');
Execute them.
Type the following commands to create new data for ‘example2’ database:
USE example2;
CREATE TABLE ex (
id smallint unsigned not null auto_increment,
name varchar(20) not null,
constraint pk_ex primary key (id)
);
CREATE TABLE ex2 (
id2 smallint unsigned not null auto_increment,
name2 varchar(20) not null,
constraint pk_ex primary key (id2)
);
INSERT INTO ex(id, name) VALUES(null, 'Sample data1');
INSERT INTO ex(id, name) VALUES(null, 'Sample data2');
Execute them.
Step 5: Grant permissions for each user.
Type the following commands:
/*User 1 has select, insert privilege on database 1*/
GRANT SELECT, INSERT ON example1.* TO 'user1'@'localhost';
/*User2 has select, update, delete privilege on table ‘ex’ of database ‘example1’ and select,
insert, update, drop privilege on database ‘example2’*/
GRANT SELECT, UPDATE, DELETE ON example1.ex TO 'user2'@'localhost';
GRANT SELECT, INSERT, UPDATE, DROP ON example2.* TO 'user2'@'localhost';
/*User3 has all privileges on database ‘example2’*/
GRANT ALL PRIVILEGES ON example2.* TO 'user3'@'localhost';
Execute them.
Step 6: If you want to check the privileges granted for each user, type this command:
SHOW GRANTS FOR 'user_name'@'localhost';
Step 7: If you want to revoke any privilege, type this command:
REVOKE privilege_name ON database_name.table_name TO user_name@’localhost’;
Example: REVOKE SELECT ON example1.* FROM 'user1'@'localhost';
Now, show grants again to check if it works:
SHOW GRANTS FOR ‘user1’@’localhost’;
Re-grant user1 select privilege to continue the following steps:
GRANT SELECT, INSERT ON example1.* TO 'user1'@'localhost';
Step 8: Login as user1, user2, user3 respectively to check the granted privileges:
Open cmd.exe, navigate to the directory: C:\Program Files\MySQL\MySQL Server
8.0\bin. Type mysql -u user1 -p and Enter. Then you are prompted for a password,
type ‘password1’ and Enter.
*Notes: If you already set mysql as environment variable, then just open cmd.exe
and type mysql -u user1 -p and Enter.
User1 has select, insert privilege on database ‘example1’, so type the following
command to check it:
Show databases;
There are only 2 databases displayed in user1’s view.
Type use example1 to start changing this database. View tables in this database:
Show tables from example1;
Then try drop command:
Expectedly, user1 does not have drop privilege, so this command is denied.
Use select command to view data fields in table ‘ex’:
Select * from ex;
Now try insert a record:
Insert into ex(id, name) values(2, ‘foobar’);
Select * from ex;
Type exit to log out as user1:
Now login as user2:
Mysql -u user2 -p
Type password2 when prompted for password.
User2 has select, update, delete privilege on table ‘ex’ of database ‘example1’ and
select, insert, update, drop privilege on database ‘example2’.
Type the following commands:
Show databases;
Use example1;
Show tables from example1;
Select * from ex;
Update ex set name = 'table_data' where id = 1;
select * from ex;
As user2 has drop privilege, try it:
Use example2;
Show tables from example2;
Drop table ex2;
Show tables from example2;
Type exit to log out.
2. Connect Python with MySQL:
Step 1: First, use pip or pip3 to install the MySQL Connector Python package in cmd.exe:
Pip3 install mysql-connector-python
Step 2: After successfully installed, login to MySQL as root user to create a new user to
interact with MySQL via MySQL Connector Python:
FLUSH PRIVILEGES;
DROP USER IF EXISTS 'mydb'@'localhost';
CREATE USER 'mydb'@'localhost' IDENTIFIED BY 'mydb';
DROP DATABASE IF EXISTS python_db;
CREATE DATABASE python_db;
GRANT ALL PRIVILEGES ON python_db.* TO 'mydb'@'localhost';
Step 3: Write Python script in Pycharm IDE.
In Pycharm GUI, create a new project, create a new Python file. First, import the
necessary package: mysql.connector:
import mysql.connector
Then, login as user ‘mydb’:
db_connection = mysql.connector.connect(
host="localhost",
user="mydb",
passwd=input(‘Enter your password: ’)
)
Type print(db_connection)
Run this file to check if you are connected. If yes, the following line appears in the
console:
Now start using the database ‘python_db’ created by root. Type the following
commands:
db_cursor = db_connection.cursor()
db_cursor.execute('USE python_db;')
db_cursor.execute('DROP TABLE IF EXISTS student')
db_cursor.execute('''CREATE TABLE student (
id smallint not null auto_increment primary key,
name varchar(20)
);''')
# Insert data into table, then use Select command to view them
db_cursor.execute('INSERT INTO student(id, name) VALUES(%s, %s)', (1, 'An'))
db_connection.commit()
db_cursor.execute('INSERT INTO student(id, name) VALUES(%s, %s)', (2,
'Binh'))
db_connection.commit()
db_cursor.execute('SELECT * FROM student')
result = db_cursor.fetchall()
print(result)
Run this file:
Try updating a record:
# Update a record
db_cursor.execute('UPDATE student SET name=%s WHERE id=%s', ('Anh', 1))
db_connection.commit()
db_cursor.execute('SELECT * FROM student')
print('After Update:')
print(db_cursor.fetchall())
Run this file:
Finally, close the cursor and connection:
db_cursor.close()
db_connection.close()
