23
Jul 08

MySQL: Database Dump tricks

:: articles :: by Gilberto Saraiva

Folks,

Let me relate this:

Sometimes when you are dumping a database to another host your connection can down, and you will lost all your job and start again? No, no, no, you can make some tricks to continue where you stopped.

So let me enumarate the tricks:

MySQLDump commands:

  • Use –add-drop-table
    For don’t lose what you already done, you can config dump to don’t drop the table you are transfering.
    Command: 
     DOS |  copy code |? 
    1
    --add-drop-table=FALSE
  • Use –no-create-info
    To avoid the creation of the table, you need to set this option as true.
    Command: 
     DOS |  copy code |? 
    1
    --no-create-info=TRUE
  • Use –where option
    the –where option will work as a SQL filter for you, so if you have a indexed table you can
    Command: 
     DOS |  copy code |? 
    1
    --where="ID > 300"

    • MySQL commands:

  • Use –force
    To avoid a break on the processing if the dump post a SQL that can cause a error, you need to Force to MySQL continue even with a SQL error and it will keep the rest of the process working.
    Command: 
     DOS |  copy code |? 
    1
    --force

    • So, now you know what the commands do and can create a full command line like this one:

     DOS |  copy code |? 
    1
    2
    C:\MySQL\Bin>mysqldump -h 10.0.0.1 -u root -p123456 --add-drop-table=FALSE --no-create-info=TRUE --where="ID > 300" MyDatabase MyTable | mysql -h www.mysite.com -u MyWebUser -p123456 --force MyWebDatabase
    3
    

    Piece of cake, uhm?
    
          
    
          
    
          articles —   No comments
    
				
    
			
    
					
    
			  
    22
    Jul 08
    
				
    MySQL: Database Dump to another Host
    
        
    :: articles :: by Gilberto Saraiva
    

				
    
					
    Folks,
    

    I’m not a big fan of database programming, but sometimes we need to use then and sometimes we need to manipulate HUGE databases.
    

    Today I’ve take a big problem, a MySQL database with ~4 GB of size to send to webserver on USA, I tryed to use SQLYog, but this one is to slow and don’t provide a way to retry o continue if some error raise. So 2 hours has pass, and I decided to learn a little about MySQL dump system, and for my luck the mysqldump executable make all the job for me, we need only pass the right command line.
    

    Lets take a look on the possibilities:

    

  • MySQLDump is a application that provide a easy way to backup your table(s) or full database(s), keep this idea on the mind!
    • 

  • When we create a dump we can output the structure as a SQL file, normal or compressed one.
    • 

  • And after reading a little about the dump, I founded the glory magic of create a output to another host
    • 

    

    Let me show the glory magic of dump a database to another host:
    
On a shell (DOS or other one):
    

    

     DOS |  copy code |? 
    1
    2
    C:\MySQL\Bin>mysqldump -h 10.0.0.1 -u root -p123456 MyDatabase MyTable | mysql -h www.mysite.com -u MyWebUser -p123456 MyWebDatabase
    3

    
Where:

    

  • -h 10.0.0.1: -h param to indicate the host, the default is localhost, so if you are on the machine that have the database to copy, the use isn’t needed.
    • 

  • -u root: -u param to indicate the mysql user.
    • 

  • -p123456: -p param to indicate the mysql user password. Note: the password is together the -p param without space between.
    • 

  • MyDatabase MyTable: MyDatabase indicate the database name, and MyTable indicate the table name.
    • 

  • | mysql: This param makes mysqldump use mysql command line as the output place.
    • 

  • -h www.mysite.com: Indicate the host to mysql command line.
    • 

  • -u MyWebUser: indicate the mysql user to mysql command line.
    • 

  • -p123456: indicate the mysql user password to mysql command line.
    • 

  • MyWebDatabase: indicate the database to be used by mysql command line.
    • 

    

    This is the simple way to get things working fast, you can advance with your knowlegdes by searching more on the web and reading the following pages:
    
http://dev.mysql.com/doc/refman/5.0/en/upgrading-to-arch.html
    
http://www.tutorialspoint.com/mysql/mysql-database-export.htm
    

    Very good uhm?
    

    Hugs for all
    
          
    
          
    
          articles —   No comments