MySQL - databases

More complete cheatsheet

https://devhints.io/mysql
https://devhints.io/sql-join
https://quickref.me/mysql
        

Information

Database Name

Table Name

Column(s)

Description	Usage
Get column names from table	SELECT COLUMN_NAME FROM information_schema.columns WHERE TABLE_SCHEMA='' AND TABLE_NAME='';
Get Key relationships from table	SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '';
Check for column uniqueness	select count(distinct ), count() from ;
Get data types of fields (with column names)	SELECT COLUMN_NAME, DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where table_schema = '' and table_name = '';

Queries

Database Name

Table Name

Column(s)

Usage	Example	Description
LEFT JOIN	To be copied
filetype:<filetype>	png images filetype:png	Search by filetype

Restore Database

This restore has to be ran with the database name of the database that the database BEGAN with. ( I THINK )
The restore is simply a series of queries that builds out the database from the initial state
mysql -u steven -p mysql < /home/steven/Upwork/DraftSharks/db/draftsharks_production.sql;
        

Privileges

GRANT ALL PRIVILEGES ON  * . * TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, DELETE ON  * . * TO 'user'@'localhost' IDENTIFIED BY 'password';
REVOKE ALL PRIVILEGES ON * . * FROM 'user'@'host'; -- one permission only
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'host'; -- all permissions

# to save and reload changes
FLUSH PRIVILEGES;
        

Purge anything related to mysql

# Check first to see what you're removing (2nd column)
dpkg -l | grep mysql

for str in $(dpkg -l | grep mysql | awk {'print $2'}); do
    sudo apt-get purge $str -y
done
        

Install MySQL

Install MariaDB

# If this part hangs, on the start command, see the BUG ALERT below 
sudo apt install mariadb-server
sudo systemctl start mariadb.service
sudo mysql_secure_installation

BUG ALERT: # If you previously had MySQL installed, you will need to run these commands to fix it
echo "/usr/sbin/mysqld { }" | sudo tee /etc/apparmor.d/usr.sbin.mysqld
sudo apparmor_parser -v -R /etc/apparmor.d/usr.sbin.mysqld
# info source: https://stackoverflow.com/questions/40997257/mysql-service-fails-to-start-hangs-up-timeout-ubuntu-mariadb

        

mariadb main process exited failed with result exit-code

# try this first:
systemctl start mysqld
systemctl start mysql.service
systemctl start mariadb

# if that doesn't work, then this has worked in the past
cd /var/lib/mysql
ls
rm -r *
mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
systemctl start mysqld
systemctl start mysql.service
systemctl start mariadb
        

mysqld: error while loading shared libraries: libaio.so.1: canno....

# completely remove app armor; use at your own risk/discretion
# NOT RECOMMENDED; this removed my ability to open VSCode
sudo /etc/init.d/apparmor stop
sudo /etc/init.d/apparmor teardown
sudo update-rc.d -f apparmor remove
        

Migrate To Postgres

Supposedly making a lisp script after having pgloader installed will do this very easily.
Not tested. Look for downloads of pgloader at https://pgloader.io/

/* content of the script.lisp */
LOAD DATABASE
FROM mysql://dbuser@localhost/dbname
INTO postgresql://dbuser@localhost/dbname;


/*run this in the terminal*/
pgloader script.lisp