PHP 12 - Laravel Blog (Database)

By Sheldon L

Published at 2020-10-22

Updated at 2020-10-22

---

Basic Syntax

• https://www.tutorialkart.com/

mysql -uroot
# OR
mysql -u [user] -p

SHOW DATABASES;

USE database_name;  # select database

SHOW TABLES;

ALTER TABLE categories ADD is_column 
ENUM('0', '1') NOT NULL AFTER slug; # add a column

Allow Remote Access to Database Manually

ssh ...@...
sudo vim /etc/mysql/my.cnf # ubuntu

# find config under [mysqld] header
# find config and edit like follow:
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
bind-address    = *
# skip-networking  # if skip-networking exists, comment it

sudo systemctl restart mysql

# Creat new allowence
mysql -u [user] -p mysql
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';

# Update allowence
mysql> update db set Host=[REMOTE_IP] where Db=[webdb];
mysql> update user set Host=[REMOTE_IP] where user=[webadmin];
# The Remote IP can be your VPS or LAN...

# Open port 3306
sudo ufw allow 3306

# Test
mysql -u webadmin –h [SERVER_IP] –p

Database Transfer

• https://www.tecmint.com/transfer-mysql-databases-from-old-to-new-server/

• Single database:

# package
mysqldump -u [user] -p --opt [database_name] > [database_name].sql

# transfer
scp [database_name].sql user@example.com:~/

# reinstall
mysql -u [user] -p [newdatabase] < [database_name].sql

• All databases:

# package
mysqldump -u [user] -p --all-databases > all_databases.sql

# transfer
scp all_databases.sql user@example.com:~/

# reinstall
mysql -u [user] -p --all-databases < all_databases.sql

-Check

# check
mysql -u user -p
> show databases;