Backing up a live MySQL DB w/o Locking its tables!

I recently had a project that we have to build a back up tool for our databases. Along time ago I was told by a supervisor that it was essentially impossible to back up a live sql DB with out locking its tables. After literally 5 to 10 minutes of googling and looking through documentation I came up with this, and it works!

mysqldump -u username -ppassword -h hostname --opt --skip-lock-tables --single-transaction db_name > db_name.sql

Hope this helps!

MySql - tut

linux~

1) Start after a fresh install:

#> mysql -u root

2) Set the password for first time:

From inside SQL:

mysql> set password for 'root'@'localhost' = password ('test')

From outside SQL:

#> mysqladmin -u root password test1

3) If password is set, to change password:

#> mysqladmin -u root -ptest1 password test2

4) See if mysql is running:

#> ps -ef | grep mysqld

5) Starting and Stopping mySQL:

Starting:

#> ./etc/init.d/mysql start

Stop:

#> ./usr/bin/mysqladmin -u root -p shutdown

6) Adding a user to Mysql Db

mysql> use mysql;

mysql> insert into user (host, user, password, select_priv, insert_priv, update_priv) values ('localhost', 'guest', password('guest123'), 'Y', 'Y', 'Y');

mysql>flush privileges;

mysql> select host, user, password from user where user = 'guest';

7) creating a database call tutorials:

mysql> create database tutorials

8) view values in the table

SELECT * FROM table_name