• create user in mysql database

    CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
    

    list all users

    mysql -u root -p mysql
    SELECT User FROM mysql.user;
    
  • grant access to specific db
    GRANT ALL PRIVILEGES ON mydb. * TO 'newuser'@'%' IDENTIFIED BY 'mypass';
    FLUSH PRIVILEGES;
    

    when I do not use IDENTIFIED BY than I receive Access denied for user when I connect from remote comp, even I can connect from localhost with same password. Note that in rails, if you skip defining username and password, it will use current linux user.

  • to see all grants (note that single quote ' is required)

    SHOW GRANTS FOR 'newuser';
    SHOW GRANTS FOR 'newuser'@'localhost';
    
  • to remove a grant
    REVOKE ALL PRIVILEGES, GRANT OPTION FROM newuser
    
  • change some user password you can do in three ways

    SET PASSWORD FOR 'myuser'@'localhost' =newpass;
    
    ALTER USER [email protected] IDENTIFIED BY 'newpass';
    
    USE mysql;
    UPDATE user SET authentication_string = PASSWORD('newpass') WHERE user = 'myuser' AND host = 'localhost'
    FLUSH PRIVILEGES;
    
  • change root password:

    sudo /etc/init.d/mysql stop
    sudo mysqld --skip-grant-tables &
    mysql -u root mysql
    UPDATE user SET authentication_string=PASSWORD('asdasd') WHERE User='root'; FLUSH PRIVILEGES; exit;
    
  • login mysql -u root -p
  • select database USE mysql, create database CREATE DATABASE mydb
  • import dump with mysql -u newuser -p dbname < web001db.sql
  • create dump specific tables and rows… only one where, no-create-info to keep old data while you are importing. use >> if you need two tables with specific where
    mysqldump -h<hostname> -u<username> -p <databasename> users --where 'created > DATE_SUB(now(), INTERVAL 7 DAY)' --single-transaction --no-create-info --set-gtid=purged=OFF >> users.sql
    
  • delete table is drop table users; This will also remove index and foreign keys that belongs to table.
  • to remove column you need first to remove foreign keys and index. If you have two or more foreign keys than remove all them by name… find name in mysql if not defined in db/schema.rb, sometimes name is different for index and key.

    SHOW CREATE TABLE operators;
    alter table operators drop foreign key fk_rails_7bd209f074;
    alter table operators drop index fk_rails_7bd209f074;
    alter table operators drop column user_id;
    
  • drop all local databases
    mysql -uroot -p<password> -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| gawk '{print "drop database `" $1 "`;select sleep(0.1);"}' | mysql -uroot -p<password>
    
  • to connect to remote datatabase url using command line cli, for example heroku
    mysql -u hj9gpgm687w1qwx -pvuxns3sidwrjjf8 -h tviw6wn5xwxejwj.cbetxkdyhwsb.us-east-1.rds.amazonaws.com -P 3306 u6rhqznmtth3j4g
    
    # or aws
    DATABASE_URL=mysql2://dbuser:[email protected]/web001db
    mysql -u dbuser -p123456 -h xn01-db01.hrblhe.ap-southeast-1.rds.amazonaws.com web001db
    
    
  • to enable access to mysql on my own local ubuntu mysql user
    # create user and grant access in one command
    GRANT ALL ON wordpressdb.* TO 'wpadmin'@'192.168.1.100' IDENTIFIED BY '%u#098Tl3' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
    

    change port first see on which interface it is using

    sudo netstat -plutn | grep 3306
    # or see if there is a difference
    nmap 127.0.0.1
    nmap 192.168.3.2
    

    if it is 127.0.0.1 than change binding in /etc/mysql/my.cnf (not in /etc/mysql/mysql.conf.d/mysqld.cnf)

    # bind-address = 127.0.0.1
    bind-address = 0.0.0.0
    

    make sure that you do not have firewals (on ubuntu sudo ufw status) and enable port forwarding on routers for TCP 3306

  • restart

    # restart mysql with
    sudo systemctl restart mysql.service
    # or
    sudo /etc/init.d/mysql restart
    # check if port is enabled
    nmap 192.168.1.100
    
  • repair https://stackoverflow.com/questions/8843776/mysql-table-is-marked-as-crashed-and-last-automatic-repair-failed
    sudo service mysql stop
    cd /var/lib/mysql/$DATABASE_NAME
    myisamchk -r $TABLE_NAME
    myisamchk -r -v -f $TABLE_NAME
    sudo service mysql start
    
  • search by regexp
    # when column is string or text
    relation.where('column LIKE :search', search: "%#{search_value}%")
    # otherwise use cast
    relation.where('CAST(column AS CHAR) LIKE :search', search: "%#{search_value}%")
    
  • convert time zone. Only once, you need to upload timezone table using mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql and than you can use
    SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','MET') AS time
    
  • on github actions there is an error
    Mysql2::Error: Can't connect to local MySQL server through socket
    '/var/run/mysqld/mysqld.sock' (2)
    

    solution is to start server, in yml it is

    jobs:
      build:
        runs-on: ubuntu-latest
        steps:
        - run: |
            sudo /etc/init.d/mysql start
    
  • show all datatabases (schemas)
    SHOW DATABASES;
    USE mydb;
    
  • show all tables
    SHOW TABLES;
    
  • show table definition
    SHOW COLUMNS FROM users;
    
  • mysql settings could be different on production and localhost, for example
    ActiveRecord::StatementInvalid (Mysql2::Error: Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'trk_datatables_demo_development.comments.body' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by):
    

    to inspect sql more you can run

    SELECT @@session.sql_mode;
    +-----------------------------------------------------------------------------------------------------------------------+
    | @@session.sql_mode                                                                                                    |
    +-----------------------------------------------------------------------------------------------------------------------+
    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
    +-----------------------------------------------------------------------------------------------------------------------+
    

    so to update you can run

    SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
    

    but this is only until it is restarted. To make it permanent add to

    # /etc/mysql/mysql.conf.d/mysqld.cnf
    [mysqld]
    sql-mode = NO_ENGINE_SUBSTITUTION
    
  • see cpu usage
    select * from INFORMATION_SCHEMA.PROCESSLIST where db = 'web001db' and INFO is not null;
    

    Find host url ip connection

    SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short,
         GROUP_CONCAT(DISTINCT user) AS users,
         COUNT(*) AS threads
    FROM information_schema.processlist
    GROUP BY host_short
    ORDER BY COUNT(*), host_short;