Blog Archives

Postgres – Show current settings

This is VERY easy show all;    

Posted in PostgreSQL, SQL

Forgot root password? Reset it (MySQL)

If you need to get into MySQL but don’t have a password all you need is linux root or sudo. Here are the steps. sudo vi /etc/my.cnf   or sudo vi /etc/mysql/my.cnf add this:  skip_grant_tables under [mysqld] sudo /etc/init.d/mysqld restart mysql

Posted in Linux, MySQL, SQL

Move database files from one server to another

I like to use scp to transfer files of a database from one box to another, then follow-up with an rsync. this is how I do it: sudo rsync -avz /source/path/* user@boxname:/destination/path/ Lets break it down:   -a, or –archive

Posted in Linux, MySQL, SQL

ERD with Dia

I recently found a very cool trick using Dia to make an ERD. sudo yum install dia Get this: Then run a script like this: mysql -uyouruser -pyourpass -A -Dyourdatabase -e ‘SELECT CONCAT(“desc “,table_name,”;”) AS cmd FROM (select table_name

Posted in Linux, MySQL, SQL

Unknown table engine ‘InnoDB’

Recently I restarted MySQL. All of my InnoDB tables had error: Unknown table engine ‘InnoDB’ This is how I fixed it: Ran:  SHOW ENGINES;   and InnoDB was not listed Checked my.cnf …. it did not have skip-innodb cd to

Tagged with:
Posted in Linux, MySQL, SQL

How to see whats running in MySQL

Short List Show Processlist; Detailed List Show full Processlist; If you like the linux TOP or HTOP try this: watch -n1 ‘echo “show processlist” | mysql -uyourusername -pyourpass | grep -v “show processlist”‘ Save it as a .file. but make

Tagged with:
Posted in Data Warehouse, ETL, Linux, MySQL, SQL