Tuesday, July 27, 2010

MySQL Innodb table database backups!

It is useful to be able to do database backups and restoration at the command line. Here's my commands for doing so, no 3rd party tools needed for this, just mysql. You can restore the database to a different server entirely.

mysqldump -h 127.0.0.1 -P [my_port] -u [me] -p[password] -B my_database >the_bak

where:
[password] is the database password (no space).
[me] is the mysql admin account username
my_database is the name of your database.
[my_port] database access port, default =3306

Restoration:
create database:
mysql -u[me] -p[password] information_schema -e "create database my_database"

restore database:
mysql -u[me] -p[password] my_database < ./the_bak.sql

New MySQL 5 stored procedures

Seems the old syntax of my stored procedure creation doesn't work in MySQL 5. Don't use $$ as a delimiter anymore!

Here's my NEW sample for you. I included a cursor.

DELIMITER |
CREATE PROCEDURE sp_my_stored_proc()
BEGIN

declare done int default 0;
declare n_value bigint default 0;
declare str_value varchar(200) default '';
declare cur1 cursor for select the_id, the_val from my_table;
declare continue handler for not found set done=1;

open cur1;
repeat
fetch cur1 into n_value, str_value;

if not done then
[multiple statements can go here, each terminated by ;]
end if;

UNTIL done END REPEAT;
CLOSE cur1;
END |