selfhosted:mysql [NetGeekery]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== MYSQL ======

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

LOGIN:
    $ sudo mysql -h ${host} -u ${user} -p
EXIT:
    mysql>$ exit

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

    $ mysql_secure_installation
    ## restart mysql

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