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';
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
