mysqldump : MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

1
vote
1answer
829 views

Compare two MySQL dump files

I am trying to compare two MySQL dump files. One was made from a fresh install of a web application and the other is a dump from the same web application that has been upgraded from a previous version ...
4
votes
1answer
1k views

How can I determine the size of a dump file prior to dumping?

How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?
2
votes
1answer
512 views

MySQL backup InnoDB

I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full ...
3
votes
1answer
285 views

Backing up a 400-500 GB database on MySQL/Amazon RDS?

We have an Amazon RDS small instance for a MySQL datawarehousing database. Database size will be around 400-500 GB very shortly. What is the best way to backup our database? Can it be done ...
0
votes
2answers
616 views

How to restore mysql dump file? [closed]

We have created a dump file (.sql) using the command mysqldump in mysql5.5. Now we want to restore the same. We are using the command :- -u[username] -p[pwd] [dbname] < [.sql file] But we are not ...
0
votes
1answer
2k views

How to copy MySQL Database from Linux to Windows?

I would like to copy a MySQL database form Linux to Windows, from a little research I found this url http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html where the author says we ...
2
votes
2answers
993 views

mysqldump: flush-privileges option

I'd like to get some clarification about the --flush-privileges option for mysqldump. Here's the description of the option from the MySQL docs: --flush-privileges Send a FLUSH PRIVILEGES ...
0
votes
2answers
573 views

How to take a database dump for selective set of values in few tables?

create table foo (id, val1, user_id,...) create table bar (id, foo_id, val2, ...) create table baz (id, bar_id, val3, ...) select * from foo where user_id = 1; select * from bar where id in (select ...
1
vote
2answers
2k views

how to import mysql system “mysql” database [closed]

This question might be already answered but it's almost impossible to google it. When I perform full backup of mysql server using mysqldump --all-databases then reinstall it from scratch I want to ...
2
votes
2answers
328 views

How does mysqldump take place when there are more than one database?

I'm writing a shell script wherein I'm doing a mysqldump of all databases. My questions are: Are the tables of all databases in the server locked during the mysqldump? If I have to do an ...
4
votes
4answers
7k views

There is any option for mysqldump to ignore databases for backup in mysql

we have 40 databases in our server. we want to take 36 databases backup using mysqldump. how can i ignore remainig 4 databases in mysqldump command. there is any option for mysqldump to ignore ...
0
votes
0answers
52 views

Phpmy admin not showing my table but mysqldump

I have a db running and suddenly when I go into my phpmyadmin under the db it shows no tables and when I tried to do dump it shows me some of my tables are crash. What is best survival in this case?
0
votes
1answer
192 views

mysqldump including deleted rows [closed]

I'm trying to move a database from a Windows2003Server with mysql 5.0.27-community-nt to a new server with CenTOS. When using mysqldump on the database on the windows server, one table (i haven't ...
0
votes
1answer
2k views

Problem with (not) Locking Tables during Restore

First time here in DBA. I'm working to create a simple and strong database backup plan. My solution that is already working to create a gzipped, encrypted backup is: $string = "mysqldump -u$db_user ...
3
votes
3answers
4k views

Mysqldump tables excluding some fields

Is there a way to mysqldump a table without some fields? Let me explain: I have a MySQL database called tests. In tests I have 3 tables: USER, TOTO and TATA. I just want to mysqldump some fields of ...
1
vote
3answers
2k views

how to dump a single stored procedure from a database

I need to create a dump file that contains a single stored procedure from a database. Not all routines from that database
3
votes
2answers
5k views

How do I dump only the data for all tables using mysqldump

I have a new database with schema and primary key, FK, index constraints created for all tables. How do I dump only the data from an existing database using mysqldump, so that I can import only the ...
1
vote
2answers
514 views

MySQL: optimal configs / methods for ALTER of large MyISAM table to InnoDB (17gb+)

I have a large MySQL (5.1) MyISAM table (13gb table, 4GB indexes - 40mm+ records) I'm going to be converting this table to InnoDB in a production environment. The DB is on a cloud server with ...
3
votes
1answer
3k views

Dump of a mysql table on import replaced existing records

I took a dump using mysqldump.. mysqldump -u... -p... mydb t1 > mydb_table.sql Then I imported the dump in another database having same table, but different records.. mysql -u...-p... mydb < ...
0
votes
1answer
157 views

mysqldump not generating USE statement

I've got a small script which allows me to select a database, and a number of tables, then output them via mysqldump. Currently this does not include the USE 'db_name' statement in the output file, ...
0
votes
1answer
587 views

Proper procedure for migrating a MySQL database to another Debian machine?

I have one server running an older Debian version with MySQL 5.x and a newer Debian server, also running MySQL. I've created a backup of all databases on the first server like so: mysqldump -uuser ...
1
vote
1answer
286 views

MySQL Dump all rountines except one

We want to export a large amount of tables and routines and we've do this often. Each database, table and routine should be dumped with one exception: a procedure with a specific name. So I was ...
4
votes
2answers
9k views

How to increase fast backup and restore of 500GB database using mysqldump?

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables. Backup and restore ...
1
vote
1answer
177 views

Translating backup size to database size

I have a client who gave me a 4 gig datafile from mysqldump that I need to restore somewhere. Is there a good rule of thumb for translating the size of a backup into the size of the database once it ...
0
votes
1answer
2k views

mysql duplicate entry error 1062 when restoring backup

Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this. Requirement: To create an exact replica 'db4' of an existing DB 'db3'. Procedure ...
2
votes
1answer
405 views

MySQL Dump/Import progress on Windows

I'm going to dump a considerable big datatabase (7gb) from a MySQL 5.1 instance running on Windows into a .sql script and import it into a MySQL 5.5 instance, also on Windows (another machine). I'd ...
0
votes
1answer
256 views

mysqldump, preserve update_time and modify_time attributes as listed by show table status from

Does mysqldump preserve the create_time and update_time attributes that are output by show table status from? If not, is there an option that does this? From the manual it looks like mysqldump ...
4
votes
2answers
4k views

Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf?

If you look at the sample config such as /usr/share/mysql/my-large.cnf, you will find.. [mysqld] max_allowed_packet = 1M [mysqldump] max_allowed_packet = 16M What is the point? Because when you ...
1
vote
1answer
3k views

mysqldump not escaping single quotes properly

I use mysqldump to backup my mysql database. The problem is that the sql file generated by mysqldump doesn't escapes single quotes properly. Here is an example of the mysqldump generated sql script : ...
2
votes
1answer
813 views

Is there any risk associated with stopping mysqldump incomplete?

I started to backup 30GB table at wrong time and now I want to stop it because it is estimated to take 7.5hours. I used following command and options: mysqldump -u db -p user table --hex-blob | gzip> ...
2
votes
2answers
479 views

MySQL, import SQL dump with another database

I have an SQL dump of db1 that contains this line: "USE db1" If I import that file into db2 with: mysql -D db2 -uroot -p < /var/backups/db1.sql.gz Is there the risk that db1 will be overriden? ...
0
votes
1answer
362 views

Incomplete MYSQL Dumps

I am using MySQL dump to back up my MySQL Databases and I'm having trouble with a couple dumps on one box. Server Specs Windows 2008 MySQL 5.1.48-community I've tried various ...
2
votes
1answer
7k views

mysqldump: Error: 'Can't create/write to file

Can't create temporary files on /var/tmp. mysqldump: Error: 'Can't create/write to file '/var/tmp/mysql.k6NKGk/#sql_5dc_2.MAI' (Errcode: 2)' when trying to dump tablespaces On my OS. id mysql ...
3
votes
1answer
435 views

adding another table to existing MySQL replication scenario

I'm running a simple MySQL replication scenario MASTER ➙ BLACKHOLE ➙ SLAVE, replicating only a number of tables via replicate-do-table on BLACKHOLE and SLAVE. Now I would like to add several more ...
3
votes
3answers
2k views

How to change data structure in mysql using mysqldump without deleting files

Essentially what I'm trying to do is sync a production server with a sandbox server, but only the table structures and stored procedures. The procedures aren't any problem since they can be overriden, ...
4
votes
2answers
5k views

mysql.proc keeps crashing, cannot do a mysqldump?

Due to some problems with InnoDB, I'm going to dump all databases to a new server: mysqldump -E -R --all-databases | pv -b | mysql -u root -p -h new.server The dump process stopped with ...
5
votes
7answers
36k views

How to import a .sql file in MySQL?

I am trying to import a .sql file using MySQL Workbench and I get this error: ERROR 1046 (3D000) at line 28: No database selected I have first created an empty database called with the same name as ...
6
votes
1answer
3k views

How should I migrate a large MySQL database to RDS?

I've already looked into this a little bit. I realize there are similar questions on Stack Overflow, and Amazon themselves have a helpful document giving advice here: ...
1
vote
0answers
87 views

MySQL replication backup

How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu. What happens if, I issue or schedule ...
3
votes
3answers
4k views

how to use a different tmp dir for INTO OUTFILE select with MySQL?

I am trying to dump a large table (40GB, 600,000 rows) using a SELECT ... INTO OUTFILE query. I want to use that because I want my results sorted in a particular way (so mysqldump is not good enough). ...
1
vote
1answer
175 views

Creating dump file with use command

I am taking backup of 2 tables from different schema. I need to create the "use dbname" statement at the beginning of each table. mysqldump --databases company --tables loadme --databases new_company ...
1
vote
2answers
2k views

Database table data transfer between different servers using bcp.exe and mysqldump

I have 2 servers, both are exact clones of another, one is PRODUCTION live use and the other is strictly for backend DATAENTRY. Using MSSQL on two different servers I am using the following command ...
4
votes
3answers
4k views

Mysql backup strategies?

We have a innodb mysql database so we are lost with lots of different mysql backup strategies. Some talk about mysqldump some talk about third part tools. Some of the tables we also plan to run ...
1
vote
1answer
164 views

Access denied Error in MySQL and Drupal

We are trying to deploy a Drupal application on our production server. We see this strange error Lost connection to MySQL server at ''Access denied root@ipaddres password(yes) after deploying. We ...
1
vote
1answer
178 views

How export a database based in a primary key of a table?

So, I'm trying to export all the DB structure of my MySQL db. Attached is my DB structure, I have more then this, but I want these tables only. Basically Tools relate to Factsheets and Factsheets ...
3
votes
2answers
1k views

How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?

Because mysqldump takes forever, we decided to just move around the files. Because the 2 databases are on the same server, a cp will be fast. The problem is, from research, MyISAM is okay with being ...
7
votes
2answers
5k views

Slow MySQL Database Import with mysqldump and USE .. SOURCE

I have about 12 tables in MySQL innoDB, one of them has 11 million records in it. I used this command to back things up: mysqldump -u [USERNAME] -p [DBNAME] | gzip > [/path_to_file/DBNAME].sql.gz ...
4
votes
1answer
1k views

What's the fastest way to replicate a MySQL database from one machine to another?

What's the fastest way to replicate a MySQL database from one machine to another? The two machines are connected via LAN. How to do it without mysqldump? If using mysqldump, is the fastest way $ ...
13
votes
2answers
31k views

Safest way to perform mysqldump on a live system with active reads and writes?

I'm not sure if this is true but I remember reading if you run the following command in linux mysqldump -u username -p database_name > backup_db.sql while reads and writes are being made to a ...
1
vote
2answers
151 views

Copying a database for testing in a safe way

For testing purposes I have to create a copy of a production MySQL database that cannot be touched. These are the steps I would follow: Create a reduced size database backup: mysqldump prodDB ...