Categories
Database

Access MySQL Externally

Config update

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add this line

bind-address = 0.0.0.0

Allowing user on MySQL

mysql -u root -p

To check if the user can be accessed externally. If only localhost is listed, you are not allowed to access externally.

SELECT host FROM mysql.user WHERE User = 'root';

User creation

CREATE USER root@0.0.0.0 IDENTIFIED BY 'your_password';

Provide privileges

GRANT ALL PRIVILEGES ON *.* TO root@0.0.0.0;
FLUSH PRIVILEGES;
quit

MySQL Version 8.04+

Possible issue “Authentication plugin ‘caching_sha2_password’ and the fix is:

ALTER USER root@0.0.0.0
IDENTIFIED WITH mysql_native_password
BY 'your_password';

Restart MySQL

sudo systemctl restart mysql
Categories
Database

Postgres backup restore command line

Backup

pg_dump -U DB_USER -W -F t DB_NAME > /path/to/file/dump_name.sql

Restore

Using psql

psql -U DB_USER DB_NAME < dump_name.sql

Using pg_restore

pg_restore -d DB_NAME /path/to/file/dump_name.sql -c -U DB_USER