Mysql

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 myuser@localhost 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;

if you know current root password, you can update root password using

mysql_secure_installation

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;
```
show table create to see all foreign keys like CONSTRAINT ... FOREIGN_KEY...
```
SHOW CREATE TABLE 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;

load from csv

# lib/tasks/db.rake
namespace :db do
desc "Load csv dump"
task :load_csv, [:filename] => [:"pakyow:prepare"] do |_, args|
  # https://stackoverflow.com/a/14133740/287166
  # https://stackoverflow.com/a/62965185/287166
  # mysql> set global local_infile=true;
  table_name = File.basename(args[:filename], ".*")
  cols = open(args[:filename]).gets.split(",")
  underscored_cols = cols
    .map do  |col|
      column = col.split.join.underscore
      column = "isbn10" if column == "asin"
      column
    end
  # all tables should sort id desc
  # compare schema with: describe books; show create table books;
  # show schema from all tables; price_engine_development
  # prices.csv should move Price Log to the end so source is not currupted
  system "mysql --local_infile=1 -u#{opts[:user]} #{opts[:database]} -e \"" + <<~SQL + "\""
  SET FOREIGN_KEY_CHECKS = 0;
  LOAD DATA LOCAL INFILE '#{args[:filename]}' INTO TABLE #{table_name}
  FIELDS TERMINATED BY ','
  ENCLOSED BY '\\\"'
  IGNORE 1 LINES
  (#{underscored_cols.join(",")});
  SET FOREIGN_KEY_CHECKS = 1;
  SQL
  # LINES TERMINATED BY '\r\n'
  puts "Completed loading #{opts[:database]}"
end