MySQL's Standard Dump/Backup Utility

learn more… | top users | synonyms

0
votes
1answer
137 views

mysql database migration of schemas with updated data and structures

I am a newbie with MySQL database dumps and am currently trying to work with data migration between a source schema and a target schema. Assuming the source schema is defined by (...
2
votes
1answer
467 views

mysqldump vs mysqlpump

I just heard about mysqlpump - it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? ...
0
votes
1answer
122 views

MySQL database dump bombs out Error 1064 when using LOCK TABLES

This specific machine is running MySQL version 5.0.27. I have tried to get a full dump of this database several times and each time it bombs out. Tried getting just a structure dump of it and this is ...
0
votes
2answers
35 views

Checksum of data imported from mysqldump not equal to the original checksum?

I'm tring to create a mysql replication instance. So I mysqldump --complete-insert --skip-dump-date -uuser -ppass db table > master.sql. Also I've imported the master.sql to the slave instance ...
0
votes
0answers
16 views

Error with grants when migrated to NDB cluster

we are trying to migrate the data from mysql 5.1 to mysql 5.6 NDB cluster 7.4.x. 1. we migrated the data using some scripts and restored the grants but the users are not able to login, We checked ...
3
votes
3answers
3k views

mysqldump doesn't restore database. why not?

Why doesn't my restore operation work? I created a dumpfile for a database using: mysqldump -u root -p databasename > /home/databasename_bkup.sql I then opened the dumfile and confirmed ...
1
vote
1answer
720 views

Setting MySQLDump to keep running in the background after session times out

I'm tryng to set up a new Slave DB by copying another Slave DB. Normally I would simply use MySQLDump to dump the Slave and create a .sql file, then import that into the new database. However, as our ...
1
vote
1answer
16 views

Grabbing SQL Dump of Master DB Without Downtime

I'm curious whether downtime will be necessary to grab a SQL dump of my master database. Right now, I'm in the process of rebuilding my one slave. There is actually only one database from master that ...
0
votes
3answers
33 views

How to take backup of a database in mysql?

For Back up I have used the following commands. mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql mysqldump -u [username] -p[root_password] --single-transaction --routines --...
0
votes
1answer
259 views

error 2013 (hy000) lost connection to mysql server during query while load of mysqldump

I'm trying to load mysqldump and I keep getting following error: ERROR 2013 (HY000) at line X: Lost connection to MySQL server during query /etc/my.cnf: [mysqld] max_allowed_packet = 16M ...
1
vote
1answer
4k 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 ...
0
votes
1answer
778 views

Operation failed with exitcode 1 - while importing from a dump folder

I'm new to dbs and I'm trying to learn how a certain db works. They gave me a dump folder to play with it but, after setting up a connection (works), while trying to import, I get this annoying ...
2
votes
1answer
40 views

Trying to restore SQL file from mysqldump, results in syntax error

I ran the following sqldump call in a MySQL 5.7 instance (Windows OS): mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=1 --routines -A > ~/dump.sql I ...
0
votes
1answer
364 views

Migrating/Upgrading a MySQL 5.1 schema to MySQL 5.7 schema

I'm bit green on MySQL upgrades, but my question has to do with single schemas (dbs). I have some hosted databases on a server using MySQL 5.1 Community, and I currently use mysqldump to back those ...
0
votes
1answer
478 views

NDB Cluster seems to ignore foreign_key_checks=OFF

According to MySQL documentation on the local foreign_key_checks variable Beginning with MySQL Cluster NDB 7.3.2, setting this variable has the same effect on NDB tables as it does for InnoDB ...
1
vote
1answer
24 views

Changing number of rows when importing table [duplicate]

I'm currently importing a table with about 17 million rows in MySQL. What catches my attention is, the number of row seems to be fluctuate. I used the command show table status while my importing ...
3
votes
4answers
85 views

Is mysqldump a “hot” backup kind?

I see in the documentation that the database backup tools are divided into four categories: hot backups, cold backups, physical backups and logical backups. I understand the the most important ...
0
votes
2answers
101 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 ...
3
votes
1answer
31 views

Issue with running multiple mysql commands in a bash for loop

I try to restore some .sql backups using a shell script with a for loop. I run by bash command.sh 'password' using the command.sh below. However, when I typed it out, it didn't run and instead, mysql ...
1
vote
1answer
48 views

How to restore MySQL databases from database files?

Our MySQL on Ubuntu 15.04 stopped working after we moved the computer to a new location and kept giving the missing socket file error. We couldn't really fix it after trying sudo apt-get install -f ...
5
votes
4answers
3k views

Exporting/Importing a large MySQL database without mysqldump

I'm attempting to export about 150GB of data from a database with hundreds of tables, and have found mysqldump to be very buggy. After running a simple --all-databases dump succeeds, but the import ...
1
vote
2answers
452 views

Recommendation about mysqldump?

I have approx 90 GB sized database, and I need to schedule a mysqldump backup. How long will it take to perform a full hot backup approximately? I'm asking because I've never had to backup a database ...
1
vote
3answers
305 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
33 views

Sync two mysql database one in intranet and the other on internet

I'm making the training and placement module for my college. But I'm facing problems to sync the databases, one that will be used by the students and the other that will be used by the HR of a company....
3
votes
2answers
6k views

How do I show warnings when loading data created from mysqldump?

I've got a large .sql file with large insert into ... values ... statements. Many of these statements are generating warnings during execution. How can I get mysql to print the warnings? If it hit ...
1
vote
1answer
372 views

Slave Waiting to reconnect

I have added new slave host to master for replication, but missing some of the parameters. mysql> show slave status\G *************************** 1. row *************************** ...
1
vote
2answers
190 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
168 views

MySQL Workbench can either Dump or Restore - How Do I Configure To Do Both?

I have previously posted this question on Stack Overflow, but have not solved the problem. Hopefully it is not incorrect to post it here in the hope that it will be seen by a some different ...
30
votes
8answers
119k views

MySQL DB import/export command line in Windows

How to export / import a database in MySQL through the command line? MySQL system configuration, WampServer server installed. OS: Windows The advice for my local host, not an live hosting...
4
votes
1answer
8k views

Problem with View When Restoring a MySQL Backup

I have a database (InnoDB) with some tables, views and routines... Periodically I do a backup using the command line: mysqldump -u user -ppassword --routines db_name > backup.sql But I have a ...
0
votes
0answers
57 views

mysqldump: Got error: 1049: “Unknown database 'xx'” when selecting the database - because database deleted during backup? DBaaS

I receive this error during dump (runs a few hours, because 31 GB data files) mysqldump: Got error: 1049: "Unknown database 'ZETF_353D082E_422C_47C4_905F_FF961EEA29B8'" when selecting the database ...
1
vote
3answers
4k 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 ...
1
vote
0answers
16 views

Howto restore Trigger filesystem copy (*.TRG and *.TRN files) in addition to mysqldump?

We use mysqldump for backup (without Trigger/Events/Routines included). mysqldump --opt --flush-logs --single-transaction --ignore-table=mysql.event --all-databases The triggers we copy with cp to ...
1
vote
0answers
96 views

SQL Error while restoring big database in Mysql

I have successfully created a MYSQL dump on RHEL 6 machine. The dump file is having 40 GB. When I try to restore it in an UBUNTU machine I got this error.
5
votes
2answers
6k views

mysqldump Error 2013

I have a database installed, that I would like to backup in mysql. The problem is mysqldump fails on exporting the 'maia_mail' table # mysqldump -u root -p maia > maia.sql mysqldump: Error 2013:...
0
votes
1answer
18 views

MySql Moving from development to production into a different user

I have a project that need to be moved to production (for the first time). The database includes few tens of tables and procedures/functions. They were all created under the ROOT user (yes, bad ...
0
votes
1answer
785 views

Cannot locally import UTF-8 encoded SQL database on Mac

I was importing a confirmed UTF-8 encoded SQL database into Sequel Pro on Mac and got thrown this error halfway through: An error occurred when reading the file, as it could not be read in the ...
1
vote
1answer
36 views

ALTER TABLE engine from MyISAM to InnoDB: why does mysqldump --order-by-primary help me?

I found an interesting mysqldump option in MySQL 5.7 Reference Manual. In internet there are no further details and explanations. --order-by-primary Dump each table's rows sorted by its primary ...
1
vote
0answers
40 views

mysqldump show Table doesn´t exist when doing LOCK TABLES

Our server had power outage and after start some of mysql tables was inaccessible. I tried many things but nothing helped and currently i am at freeze point. I used command : mysqldump database ...
1
vote
1answer
40 views

Restoring a mysql database using --single-transaction

I learned that specifying "--single-transaction" (yes we do have InnoDB) option into the mysqldump tool produces a dump utilizing transactions. However, I am failing to find a way to restore the ...
9
votes
2answers
12k views

Best practices for backing up a MySQL DB

I've recently discovered that our production web servers that run off MySQL are not being backed up regularly (or at all). I'm used to backing up SQL Server DB's but don't have a ton of experience ...
0
votes
0answers
36 views

fix `mysqldump: Error 2013: Lost connection` on “cloud” server

I have 6 months of backups left to perform and a single day can take up 200MB in raw mysqldump --extended-insert=false --complete-insert form. If I try to dump more than a day worth of data I get ...
1
vote
1answer
55 views

mysqldump with LIMIT and --order-by-primary

I want to use mysqldump to retrieve X first rows from a database (where "first" are ordered by primary key). I'm trying to use mysqldump the following way: mysqldump [connection-info] -where"1=1 ...
1
vote
1answer
87 views

InnoDB files created even innodb_file_per_table is OFF in mysql-5.7

We have installed mysql 5.7 in ubuntu 16.04 and we got big surprise because databases are being stored individually in each folder even parameter innodb_file_per_table is 0. We decided to change ...
0
votes
1answer
88 views

How to make mysqldump use /*!40101 SET character_set_client = utf8mf4 */;?

When I use mysqldump to export mysql database, it always produce a dump.sql containing ...some other things... /*!40101 SET character_set_client = utf8*/; ...some other things... This is the ...
0
votes
0answers
41 views

Table data is not getting dumped

This is just for test purpose. I'm taking backup (INNODB DB) with --single-transaction and from another terminal I'm running alter table on one of the table of same DB on which dump command is running....
0
votes
1answer
48 views

error 1146 when importing mysql DB

I'm trying tu dump and re-import a database, I'm using the following bash script to export (I have reasons to export each table separately) for x in `mysql --skip-column-names -h [host] -u [user] -p[...
40
votes
3answers
47k views

Why does InnoDB store all databases in one file?

It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
0
votes
0answers
114 views

mysqldbcompare SELECT command denied to user

I am trying to run mysqldbcompare to see the difference between two tables: mysqldbcompare --server1=server1:2016@localhost --server2=server2:2016@localhost user:user --difftype=sql --run-all-tests ...
14
votes
7answers
93k 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 ...