how to dump database or tables
i am working on my pc with mysql-5.0.27-community-nt i have created several data bases and tables in that.. to take backup, we have to use dump., i dont know the correct syntax how to use dump to take backup to a specific location., after that how to resore. please help me regarding this. thanks in advance
Re: how to dump database or tables
Which OS your are using ? Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 2:25 PM, muralikrishna g muralikrishn...@gmail.comwrote: i am working on my pc with mysql-5.0.27-community-nt i have created several data bases and tables in that.. to take backup, we have to use dump., i dont know the correct syntax how to use dump to take backup to a specific location., after that how to resore. please help me regarding this. thanks in advance
Re: how to dump database or tables
it should be windows.. . On Fri, Jan 29, 2010 at 2:29 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Which OS your are using ? Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 2:25 PM, muralikrishna g muralikrishn...@gmail.comwrote: i am working on my pc with mysql-5.0.27-community-nt i have created several data bases and tables in that.. to take backup, we have to use dump., i dont know the correct syntax how to use dump to take backup to a specific location., after that how to resore. please help me regarding this. thanks in advance
Re: how to dump database or tables
*Make a backup of yours databases:* shell mysqldump -u user -p --all-databases -e path/file.dmp Implicit to this command quoted above, you will have the --opt option ( shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset ) and -e ( 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 ). Consider to use -e option when you have a big backup - *you will get more faster restore*, improving the time-recovery. *Make a backup of a table:* shell mysqldump -u user -p mysql user -e path/file.dmp In this last way, you will copy to a file only a table of mysql database - its simply like that. *Restore the backup:* To restore, use mysql client, like this... shell mysql -u user -p path/file.dmp Other considerations regards of the operations of restore a backup is to apply the correct O_DSYNC innodb_flush_method to InnoDB, disable foreign key checks and autocommit. These practicals will execute you backup faster then other way. When you use MyISAM, configure in my.cnf the bulk_insert_buffer_size with a value big enough and don't worry, start restore and be happy. *See it on manual:* http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet 2010/1/29 Anand kumar anand@gmail.com it should be windows.. . On Fri, Jan 29, 2010 at 2:29 PM, Suresh Kuna sureshkumar...@gmail.com wrote: Which OS your are using ? Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 2:25 PM, muralikrishna g muralikrishn...@gmail.comwrote: i am working on my pc with mysql-5.0.27-community-nt i have created several data bases and tables in that.. to take backup, we have to use dump., i dont know the correct syntax how to use dump to take backup to a specific location., after that how to resore. please help me regarding this. thanks in advance
RE: how to dump database or tables
-Original Message- From: Anand kumar [mailto:anand@gmail.com] Sent: Friday, January 29, 2010 4:36 AM To: Suresh Kuna Cc: muralikrishna g; mysql@lists.mysql.com Subject: Re: how to dump database or tables it should be windows.. . [JS] This will get you backed up... = REM - This job compacts zips the US MySQL databases REM REM - Parameterize the directory, to make it easier to change. REM REM - Create target zip files with a suffix of REM yymmdd_hhmmss, where yymmdd_hhmmss is a date_time stamp like 030902_134200 SET todirname=c:\users\svadmin\documents\MySQL Backups REM - Clean out any old zip files. DEL /Q %todirname%\*.zip SET hh=%time:~0,2% REM - Since there is no leading zero for times before 10 am, have to put in REM a zero when this is run before 10 am. IF %time:~0,1%== SET hh=0%hh:~1,1% SET yymmdd_hhmmss=%date:~12,2%%date:~4,2%%date:~7,2%_%hh%%time:~3,2%%time:~6,2% REM - Dump and zip the MySQL databases SET dump_name=%yymmdd_hhmmss%_us_gii_mysql_backup mysqldump [-hhost] -ppassword -uuser --all-databases | C:\Program Files\7-Zip\7z.exe a %todirname%\%dump_name% -si%dump_name% = To restore the databases, you'll have to supply the archive name. = C:\Program Files\7-Zip\7z.exe e zip file name | mysql [-hhost] -ppassword -uuser reload_log.txt = That should do you. 7-Zip is a free compression/decompression program for Windows. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
