creating mysqldump to backup database

Question

I know how mysqldump works. But dont know where to use it?

If I execute this command after starting mysql program then it says error.

I am using ubuntu. So how can I use this utility?

Answer 1

Backup your database this way too..

mysql -u root -p DB_NAME > db_name_backup.sql

If you want to backup all database simply run this

mysql -u root -p > mysql_db_backup.sql

You will learn more about mysql and mysqldump here..

Guide:
mysqldump and mysql
MySQL Database Backup using mysqldump

shell> mysqldump --opt db_name > backup-file.sql

You can read the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating databases by copying data
from one MySQL server to another:

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

It is possible to dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

If you want to dump all databases, use the --all-databases option:

shell> mysqldump --all-databases > all_databases.sql

If tables are stored in the InnoDB storage engine, mysqldump provides a
way of making an online backup of these (see command below). This
backup just needs to acquire a global read lock on all tables (using
FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as
this lock has been acquired, the binary log coordinates are read and
lock is released. So if and only if one long updating statement is
running when the FLUSH...  is issued, the MySQL server may get stalled
until that long statement finishes, and then the dump becomes
lock-free. So if the MySQL server receives only short (in the sense of
"short execution time") updating statements, even if there are plenty
of them, the initial lock period should not be noticeable.

shell> mysqldump --all-databases --single-transaction > all_databases.sql

For point-in-time recovery (also known as “roll-forward”, when you need
to restore an old backup and replay the changes which happened since
that backup), it is often useful to rotate the binary log (see
Section 8.4, “The Binary Log”) or at least know the binary log
coordinates to which the dump corresponds:

shell> mysqldump --all-databases --master-data=2 > all_databases.sql
or
shell> mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql

The simultaneous use of --master-data and --single-transaction works as
of MySQL 4.1.8. It provides a convenient way to make an online backup
suitable for point-in-time recovery if tables are stored in the InnoDB
storage engine.

For more information on making backups, see Section 6.1, “Database
Backups”.

Answer 2

If it's an entire DB, then:

$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql

If it's all DBs, then:

$ mysqldump -u [uname] -p[pass] --all-databases > all_db_backup.sql

If it's specific tables within a DB, then:

$ mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql

You can even go as far as auto-compressing the output using gzip (if your DB is very big):

$ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz

If you want to do this remotely and you have the access to the server in question, then the following would work (presuming the MySQL server is on port 3306):

$ mysqldump -P 3306 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql

To IMPORT:

ype the following command to import sql data file:

$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

In this example, import 'data.sql' file into 'blog' database using vivek as username:

$ mysql -u sat -p -h localhost blog < data.sql

If you have a dedicated database server, replace localhost hostname with with actual server name or IP address as follows:

$ mysql -u username -p -h 202.54.1.10 databasename < data.sql

OR use hostname such as mysql.cyberciti.biz

$ mysql -u username -p -h mysql.cyberciti.biz database-name < data.sql

If you do not know the database name or database name is included in sql dump you can try out something as follows:

$ mysql -u username -p -h 202.54.1.10 < data.sql

REfer: http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

Answer 3

You use it directly on the terminal, just like mysql it self, and pass the parameters directly to it.

mysqldump -u [user] -p[password] [database name] > dumpfilename.sql

Answer 4

mysqldump -u MYSQL_USER -h MYSQL_SERVER -pMYSQL_PASS --all-databases > "dbs.sql"

Answer 5

yes you can.

see http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html for more information on the tool.