I have a mysqldump backup of my mysql database consisting of all of our tables which is about 440 megs. I want to restore the contents of just one of the tables form the mysqldump. Is this possible? Theoretically, I could just cut out the section that rebuilds the table I want but I don't even know how to effectively edit a text document...
I have one table spread across two servers running MySql 4. I need to merge these into one server for our test environment. These tables literally have millions of records each, and the reason they are on two servers is because of how huge they are. Any altering and paging of the tables will give us too huge of a performance hit. Becau...
I have had some experience with optimizing the my.cnf file but my database has around 4 million records (MyISAM). I am trying to restore from a mysqldump but every time I do I eventually get the dreaded "Repair With Keycache", that may take days. Is there anyway to get past this and let it roll as "Repair By Sorting"? I have 2GB RAM, ...
Hi All, Can anyone provide any input on how to take complete backup of mysql database using mysqldump. when I am taking backup my tables from specified database are only getting backed up not the procedures and functions. Here's the backup command I am using : Operating system i am working on Windows Vista mysqldump -u username -p db1...
mysqldump is reasonably fast, but dumps of a medium-sized database (20-30 megs) take several minutes to load using mysql my_database < my_dump_file.sql Are there some mysql settings I can tune to speed up the load? Is there a better way to load saved data? I've experimented using the mysqlimport utility with CSV-based dumps. These loa...
I have a crontab set up that errors out every time I attempt to do it. It works fine in the shell. It's the format I'm using when I attempt to automatically insert the date into the filename of the database backup. Does anyone know the syntax I need to use to get cron to let me insert the date into the filename? mysqldump -hServer -uUse...
I've been using both mysql and mysqldump to teach myself how to get data out of one database, but I consider myself a moderate MySQL newbie, just for the record. What I'd like to do is get a sub-set of one database into a brand new database on a different server, so I need to create both the db/table creation sql as well as populating...
I'm trying to take a full dump of my database. But while taking a dump, mysqldump skips a few tables. Especially those with foreign keys. But its not that every table with foreign keys is skipped. Some specific tables only! I tried the -f switch. It forced it to include a few tables but still two tables are being skipped. Is this normal?...
I have a mysql shell but for security reasons I cannot run the mysqldump command. I have a table that I made a while ago with a lot of columns, and I want to generate a new "create table" command to create that table on a different database. Is there some command I can run in the mysql shell to generate that? Thanks. ...
Hello, I use rsync to back up the files on my server, and mysqldump to back up my database. Here's my concern: A mysqldump on my database takes about 30 seconds. I have a table called photos that stores info about the images a user has uploaded, including the path to the file. I am worried about what will happen when photos are uplo...
Hi, I have a MySQL Database of about 1.7GB. I usually back it up using mysqldump and this takes about 2 minutes. However, I would like to know the answers to the following questions: 1) Does mysqldump block read and/or write operations to the database? Because in a live scenario, I would not want to block users from using the database ...
I'm using mysqldump in a cron job to backup a database with over 2 million rows. It creates a text file which can be used to restore the datalog from the command line. I thought it would be useful to edit the dump before a restore as a quick way of changing values and table or column names - at least until I learn more and become confi...
Hi my database has started to go over 2GB in backed up size, so I'm looking at options for splitting the file and then reassembling it to restore the database. I've got a series of files from doing the following backup shell file: DATE_STRING=\date +%u%a\ BACKUP_DIR=/home/myhome/backups /usr/local/mysql_versions/mysql-5.0.27/bin/mys...
I've got a problem that's causing me some headaches and I could use some ideas on how to investigate/fix it. Setup: I'm running a Rails app (Apache2 w/ Passenger) on server A (Rails.A) I'm running the master DB on a dedicated DB server B (DB.B) I'm running a slave DB on server A (DB.A) with standard MySQL replication (just for backup ...
We're having a problem where a mysqldump script is spending 90% of it's time populating a small handful of the tables it deals with. Eliminating FK's and indexes eliminates the speed problem, but is not an acceptable solution. The dump script DOES have: /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET ...
I'm trying to move a MySQL database from a Linux machine to one running Windows Vista. (I know, that seems the wrong direction, never mind why I want to do this.) Using mysqldump, I've obtained an .sql file, which I have moved to the Windows machine. What next? If I were moving to another Linux machine, one could do this sort of thin...
I do datamining and my work involves loading and unloading +1GB database dump files into MySQL. I am wondering is there any other free database engine that works better than MySQL on huge databases? is PostgreSQL better in terms of performance? I only use basic SQL commands so speed is the only factor for me to choose a database ...
how can i backup my 10mb mysql database? i dont think wampserver supports mysqldump. i want to migrate it to a webserver. ...
tmp-file contains: database_1 database_2 database_3 I want to run a command like "mysqldump DATABASE > database.sql && gzip database.sql" for each line in the above file. I've got as far as cat /tmp/database-list | xargs -L 1 mysqldump -u root -p I guess I want to know how to put the data passed to xargs in more than once (and not j...
mysqldump --compact --no-create-info -h192.168.150.180 -uroot -p live pnlbus_stops | sed s/pnlbus_stops/bus_stops/g | mysql test I am getting an error: ERROR 1062 (23000) at line 1: Duplicate entry 'AN' for key 1 This is because bus_stops table in the test DB has foreign key constraints. How do I truncate the bus_stops table from test...