Quick Mysql Tips

mysql dump limited data

mysqldump -uroot -pxx dbname dbtable --where="true order by transactionid desc limit 1"

Logging slow queries

To enable the slow query log, start mysqld with the –log-slow-queries[=file_name] option. Use the mysqldumpslow command to summarize the queries that appear in the log. For example:

mysqldumpslow /path/to/your/mysql-slow-queries.log -t 10

shows you top 10 performance killers.

Lost Password

  • shutdown mysql, then
 mysqld –skip-grant-tables –u root
 mysql -u root
 mysql> use mysql;
 mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE User = 'root';
 mysql> FLUSH PRIVILEGES;
  • if get error on ip cannot access db
GRANT ALL ON *.* TO 'someuser'@'somehost';
  • if get error on password, check if the host is correct.

Add new User 

GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY 'password';

Remove foreign key constraint

Mass Update Substring

update table_name set column=replace(column, 'old_substring', 'new_substring') where title like '%/abc/123%'

* Sometimes it may be very difficult to use regex in mysql. If that is the case, use php or perl to do the query, extract the relevant variables, then do a manual update query. Abit more time consuming but this method is very reliable.

Exporting to cvs

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

or doing it from command line

mysql -uexampleuser -pletmein exampledb -B -e "select * from \`person\`;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

Check Mysql Config

mysql -uroot -p
show variables;

sort mysql database/tables based on table size

Like it.? Share it:
Tags:

Comments are closed.