More MySql tips

dump db

mysqldump --host='127.0.0.1' --user=root --password='passwd' --column-statistics=0  db_name>dw.sql

redirect result to a csv file

select * from usermanager_users
 into outfile '/tmp/test.csv' fields terminated by ',' enclosed by '"' 
 lines terminated by '\n';

redirect result on command line

mysql -u [username ] -p[password] [db_name] -e " SELECT * FROM [tbl_name] " > file_name

Case sensitive compare

#Will match first name Mary, but not match mary
SELECT first_name FROM  `name` WHERE first_name  REGEXP BINARY 'Mary';

Show stored procedures

SHOW PROCEDURE STATUS WHERE Db = 'db_name'\G

# show details of procedure
SHOW CREATE PROCEDURE procedure_name\G

Create a mysql user

CREATE USER lshen@'10.18.276.170' IDENTIFIED   BY 'eikdkdk123scx';

select * from mysql.user where User="lshen"\G

GRANT SELECT  ON *.* TO lshen@'10.18.276.170';

FLUSH PRIVILEGES;

Query Cache

If swap between different MySQL versions, cache may cause problem. For example, they may use the different end character. Therefore, need to flush cache.

#To check whether the query cache is present in your MySQL server
mysql> SHOW VARIABLES LIKE 'have_query_cache';

#To monitor query cache performance
mysql> SHOW STATUS LIKE 'Qcache%';

#removes all query results from the query cache
mysql> RESET QUERY CACHE ;

Slove authentication method unknown to the client issue

Error message: Unable to open PDO connection [wrapped: SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client.

root cause:This error is coming from MySQL 8.x’s new default auth‐plugin (caching_sha2_password) which older PHP/MySQL clients (PDO_MYSQL via mysqlnd) don’t yet understand.

source database server is using Mysql8.4. data warehouse is using php7.3

Solution: upgrade data warehouse to use PHP 8.0

<       apk add bash curl php7-mysqlnd php7-fileinfo php7-soap php7-pdo_mysql php7-xmlwriter mysql-client \
<        php7-dom php7-tokenizer msmtp gnu-libiconv ncurses util-linux && \
---
>        apk add bash curl php8-mysqlnd php8-fileinfo php8-soap php8-pdo_mysql php8-xmlwriter mysql-client mariadb-connector-c \
>        php8-dom php8-tokenizer msmtp gnu-libiconv ncurses util-linux && \