Databases
StackOverflow2013
Postsdbo
POmysqldump without interrupting live production INSERT
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysqldump without interrupting live production INSERT
    primarykey
    data
    text
    <p>I'm about to migrate our production database to another server. It's about 38GB large and it's using MYISAM tables. Due to I have no physical access to the new server file system, we can only use mysqldump.</p> <p>I have looked through this site and see whether will mysqldump online backup bring down our production website. From this post: <a href="https://stackoverflow.com/questions/104612/run-mysqldump-without-locking-tables">Run MySQLDump without Locking Tables</a> , it says obviously mysqldump will lock the db and prevent insert. But after a few test, I'm curious to find out it shows otherwise. </p> <p>If I use</p> <pre><code>mysqldump -u root -ppassword --flush-logs testDB &gt; /tmp/backup.sql </code></pre> <p>mysqldump will eventually by default do a '<strong>--lock-tables</strong>', and this is a <strong>READ LOCAL</strong> locks (<a href="http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_lock-tables" rel="nofollow noreferrer">refer to mysql 5.1 doc</a>), where concurrent insert still available. I have done a for loop to insert into one of the table every second while mysqldump take one minute to complete. Every second there will be record inserted during that period. Which mean, mysqldump will not interrupt production server and <strong>INSERT can still go on</strong>.</p> <p>Is there anyone having different experience ? I want to make sure this before carry on to my production server, so would be glad to know if I have done anything wrong that make my test incorrect.</p> <p>[My version of mysql-server is 5.1.52, and mysqldump is 10.13]</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USsylye
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmysqldump without interrupting live production INSERT
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POmysqldump without interrupting live production INSERT
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POmysqldump without interrupting live production INSERT
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou should gain the access to new server **FIRST**, and probably looking mysql replication to solve the migration . **REMEMBER:** You can dump, and install to new server, **BUT** after you dump the data, there will be **MORE** new write, how you going to synchronize the new write to the new server?
      singulars
      1. POmysqldump without interrupting live production INSERT
      1. USajreal
    2. COFor the subsequent new data write, we will be solving it by using mysqlbinlog, that should be well taken care. Now my main concern will be the first time backup using mysqldump. I just want to make sure whether mysqldump will prevent INSERT or not. From my test, it didn't show it will. Just want to make sure how is other experience.
      singulars
      1. POmysqldump without interrupting live production INSERT
      1. USsylye
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload