MYSQL database sych live -> sandbox? How to w/ 25 gig remote DB?



  • Not so much a coding issue, but certainly a development related WTF...

    My "sandbox" machine (used in dev / support for a pretty big Moodle derived website) has a copy of our "live" database in it. Overall its a nice setup - I can do almost anything to my sandbox and see what the effect on the live server would be, plus my dev work doesn't slow down when the server load goes up. The one drawback is DB synchronization; I like to stay up to date with the live DB because occasionally I'm called on to analyse why something went wrong for a user, and that's much simpler if I can reproduce the problem by logging in as the user who had the issue (which I can do on the live site, but then I can't test code changes). I alos like to occasionally wipe away the DB changes I've made locally, as sometimes I end up diverging from the live DB in non-trivial ways.
    Both of these goals are well accomplished by using a dump file from the love server to clear out everything in the sandbox and write in all the data from the live server. Which worked ok until the site traffic went up 10 fold; now the database dump is roughly 25 gigabytes. Running that many "insert" queries takes over an hour (more on slower machines, like the Gen 1 Mackbook Pro I was using until recently), and pulling that much data in from the remote server that hosts the live site is also annoying.

    Please tell me there's a better way to do this ...



  •  Doesn't on older version of MySQL have the ability topull data from a live server - `LOAD DATA FROM MASTER`

     

    Maybe downgrade your install and try that!



  • Why do you have to do it using individual "insert" queries? Surely MySQL has some sort of bulk backup/restore functionality?



  • Why do you have 25GB of remote data? Sounds like you've got a lot of crap in there, probably log hits. Just exclude those tables from the dump.



  • I know this is probably way too late for you to ever see it, but a bit of advice I learnt years ago, that took one experience of reloading a big table from 14 hours to 45 minutes.

    ALTER TABLE xyz DISABLE KEYS;

    Reload your data by whatever method (sql dump file, load data infile or even remote connection if you have to)

    ALTER TABLE xyz ENABLE KEYS;

    In this way, it saves him having to add to the index(es) after every record is inserted - updating those BTREEs one record at a time takes way longer than rebuilding the BTREE once from scratch it seems. There's also a potential to save on disk thrashing, as the heads are not constantly jumping back between the MYD and MYI files for each record (assuming you're using MyISAM tables). You might also want to consider temporarily disabling binlogs and other logging for the same reason i.e. to avoid thrashing. On some of our production servers, we actually kept the logs on a completely different disk (controlled by a 2nd disk controller) from where we stored the tables, and the performance speedup was huge.

    If you are using InnoDB, then [inserty-deity-here] help you. Or don't use InnoDB (usually the simpler option).



  • @daveime said:

    ALTER TABLE xyz DISABLE KEYS;

    Reload your data by whatever method (sql dump file, load data infile or even remote connection if you have to)

    ALTER TABLE xyz ENABLE KEYS;

    This has been the default behavior of mysqldump for quite some time.

    @daveime said:

    If you are using InnoDB, then [inserty-deity-here] help you. Or don't use InnoDB (usually the simpler option).

    Right, because the inferior performance and lack of features of MyISAM are obviously preferable to an actual transactional, safe storage engine. Have fun with your corrupt disk, dumbass.


  • ♿ (Parody)

    @morbiuswilters said:

    @daveime said:
    If you are using InnoDB, then [inserty-deity-here] help you. Or don't use InnoDB (usually the simpler option).

    Right, because the inferior performance and lack of features of MyISAM are obviously preferable to an actual transactional, safe storage engine. Have fun with your corrupt disk, dumbass.

    Whatever. That transactional integrity junk just gets in your way and slows you down from getting shit done. Kinda like compiler errors.



  • As someone correctly said, Insert query is not option here. Insert query will make you cry and weep till your room floods.



    Best use BULK UPLOAD option and save your times. Good luck.



  • @Nagesh said:

    As someone correctly said, Insert query is not option here. Insert query will make you cry and weep till your room floods.



    Best use BULK UPLOAD option and save your times. Good luck.

    What the hell are you talking about? There is no BULK UPLOAD command.



  • @boomzilla said:

    Whatever. That transactional integrity junk just gets in your way and slows you down from getting shit done. Kinda like compiler errors.

    Not falling for this one...



  • @morbiuswilters said:

    @Nagesh said:
    As someone correctly said, Insert query is not option here. Insert query will make you cry and weep till your room floods.



    Best use BULK UPLOAD option and save your times. Good luck.

    What the hell are you talking about? There is no BULK UPLOAD command.

    You are deliberate drawing me in conversation. You know EXACTLY what I am trying to tell him. If you want attention, send me PM. Lot of people do. I ignore most PM's but I make sure you get reply.

    ktxhbai,


    Nagesh



  • @Nagesh said:

    You know EXACTLY what I am trying to tell him.

    No, I don't.



  • @morbiuswilters said:

    @Nagesh said:
    You know EXACTLY what I am trying to tell him.

    No, I don't.

    Nor do I.

    Morbs, please explain what the hell Naggy's on about - because I ain't got a clue!



  • @Cassidy said:

    @morbiuswilters said:

    @Nagesh said:
    You know EXACTLY what I am trying to tell him.

    No, I don't.

    Nor do I.

    Morbs, please explain what the hell Naggy's on about - because I ain't got a clue!


    Possebilities:
    * He's referring to mysqldump (if you didn't know any better, you could think it's a bulk upload i suppose)
    * Some script an admin/other programmer/someone knowing shell scripting/anyone with enough brain to use google put on the system
    * He's being naggy about something cs ate (wouldn't be the first one)
    * He finally lost his mind because there aren't any fora where non-delusional it guys/students/... are and the mods don't go into overdrive when an thread detours like this one, this is the sanest bunch.


  • @roelforg said:

    Possebilities:
    * He's referring to mysqldump (if you didn't know any better, you could think it's a bulk upload i suppose)
     

    I think of the mysqldump command as "taking a backup", not a bulk upload operation, but...

    @Nagesh said:

    Best use BULK UPLOAD option

    .. suggests it's an option to something, a directive to a command or a clause to a statement.

    I suspect Naggy's got his DBs mixed up somewhere.

     



  • @Cassidy said:

    @roelforg said:

    Possebilities:

    • He's referring to mysqldump (if you didn't know any better, you could think it's a bulk upload i suppose)
     

    I think of the mysqldump command as "taking a backup", not a bulk upload operation, but...

    @Nagesh said:

    Best use BULK UPLOAD option

    .. suggests it's an option to something, a directive to a command or a clause to a statement.

    I suspect Naggy's got his DBs mixed up somewhere.

     



    mysqldump can take a dump* and load it back to the db.
    • i think it generates SQL files full of inserts

      Another opt: LOAD DATA LOCAL INFILE


  • @roelforg said:

    mysqldump can take a dump* (it generates SQL files full of table creation and row insert statements) which can then be used to rebuild and populate the database.

    FTFY. mysqldump does NOT "load it back into the db", it doesn't perform recovery.

    (pedantic dickweedery because I didn't want to give anyone the wrong impression)



  • @Cassidy said:

    @roelforg said:

    mysqldump can take a dump* (it generates SQL files full of table creation and row insert statements) which can then be used to rebuild and populate the database.

    FTFY. mysqldump does NOT "load it back into the db", it doesn't perform recovery.

    (pedantic dickweedery because I didn't want to give anyone the wrong impression)

    Quote from teh manpagez:

    [quote user="Ubuntu Manpage for mysqldump on oneiric (http://manpages.ubuntu.com/manpages/oneiric/man1/mysqldump.1.html)"]

     A common use of mysqldump is for making a backup of an entire database:

               shell> mysqldump db_name > backup-file.sql

           You can load 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

           To dump all databases, use the --all-databases option:

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

           For InnoDB tables, mysqldump provides a way of making an online backup:

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

           This backup acquires 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 the
           lock is released. If long updating statements are running when the
           FLUSH statement is issued, the MySQL server may get stalled until those
           statements finish. After that, the dump becomes lock free and does not
           disturb reads and writes on the tables. If the update statements that
           the MySQL server receives are short (in terms of execution time), the
           initial lock period should not be noticeable, even with many updates.

           For point-in-time recovery (also known as "roll-forward," when you need
           to restore an old backup and replay the changes that happened since
           that backup), it is often useful to rotate the binary log (see
           Section 5.2.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 --master-data and --single-transaction options can be used
           simultaneously, which provides a convenient way to make an online
           backup suitable for use prior to point-in-time recovery if tables are
           stored using the InnoDB storage engine.

           For more information on making backups, see Section 6.2, "Database
           Backup Methods", and Section 6.3, "Example Backup and Recovery
           Strategy".

    [/quote]



  • @roelforg said:

    Quote from teh manpagez:

    Did you even read that? mysqldump does not handle recoveries. Please stop posting on things you obviously have no clue about.



  • @roelforg said:

    * i think it generates SQL files full of inserts

    sigh Yes, that's exactly what it does.

    @roelforg said:

    Another opt: LOAD DATA LOCAL INFILE

    That is a terrible option. You have to have each table in its own file; the files have to be fully uncompressed on disk; it doesn't handle schema, just data; it can get into character encoding issues. Just use mysqldump (or preferably a hot copy, if that's an option..)


  • ♿ (Parody)

    @morbiuswilters said:

    @roelforg said:
    Quote from teh manpagez:

    Did you even read that? mysqldump does not handle recoveries. Please stop posting on things you obviously have no clue about.

    I thought he was trying to post about things where Nagesh is clueless.



  • @morbiuswilters said:

    @roelforg said:
    Quote from teh manpagez:

    Did you even read that? mysqldump does not handle recoveries. Please stop posting on things you obviously have no clue about.



    If i understand the manpage correctly (this includes the parts i didn't quote (huuuge page, btw)).

    mysqldump can create a sqlscript that, when you used the right opts for generating, can create a near identical copy of one db (or all of em) when used.

    And i don't do db's much.

    Note: it does mention recovery several times on the manpages.



  • @roelforg said:

    mysqldump can create a sqlscript that, when you used the right opts for generating, can create a near identical copy of one db (or all of em) when used.

    Yes, that's what it's used for. Nobody disputed that and that's what we've been talking about since the beginning. The problem the OP is having is that the dump files are too big. My guess is he doesn't need a lot of the crap in the dump file for his sandbox so he can omit it.

    @roelforg said:

    Note: it does mention recovery several times on the manpages.

    Yes, it can create a dump. It doesn't load a dump (do recovery). The mysql command line can be used for that.



  • @roelforg said:

    If i understand the manpage correctly (this includes the parts i didn't quote (huuuge page, btw)).
    mysqldump can create a sqlscript that, when you used the right opts for generating, can create a near identical copy of one db (or all of em) when used.

    It can. It does. I've used it successfully on many an occasion, but I've used it to create a dump; I didn't use the command to restore a database, which is why I was questioning Nagesh's BULK UPLOAD option - it's not an option to any mysql command/statement I've come across (doesn't mean it doesn't exist, just that it's new to me and I'm keen on learning more.

    @roelforg said:

    And i don't do db's much.
    Note: it does mention recovery several times on the manpages.

    I'm guessing you don't "do" manpages often, since you'll see that although recovery IS mentioned several times, the mysqldump command isn't shown in the examples of acutally performing a recovery - only in taking a backup. It's actually in the parts you quoted - go back up there and take a read, you'll see that all mentions of taking backup involve the mysqldump command, and recovery actually uses the mysql command to re-run those statements.

    Hope that clears it up.



  • @Cassidy said:

    @roelforg said:

    If i understand the manpage correctly (this includes the parts i didn't quote (huuuge page, btw)).

    mysqldump can create a sqlscript that, when you used the right opts for generating, can create a near identical copy of one db (or all of em) when used.

    It can. It does. I've used it successfully on many an occasion, but I've used it to create a dump; I didn't use the command to restore a database, which is why I was questioning Nagesh's BULK UPLOAD option - it's not an option to any mysql command/statement I've come across (doesn't mean it doesn't exist, just that it's new to me and I'm keen on learning more.

    @roelforg said:

    And i don't do db's much.

    Note: it does mention recovery several times on the manpages.

    I'm guessing you don't "do" manpages often, since you'll see that although recovery IS mentioned several times, the mysqldump command isn't shown in the examples of acutally performing a recovery - only in taking a backup. It's actually in the parts you quoted - go back up there and take a read, you'll see that all mentions of taking backup involve the mysqldump command, and recovery actually uses the mysql command to re-run those statements.

    Hope that clears it up.


    Manpages are common for me.

    I wanted to prove mysqldump can recover (i know it's nothing but wrapping around the client but give me one db dumper* that doesn't generate (or has an option to) sql scripts as dump*

    • non-sql db's excluded


      FYI. Originally the mysqldump discussion started with me suggesting that mysqldump could be nagesh's BULK UPLOAD and could be the op's solution.

      Just sayin'.

      I'll try, though, to not derail threads anymore with a discussion that was nothing more but 2 people misinterpreting eachothers posts.

      So, could or couldn't mysqldump solve the op's broblem?


  • @roelforg said:

    I wanted to prove mysqldump can recover

    No, it doesn't. The mysql client handles recovery. mysqldump just makes dumps (which can then be fed to the mysql client) like its name implies.

    @roelforg said:

    FYI. Originally the mysqldump discussion started with me suggesting that mysqldump could be nagesh's BULK UPLOAD and could be the op's solution.

    Just sayin'.

    I'll try, though, to not derail threads anymore with a discussion that was nothing more but 2 people misinterpreting eachothers posts.

    So, could or couldn't mysqldump solve the op's broblem?

    No. mysqldump is what he is already using. mysqldump isn't "BULK UPLOAD" (whatever the hell that is, nobody's been able to find out yet) because it doesn't upload any data. The problem is the size of the data, not how it is being dumped. He has to download 25GB from the server and then load it on his local machine. My guess is there are some large tables he doesn't strictly need (like a table that logs web hits), so he can probably skip those tables and get a much smaller dump. A hot copy would allow him to skip the load time (but he'd still have to download the entire 25GB--probably more since a dump file will generally be smaller than the size of the corresponding MySQL datadir). However, he doesn't have access to the server to do that, so it's a non-starter.

    Are we clear now?



  • @roelforg said:

    Originally the mysqldump discussion started with me suggesting that mysqldump could be nagesh's BULK UPLOAD and could be the op's solution.

    So, could or couldn't mysqldump solve the op's broblem?

    If you'd bothered to pay attention [b]to the actual OP[/b] you would already know the answer is "no".
    @swiers, in the frigging OP said:
    Both of these goals are well accomplished by using a dump file from the love server [...]

    Running that many "insert" queries takes over an hour (more on slower machines, like the Gen 1 Mackbook Pro I was using until recently), and pulling that much data in from the remote server that hosts the live site is also annoying.

    Please tell me there's a better way to do this ...

    As you can see:

    • He is already using a dump file
    • The problem is that it takes too long to run individual insert queries
    • An additional problem is the size of the dump file

      Therefore, using mysqldump to solve his problem won't work [b]because using mysqldump is the problem[/b].

    Is that finally clear enough for you?



  • @roelforg said:

    Manpages are common for me.
    I wanted to prove mysqldump can recover

    NO IT CAN'T RECOVER! It can create a backup that can be used to recover, but the COMMAND ITSELF DOESN'T PERFORM RECOVERY.

    This is what I'm trying to tell you, and clarify it for the benefit of other people reading this thread that may get the wrong impression from your sentences.

    STOP SAYING THAT MYSQLDUMP CAN RECOVER.

    @roelforg said:

    If i understand the manpage correctly (this includes the parts i didn't quote (huuuge page, btw)).
    mysqldump can create a sqlscript that, when you used the right opts for generating, can create a near identical copy of one db (or all of em) when used.

    That bit is CORRECT, as you've been told before:

    @Cassidy said:

    I didn't use the command to restore a database...

    the mysqldump command isn't shown in the examples of acutally performing a recovery - only in taking a backup.

    all mentions of taking backup involve the mysqldump command, and recovery actually uses the mysql command to re-run those statements.

     

    I'm guessing English isn't your first language, but you need to be aware of mixing together your terminology and misleading people.

    @roelforg said:

    (i know it's nothing but wrapping around the client but give me one db dumper* that doesn't generate (or has an option to) sql scripts as dump*

    Peculiarly, I also thought of mysqldump as a mysql client wrapper, but I can't get it to use .my.cnf.



  • @Cassidy said:

    @roelforg said:

    Manpages are common for me.

    I wanted to prove mysqldump can recover

    NO IT CAN'T RECOVER! It can create a backup that can be used to recover, but the COMMAND ITSELF DOESN'T PERFORM RECOVERY.

    This is what I'm trying to tell you, and clarify it for the benefit of other people reading this thread that may get the wrong impression from your sentences.

    STOP SAYING THAT MYSQLDUMP CAN RECOVER.

    @roelforg said:

    If i understand the manpage correctly (this includes the parts i didn't quote (huuuge page, btw)).

    mysqldump can create a sqlscript that, when you used the right opts for
    generating, can create a near identical copy of one db (or all of em)
    when used.

    That bit is CORRECT, as you've been told before:

    @Cassidy said:

    I didn't use the command to restore a database...

    the mysqldump command isn't shown in the examples of acutally performing a recovery - only in taking a backup.

    all mentions of taking backup involve the mysqldump command, and recovery actually uses the mysql command to re-run those statements.

     

    I'm guessing English isn't your first language, but you need to be aware of mixing together your terminology and misleading people.

    @roelforg said:

    (i know it's nothing but wrapping around the client but give me one db dumper* that doesn't generate (or has an option to) sql scripts as dump*

    Peculiarly, I also thought of mysqldump as a mysql client wrapper, but I can't get it to use .my.cnf.


    My ability to form good sentences decreases as the evening progresses.

    I admit defeat, after reading the thread again i found out that you ment using mysqldump itself to recover while i ment you can use the dumpfile to recover an db as it's a big sql script.

    Both of us are right and both of us are wrong and i shouldn't post when it's 11:30pm.

    Can we get on with our lives?



  • @roelforg said:

    I admit defeat, after reading the thread again i found out that I kept writing about using mysqldump itself to recover while you pointed out that it can't recover, but you can use the dumpfile to recover an db as it's a big sql script.
     

    FTFY. What you meant and what you wrote appear to be two different things, and I can only judge you upon what you wrote.

    I think you're understanding it, but you're still not expressing it correctly in your posts.



  • @Cassidy said:

    [rage... building...]
     

    one of usssss....



  • @Cassidy said:

    Peculiarly, I also thought of mysqldump as a mysql client wrapper, but I can't get it to use .my.cnf.

    It's not, it's its own program. You should be able to specify args in .my.cnf by using a [mysqldump] section. However, you can't specify a DB by doing database= in the .my.cnf, you have to still specify that on the command line, like so: mysqldump [database]



  • @dhromed said:

    one of usssss....
     

    Noooo.... must resist the dark side.....

    @morbiuswilters said:

    You should be able to specify args in .my.cnf by using a [mysqldump] section. However, you can't specify a DB by doing database= in the .my.cnf, you have to still specify that on the command line, like so: mysqldump [database]

    Ooh, okay - useful info. I read somewhere that the [client] section is appropriate to both, so tried it in there and failed.

    Interestingly, the inline help (-?) shows it takes "--defaults-file=other.cnf " as an option, yet this isn't mentioned in the man page. May change some scripts over now...



  • @Cassidy said:

    Interestingly, the inline help (-?) shows it takes "--defaults-file=other.cnf " as an option, yet this isn't mentioned in the man page. May change some scripts over now...

    Better to use --defaults-extra-file. That loads the file at the end, permitting you to override or add settings. --defaults-file loads only that file, skipping anything in /etc/my.cnf, which you probably don't want to do. (For mysqldump there may not be anything interesting in /etc/my.cnf, but for the mysql command-line it will include things like max allowed packet and the client charset and collation.)



  • @morbiuswilters said:

    Better to use --defaults-extra-file
     

    Aha, that's the fellah (I have many common commands aliased I forget the raw options). Will try it with mysqldump[1] later.

    (My /etc/my.cnf has a line or two that changes the default prompt to reflect "user::DBname" so I know who the hell I am and what schema I'm toying with at a glance.And so can my users)

    [1] which still doesn't perform recovery, I tell thee!



  • Sweet testing setup BTW. Some of us have to go through an extensive data capture process to replicate and smoke test more difficult problems.


Log in to reply