1 or permanently

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 all db

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

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;

allow log in without password is actually empty password ""

ALTER USER 'username'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '';
FLUSH PRIVILEGES;

change root password:

sudo /etc/init.d/mysql stop # macos: sudo mysql.server stop
sudo mysqld --skip-grant-tables &
mysql -u root mysql
ALTER USER 'root'@'localhost' IDENTIFIED BY 'dule';
FLUSH PRIVILEGES

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, use script to run single mysql command

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

or to change socket location to match app configuration, eg in

# /etc/mysql/mysql.conf.d/mysql.cnf
[mysql]
socket  = /tmp/mysql.sock

and

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
socket  = /tmp/mysql.sock

and restart sudo service mysql restart

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

create a row with

INSERT INTO table_name(column1, column2) VALUES (value1, value2);
# when you are defining all columns
INSERT INTO table_name VALUES ("01", value1, value2);
# use SET
INSERT INTO table_name SET id=2, column1=value1

error for mysql 8.3 on macos is
```
client.c:1318:3: error: call to undeclared function 'mysql_ssl_set'; ISO C99 and later do not support implicit function declarations
[-Wimplicit-function-declaration]
```
solution is to downgrade and force with brew link [email protected] --force https://github.com/feast-dev/feast/issues/3916#issuecomment-1931711571 ``` brew uninstall mysql brew install [email protected] We’ve installed your MySQL database without a root password. To secure it run: mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run: mysql -u root

[email protected] is keg-only, which means it was not symlinked into /opt/homebrew, because this is an alternate version of another formula.

If you need to have [email protected] first in your PATH, run: echo ‘export PATH=”/opt/homebrew/opt/[email protected]/bin:$PATH”’ » /Users/dule/.bash_profile

For compilers to find [email protected] you may need to set: export LDFLAGS=”-L/opt/homebrew/opt/[email protected]/lib” export CPPFLAGS=”-I/opt/homebrew/opt/[email protected]/include”

For pkg-config to find [email protected] you may need to set: export PKG_CONFIG_PATH=”/opt/homebrew/opt/[email protected]/lib/pkgconfig:/opt/homebrew/opt/zlib/lib/pkgconfig”

To start [email protected] now and restart at login: brew services start [email protected] Or, if you don’t want/need a background service you can just run: /opt/homebrew/opt/[email protected]/bin/mysqld_safe –datadir=/opt/homebrew/var/mysql

brew link [email protected] –force

note that you need to have mysql in your PATH

export PATH=”/opt/homebrew/opt/[email protected]/bin:$PATH”

or permanently

echo ‘export PATH=”/opt/homebrew/opt/[email protected]/bin:$PATH”’ » /Users/dule/.bash_profile ``

Contents

or permanently