mysql.sql

# Run a new instance
# https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization.html
# 
mysqld --initialize-insecure --user=mysql --datadir=/mnt/mysql/data
mysqld_safe --defaults-file=/etc/mysql/my-3307.conf &
mysql -h 127.0.0.1 -P 3307 -u root -p

# Shutdown the instance
mysqladmin -h 127.0.0.1 -P 3307 -u root -p123456 shutdown

# Create user and Grant privileges
# https://support.rackspace.com/how-to/installing-mariadb-server-on-centos/
SELECT User, Host FROM mysql.user;
CREATE USER 'root'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
SHOW GRANTS FOR 'root'@'%';

revoke ALL ON `supervisor`.* TO 'supervisor'@'%'

# Change password
ALTER USER 'root'@'%' IDENTIFIED BY '654321';

# Backup use select into outfile
mysql -h 192.168.1.2 -u username -p'passwd' dbname -e "select * from table_name" > outfile.txt

# Restore
load data infile '/path/to/outfile.txt' into table table_name CHARACTER SET utf8mb4;
# Skip error
load data infile '/path/to/outfile.txt' IGNORE into table table_name CHARACTER SET utf8mb4;

# Group concat 
# See: https://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field
select group_concat(id SEPARATOR ', ' ) from (select * from  file_type where file_ext in ('pptx','ppt')) as t1;
                                         
# Group by, Having, FROM_UNIXTIME
select gid, user_id, FROM_UNIXTIME(user_ptime, "%Y-%m-%d"), count(*) as total 
from test group by gid, user_id, FROM_UNIXTIME(user_ptime, "%Y-%m-%d") having total > 100 
order by total desc;

# How to rename a database in MySQL?
# https://tableplus.com/blog/2018/11/how-to-rename-a-database-in-mysql.html
mysqldump -u username -ppassword -R db_name > db_name.sql
mysqladmin -u username -ppassword create new_db_name
mysql -u username -ppassword new_db_name < db_name.sql      

# function
select FROM_UNIXTIME(1344954515,'%Y-%m-%d %H:%i:%S'); 
select UNIX_TIMESTAMP('2012-09-04 18:17:23');

# load file 
LOAD DATA INFILE '/var/lib/mysql-files/65'
IGNORE INTO TABLE note
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(nid, user_id, title, content, update_time, create_time);