Hi,

Here i share with you some tips and tricks about mysql dump options.

Generally we use the command "mysqldump" to take the mysql database dump.

Syntax:

mysqldump [options] db_name [tables] > File path

Example:

mysqldump -u root testdb > /home/backup/db_dump.sql

This command is used to take the backup of database "testdb" tables and its data's into specified file path.


i will share with you the more tips about mysqldump in the up coming posts.... till good bye...

Regards,
S.Ashokkumar.

hi,
explain how we take dump as particular table...


We need more information from u....


Thanks,
Prasath.K

Hi,

To take a backup of particular table, we can use the following command...

Syntax:

mysqldump <options> <databasename> <tablename> > <file path>

Example:

mysqldump -u root --extended-insert testdb testtable > /home/backup/dump_table.sql

Options:

--extended-insert => Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.

Regards,
S.Ashokkumar

Hi,
It is very useful one...........................


Thanks,
Prasath.K

Hi,

For generally, when taking the mysql database backup by using general options, the store procedure and functions are wont include in the backup script... so we use the some special options for take the backup to stored procedure and functions.


Example :

mysqldump -u root --routines testdb > /home/backup/dump_data.sql

Options:

--routines => Dump stored routines (functions and procedures) from the dumped databases

Regards,
S.Ashokkumar

Hi,
How we take back up routines & also tables simultanously


Thanks,
Prasath.K

Hi,

Table are default while we take the dump.... for the routines we include the additional options... see the above post.... this command is used to take the backup of tables and routines...


Regards,
S.Ashokkumar

Hi,

To take the backup of all the database, we can use the following command,


Example:

mysqldump -u root --routines --extended-insert --all-databases > /home/backup/mysql_dump.sql

Regards,
S.Ashokkumar

Hi techies,

I am using mysqldump utility for taking mysql backup, have written a shell script and scheduled that script to run around 12:00 am for backup process.

It's taking backup and keep the backup (*.sql) file in the same server path itself. I want to store the backed contents in some other (remote) server location so that i can able to restore it if the running mysql server crashed completely.

Can u please help me out ?

__________________
Keep smiling...

Hi,


We cant able to store the backup file in the remote server while taking the backup.... Only the way is, first take the backup in the db server(same server), then move the backup file to remote server.


Regards,
S.Ashokkumar.

Hi Ashok,

thanks, this could be possible if the destination file server also running with MySQL. So that we can deploy the shell script in the destination server and connect the main db server for taking backup.

__________________
Keep smiling...

Use WHERE condition with mysqldump

Here is another tip that we can use WHERE condition with mysqldump.

mysqldump --where='where_condition', -w 'where_condition'

this option dump only the rows that satisfied the where condition.

Eg.

__________________
Keep smiling...

hi,

Here is one more tips..

To add the drop database statement in backup script, we need to included the additional options in the command.


Example:

mysqldump -u root --add-drop-database test_db > /home/backup/dump_sample.sql

Option:

--add-drop-database => Add a DROP DATABASE statement before each CREATE DATABASE statement.

Regards,
S.Ashokkumar.

hi,

To add the drop table statement while creating the backup of database, we can use the option "--add-drop-table " in the mysqldump command.

Example:

mysqldump -u root --add-drop-table test_db > /home/backup/dump_sample.sql

Option:

--add-drop-table => Add a DROP TABLE statement before each CREATE TABLE statement.

Regards,
S.Ashokkumar.

hi,

To add the lock table statement while taking the backup in the script, we can use the option "--add-locks".

Example:

mysqldump -u root --add-locks test_db > /home/backup/dump_sample.sql

Option :

--add-locks => Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded.

Regards,
S.Ashokkumar.

hi,

For continue the operation even if the SQL error occurs during the table dump, we need to include the additional option "--force" in the mysqldump command.


Example:

mysqldump -u root --force test_db > /home/backup/dump_sample.sql

Option:

--force => Continue even if an SQL error occurs during a table dump.

Regards,
S.Ashokkumar

hi,

To create the XML formated dump, we can use the option "--xml" in the mysqldump command.

Example:

mysqldump -u root -xml test_db > /home/backup/dump_sample.sql

Option:

--xml => Write dump output as well-formed XML.

Regards,
S.Ashokkumar.

Hi Techies,

Can someone explain about how to avoid excessive memory usage while exporting records from large databases ?

__________________
Keep smiling...

Yes we've face this same problem
Error Message is "mysqldump: Error 5: Out of Memory (Needed 4236040 bytes) when dumping table 'table_name' at row_number"

Need to increase the RAM memory or MySQL buffer size.

Thanks,
R.Gopi

Yes, increasing RAM / Buffer size could solve this problem.

We can also use

Generally mysqldump uses --quick option by default. --quick option will retrieve data from table one by one rather than loading the entire data from large table and buffer it in memory before dumping it. This could cause excessive memory usage. You can use --skip-quick option if you want skip this option.

__________________
Keep smiling...