MySQL other commands

MySQL default port = 3306

MySQL USER

show grants for 'SUPER'@'%'
CREATE USER 'SUPER'@'%' IDENTIFIED BY 'toor'
GRANT SELECT ON  *.* TO 'SUPER'@'127.0.0.1'
ALTER USER  'SUPER'@'%' PASSWORD EXPIRE
flush privileges;

GRANT ALL PRIVILEGES ON *.* TO 'SUPER'@'%' WITH GRANT OPTION;

mysql -u SUPER -P 3306 -h 127.0.0.1 -p toor
show grants for 'SUPER'@'%';

select @@port , @@hostname;

show global variables\G
select @@version , @@port , @@hostname , @@datadir , @@socket , @@log_error , @@pid_file , @@max_connections , @@log_bin , @@expire_logs_days;

show global variables like "datadir";
show global variables like "%port%";
SHOW GLOBAL VARIABLES LIKE '%bin%';
SHOW GLOBAL VARIABLES LIKE '%log%';
show slave status\G
show master status\G

select  host,user,authentication_string from mysql.user where user like 'R%';


sfx=$(ps -efa | grep mysql | grep 'suffix')
if [ "x${sfx}" != "x" ]
  then
    instlist=$(ps -ef | grep 'mysqld '| grep -o '@.*' | cut -c2- | awk '{print $1}' | sort );
  else
    instlist=$(ps -efa | grep 'mysqld ' | grep -o 'port=.*' | cut -c6- | sort)
fi
echo $instlist
for i in $instlist; do echo $i ; done

select * from information_schema.processlist where state like '%lock%';


su ansible -c "mysqladmin --login-path=labelPORT status processlist shutdown"
instlist="3366 3367 3368 3369";for INSTANCE in $instlist; do echo ${INSTANCE};mysql --login-path=label${INSTANCE} -e "\s" | grep Up ; done

NO COLOR
echo $PS1
export PS1="[\u@\h \d \@ \w]$ "


mysql --login-path=label${INSTANCE} -e 'SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES table_schema; ';


   instlist=$( ps -efa | grep 'mysqld ' | grep -o 'pid-file=.*' | awk '{print $1}' | cut -d'.' -f1 | rev | cut -c1-4 | rev | sort );
   for INSTANCE in $instlist; do echo ${INSTANCE};mysql --login-path=label${INSTANCE} -e "\s" | grep "Server version" ; done


5.6=>

instlist56=$(ps -efa | grep 'mysqld ' | grep -o 'port=.*' | cut -c6- | sort )
for INSTANCE in $instlist56; do echo ${INSTANCE};/home/usr/bin/mysql --login-path=label${INSTANCE} -e "\s" | grep "Server version"; done

instlist57=$(ps -ef | grep 'mysqld '| grep -o '@.*' | cut -c2- | awk '{print $1}' | sort );
for INSTANCE in $instlist57; do echo ${INSTANCE};mysql --login-path=label${INSTANCE} -e "\s" | grep "Server version"; done

<=5.7

echo $instlist


sfx=$(ps -efa | grep mysql | grep 'suffix')
if [ "x${sfx}" != "x" ]
  then
    instlist=$(ps -ef | grep 'mysqld '| grep -o '@.*' | cut -c2- | awk '{print $1}' | sort );
  else
    instlist=$(ps -efa | grep 'mysqld ' | grep -o 'port=.*' | cut -c6- | sort)
fi


echo $instlist
for INSTANCE in $instlist;
do
echo ${INSTANCE};
mysql --login-path=label${INSTANCE} -e 'SELECT table_schema "database", sum(data_length + index_length)/1024/1024/1024 "size in GB" FROM information_schema.TABLES table_schema ORDER by 2 DESC; ';
done

mysql_config_editor set --login-path=label3306 --host=127.0.0.1 --user=USER --port=3306 --password
mysql_config_editor print --all

mysql -u db_user -S/home/mysql/socket/my.sock -p
/home/usr/bin/mysql --login-path=label3306 -e 'show global variables like "%expire_logs_days%"'
/home/usr/bin/mysql --login-path=label3306 -e 'show global variables like "%log%"'
/home/usr/bin/mysql --login-path=label3306 -e 'show global variables like "general_log_file"'
/home/usr/bin/mysql --login-path=label3306 -e 'show global variables like "log_error"'
/home/usr/bin/mysql --login-path=label3306 -e 'show global variables like "%log_bin%"'
/home/usr/bin/mysql --login-path=label3306 -e 'show global variables like "%relay_log%"'
/home/usr/bin/mysql --login-path=label3306 -e 'show global variables like "%slow_query_log%"'
CLUSTER RHEL7 : sudo pcs status
sudo su - ansible + root on labelJUmper
mysql -h 127.0.0.1 -u USER -p -P port
mysql -u USER -P 3306 -h 127.0.0.1 -p
systemctl status mysqld@port
mysql --login-path=labelPORT


/opt/rh/mysql55/root/usr/bin/mysql -uroot -p pact  -e 'SELECT * from table where field in ('3520','3535','3540') into OUTFILE  'autorisatie.csv' FIELDS TERMINATED BY ',';'


How To Copy a Folder
https://www.cyberciti.biz/faq/copy-folder-linux-command-line/
cp -var /home/source/folder /backup/copy/destination

Where,

-a : Preserve attributes such as file ownership, timestamps
-v : Verbose output.
-r  : Copy directories recursively.

Use Linux rsync Command to copy a folder
rsync -av /path/to/source/ /path/to/destination/

To backup my home directory, which consists of large files and mail folders to /media/backup, enter:
$ rsync -avz /home/vivek /media/backup

copy a folder to remote machine called server1.cyberciti.biz as follows:
$ rsync -avz /home/vivek/ server1.cyberciti.biz:/home/backups/vivek/

Where,

-a : Archive mode i.e. copy a folder with all its permission and other information including recursive copy.
-v : Verbose mode.
-z : With this option, rsync compresses the file data as it is sent to the destination machine, which reduces the amount of data being transmitted something that is useful over a slow connection.
You can show progress during transfer using –progress or -P option:
$ rsync -av --progress /path/to/source/ /path/to/dest