Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
1
vote
2answers
298 views

Determine size of mysqldump prior to dumping

This question is a followup on this previous question. Q: How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump? I have tried using the most upvoted ...
0
votes
1answer
4k views

MYSQLDUMP --skip-lock-tables

--skip-lock-tables when i am taking mysqldump with this option or without this,I am not able to identify any difference. Tables are not getting locked in any of these cases. I thought mysql will ...
1
vote
1answer
347 views

Identify mysqldump activity in MySQL log file?

I have a database that is being regularly backed up by mysqldump by... something. It's not a script that I wrote, not sure whose it is. I'm trying to find out more about these dumps, such as what ...
0
votes
1answer
823 views

After migrating from MySQL 5.7.16 to MariaDB 10 database fails to start

MySQL Server Version is 5.7.16-0ubuntu0.16.04.1 (Ubuntu) I want to switch over to MariaDB, but I can't get MariaDB to accept the existing database. I also tried doing a slow shutdown with InnoDB ...
2
votes
2answers
1k views

What's the fastest way to execute a MySQL Dump of a large Database?

I currently have a rather large database that I create dumps from the our websites Live Server to my Test Server (and vice versa). Creating the dump is fairly quick (takes about 30 seconds). However, ...
1
vote
1answer
663 views

Is it necessary to delete a database before restoring the backup in MySQL?

As a rule, I always drop the database, and then create a new one and only after that restore a dump file. But I'm wondering, is it necessary to do? For example, if I lose some data and begin to ...
1
vote
1answer
3k views

In phpMyAdmin, how can I execute mysqldump query?

I wanted to execute mysqldump -u username -p –all-databases > dump.sql However, I can't see the appropriate field in PHPMYADMIN. Where I can do that? SQL error
0
votes
2answers
3k views

Can't read password from config using --defaults-extra-file in MySQL

I am trying to backup all databases automatically using a batch script. The password is read from a config file called config.cnf. Below is the batch script: @echo off C:\MySQL\bin\mysqldump.exe --...
0
votes
2answers
196 views

Performing frequent backups of a large MySQL slave

We replicate our production MySQL databases (120 GB on production) to a dedicated local desktop machine. The local slave is a 2014-era machine with 8 GB of RAM. A full mysqldump of the databases on ...
0
votes
1answer
77 views

Constant MysQL crashes. How to fix?

MysQL is frequently going down. Error logs contain: [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it | 149 occurrences [ERROR] Native table 'performance_schema'....
0
votes
2answers
1k views

MySQL Unique Key Limit

I have been given a database dump that I am trying to rebuild. When I import I get the following error: Too many key parts specified; max 16 parts allowed There are indeed more than 16 key parts in ...
1
vote
2answers
388 views

Large MYSQL DB on single file

I have a large MYSQL innodb database (115GB) running on single file mode in MySQL server. I NEED to move this to file per table mode to allow me to optimize and reduce the overall DB size. Im ...
1
vote
2answers
2k views

MySQL crash/restart during mysqldump

General: Standalone MySQL-5.5.43 in OpenVZ on ProxMox-3.4-6/102d4547 (Debian, 2.6.32-39-pve). Container uses 18Gb of RAM and 100Gb of disk. This container runs this service more than a year. There's ...
1
vote
1answer
2k views

Fastest way to do a mysqldump restore on Amazon RDS?

We need to restore a database from a 7-Gb mysqldump file to a RDS Amazon instance. The full restore takes between 3 and 4 hours (probably because of network latency since the dump has to reside on a ...
0
votes
2answers
502 views

Galera backup failing with error “mysqldump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table”

We face the same issue like this posting on dba.stackexchange.com Deadlock from mysqldump --single-transaction, is it possible?. We offer DBaaS and can't control the DDL statements of our customers. ...
0
votes
0answers
152 views

Reasonable times for a mysqldump restore?

NOTE: question edited to take into account restore over LAN vs. local restore. Are there benchmarks available for the time spent doing a database restore from a mysqldump file? Based on my ...
1
vote
2answers
540 views

Backing up Master-Master replicated MySQL databases

I have two MySQL 5.5 databases which replicate each other. What's the best way to back these machines up? I would like to prioritise speed/ease of restoration. Should I add extra slaves to both ...
4
votes
1answer
7k views

MySQL settings useful to speed up a mysqldump import

Recently I had to import a 7 Gb MySQL dump file to a MySQL 5.6 server. The import took around 7 hours on a mono-core CPU with 1 Gb of RAM. Someone else tested the import on a MySQL server which has, ...
1
vote
2answers
510 views

How to transfer a few databases from MariaDB master to another master

I must transfer databases (everything without system's databases) from one replication server to another replication server. Everything is on MariaDB (10.0.20) and i need to do it with minimal ...
0
votes
3answers
2k views

mysqldump: ERROR 2006: MySQL server has gone away when attempting to push data to RDS instance

I'm attempting to migrate a MySQL database (10M+ records) from an EC2 instance into an RDS Instance using a script: #!/bin/bash mysqldump --databases my_db \ --compress \ --order-by-primary \...
3
votes
2answers
1k views

Why MySQL Database is Bigger After Dump/Import?

I have 2 VPS and plan to migrate the DB from first to second. The original DB is taking around 15GB of storage and I manage to dump out a 7GB SQL. However, when I imported the SQL to second VPS, it ...
5
votes
3answers
2k views

Non-Blocking MySQL Backup

I'm running a MySQL server with a couple of customer databases, some of which are quite large. We are doing a complete mysql dump once a day. It takes about half an hour to complete. During this time,...
1
vote
2answers
313 views

MySQL Round Trip import and export not the same

I'm setting up a development environment for a content management system with a database backend. The source for the dev environment is a virtual machine snapshot from production the target ...
0
votes
0answers
94 views

Does mysqlnd expose PDO connection data to process/log files?

So I was trying to figure out how to write an automated backup script for php on a shared webhost to execute using mysqldump, with no root access to core php.ini or mysql config on the remote database ...
0
votes
1answer
61 views

Mysqldump backup size issue - 5.6

I'm using MySQL 5.6 with about 5 databases on linux. I have scheduled daily full backups using mysqldump. All databases use InnoDB engine. What I observe is that the backup size seems to vary ...
2
votes
1answer
693 views

Using mysqldump to initialize slave database

I've facing an issue I don't really understand. I wan't to synchronize a slave MariaDB database to a master one. I try to use the mysqldump option --master-data=1 which is adding a "CHANGE MASTER TO"...
1
vote
1answer
2k views

How to search in blob binary wise, and how to export data binary wise

I've a table with a blob field for each row. The blob contains binary data, which can be represented as bytes or text ( using mysql workbench - open value in viewer) Does anybody know how can I ...
0
votes
2answers
449 views

Mysqldump failing while restoring database - MySQL 5.6

I have dumped one of my databases of size 5-6GB. I'm trying to restore to another instance but the same is getting failed after sometime with the following error: ERROR 2013 (HY000) at line 12006: ...
1
vote
2answers
702 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
3answers
310 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 --...
1
vote
1answer
153 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 ...
2
votes
1answer
1k views

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

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 ...
1
vote
1answer
63 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 ...
2
votes
1answer
494 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 ...
5
votes
4answers
5k 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 ...
1
vote
1answer
461 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 ...
1
vote
1answer
876 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....
1
vote
1answer
893 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 ...
0
votes
1answer
144 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 ...
1
vote
1answer
129 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 ...
2
votes
0answers
2k 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 ...
2
votes
1answer
3k 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 ...
3
votes
2answers
2k 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
4k 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
824 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 ...
1
vote
2answers
336 views

mysql - merge two large instances to one server (40GB of binaries) [closed]

I'm need to merge two mysql instances (both mysql V5.6) onto one server. The binaries of 12 databases are approx. 40GB. I read other posts about mysqldump but as I've previously had many issues ...
3
votes
1answer
3k 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
1answer
51 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
1k 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[...
3
votes
2answers
1k 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 ...