MySql tips

Connect

#enter password manually
mysql --host='10.22.999.162' --user=leo --password

#password in command
mysql -h 10.49.41.213 -u leo --password='mysecret'

Show comments

1. Find table structure
> show columns from book;
> desc book;

get more details
> show create table book;

2 . Find table index
> show index from book;

3. Find permission of user
>  show grants;

show grants for 'cs_user'@'10.32.%.%' 

4. Find stored procedures
>  show procedure status

5. Find triggers
>  show triggers

6. Find more shows

More show commands

> help show;

Date time functions

 >  select date_format(convert_tz(from_unixtime(1370132292), "UTC", "America/Vancouver"), "%Y %m %d");

Stored Procedure

1. create procedure
drop procedure if exists countbook;

DELIMITER //

create procedure countbook()
BEGIN
    select count(*)
    from book;
END //

DELIMITER ;

2. call procedure
> call countbook() 

3. more notes
Stored procedure can take parameters. Parameters can be inputs or outputs. Inside procedure, can declare function (see ben forta MySql crash course)

More Store Procedure

 //find procedure
show procedure status where db="myDW"\G

//show procedure details
show create procedure load_dim_date\G

//flip that from security_type=definer to the invoker
ALTER PROCEDURE update_dim_date SQL SECURITY INVOKER;

More show process list

show full processlist
# 1234 is process id coming from show processlist
kill 1234

Find mysql server hostname

show variables where Variable_name = 'hostname';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| hostname      | ip-10-32-210-21.corp.leotest.com |
+---------------+--------------------------------------+
1 row in set (0.09 sec)

Find mysql users

select * from mysql.user \G

Find mysql version

select version();

Update allowed ip for a user

# if version 5.7.8 and up, can use remame statement
RENAME USER 'qrhen'@'10.17.259.170' TO 'qrhen'@'10.31.248.110';

Grant permission to user

GRANT SELECT ON `MyTabble`.* TO `lyhen`@`10.33.264.%`;

FLUSH PRIVILEGES;

SHOW GRANTS FOR `lyhen`@`10.33.264.%`;