• 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
  • 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
    
    
  • 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}%")