######################### DATABASES using MySQL and PHPmyadmin ################################
#### basic MySQL configuration:
### install MySQL
apt install mysql-server
## to access MySQL on the server's terminal:
mysql -u root -p
# inform the password for the user root (or sudo) and you should get this prompt:
mysql>
#########################################################################################
#### basic MySQL commands:
# MySQL commands:
mysql> show databases; #shows the databases
mysql> use database_name; #selects the specified database
mysql> show tables; #shows the tables of the selected database
mysql> select * from table_name; #shows all the content of the specified table
mysql> select field_name from table_name; #shows the specified field from the specified
# database
mysql>create database database_name; #CREATES a new database
mysql>drop database database_name; #deletes the database
## create user with full access and privileges connecting only from local server:
mysql> CREATE USER 'USERNAME'@'localhost' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'localhost' WITH GRANT OPTION;
## create user with full access and privileges connecting from any host:
mysql> CREATE USER 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' WITH GRANT OPTION;
# update user's password:
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-PASSWORD") where user='user_name';
mysql> flush privileges;
mysql> quit
# DDL (Data Definition Language) commands that allow to create tables and associated elements:
# create table:
mysql> CREATE TABLE table_name (CODE INT,NAME VARCHAR(50)); #column_name data_type, [repeat...]
# delete table:
mysql> DROP TABLE table_name;
# view table:
mysql> DESC table_name;
# DML (Data Manipulation Database) commands that allow to edit/include data in the database:
# select database (to use DML functions):
mysql>use database_name;
# select data:
SELECT 'field_1', 'field_2' FROM 'table_name' WHERE 'condition';
# insert data:
INSERT INTO table_name (field_1,field_2) VALUES ('value_A','value_B');
# update data:
UPDATE table_name SET field_1='value_1',field_2='value_2' WHERE 'condition';
# delete data:
DELETE FROM table_name WHERE 'condition';
# DCL (Data Control Language) commands that control permissions for data manipulation and
# users' privileges:
# set full privileges for user USERNAME at LOCALHOST:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'LOCALHOST' WITH GRANT OPTION;
# remove permission of the INSERT command for that user:
mysql> REVOKE INSERT ON *.* FROM 'USERNAME'@'LOCALHOST';
#########################################################################################
#### basic PHPmyadmin configuration:
### install the complementary packets and PHPmyadmin:
apt install apache2 libapache2-mod-php php php-mysql phpmyadmin
# select the options: apache, yes, choose_your_password_for_phpmyadmin
## to access the web interface for management, in the browser:
https://server's_IP/phpmyadmin
# inform the user 'phpmyadmin' and the password set during the installation for administrative
# management of PHPmyadmin, OR (after you have created a regular user) inform the user and password created to
# effectively manipulate the database.
#########################################################################################
ATTENTION: Always read a script before you run it!!!
To run a basic MySQL Server and PHPmyadmin configuration script, run the following command line in your server's terminal:
wget -nc https://www.maycke.com.br/guides/raw/mysql-phpmyadmin.sh && chmod 700 mysql-phpmyadmin.sh && sudo ./mysql-phpmyadmin.sh && sudo rm mysql-phpmyadmin.sh
#########################################################################################
📬 Contact