Projects for CS307

ssh
firewall
port 5432 allowed for postgresql, port 3306 allowed for mysql.

Based on official doc

sudo apt install mysql-server

now check the status of mysql by

sudo service mysql status

we should be able to see mysql service running:

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2024-04-26 01:55:41 HKT; 19s ago
    Process: 17741 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 17749 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 4461)
     Memory: 365.6M
        CPU: 2.184s
     CGroup: /system.slice/mysql.service
             └─17749 /usr/sbin/mysqld

Now we need to change the setting so that the server can listen to other IPs

$ sudo ss -tap | grep mysql

LISTEN 0      151        127.0.0.1:mysql           0.0.0.0:*     users:(("mysqld",pid=17749,fd=23))
LISTEN 0      70         127.0.0.1:33060           0.0.0.0:*     users:(("mysqld",pid=17749,fd=21))

We could see that the service only listening to localhost at the moment, we need to change that.

sudo nvim /etc/mysql/mysql.conf.d/mysqld.cnf

vim is also available in this case.
In the vim or nvim, you can search for text with /:

/bind

Now press enter, the cursor should be around a line that looks like this:

bind-address           = 127.0.0.1

Press i to enter INSERT mode in editor, and change the line to:

bind-address           = 0.0.0.0

To save the change and exit vim or nvim, press esc to exit INSERT mode, then input

:wq

Now you should be back to the command line. Remember to restart the service with

sudo service mysql restart

Enter mysql -u root -p to get into mysql service,
if this does not work, try sudo mysql

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
FLUSH PRIVILEGES;
exit;

And try again.

Next, we will create user for both local and remote clients:

create user 'client'@'%' IDENTIFIED BY 'new_password';
GRANT ALL PRIVILEGES ON *.* TO 'client'@'%' WITH GRANT OPTION;
GRANT FILE ON *.* TO 'client'@'%';

You can replace client with whatever username you like, '%' means from any ip, you can also replace it with a specific ip if you like. More specific here

You can only grant part of the privileges for a specific user like following:

GRANT SELECT ON project2.* TO 'guest'@'%';
REVOKE SELECT on project2.users FROM 'guest'@'%';
FLUSH PRIVILEGES;

The above code will allow 'guest'@'%' to access database project2 except table users.

From the client side, we should install mysql-client and header file:

sudo apt install mysql-client
sudo apt-get install libmysqlcppconn-dev
sudo apt-get install libmysqlclient-dev

then access the server with

mysql -u client -p -h 11.4.51.4

Replace client with your username, and replace the ip address with the actual ip of your server.

This has to be enabled on both server and client sides:

Add the following lines to /etc/mysql/my.cnf or /etc/mysql/my.ini, then restart the service.

[mysqld]
local_infile=1

For normal use, start mysql with mysql --local-infile=1 -u myusername -p, and the execute the following sql queries:

USE project1;
CREATE TABLE IF NOT EXISTS rides (rail_user VARCHAR(255), start_station VARCHAR(255), end_station VARCHAR(255), price DOUBLE PRECISION, start_time TIMESTAMP, end_time TIMESTAMP);
LOAD DATA LOCAL INFILE "/tmp/rides.csv" into table `rides`
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

this should upload rides.csv to the database.

Based on official doc

sudo apt install postgresql

Similar process.