mysqldump : MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

1
vote
1answer
2k views

MySQL - MySqlDump uses \n instead of null

Using Windows with MySQL 5.1. I am running a script that uses mysqldump to backup a database. The mysqldump syntax used is: mysqldump --user=root --port=3306 --password=topsecret some_database ...
1
vote
1answer
3k views

Write lock during mysqldump even using --lock-tables=false

When I try to dump an online database using --lock-tables=false, others cannot update the table. Are there any methods to solve this? As I am using MyISAM, does --single-transaction for InnoDB help? ...
1
vote
1answer
4k views

ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full

There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
1
vote
0answers
98 views

How do I restore a mysql dump that was created in xml format?

I have a rather big XML formatted MySQL dump It was created with the --xml param (-X) param of mysqldump. I need to import it in. This article from 2010 indicates that there was no "built in" way ...
1
vote
2answers
54 views

ALTER DATABASE script preventing restore of MySQL database in another server

While backing up the database with mysqldump --routines options, I see ALTER DATABASE in the middle of the output dump, near the routines. /*!50003 DROP FUNCTION IF EXISTS `f_my_function` */; ALTER ...
1
vote
1answer
21 views

I really can't get rid of these conditional statements in mysqldump?

My mysqldump DDL file is filled with tons of this: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `my_trigger`... I've searched to no avail for an option to silence this. ...
1
vote
0answers
26 views

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect [duplicate]

I am troubleshooting a backup script (bash), and I have limited working knowledge of the current configuration since the last 3 predecessors did not leave any documentation. I do know that my server ...
1
vote
0answers
141 views

To configure MySQL Enterprise Workbench for Incremental Backup with Online Backup option

Could not acquire management access for administration RuntimeError:Could not initialize WMI interface: Workbench.wmiOpenSession():Could not connect to target machine. When i am trying to configure ...
1
vote
0answers
158 views

Are ZRM (Zmanda Recovery Manager) incremental backups reliable?

I had an issue while restoring incremental backups and I'm wondering if they are reliable. I'm making weekly full backups and daily incremental backups and I needed to restore a single database after ...
1
vote
0answers
137 views

mysqldump with --single-transaction seems to be “fixing” InnoDB tables with page corruption - why?

I'm trying to find out what exactly is happening here. I've been intentionally corrupting index pages in InnoDB tables, changing bytes in user record areas that actually contain information (largest ...
1
vote
2answers
163 views

Using mysqldump on RDS to export a large file in TSV format

I would like to export a single, large table (30GB) from an RDS MySQL server on a regular basis into a TSV file, so that we can load the file into Vertica. However, RDS doesn't provide a way to use ...
1
vote
1answer
282 views

Options to include with mysqldump when taking backup of hybrid databases

I'm planning on taking backups of all MySQL databases on a remote production server. The combined database size is around 2 GB. Most of the tables are InnoDB ~1.8 GB and the rest are MyISAM. Now I've ...
1
vote
0answers
582 views

MySQL dump error (#1005 - Can't create table 'novephp_cz_bv.jos_jquarks_customsets' (errno: 150))

I´m trying dump mysql database from MySQL 5.1. to MySQL 5.5. But I´m getting an error "#1005 - Can't create table 'novephp_cz_bv.jos_jquarks_customsets' (errno: 150)". Problematic part of script is ...
1
vote
1answer
33 views

mysqldump: dump several tables from db. what will be locked?

I have active database with dozens of tables. Among them several are unused (no queries/insertions are performing over them for a while). I want to dump that inactive tables. I would suggest that it ...
1
vote
1answer
309 views

Make MySQL dump from parts of each table

I need to export some data from different tables. I tried this query select * into outfile 'C:\Users\\User\\Desktop\\ololo.sql' from db.users where id % 2 = 1; After this query, the SQL file was ...
1
vote
1answer
3k views

Extremely slow mySql restores

Please bear with me as I ask this question, which looks similar to questions already on DBA@SE. In the current configuration, we have a server (a Linux quadcore box with 4 GB RAM) - Both the Database ...
1
vote
0answers
238 views

restore using mysqlbinlog failing

While doing restore from a MySQL binary log file, it is failing with the following error . mysqlbinlog mysql-bin.000036 | mysql -u root -p asdb ERROR 1032 (HY000) at line 48: Can't find record in ...
1
vote
0answers
93 views

mysqldump issues with DreamHost

Background: I sought to rename a database on Dreamhost web hosting VPS account. I also wanted to rename a database user for the database to be created above. I followed the approached detailed at ...
1
vote
0answers
607 views

How do I safely import MySQL data in a directory to a running MariaDB server?

We're integrating another office into ours. That includes some of their data. I have a new installed MariaDB 5.5 server. The data dir = /home/data/MariaDB. I've done a new/fresh install into that ...
1
vote
2answers
646 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 ...
1
vote
0answers
88 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 ...
1
vote
1answer
3k views

Repair with keycache in mysql runs EVERY time. Can't even log why it's happening

I have a MyISAM table w/ ~1million rows and two indexes - one's a three-column key, and the other is a spatial index for bounded SELECT statements on a separate point column. My insert statements ...
1
vote
3answers
5k views

What is the best way to recover from a mysql replication fail?

Today, the replication between our master mysql db server and the two replication servers dropped. I have a procedure here which was written a long time ago and i'm not sure it's the fastest method to ...
0
votes
1answer
343 views

Why does importing a MySQL dump cause so many I/O?

I imported 100 SQL-format dump files. The total size was 20 GB. The resulting .ibd files after the import had a total size of 40 GB. mysqld.exe wrote 1.1 TB and read 120 GB on the disk. Why so many ...
0
votes
2answers
869 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 ...
0
votes
2answers
56 views

Fixing MyISAM database without losing data

My old server was crashed but I was able to recover the databases from MySQL through testdrive. I'm trying to dump a database using mysqldump but it gives error for certain tables are crashed and ...
0
votes
1answer
44 views

How can i make dump of big mysql table? [closed]

I have server with db(36Gb data), only mysql access. When i'm trying to use mysqldump utility, i get connection timeout(or something like this). I doesn't have ssh access for copy /var/mysql/data. ...
0
votes
1answer
55 views

Point in time forward MySQL Dump

I have a MySQL table that stores a list of bookings that's made online. I need to have these bookings dumped to a CSV file which will then be picked up by a client server application through a SSH ...
0
votes
1answer
806 views

“Display width out of range for column” error when importing mysql 5.1 dump into 5.5

I am migrating to a new server, and trying to move my database from a 5.1 to 5.5 server. I created a dump with mysqldump -u root -p --add-drop-database --triggers dbname > dbname.sql When I ...
0
votes
1answer
8k 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'@'%') ...
0
votes
1answer
3k 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 ...
0
votes
1answer
256 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
3k 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 ...
0
votes
2answers
29 views

Consistent mysqldump of mixed InnoDB and MyISAM tables from RDS instance

I have MySQL db on dedicated server. I would like it to be an external master for an RDS instance. And I want to make backups of RDS. (Currently we have mix of both InnoDB and MyISAM but we are slowly ...
0
votes
1answer
40 views

Copying MySQL schema, some tables not getting recognized!

From the 'data' folder of my colleague I copied the entire 'MyDatabase' folder and pasted that in my own 'data' folder.Now, some tables are getting recognized while others are not.**All the tables are ...
0
votes
1answer
31 views

Why does mysqldump require drop command?

I am trying to backup a large table and after reading various other answers on this site have tried mysqldump --user=user --password=password dbname1 tablename | mysql --user=user --password=password ...
0
votes
2answers
52 views

understanding mysqldump auto-backup performance issues and inner functionality

Is there a performance issue, if I run a cronjob in background with mysqldump --single-transaction with a 60 seconds interval? Will my original database keep functioning without an issue while the ...
0
votes
1answer
56 views

Backup a huge database 200gb+

i have a mysql database where i need to take a backup of the log database. the problem is the log database i over 200gb and contains over 280 millions of rows, i need right now to remove a kind of ...
0
votes
1answer
66 views

How to run a mysqldump from a .txt or .sql file inside a command line window?

If I run the follow line directly in command line window it will works good. C:\xampp\mysql\bin>mysqldump -u root d3bi>C:\Users\me\Desktop\test\testDump.sql How can I run a .txt or .sql file ...
0
votes
1answer
15 views

Import lots of mysqldumps with missing CREATE DATABASE

I have a bunch of mysqldumps of my old server with one database each. Unfortunately we forgot to add CREATE DATABASE ... in all those dumps. How can I import those files in one batch?
0
votes
1answer
38 views

Mysql events table

I am trying to create a script to backup all mysql databases. This is the command that I am trying : sudo mysqldump -u user -ppassword --all-databases > /tmp/all-database.sql And this is the ...
0
votes
1answer
29 views

mysqldmup with data only

Is there a way to backup data using mysqldump --no-create-info ... where the inserts in the backup file will look like this: insert into t1(col1, col2)values(val1,val2); instead of insert into t1 ...
0
votes
1answer
21 views

how do i print a record coming from joinig query as follows in mysql [closed]

tbl_A --------------- | a_id | a_name | |------|--------| | 1 | john | | 2 | paul | | 3 | joy | --------------- tbl_B --------------- | a_id | b_name | |------|--------| | 1 | ...
0
votes
1answer
96 views

Copy database from one mysql sever to another mysql server

Following are the details of the database and server: Server1: Host Name: abc Port Num: 01 database: test Server2: Host Name: xyz Port Num 02 database: spareparts userid: spts password: abcdefgh@123 ...
0
votes
3answers
439 views

How to upload 1.5GB sql file on mysql?

Whenever I tried to import file in MySql on my Linux Mint system, "You probably tried to upload a file that is too large. Please refer to documentation for a workaround for this limit." this message ...
0
votes
1answer
1k views

mysql dump a table to remote host [closed]

i want to dump a table from localhost host (with public ip), here is the command so far, the command will be run on localhost mysqldump --opt --host localhost --user=username --password=password ...
0
votes
1answer
144 views

Exporting Very Large Database from mysql 5.5

Before I proceed, I would like to confirm can I export a very large table of 3.6GB size using the MySQL Admin Export feature. I have exported tables of small size like 28 MB but just wondering ...
0
votes
1answer
76 views

Sync MySQL 4.0 table to MySQL 5.6 server every few minutes

I am tasked with getting a copy of a table on a MySQL 4.0 server synced to a completely different database on a MySQL 5.6 server every few minutes for the purposes of having that table available ...
0
votes
1answer
133 views

want to download 3GB database table from my development mysql server [duplicate]

I need to download database structure with data. Total size of the DB is more than 3GB. If i use phpmyadmin it is not working. I have also tried by increasing the execution time in php.ini. I need ...
0
votes
1answer
928 views

Is it safe to use mysqldump to backup and restore a master-master cluster

Not sure if the specifics matter but just to get it out of the way I am talking about a Galera cluster on XtraDB (MariaDB). I have a master-master setup and am interested in backing up and restoring ...