Databases

MySQL

Creating a User

# Note that localhost could be a location somewhere else, like a source IP Address of machine connecting to mysql
create user 'myuser'@'localhost' identified by 'password';

Deleting a User

# Note that localhost could be a location somewhere else, like a source IP Address of machine connecting to mysql
drop user 'user'@'localhost';

Showing Users

select user from mysql.user;
select user,host from mysql.user;

Logging in Remotely

# You can -p'mypassword' as well
mysql -u myuser -p -h mydbhostname.com -D mydatabase

Privileges

ALL PRIVILEGES- as we saw previously, this would allow a MySQL user full access to a designated database (or if no database is selected, global access across the system)
CREATE- allows them to create new tables or databases
DROP- allows them to them to delete tables or databases
DELETE- allows them to delete rows from tables
INSERT- allows them to insert rows into tables
SELECT- allows them to use the SELECT command to read through databases
UPDATE- allow them to update table rows
GRANT OPTION- allows them to grant or remove other users’ privileges

Granting Privileges

# Note that localhost could be a location somewhere else, like a source IP Address of machine connecting to mysql
grant all privileges on mydb.* to 'user'@'localhost';
flush privileges;

# Granting one privilege:
grant select privilege on *.* to user@host;

Revoking Privileges

# Note that localhost could be a location somewhere else, like a source IP Address of machine connecting to mysql
revoke DROP on databasename.tablename from 'username'@'localhost';
flush privileges;

Showing Privileges

show grants for 'username'@'host';

Updating Data

update table set column1=newvalue1, column2=newvalue2, where condition;

Deleting Data

delete from table_name where condition;

Checking MySQL Status

service mysqld status
ps aux | grep mysql

Backup

# Backup directly to a remote host (zabbix is the DB name)
# That pipes the mysqldump command through gzip, then to through and SSH connection. SSH on the remote side runs the ‘cat’ command to read the stdin, then redirects that to the actual file where I want it saved.
mysqldump -u root -p zabbix | gzip -c | ssh caleb.sargeant@server.example.com "cat > zabbix.sql.gz"

Restore

The file must be in .sql format. It can not be compressed in a .zip or .tar.gz file.

mysql -p -u username database_name < file.sql

Size of DB

SELECT table_schema "zabbix",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;

select table_schema "DB Name", round(sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" From information_schema.tables group by table_schema;

Resetting Root Password

/etc/init.d/mysqld stop
mysqld_safe --skip-grant-tables &
mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
/etc/init.d/mysqld stop
/etc/init.d/mysqld start

Resetting User Password

https://linuxize.com/post/how-to-change-mysql-user-password/

# MySQL v5.7.6 or later / MariaDB 10.1.20 or later
ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';
FLUSH PRIVILEGES;

# If the above didn't work:
UPDATE mysql.user SET authentication_string = PASSWORD('NEW_USER_PASSWORD') WHERE User = 'user-name' AND Host = 'localhost';
FLUSH PRIVILEGES;

# MySQL v5.7.5 or earlier / MariaDB 10.1.20 or earlier
SET PASSWORD FOR 'user-name'@'localhost' = PASSWORD('NEW_USER_PASSWORD');
FLUSH PRIVILEGES;

Update Encrypted Password

https://stackoverflow.com/questions/56527343/update-encrypted-password-in-mysql-database

update table set password=encrypt(password);

Allow Root Access without Password as Root

https://serverfault.com/questions/563714/allow-linux-root-user-mysql-root-access-without-password

In ~/.my.cnf file as root user:

[client]
user=root
password=somepassword

Checking the Version

# https://stackoverflow.com/questions/8987679/how-to-retrieve-the-current-version-of-a-mysql-database-management-system-dbms
select @@version;

Checking Database Size

https://stackoverflow.com/questions/1733507/how-to-get-size-of-mysql-database

SELECT table_schema "DB Name",
      ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY table_schema;

Connect to Database Remotely

https://docs.rackspace.com/support/how-to/mysql-connect-to-your-database-remotely/

mysql -u fooUser -p -h 44.55.66.77

Import Database with Progress Bar

https://gist.github.com/infusion/492418723b6736784af1

https://dba.stackexchange.com/questions/17367/how-can-i-monitor-the-progress-of-an-import-of-a-large-sql-file

pv dump.sql.tar.gz | tar xO | mysql -u $user -p $database
# Or
pv sqlfile.sql | mysql -u root -p database

Reinstall Mysql after Deleting /var/lib/mysql

https://dba.stackexchange.com/questions/103625/how-to-reinitialise-var-lib-mysql-files

https://mariadb.com/kb/en/mysql_secure_installation/

mkdir /var/lib/mysql
mkdir /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql
mysql_secure_installation

Could not open mysql.plugin table

https://stackoverflow.com/questions/34198735/could-not-open-mysql-plugin-table-some-plugins-may-be-not-loaded/46147066

systemctl stop mariadb
# This will delete all database data!
rm -R /var/lib/mysql/*
mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
systemctl start mariadb

Mysql Refuses Remote Connections

https://serverfault.com/questions/586651/mysql-refuses-to-accept-remote-connections

# my.cnf
[mysqld]
bind-address = 0.0.0.0

Authentication Plugin Caching Sha2 Password Cannot be Loaded

https://stackoverflow.com/questions/49194719/authentication-plugin-caching-sha2-password-cannot-be-loaded

ALTER USER 'yourusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'youpassword';

Change MySQL Temp Folder

https://stackoverflow.com/questions/49899160/change-mysql-tmp-to-somedrive-mysqltmp-in-centos-7-for-error-code-28

nano /etc/mysqld.cnf
  [mysqld]
  tmpdir=/var/lib/mysql/tmp
mysqld --verbose --help | grep tmp

Installing MySQL

https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04

apt install mysql-server
mysql_secure_installation

Json Like Query

https://stackoverflow.com/questions/42918348/postgresql-json-like-query

select * from module_data where data::json->>'title' like '%Board%'
select * from module_data where data->>'title' like '%Board%'

Find Columns in Tables

https://stackoverflow.com/questions/193780/how-can-i-find-all-the-tables-in-mysql-with-specific-column-names-in-them

SELECT DISTINCT TABLE_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE COLUMN_NAME IN ('columnA','ColumnB')
      AND TABLE_SCHEMA='YourDatabase';

PostgreSQL

Deleting Rows

delete from msisdn_seen where msisdn like '27234843223';

Selecting Rows

https://stackoverflow.com/questions/924729/how-to-best-display-in-terminal-a-mysql-select-returning-too-many-fields

# remember to use \G to display it nicely
select * from msisdn_seen where msisdn like '27234843223';

Updating Data in Table

update table_name set column1=value1 where condition;

Connect to Database

Same as use in mysql https://www.tutorialspoint.com/postgresql/postgresql_select_database.htm

\c database;

Backup Database

https://dba.stackexchange.com/questions/17740/how-to-get-a-working-and-complete-postgresql-db-backup-and-test

pg_dumpall -U postgres -h localhost --clean --file=dump.sql

Extracting JSON

https://stackoverflow.com/questions/42918348/postgresql-json-like-query https://popsql.com/learn-sql/postgresql/how-to-query-a-json-column-in-postgresql

select event_time,detail->>'msisdn',detail->>'attachment' from table where whatever=whatever and event_time > '2021-12-22 00:00:01' and event_time <= '2022-01-07 23:59:59' and detail->>'attachment' like '%this%' order by id desc;

Reducing Disk Usage

https://stackoverflow.com/questions/39198380/delete-temporary-files-in-postgresql#:~:text=1%20Answer&text=The%20temporary%20files%20that%20get,not%20delete%20them%20by%20hand.

Temporary files are created in base/pgsql_tmp. Rebooting psql forces the clearing of tmp files by restarting the cleanup query.

Show Running Queries

https://gist.github.com/rgreenjr/3637525 https://stackoverflow.com/questions/11291456/terminate-hung-query-idle-in-transaction#:~:text=Long%2Dlasting%20%22idle%20in%20transaction%22%20should%20be%20avoided%2C,can%20cause%20major%20performance%20problems.

SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

select * from pg_stat_activity;

Connect to PostgreSQL server FATAL no pg_hba.conf entry for host

https://dba.stackexchange.com/questions/83984/connect-to-postgresql-server-fatal-no-pg-hba-conf-entry-for-host

# postgresql.conf
listen_addresses = '*'

# pg_hba.conf
# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

service postgresql restart

Database Sizes

https://makandracards.com/makandra/37935-postgresql-show-size-of-all-databases

SELECT pg_database.datname as "database_name", pg_database_size(pg_database.datname)/1024/1024 AS size_in_mb FROM pg_database ORDER by size_in_mb DESC;