mysqldump : MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

0
votes
1answer
378 views

Is there a system wide way to change MariaDB to create tables using ROW_FORMAT = DYNAMIC

I've got a 700MB dump file from a mysql database that I was trying to undump into a new install of MariaDB and I got this error ERROR 1118 (42000) at line 7481: Row size too large (> 8126). ...
1
vote
1answer
230 views

Using a mysqldump export to create data with a different database name

I am using the following to import database using a mysqldump export: mysql -u root -p example_db < path/to/export.sql I would like to import the database to a database with a different name to ...
1
vote
1answer
183 views

MySQL import LOCK doesn't prevent PRIMARY key issues

I am using Django and only want to backup mysql data (not it's structure; Django will do that, and it will otherwise mess up Django-South's migration checks). Some info; my tables are almost all ...
3
votes
1answer
287 views

how to verify mysql backup file

Is There any tool to verify the backup taken using mysqldump without manual restoration ? I have to take full backup daily so it is difficult for me to verify the backups daily by restoring them. As ...
0
votes
1answer
717 views

Speeding up mysqldump / reload

Converting a large schema to file-per-table and I will be performing a mysqldump/reload with --all-databases. I have edited the my.cnf and changed "innod_flush_log_at_trx_commit=2" to speed up the ...
3
votes
1answer
307 views

mysqldump freezing on a specific table

I dumped a database (sys_data) which is very big (800GB, all data in one ibdata file) from a remote server. But the dump was blocked at a table (tb_trade_376). My dump command: mysqldump -uxx -pxx ...
2
votes
1answer
258 views

Table optimize command crashed MySQL server

I have a large 5GB table on one of my MySQL databases that I wanted to optimize. I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a ...
1
vote
2answers
3k views

Issue on mysqldump in MySQL5.6

I did upgrade the MySQL server from 5.5 to 5.6 I am trying to take dump for a table like this mysqldump -uroot -p dba admin > D:\admin.sql But it gets following error mysqldump: Couldn't ...
1
vote
1answer
200 views

Unable to use mysql_upgrade:

I updated mysql recently and found problem related to privileges and backup. When I am using mysql_upgrade command it says- mysqlcheck: Got error: 1064: You have an error in your SQL syntax; check ...
0
votes
1answer
1k views

passing --single-transaction in mysqldump command line tool

I'm using MySQL GUI tool to backup my innodb database and I use the option '--single-transaction' to get a consistent copy as backup. I have however, noticed that if I backup using command line ...
2
votes
1answer
773 views

Load different date formats in mysql

I have a date field in my datafile which I am trying to load in MySQL, but the date field is in dd-mon-yy format and MySQL only understands YYYY-MM-DD. Is there a way by which I can load DD-MON-YY ...
0
votes
1answer
2k views

Database user specified as a definer

I have a view in my database. problem is below Error SQL query: SELECT * FROM `lumiin_crm_prod`.`v_contact` LIMIT 1 ; MySQL said: 1449 - The user specified as a definer ('lumicrm'@'%') ...
1
vote
1answer
167 views

mysqldump error on bash script

I'm trying to run a bash script to backup my database with Cronjob. # Backup and compress each database for database in $mysql_databases do if [ "${database}" == "information_schema" ] || [ ...
1
vote
1answer
2k views

mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES

I'm trying to backup a drupal site database but I'm having some issues. When I ran the following command: mysqldump -uroot -p drupaSite > drupaSite.sql I get the following error: mysqldump: Got ...
2
votes
2answers
604 views

Tool to export data with all relational data?

Is there a tool to export data from selected rows in a table with all data stored in other tables in other tables linked by relational design? The purpose is to ease migrations of bits of data ...
0
votes
1answer
327 views

ERROR 1034 (HY000) : Wrong aligned block, while importing in mysql

I got a SQLdump from my database and now I want to import it in to an other mysql server with same version(5.5.22-0ubuntu1) in ubuntu 12.04 .but at the middle of importing, it said: ERROR 1034 ...
0
votes
2answers
3k views

importing MySQL dump to SQL Server 2012

I have a database schema from MySQL which I want to import to SQL SERVER, I have tried dumping from MySQL with compatibility for SQL Server, but with no success. Tried dumping as Excel still no dice. ...
3
votes
2answers
5k views

How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the ...
1
vote
1answer
592 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
880 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
423 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
250 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
465 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 ...
1
vote
2answers
700 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
431 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
1k 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
263 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 ...
1
vote
3answers
4k 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
169 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
1k 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 ...
1
vote
2answers
2k 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
2answers
1k 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
3k 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
394 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 ...
2
votes
1answer
1k 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
110 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
515 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
269 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
7k 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
158 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
1k 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
323 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
200 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 ...
2
votes
2answers
3k 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
2k 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
537 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
400 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
278 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 ...