# Databases

> Markdown source of <https://docs.calebsargeant.com/computing/linux/databases/>.

## MySQL

### Creating a User

``` bash
# 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

``` bash
# 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

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

### Logging in Remotely

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

### Privileges

``` bash
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

``` bash
# 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

``` bash
# 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

``` bash
show grants for 'username'@'host';
```

### Updating Data

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

### Deleting Data

``` bash
delete from table_name where condition;
```

### Checking MySQL Status

``` bash
service mysqld status
ps aux | grep mysql
```

### Backup

``` bash
# 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`

### Use

<https://www.tutorialspoint.com/mysql/mysql-select-database.htm>

``` bash
show databases;
use database;
```

### Setting up Replication

<https://www.digitalocean.com/community/tutorials/how-to-set-up-replication-in-mysql>

### Size of DB

``` bash
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

``` bash
/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/>

``` bash
# 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>

``` bash
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:

``` bash
[client]
user=root
password=somepassword
```

### Checking the Version

``` bash
# 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>

``` bash
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/>

``` bash
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>

``` bash
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/>

``` bash
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>

``` bash
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>

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

### Access Denied for User Root at Localhost

<https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost>

### Authentication Plugin Caching Sha2 Password Cannot be Loaded

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

``` bash
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>

``` bash
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>

``` bash
apt install mysql-server
mysql_secure_installation
```

### Json Like Query

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

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

### Converting Epoch to Human Readable Date

<https://stackoverflow.com/questions/23994889/converting-epoch-number-to-human-readable-date-in-mysql>

``` bash
select from_unixtime();
```

### Find Columns in Tables

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

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

## PostgreSQL

### Showing Users

<https://ubiq.co/database-blog/how-to-list-all-users-in-postgresql/#>:~:text=Using%20psql%20command,-Log%20into%20PostgreSQL&text=Enter%20password%20to%20log%20into%20PostgreSQL.&text=Enter%20%5Cdu%20command%20to%20list%20all%20users%20in%20PostrgeSQL.&text=You%20will%20see%20the%20list,user%2C%20enter%20%5Cdu%2B%20command.

``` text
\du+
```

### Deleting Rows

``` bash
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>

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

### Updating Data in Table

``` bash
update table_name set column1=value1 where condition;
```

### Show Tables

<https://www.postgresqltutorial.com/postgresql-show-tables/>

``` bash
\dt
```

### Connect to Database

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

``` bash
\c database;
```

### Backup Database

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

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

### Disable Pager

<https://stackoverflow.com/questions/39850860/disable-wrapping-in-psql-output>

``` bash
PAGER="less -S" psql
```

### 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>

``` bash
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.

``` sql
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>

``` bash
# 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
```

### Vacuum

<https://stackoverflow.com/questions/4759503/how-can-i-perform-the-vacuum-using-the-postgresql#>:~:text=Connect%20to%20the%20database%20and,which%20will%20also%20update%20statistics.

Vacuuming your postgres db must be done once in a while.

``` bash
VACUUM;
```

### Database Sizes

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

``` bash
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;
```