mysql client and server in ubuntu os:

security group add inbound :

mysql - port number 3306 - coustome -00000

client ec2(database-client):

sudo hostnamectl set-hostname DB-client ( change a hostname ) bash

sudo apt update

sudo apt install mysql-client -y

mysql --version

check if it installed or not :

mysql --version
mysql  Ver 8.0.43-0ubuntu0.24.04.2 for Linux on x86_64 ((Ubuntu))

server ec2(database-server): it is the main db server

sudo hostnamectl set-hostname DB-server ( change a hostname ) bash

sudo apt update

sudo apt install mysql-server -y

sudo systemctl status mysql

mysql --version

check if it installed or not :

mysql --version
mysql  Ver 8.0.43-0ubuntu0.24.04.2 for Linux on x86_64 ((Ubuntu))

Login to mysql as a root user

sudo mysql ( it will goto mysql inside)

ubuntu@DB-server:/etc/mysql/mysql.conf.d$ sudo mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec)

mysql> create database pramod; Query OK, 1 row affected (0.01 sec)

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | pramod | | sys | +--------------------+ 5 rows in set (0.00 sec)

mysql> select host,user,plugin from mysql.user; +-----------+------------------+-----------------------+ | host | user | plugin | +-----------+------------------+-----------------------+ | localhost | debian-sys-maint | caching_sha2_password | | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | | localhost | root | auth_socket | +-----------+------------------+-----------------------+

mysql> exit

untill this above steps still no password are set to database to login. root user can directly logged in here.

NOW setting a password for database in database server:

this is a command to install secure mysql: plugin :

sudo mysql_secure_installation -y ( setting a password for database first run this command)

this is the command to set password for root user:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Passw@rd//;098';

this is the command to set password for another user pramod:

CREATE USER 'pooja'@'%' IDENTIFIED WITH mysql_native_password BY 'Passw@rd//;098';

FLUSH PRIVILEGES;

after this test sql server if it is working by running sample sql queries:

now in dB-server:

ubuntu@DB-server: sudo mysql -u root -p Enter password: Passw@rd//;098 Welcome to the MySQL monitor.

mysql> select host,user,plugin from mysql.user; +-----------+------------------+-----------------------+ | host | user | plugin | +-----------+------------------+-----------------------+ | % | pooja | mysql_native_password | | localhost | debian-sys-maint | caching_sha2_password | | localhost | mysql.infoschema | caching_sha2_password | | localhost | mysql.session | caching_sha2_password | | localhost | mysql.sys | caching_sha2_password | | localhost | root | mysql_native_password | +-----------+------------------+-----------------------+

mysql>exit

This step is for from client machine if we want to run any sql queries or loginto sql we need to do this below

(( here we can add 0.0.0.0 or backend server ip also we can add because it should communicate to database server ))

ubuntu@DB-server:/etc/mysql/mysql.conf.d$ sudo vi mysqld.cnf

goto this file change localhost (127.0.0.1) ip to 0.0.0.0 in bind-address:

localhost which is more compatible and is not less secure.

bind-address = 0.0.0.0
mysqlx-bind-address = 127.0.0.1

ubuntu@DB-server:/etc/mysql/mysql.conf.d$ sudo systemctl restart mysql

ubuntu@DB-server:/etc/mysql/mysql.conf.d$ sudo systemctl status mysql

db-client :

ubuntu@BD-client: mysql -u pooja -h 54.175.114.137 -p Enter password: Passw@rd//;098

Welcome to the MySQL monitor.

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | performance_schema | +--------------------+

db-server :

mysql> GRANT CREATE, SELECT, DELETE ON pramod.* TO 'pooja'@'%' with grant option; Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON pramod.* TO 'pooja'@'%' with grant option; Query OK, 0 rows affected (0.01 sec)

giving permission of all like root : below command

mysql> GRANT ALL ON . TO 'pooja'@'%' with grant option; Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | pramod | | sys | +--------------------+

db-client:

after permission now pooja user can do all permission in sql, and it shows all the database of complete DB-server.

ubuntu@BD-client: mysql -u pooja -h 54.175.114.137 -p Enter password: Passw@rd//;098

Welcome to the MySQL monitor.

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | pramod | | sys | +--------------------+

mysql> create database rampuri; Query OK, 1 row affected (0.01 sec)

mysql> mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | pramod | | rampuri | | sys | +--------------------+ 6 rows in set (0.00 sec)

mysql> use pramod; Database changed

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | pramod | | rampuri | | sys | +--------------------+ 6 rows in set (0.01 sec)

mysql> use pramod; Database changed

mysql> CREATE TABLE employees ( -> id INT AUTO_INCREMENT PRIMARY KEY, -> first_name VARCHAR(50) NOT NULL, -> last_name VARCHAR(50) NOT NULL, -> email VARCHAR(100) UNIQUE, -> hire_date DATE, -> salary DECIMAL(10,2), -> department VARCHAR(50), -> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -> ); Query OK, 0 rows affected (0.05 sec)

mysql> DESCRIBE employees; +------------+---------------+------+-----+-------------------+-------------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+-------------------+-------------------+ | id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(50) | NO | | NULL | | | last_name | varchar(50) | NO | | NULL | | | email | varchar(100) | YES | UNI | NULL | | | hire_date | date | YES | | NULL | | | salary | decimal(10,2) | YES | | NULL | | | department | varchar(50) | YES | | NULL | | | created_at | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | +------------+---------------+------+-----+-------------------+-------------------+ 8 rows in set (0.00 sec)

mysql> INSERT INTO employees (first_name, last_name, email, hire_date, salary, department) -> VALUES ('Pooja', 'Sharma', 'pooja@example.com', '2025-01-15', 75000.00, 'IT'); Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM employees; +----+------------+-----------+-------------------+------------+----------+------------+---------------------+ | id | first_name | last_name | email | hire_date | salary | department | created_at | +----+------------+-----------+-------------------+------------+----------+------------+---------------------+ | 1 | Pooja | Sharma | pooja@example.com | 2025-01-15 | 75000.00 | IT | 2025-10-29 10:14:37 | +----+------------+-----------+-------------------+------------+----------+------------+---------------------+ 1 row in set (0.00 sec)

below querie is to check size of database:

mysql> SELECT -> table_schema AS Database, -> table_name AS Table, -> ROUND(data_length/1024/1024, 2) AS Data_MB, -> ROUND(index_length/1024/1024, 2) AS Index_MB, -> ROUND((data_length + index_length)/1024/1024, 2) AS Total_MB, -> table_rows AS Rows -> FROM information_schema.TABLES -> WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys') -> ORDER BY Total_MB DESC; +----------+-----------+---------+----------+----------+------+ | Database | Table | Data_MB | Index_MB | Total_MB | Rows | +----------+-----------+---------+----------+----------+------+ | pramod | employees | 0.02 | 0.02 | 0.03 | 0 | +----------+-----------+---------+----------+----------+------+ 1 row in set (0.01 sec)

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | pramod | | rampuri | | sys | +--------------------+ 6 rows in set (0.00 sec)

for rampuri no data added so its showing empty:

mysql> use rampuri; Database changed mysql> -- Pretty table sizes mysql> SELECT -> table_schema AS DB, -> table_name AS Table, -> ROUND(data_length/1024/1024, 2) AS Data MB, -> ROUND(index_length/1024/1024, 2) AS Idx MB, -> ROUND((data_length + index_length)/1024/1024, 2) AS Total MB, -> table_rows AS Rows -> FROM information_schema.TABLES -> WHERE table_schema = DATABASE() -> ORDER BY Total MB DESC; Empty set (0.00 sec)

mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> -- Pretty table sizes mysql> SELECT -> table_schema AS DB, -> table_name AS Table, -> ROUND(data_length/1024/1024, 2) AS Data MB, -> ROUND(index_length/1024/1024, 2) AS Idx MB, -> ROUND((data_length + index_length)/1024/1024, 2) AS Total MB, -> table_rows AS Rows -> FROM information_schema.TABLES -> WHERE table_schema = DATABASE() -> ORDER BY Total MB DESC; +-------+------------------------------------------------------+---------+--------+----------+------+ | DB | Table | Data MB | Idx MB | Total MB | Rows | +-------+------------------------------------------------------+---------+--------+----------+------+ | mysql | help_topic | 1.52 | 0.09 | 1.61 | 1314 | | mysql | help_keyword | 0.13 | 0.13 | 0.25 | 1092 | | mysql | help_relation | 0.09 | 0.00 | 0.09 | 2871 | | mysql | global_grants | 0.09 | 0.00 | 0.09 | 118 | | mysql | replication_group_member_actions | 0.02 | 0.02 | 0.03 | 2 | | mysql | replication_asynchronous_connection_failover | 0.02 | 0.02 | 0.03 | 0 | | mysql | proxies_priv | 0.02 | 0.02 | 0.03 | 1 | | mysql | procs_priv | 0.02 | 0.02 | 0.03 | 0 | | mysql | tables_priv | 0.02 | 0.02 | 0.03 | 2 | | mysql | help_category | 0.02 | 0.02 | 0.03 | 53 | | mysql | db | 0.02 | 0.02 | 0.03 | 3 | | mysql | time_zone | 0.02 | 0.00 | 0.02 | 0 | | mysql | time_zone_leap_second | 0.02 | 0.00 | 0.02 | 0 | | mysql | time_zone_name | 0.02 | 0.00 | 0.02 | 0 | | mysql | time_zone_transition | 0.02 | 0.00 | 0.02 | 0 | | mysql | slave_worker_info | 0.02 | 0.00 | 0.02 | 0 | | mysql | slave_relay_log_info | 0.02 | 0.00 | 0.02 | 0 | | mysql | slave_master_info | 0.02 | 0.00 | 0.02 | 0 | | mysql | time_zone_transition_type | 0.02 | 0.00 | 0.02 | 0 | | mysql | columns_priv | 0.02 | 0.00 | 0.02 | 0 | | mysql | servers | 0.02 | 0.00 | 0.02 | 0 | | mysql | server_cost | 0.02 | 0.00 | 0.02 | 6 | | mysql | user | 0.02 | 0.00 | 0.02 | 6 | | mysql | role_edges | 0.02 | 0.00 | 0.02 | 0 | | mysql | replication_group_configuration_version | 0.02 | 0.00 | 0.02 | 1 | | mysql | replication_asynchronous_connection_failover_managed | 0.02 | 0.00 | 0.02 | 0 | | mysql | plugin | 0.02 | 0.00 | 0.02 | 1 | | mysql | password_history | 0.02 | 0.00 | 0.02 | 0 | | mysql | innodb_table_stats | 0.02 | 0.00 | 0.02 | 3 | | mysql | innodb_index_stats | 0.02 | 0.00 | 0.02 | 12 | | mysql | gtid_executed | 0.02 | 0.00 | 0.02 | 0 | | mysql | func | 0.02 | 0.00 | 0.02 | 0 | | mysql | engine_cost | 0.02 | 0.00 | 0.02 | 2 | | mysql | default_roles | 0.02 | 0.00 | 0.02 | 0 | | mysql | component | 0.02 | 0.00 | 0.02 | 0 | | mysql | slow_log | 0.00 | 0.00 | 0.00 | 2 | | mysql | general_log | 0.00 | 0.00 | 0.00 | 2 | +-------+------------------------------------------------------+---------+--------+----------+------+ 37 rows in set (0.12 sec)

mysql> SELECT table_schema AS DB, table_name AS Table, ROUND(data_length/1024/1024, 2) AS Data MB, ROUND(index_length/1024/1024, 2) AS Idx MB, ROUND((data_length + index_length)/1024/1024, 2) AS Total MB, table_rows AS Rows FROM information_schema.TABLES WHERE table_schema = DATABASE() ORDER BY Total MB DESC; +-------+------------------------------------------------------+---------+--------+----------+------+ | DB | Table | Data MB | Idx MB | Total MB | Rows | +-------+------------------------------------------------------+---------+--------+----------+------+ | mysql | help_topic | 1.52 | 0.09 | 1.61 | 1314 | | mysql | help_keyword | 0.13 | 0.13 | 0.25 | 1092 | | mysql | help_relation | 0.09 | 0.00 | 0.09 | 2871 | | mysql | global_grants | 0.09 | 0.00 | 0.09 | 118 | | mysql | replication_group_member_actions | 0.02 | 0.02 | 0.03 | 2 | | mysql | replication_asynchronous_connection_failover | 0.02 | 0.02 | 0.03 | 0 | | mysql | proxies_priv | 0.02 | 0.02 | 0.03 | 1 | | mysql | procs_priv | 0.02 | 0.02 | 0.03 | 0 | | mysql | tables_priv | 0.02 | 0.02 | 0.03 | 2 | | mysql | help_category | 0.02 | 0.02 | 0.03 | 53 | | mysql | db | 0.02 | 0.02 | 0.03 | 3 | | mysql | time_zone | 0.02 | 0.00 | 0.02 | 0 | | mysql | time_zone_leap_second | 0.02 | 0.00 | 0.02 | 0 | | mysql | time_zone_name | 0.02 | 0.00 | 0.02 | 0 | | mysql | time_zone_transition | 0.02 | 0.00 | 0.02 | 0 | | mysql | slave_worker_info | 0.02 | 0.00 | 0.02 | 0 | | mysql | slave_relay_log_info | 0.02 | 0.00 | 0.02 | 0 | | mysql | slave_master_info | 0.02 | 0.00 | 0.02 | 0 | | mysql | time_zone_transition_type | 0.02 | 0.00 | 0.02 | 0 | | mysql | columns_priv | 0.02 | 0.00 | 0.02 | 0 | | mysql | servers | 0.02 | 0.00 | 0.02 | 0 | | mysql | server_cost | 0.02 | 0.00 | 0.02 | 6 | | mysql | user | 0.02 | 0.00 | 0.02 | 6 | | mysql | role_edges | 0.02 | 0.00 | 0.02 | 0 | | mysql | replication_group_configuration_version | 0.02 | 0.00 | 0.02 | 1 | | mysql | replication_asynchronous_connection_failover_managed | 0.02 | 0.00 | 0.02 | 0 | | mysql | plugin | 0.02 | 0.00 | 0.02 | 1 | | mysql | password_history | 0.02 | 0.00 | 0.02 | 0 | | mysql | innodb_table_stats | 0.02 | 0.00 | 0.02 | 3 | | mysql | innodb_index_stats | 0.02 | 0.00 | 0.02 | 12 | | mysql | gtid_executed | 0.02 | 0.00 | 0.02 | 0 | | mysql | func | 0.02 | 0.00 | 0.02 | 0 | | mysql | engine_cost | 0.02 | 0.00 | 0.02 | 2 | | mysql | default_roles | 0.02 | 0.00 | 0.02 | 0 | | mysql | component | 0.02 | 0.00 | 0.02 | 0 | | mysql | slow_log | 0.00 | 0.00 | 0.00 | 2 | | mysql | general_log | 0.00 | 0.00 | 0.00 | 2 | +-------+------------------------------------------------------+---------+--------+----------+------+ 37 rows in set (0.01 sec)

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | pramod | | rampuri | | sys | +--------------------+ 7 rows in set (0.01 sec)

mysql> use pramod Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> SELECT * FROM employees; Empty set (0.00 sec)

mysql> SELECT table_schema AS DB, table_name AS Table, ROUND(data_length/1024/1024, 2) AS Data MB, ROUND(index_length/1024/1024, 2) AS Idx MB, ROUND((data_length + index_length)/1024/1024, 2) AS Total MB, table_rows AS Rows FROM information_schema.TABLES WHERE table_schema = DATABASE() ORDER BY Total MB DESC; +--------+-----------+---------+--------+----------+------+ | DB | Table | Data MB | Idx MB | Total MB | Rows | +--------+-----------+---------+--------+----------+------+ | pramod | employees | 0.02 | 0.02 | 0.03 | 0 | +--------+-----------+---------+--------+----------+------+ 1 row in set (0.00 sec)

mysql> INSERT INTO employees (first_name, last_name, email, hire_date, salary, department) VALUES ('Pooja', 'Sharma', 'pooja@example.com', '2025-01-15', 75000.00, 'IT'); Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM employees; +----+------------+-----------+-------------------+------------+----------+------------+---------------------+ | id | first_name | last_name | email | hire_date | salary | department | created_at | +----+------------+-----------+-------------------+------------+----------+------------+---------------------+ | 2 | Pooja | Sharma | pooja@example.com | 2025-01-15 | 75000.00 | IT | 2025-10-29 11:09:40 | +----+------------+-----------+-------------------+------------+----------+------------+---------------------+ 1 row in set (0.00 sec)

after this all step now you want to see database in GUI so install phpmyadmin?

in db-server:

ubuntu@DB-server:~$ sudo apt install php phpmyadmin -y

ubuntu@DB-server:~$ sudo apt install lynx -y ( linux web browser inside terminal )

run browser :

lynx localhost

lynx localhost/phpmyadmin

goto webpage use pulic ip of instance and see the phpmyadmin page

http://54.175.114.137/phpmyadmin/

here we can modify create update delete the database and more any thing we can do.

extra:

demo python application that conects to our database try this once?