mysqldump : MySQL's Client Backup Utility

learn more… | top users | synonyms

0
votes
2answers
395 views

Mysqldump confusion?

If i will lock my tables while taking dump of production and at the same time if user will enter any data for specific table. What will happen to that specific insertion ? Will it be discarded or it ...
4
votes
3answers
3k views

Does mysqldump return a status?

I am creating a script that backups a mysql db using the mysqldump utility. I am writing this script in the shell "sh". I would like to capture the output status of mysqldump in the script (i.e. if ...
2
votes
2answers
619 views

mysqldump is not dumping my data

I am running mysqldump on Ubuntu Linux (10.0.4 LTS) my mySQL version info is: mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1 I used the following command: mysql -u ...
1
vote
1answer
1k views

How do you load a database with foreign keys on the master without out breaking replication?

I have a MySQL master-slave setup. One master; One slave; Lots of databases. I'm moving an app into this setup from third party. They provided a mysqldump file of the data and database structure. It ...
7
votes
2answers
8k views

How do I create a read only MySQL user for backup purposes with mysqldump?

I'm using the automysqlbackup script to dump my mysql databases, but I want to have a read-only user to do this with so that I'm not storing my root database password in a plaintext file. I've ...
1
vote
1answer
282 views

How to backup MySQL (mysqldump) when Memcached installed?

The server OS is CentOS, with Memcached installed Before Memcached installed, I use mysqldump -u root -p --lock-tables --add-locks --disable-keys --skip-extended-insert --quick wcraze > ...
1
vote
2answers
313 views

Backing up MySQL DB wtih mixture of innodb and myisam tables

I have a large database (almost 1GB) and it has a mixture of innodb and myisam tables. Does anyone have any general tips when backing it up or more specifically the commands i should send to ...
1
vote
2answers
447 views

Best MySQL Backup Tool/Scheduler for Windows Server 2008

Recommend a good tool. It doesnt have to be packed full of features, it just needs to be reliable. Though both would be awesome.
0
votes
0answers
434 views

error exporting data using mysql workbench

i have been getting warning of version mismatch when i was trying to export data dump using mysql workbench. So, i copied mysqldump from mysql server folder and placed it in workbench folder. Now when ...
2
votes
1answer
749 views

Severe mysqldump performance degradation using Centos Linux, 8GB PAE and MySQL 5.0.77

We use MySQL 5.0.77 on CentOS 5.5 on VMWare: Linux dev.ic.soschildrensvillages.org.uk 2.6.18-194.11.4.el5PAE #1 SMP Tue Sep 21 05:48:23 EDT 2010 i686 i686 i386 GNU/Linux We have recently upgraded ...
1
vote
2answers
541 views

Migrating mysql 4 to mysql 5

This seems to me to be a common use case, so I'm surprised so little information is about it, so sorry if it's a duplicate, but I have searched. :) I'm migrating a clients website from one CMS to ...
1
vote
2answers
291 views

difference between mysql and mysqldump

I am going to importi a database.. (db.sql) I will use something like: mysql -u root -p db < db.sql Can you tell me what the difference is between this and mysqldump? Thanks
1
vote
4answers
967 views

mysqldump with single transaction option on live production servers

I am currently investigating a mysql backup solution to implement on some production servers. After reading a while, I concluded that the best solution is to implement a mysql replication using the ...
1
vote
2answers
4k views

mysqldump create database if not exist

i want to use mysqldump so that it includes the statement to create database if not exists, and drop database if exists. is this possible? what options can I use?
5
votes
3answers
3k views

Redirect output of mysqldump to scp

I worked with a linux pro years ago who did this nifty trick. He could do a mysqldump but redirect the output to an scp/ssh connection rather than writing to disk. We utilised this quiet a bit where i ...
1
vote
3answers
278 views

MySQL Backup and Recover

I have been running mysqldump on my set of MySQL databases (MyISAM and INNDOB). Recently I wanted to restore the databases to another server, and so I created the databases and imported the dump file. ...
0
votes
1answer
584 views

Import from *.sql into mysql… IBD files missing

Running ubuntu 10.10, MySQL, I imported a set of databases from a backup. The import looks OK - I can query the databases and see data in them. The strange thing is that in the /var/lib/mysql/ ...
1
vote
2answers
4k views

Error importing large MySQL dump file which includes binary BLOBs in Windows

I'm trying to import a MySQL dump file, which I got from my hosting company, into my Windows dev machine, and i'm running into problems. I'm importing this from the command line, and i'm getting a ...
2
votes
0answers
167 views

mysqldump trigger crashed tables

We had a database crash this morning starting at 1 minute past midnight (when the database backup runs). The exception emails I was getting said "Table './core/content' is marked as crashed and ...
2
votes
5answers
1k views

Backup MySQL database and gzip

I'm trying to get a little more usefulness out of my MySQL backups. I'm wanting to gzip my backup when it's completed from a cron job. Here's the script I have so far to do the backup. #!/bin/sh ...
0
votes
3answers
1k views

Making a simple MySQL backup script to be run via cron, having permissions issues

This is a very basic mysqldump script that will do a dump and store it locally. Running mysqldump by itself works fine as does gzip (i.e. they are where they need to be in my path) ...
1
vote
1answer
75 views

remotely dump & retrieve mysql db

I've just set up a new linux server to hold my MySQL DB currently running on a Win7 box. I have root access to both machines / DB. I would like to remotely (from the linux box) connect to the DB on ...
0
votes
3answers
1k views

Mysqldump Lock Table error while running script

I have written a shell script to backup mysql all databases. The script works without any issues except this error "mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when ...
1
vote
1answer
932 views

Importing UTF8 data into MySQL from a file causes truncation

I have a file called person.sql with 1 line: INSERT INTO Person (sFullname) VALUES ('Thomas Ågren'); If I run the following command from the shell: mysql --default-character-set=utf8 -uusername ...
2
votes
1answer
710 views

mysqldump of latest 1000 records

how to take mysqldump of latest 1000 records from a database
6
votes
2answers
4k views

mysqldump error LOCK TABLES

When I execute a mysqldump I get following error: mysqldump -u root -ppassword --databases information_schema > test.sql mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to ...
1
vote
1answer
676 views

importing a mysqldump file does not import triggers due to some permission problems

Whenever I try to import a mysqldump export on another server, the triggers are never created, and if I remember correctly, I get an error messages that super user permission is required. IF I remove ...
0
votes
1answer
1k views

mysql dump of talble limit 100

I want to take a dump of a database, but the condition is to have only 100 records from each table in the DB?
0
votes
7answers
948 views

How do I run SSH to my web server using PHP?

I am backing up my database using mysqldump as I read here: http://www.tizag.com/mysqlTutorial/mysqlbackup.php It gives me some sort of command(Linux I think): mysqldump --tab=/path/to/some/dir ...
1
vote
2answers
560 views

Mysqldump stopping website service during backup process

I have a website running on a MySQL database backend which is backed up daily by the following command: mysqldump -h server.com -uuser -ppass database > /home/directory/backup`date +"%Y%m%d"`.sql ...
1
vote
2answers
136 views

Recommended Dump options for InnoDB dbs?

All my databases are innodb. The databases are small enough to not need binary log files and incremental updates. So i'd like to do simple backup dumps. What options do you guys recommend for ...
0
votes
3answers
4k views

Keep getting “MySQL server has gone away” errors during command line import

I'm using the following command to import data from a 160 MB file created on another server with mysqldump. The environment I'm importing to is running the latest WAMP Server with max_allowed_packet ...
0
votes
1answer
851 views

mysql crashed and lost table descriptions “show table status” all null

Due to a air-con failure the SAN with our mysql database files crashed. When it came up there were problems with queries taking many seconds to run, further investigation revealed that the *.frm ...
1
vote
5answers
358 views

Do I need to bring my website down just in order to use mysqldump?

I have a LAMP website with mysql backend with InnoDb engine for tables I would like to be able to use mysqldump to take periodic dumps of the database - WITHOUT having to stop the mysql server (i.e. ...
1
vote
4answers
356 views

Shell MySQL Backups - What's your solution?

I'm looking for a great solution to backup my MySQL databases - all of them. I'd like each one in at least it's own file. I need FTP ability later, but not right now. Right now I just need it to ...
0
votes
1answer
477 views

Populate a remote databse using MySQL Workbench and remote .sql file

I'm using MySQL Workbench 5.2 CE, and I try to figure out how to populate or restore a mysql_dump.sql file uploaded to the root of my domain (./mysql_dump.sql). Since the file is a little bit ...
0
votes
1answer
530 views

Restoring a Mysql database that is 7zip

I use Mysqldump –u root –ppassword databasename | 7z a -si -t7z outputfile.sql.7z -mx9 To backup my Mysql 5.1 database on my windows vista system. Question: How can I restore the database without ...
1
vote
2answers
1k views

Error code 1005 (errno: 121) upon create table while restoring MySQL database from a dump

I have a linux prod machine and a Win7 64bit dev machine. My workflow includes dumping the production MySQL database on the linux machine and restoring it in my local MySQL database on the windows ...
0
votes
2answers
567 views

What could cause sudden crash of a MySQL 5.0.67 installation?

I have an old Ubuntu 8.10 32-bit with MySQL 5.0.67. There's 5.7GB of data in it and it grows by about 100MB every day. About 3 days ago, the MySQL instance begin dying suddenly and quitely (no log ...
1
vote
3answers
299 views

Is an NTBackup of a MySQL data directory reliable

This question was asked on the MySQL forums in 2004 with no answers. I'm installing MySQL 5.0.x on a Windows 2003 Server for use with Drupal. I began to configure the backup with mysqldump when it ...
0
votes
2answers
2k views

MySQL Tables Missing/Corrupt After Recreation

Yesterday I dumped my MySQL databases to an SQL file and renamed the ibdata1 file. I then recreated it and imported the SQL file and moved the new ibdata1 file to my MySQL data directory, deleting the ...
0
votes
1answer
615 views

Get an error while dumping database in MySQL

D:\>mysqldump -uuser -ppass dbname > 1.dump.sql.txt mysqldump: Got error: 1146: Table 'dbname.admin20_cache' doesn't exist when using LOCK TABLES How to make MySQL ignore the error ?
13
votes
5answers
7k views

How can I speed up a MySQL restore from a dump file?

I am restoring a 30GB database from a mysqldump file to an empty database on a new server. When running the SQL from the dump file, the restore starts very quickly and then starts to get slower and ...
2
votes
2answers
2k views

SSH + MysqlDump Remote Backup Script

I'm trying to issue a remote mysqldump command, redirect stdout to a dmp file, then tar that up. I'm a bit confused as to how to do the redirection bits over ssh: i.e. ssh user@host.com mysqldump ...
2
votes
3answers
656 views

What settings to use for fastest reloading of a MySQL backup?

I have a MySQL database which dumps to a 3.5 GB backup (mysqldump) in about 10 minutes. But reloading this backup on a standby / test server takes nearly 2 hours [originally 12 before some tuning]. ...
0
votes
2answers
2k views

MySQL dump, output each table row on a new line whilst using --extended-insert

I'm having an issue, where for ease of use, I'd like to be able to format a command line MySQL dump so that each row of a given table is on a new line when using the --extended-insert option. Usually ...
1
vote
1answer
563 views

Migrating master-slave MySQL database servers to 2 new servers, any tips or suggestions?

I'm setting up 2 new database servers that will be replacing a current master-slave setup. All boxes are running / will be running MySQL on RHEL. Our current naming conventions: db1 - master ...
6
votes
4answers
8k views

How to dump remote database without mysqldump?

I want to dump the database on my remotely hosted site in regular intervals using a shell script. Unfortunately the server is locked down pretty tight, has no mysqldump installed, binary files can't ...
2
votes
1answer
371 views

MySQL: Auto-increment value: 0 is smaller than max used value: xx

Increasingly I'm getting tables having to be repaired dwith the message returned of: Auto-increment value: 0 is smaller than max used value: xx This has happened on tables with 200 rows and tables ...
1
vote
5answers
854 views

How to backup database dumps which only minor changes between the backups?

I have a posgresql dumpfile every night and i like to keep every one of them. Each is 14 GB in size, so my backupdrive will be full soon. The difference from one day to another are only some 100MB. ...