Calling a mysql query from linux or dos command line

There are times when you want to call a mysql command from the bash or dos script then call this script in a scheduler (cron job).
The example below will show you how to do so.


-u is where you put your username, you can either put a space or no space at all after the -u (both works). As you can see I prefer not to put any space.
-p is where you put your password.
-D is your database name. That’s a capital D.
-e this is where your query will go. It needs to be quoted (“). If it’s calling a stored procedure or stored function, you’ll need to use the CALL keyword, otherwise if it’s just a simple query don use the CALL keyword.

MySQL Backup and Restore – Using Command Line

Here is how you backup database using command line. The first line is to backup and the second line is to restore.
These commands work on both Windows and Linux. In Windows you might want to setup mysql bin directory the environment PATH, you can do this easily by pressing Window + Break > Advanced System Settings > Environment > PATH. Then run the cmd or the command prompt. If you already open a command prompt before you setup the PATH, you need to restart your command prompt

MySql doesn’t export stored procedure/function by default. It also doesn’t export triggers. If you need those, the you’ll need to add “–routines –triggers” to your command. Even if you don’t use stored procedure, function, or triggers it is safer if you issue your backup with those option enabled.

$ mysqldump -uusername -ppassword --routines --triggers --lock-tables=false database_name > YOURSQLFILE.sql
$ mysql -uusername -ppassword database_name < YOURSQLFILE.sql

If you need to backup all database. The user that we usually use to backup all database is "root". This will backup all triggers, and functions. You will need to pre-create other users that has certain privilege over stored procedure or functions.

mysqldump -uusername -ppassword --opt --events --all-databases > alldb.sql
mysql -uusername -ppassword < alldb.sql

Backup and Restore Postgres Using pg_dump and psql

This is how you backup your Postgres Database

To Backup

pg_dump --host=localhost --port=5432 --username=postgres --file=YOUR_FILENAME.sql YOUR_DB_NAME

There are other options such as if you’re interested only on the structure then you can add:

pg_dump --host=localhost --port=5432 --username=postgres --schema-only --format=p --create --inserts --file=YOUR_FILENAME.sql YOUR_DB_NAME

To Restore

psql -Upostgres -hlocalhost YOUR_DB_NAME < YOUR_FILENAME.sql

Integer Array Casting in PostgreSQL

-- Rule: "_DELETE" ON sometable
-- DROP RULE "_DELETE" ON sometable;

WHERE _sometable.account_id = old.account_id AND (old.domain_id = ANY ((( SELECT get_visible_domains('DELETE'::text) AS get_visible_domains)::integer[])));