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.
mysql -uYOURUSERNAME -pYOURPASSWORD -DYOURDATABASE -e"CALL YOURSTOREDPROCEDURE('YOUR_SP_PARAMETER')"
-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.
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
Postgres comes with a functionality called vacuum. Vacuum is intended to cleanup dead tuples or rows.
This is how you cleanup the entire DB in PostgreSQL
/usr/pg9/bin/vacuumdb --full --port=5433 --username=YOUR_USERNAME --password DB_NAME
This is how you backup your Postgres Database
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
psql -Upostgres -hlocalhost YOUR_DB_NAME < YOUR_FILENAME.sql
SET SESSION AUTHORIZATION jane;
SET search_path = schema1,schema2, schema3;
SELECT * FROM any_table_in_schema_1_2_or_3;
CREATE CAST(integer AS character varying) WITH INOUT AS IMPLICIT;
CREATE CAST (character varying AS integer) WITH INOUT AS IMPLICIT;
-- Rule: "_DELETE" ON sometable
-- DROP RULE "_DELETE" ON sometable;
CREATE OR REPLACE RULE "_DELETE" AS
ON DELETE TO sometable DO INSTEAD DELETE FROM _sometable
WHERE _sometable.account_id = old.account_id AND (old.domain_id = ANY ((( SELECT get_visible_domains('DELETE'::text) AS get_visible_domains)::integer)));
/pgsql/dev-db/tail -f postgresql.log
SELECT setval('YOUR_SEQUENCE_NAME', (SELECT MAX(id) FROM YOUR_TABLE_NAME));