MYSQL

See Common Linux Commands for [start | restart | stop | enable | status] systemctl commands

LOGIN:

  $ sudo mysql -h ${host} -u ${user} -p

EXIT:

  mysql>$ exit

  $ sudo apt-get install mysql-server
  ## -- mariadb
  $ sudo apt-get install mariadb-server mariadb-client

  $ mysql_secure_installation
  ## restart mysql

  ## create
  mysql>$ create database ${database} character set = 'utf8';

  ## show
  mysql>$ show databases;
      # +--------------------+
      # | Database           |
      # +--------------------+
      # | information_schema |
      # | mysql              |
      # | nextcloud          |
      # | performance_schema |
      # | piwigo             |
      # +--------------------+

  ## select
  mysql>$ use nextcloud;
  # mysql[nextcloud]>$

— TABLES —

  mysql>$ show tables;

— QUERY —

  ## select where column entry does not equal 0
  mysql>$ select * from ${table} where ${column}<>0;

  ## delete
  mysql>$ delete from ${table} where ${column}=${value};

— USERS —

  ## create
  mysql>$ create user ${user}@{host} identified by '${password}';
  ## grant privileges
  ## mysql>$ grant all privileges on ${database}.* to ${user}@{host} identified by '${password}';
  mysql>$ grant all privileges on ${database}.* to ${user}@{host};
  ## flush privileges
  mysql>$ flush privileges;

— BACKUPS —

  ## backup
  $ mysqldump -u root -p simple_invoices > simple_invoices_backup.sql
  ## restore
  $ mysql -u root -p simple_invoices < simple_invoices_backup.sql

  ## compressed backup and restore
  $ mysqldump -u root -p  simple_invoices | gzip -9 > simple_invoices.sql.gz
  $ gunzip < simple_invoices.sql.gz | mysql -u root -p simple_invoices

  ## overwrite
  $ mysqlimport -u root -p simple_invoices simple_invoices_backup.sql

CHANGING CHARACTER SET (TO UTF8):

  ~$ mysql > SHOW CREATE DATABASE simple_invoices;
  +-----------------+--------------------------------------------------------------------------+
  | Database        | Create Database                                                          |
  +-----------------+--------------------------------------------------------------------------+
  | simple_invoices | CREATE DATABASE `simple_invoices` /*!40100 DEFAULT CHARACTER SET latin1 */ |
  +-----------------+--------------------------------------------------------------------------+
  ~$ mysql > ALTER DATABASE `simple_invoices` CHARACTER SET utf8;
  Query OK, 1 row affected (0.00 sec)
  ~$ mysql > SHOW CREATE DATABASE simple_invoices;
  +-----------------+--------------------------------------------------------------------------+
  | Database        | Create Database                                                          |
  +-----------------+--------------------------------------------------------------------------+
  | simple_invoices | CREATE DATABASE `simple_invoices` /*!40100 DEFAULT CHARACTER SET utf8 */ |
  +-----------------+--------------------------------------------------------------------------+

  OR

  $ sudo vi /etc/mysql/my.conf
  </etc/mysql/my.conf>
  [client]
  default-character-set=utf8
  [mysqld]
  character-set-server = utf8

PRIVILEGES:

  ## table permissions:
  MariaDB (none) > grant all on tableName.* to 'user1'@'localhost';
  MariaDB (none) > flush privileges;
  MariaDB (none) > exit;

  $ mysql -u user1 -p -D tableName

RESTART:

  $ sudo /etc/init.d/mysql restart

CREATE USER:

  $ sudo mysql
  MariaDB (none) > create user user1@localhost identified by 'db_password1';
  MariaDB (none) > flush privileges;
  MariaDB (none) > select Host, User, Password, authentication_string from mysql.user;
  | Host | User | Password | authentication_string |
  | localhost | root | *FF04 ... | |
  | localhost | user1 | *1E3D ... | |
  MariaDB (none) > quit
  $ mysql -u user1 -p

DROP USER:

  $ sudo mysql
  MariaDB (none) > drop user user1@localhost;
  MariaDB (none) > flush privileges;
  MariaDB (none) > select Host, User, Password, authentication_string from mysql.user;
  | Host | User | Password | authentication_string |
  | localhost | root | *FF04 ... | |
  MariaDB (none) > quit

QUERIES:

  SELECT <column_name>,
  <function_return_value> as <column_name>,
  <function_return_value> as <column_name>,
  <variable> as <column_name>,
  WHERE <column_name>='<value>' AND <column_name>='<value>'
  GROUP BY <column_name>, <column_name>, <column_name>
  ORDER BY <<column_name>>

  ## fava example:
  SELECT account,
  units(sum(position)) as units,
  cost_number as cost,
  first(price(currency, cost_currency)) as price,
  cost(sum(position)) as book_value,
  value(sum(position)) as market_value,
  cost_date as acquision_date
  WHERE account_sortkey(account) ~ "^[01]" AND account='Assets:US:Fidelity:OJFC'
  GROUP BY account, cost_date, currency, cost_currency, cost_number, account_sortkey(account)
  ORDER BY account_sortkey(account)

ERRORS:

## 'Access denied for user 'root'@'localhost' (using password: YES)'
## login with password
$ mysql -u root -p

## wrong password
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
< /etc/mysql/mysql.conf.d/mysqld.cnf >
  [mysqld]
  skip-grant-tables = 1
  plugin-load-add = auth_socket.so
$ sudo systemctl restart mysql
$ mysql -uroot
  UPDATE mysql.user SET authentication_string=null WHERE User='root';
  FLUSH PRIVILEGES;
  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
< /etc/mysql/mysql.conf.d/mysqld.cnf >
  # [mysqld]
  # skip-grant-tables = 1
$ sudo systemctl restart mysql
$ mysql -u root -p

##  Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.
$ sudo mkdir /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld