Skip to main content
The 2024 Developer Survey results are live! See the results

Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
0 votes
0 answers
6 views

Generating MySQL Dump via Instance started from AMI

I have a live ec2 server instance running with MySQL installed locally (not using RDS), and another backup server that runs MySQL replication as a slave to the live server. I am now trying to generate ...
Patrick Teng's user avatar
0 votes
1 answer
13 views

Using mysqldump on remote server in rsnapshot returns code 127 while the very same command used locally works fine

As said in the title, I want to backup my remote mysql database with rsnapshot, using mysqldump. I don't have any syntax error, but get a "WARNING 'command" returned 127", and the sql ...
Bernard Grosperrin's user avatar
3 votes
2 answers
272 views

Why does MySQL generate a `key` statement in DDL for composite primary keys?

I am using MySQL version 8.3.0. I can create a “many to many” relationship between persons and teams tables with the following SQL statements: create table persons ( id bigint not null, last_name ...
FloT's user avatar
  • 356
1 vote
1 answer
45 views

how to verify mysql backup file using BlackHole

I'm interested in trying this solution: https://dba.stackexchange.com/a/44893/71052, also referenced here: https://dba.stackexchange.com/a/206151/71052 The problem I face is the following: mysqldump ...
fpierrat's user avatar
  • 131
3 votes
1 answer
691 views

Does mysqldump lock entire database or just a table by table?

I'm wondering if during a mysqldump executed in the following manner mysqldump --all-databases --master-data > dump.sql all tables are locked for the entire process, or just a table by table? In ...
0xC0DEGURU's user avatar
0 votes
0 answers
191 views

Probably missing something simple, source of ERROR 1146 (42S02): Table doesn't exist error

Looking to import a mysqldump .sql file into a new db. Have tried both via command line and directly within a mysql session. The following call gives the assocaited error: chris@chris-X1C6:/media/...
Chris's user avatar
  • 191
0 votes
1 answer
44 views

Can't export MariaDB large table by mysqldump when row_format is compressed

I have a MariaDB 10.2.13 server. This server contains a database called 'factory'. This database contains a table called 'client'. This is a large table. client table structure: CREATE TABLE `client` (...
user3637971's user avatar
0 votes
1 answer
106 views

mysqldump does not dump my schema or data

mysqldump is giving me a file that includes no schema or data. In researching this, I see there has been a change in MySQL and I must either use the --no-tablespaces flag, or I need to grant a ...
arnoldbird's user avatar
0 votes
0 answers
174 views

FIX: mysqldump: Couldn't execute '/*!80000 SET SESSION information_schema_stats_expiry=0 */

I've already asked this question in the "Stack overflow" website. I don't understand why they closed my question even if I didn't get a working solution for my setup. Why did I submit it? I'...
Edo Meloni's user avatar
0 votes
0 answers
11 views

Quicker & automated way to replicate mysql from master to slave

I want to replicate around 60 databases from master to slave server. I've read this guide https://dev.mysql.com/blog-archive/get-mysql-replication-up-and-running-in-5-minutes/ and few other. It's ...
Raymond's user avatar
  • 101
0 votes
1 answer
126 views

MariaDB restore error - ERROR 1556 (HY000) at line 2562: You can't use locks with log tables

MariaDB Restore fails mysql --host mydbhost --port 5500 --user root --password < /var/tmp/dump.sql Enter password: ERROR 1556 (HY000) at line 2562: You can't use locks with log tables Dump ...
fizwit's user avatar
  • 11
0 votes
2 answers
47 views

What sql statements is mysqldump executing and how does it affect database performance?

I noticed a SELECT * FROM query running on a big table from time to time and i wonder if this could be, because of mysqldump. We have mysqldump backups running for some tables. Does somebody know what ...
Lukas Räpple's user avatar
0 votes
0 answers
184 views

mysqldump on replica for backup and GTID

I use replica instead source database for backups. After enabling GTID mode I am not sure how I should use mysqldump to make correct dump. This backup is for source recovery of course. mysqldump --...
deem's user avatar
  • 113
0 votes
0 answers
115 views

Getting duplicated primary key entry in mysqldump file

We have a mysql 5.1.73 server and a myisam table (we're migrating to later version of mysql and innodb as part of this eventually). We do a mysqldump every night of a table. Basic table structure is ...
Ian's user avatar
  • 135
1 vote
1 answer
332 views

Restoring MySQL Database from mysqldump file produces seemingly random syntax errors

Sorry for the long title I wasn't sure how else to make it distinct from the auto-search results. I have a large-ish dump file (~44GB) and I'm trying to import this into MySQL on a different server. ...
DSZ's user avatar
  • 127
0 votes
1 answer
204 views

dump data from an encrypted database

Hi I have a mysql server which is using mysql kering plugin to encrypt data. I want to dump a database with mysqlump from first mysql and import it to another mysql server which does not have ...
Amir Almian's user avatar
1 vote
0 answers
48 views

Slow database performance after mysqldump backup [closed]

we are using mysql 5.6-91 (percona) as the backend database. Everyday morning mysqldump is used to back up the entire database and upload it to s3. The database size is around 70GB. The issue we are ...
Shreehari's user avatar
  • 119
0 votes
1 answer
48 views

How can I restore 2.5TB mysql dump into AWS RDS MySQL in just few hours?

I want to restore the 2.5TB mysql dump into AWS RDS MySQL. Data is without create info and destination tables have only two columns. Can anyone suggest the best strategy to restore the dump ...
Prince singla's user avatar
5 votes
3 answers
251 views

Remove redundant disable/enable key statements from mysqldump

A standard mysqldump will create a table, disable keys, insert the data, and re-enable keys. There are reasons for this. However, when a table is empty, the keys are still disabled and re-enabled. ...
TRiG's user avatar
  • 221
0 votes
1 answer
279 views

While restoring from mysqldump file, how do I write the table names to a log file?

Sometimes I've run into situations where developers have changed structures, added/removed columns on different DB servers, environments, etc for the same table and this causes errors such as ERROR ...
site80443's user avatar
  • 119
0 votes
1 answer
503 views

Incomplete backup with mysqldump

I need to backup sql db around 39GB. When I'm doing backup without single-transaction option - everything is ok, I got full dump of my DB. But when I try to add single-transaction option, I got ...
zeronight's user avatar
1 vote
0 answers
413 views

Mariadb large live database dump/restore fails when using recommended options

I have a 58Gb database that is live, using InnoDB engine and I want to dump restore it to a development machine for test purposes. I'm using mariadb 10.4 client and servers on Debian 11 Buster. I've ...
Eamonn Kenny's user avatar
1 vote
1 answer
513 views

MySQL restore of "mysql" database fails with error 3723

I have a backup process that once a day dumps all the mysql databases on one server (with mysqldump), rsyncs them to another, and then they are imported there (one at a time). I did it this way to ...
Giovanni Gigante's user avatar
0 votes
0 answers
291 views

Mysqldump geometry field error

I have MySQL 8 database with some tables that have some column of type GEOMETRY. When I back up the table with mysqldump, it outputs my geometry column as _binary '\æ\0\0\... and when I try to ...
adam78's user avatar
  • 155
1 vote
1 answer
3k views

mysqldump: Error: Index for table is corrupt; try to repair it when dumping table - does this mean repair was done?

I ran into the same database trouble that another user described here and I managed to get mysqld started by setting innodb_force_recovery = 6, which allowed me to perform a backup using mysqldump -u&...
Robert K S's user avatar
0 votes
1 answer
224 views

How to backup a replica Using mysqldump?

From MySQL Documents, and This answer they said: When using mysqldump, you should stop replication on the replica before starting the dump process to ensure that the dump contains a consistent set of ...
Niyaz's user avatar
  • 218
1 vote
1 answer
162 views

Migrate from MySQL 5.6.33 to 5.7.41 through replication without downtime

I have to migrate from outdated standalone MySQL 5.6.33 to new server with 5.7.41(a separate server) without downtime (or just as short as possible). To accomplish that I'm going to use replication (...
Artsiom Shymanouski's user avatar
3 votes
1 answer
405 views

MySQL database copy

I need to create a copy of the database in my MySQL Server using a Python program. After a little research, I found mysqldump as the better approach: import subprocess command_to_copy_db = f"...
DilLip_Chowdary's user avatar
0 votes
0 answers
274 views

mysqldump with --quick option crashes mysqld with out of memory

I am trying to dump the mysql database that contains a lot of databases. Most of them are empty/small, and there are only 3 large databases. Two are 10GB each, and another is 40GB. The tables of these ...
Barracuda's user avatar
  • 111
0 votes
1 answer
34 views

mariadb on cloud vm - speedup mysqldump

Using mysqldump to dump to backup file. The backup is running to long and looking at the VM performance mariadb is not utilising it well. Every 2hours we stop galera sync and do mysqldump on dedicated ...
Pieter's user avatar
  • 111
1 vote
1 answer
957 views

Function does not exist then dumping mariadb database to remote host

Im trying to do an initial transfer of my data to a replica server by running this command: sudo mysqldump -f -u root -pPassword --opt mydb | mysql --host=replica01 -C mydb -u restore -pPassword It ...
jared's user avatar
  • 123
0 votes
0 answers
224 views

How to include "CHECK" constraint to backup done via phpMyAdmin?

When I export my database from phpMyAdmin (quick method), it does not include CHECK constraints. However, when I run SHOW CREATE TABLE table, I see the constraints. Moreover, foreign constraints are ...
xerez's user avatar
  • 1
1 vote
1 answer
87 views

Is there a built-in `mysqldump` way to equalize or eliminate MySQL and MariaDB conditional comment versions?

We're looking to version control a limited database dump in a Git repo. Developers may have a variety, albeit limited, of database servers installed on their workstations. I'm discovering mysqldump --...
lkraav's user avatar
  • 113
1 vote
0 answers
554 views

Mysqldump crashes mysql with out of memory error

I am trying to run mysqldump to dump mysql for another replica. I am running the following command mysqldump --quick --all-databases --master-data | gzip > dump.sql.gz But when I run it, I get ...
Barracuda's user avatar
  • 111
2 votes
0 answers
900 views

MariaDB 10 SQL-Dump Error: mysqldump: unknown option '--no-tablespaces'

I'm using a MariaDB 10 installed on a QNAP TS-435XEU from QNAP-AppCenter. It is shown as V1.0.4.32. My ERP-Software is designed to work with a MySQL-Database and I've already passed some issues, e. g. ...
Lucas's user avatar
  • 21
0 votes
1 answer
326 views

Mariadb restore take long time, dump data without sql

I am trying to restore a mariadb database with size 320Gb that takes until now 10 days and it's not over yet. To take the backup I use mysqldump command: mysqldump --single-transaction -u maxscale -hx....
Giorgos's user avatar
  • 101
2 votes
2 answers
1k views

script for mysql database backup each database into separate files on windows

I am looking for mysql database backup script to backup each database into separate files windows and exclude system database like mysql, information schema. ex: db1, db2 db1_date_time.sql , ...
Saran's user avatar
  • 71
-1 votes
1 answer
950 views

MYSQL backup script to schedule in windows scheduler

I am Looking for script that 1. backup all MYSQL database daily once with date and time 2. All database mysql database consistency check. Tried this getting an error - Usage: mysqldump [OPTIONS] ...
Saran's user avatar
  • 71
0 votes
0 answers
83 views

How does the mysqldump lock the partition table in MySQL?

Does the below query needs --single-transaction when date='2022-12-03' represent the values of one partition. There is no DML (except SELECT) process on this partition but exists on other partitions. (...
Niyaz's user avatar
  • 218
0 votes
2 answers
363 views

How to perform mysqldump and import simultaneously?

I have a master database and a slave one. Recently, the slave has not synced with the master. So, I tried running mysqldump command to the master: mysqldump -h <master_host> -u <user> -p &...
duyl97's user avatar
  • 3
0 votes
0 answers
188 views

Mysql innodb - Crashed recovery help

It is really painful our mysql wp db crashed! I backed up /var/lib/mysql and tried innodb_force_recovery up to value 4. But no luck When I try backing up using mysqldump it loses connection while ...
TheMonkeyKing's user avatar
0 votes
1 answer
184 views

Copying mysql databases from /var/lib/mysql_old won't let mysql-server to start

After mysql-server on my ubuntu 20.04 server crashed, I copied /var/lib/mysql to backup folder on the server. Now I tried to restore all databases by copying from backup path to /var/lib/mysql but ...
Aly's user avatar
  • 103
1 vote
1 answer
525 views

MySQL InnoDB's History Length is growing while mysqldump is in progress

I'm using a 3-node group replication cluster of Percona MySQL (8.0.25) behind ProxySQL nodes (2.3.2). The transaction-isolation param is set to READ-COMMITTED. While I'm running mysqldump (through ...
Pavel Sapezhko's user avatar
0 votes
1 answer
450 views

Recovering MySQL database from crashed drive - innodb tables

I have a machine that crashed that contained a VERY important MySQL crash. My automated daily backups were in one of the locations where the data was fragged. So all I have is my physical /var/lib/...
R. Smith's user avatar
  • 101
0 votes
1 answer
19 views

mysqldump on percona 8

I have a production server running on AWS EC2 with GP2 SSD disk, it is working fine without any problems, but I noticed something weird, when I dump a database first time I take the dump it take ...
Khaled Hussein's user avatar
0 votes
0 answers
1k views

Error 2020: Got packet bigger than max_allowed_packet' when dumping one row from the table

I got the error message when I ran mysqldump: Error 2020: Got packet bigger than max_allowed_packet' bytes when dumping table `my_table` at row: 17223\n" According to the MySQL official user ...
ITnewbie's user avatar
  • 111
0 votes
1 answer
18k views

Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

Error trying to restore AWS MySQL RDS using backup generated by mysqldump MySQL 8 I have an AWS MySQL RDS, let's call it: productionrds.xxxx.us-east-1.rds.amazonaws.com I created a snapshot and ...
EastsideDev's user avatar
0 votes
0 answers
199 views

mysqldump extreme export slowness

I'm using MySql 5.7.33 on VmWare Vsphere Vm with 20GB RAM and 8 cores. These are my MySql settins slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 1 ...
Domenico Sgarbossa's user avatar
0 votes
1 answer
489 views

MySQL Lost Connection when Importing a dump file

I am working on a MySQL DB transfer (InnoDB tables) from a production server to a local development machine. The dump file is generated via phpMyAdmin and is about 1.6GB in size. The import process ...
Leonard's user avatar
  • 11
0 votes
0 answers
271 views

Best way to duplicate an huge Amazon Aurora 2 (MySQL 5.7) DB into an Amazon Aurora 3 (MySQL 8.0)?

I would like to upgrade to Amazon Aurora 3 (MySQL 8.0) an Amazon Aurora 2 (MySQL 5.7). In-place upgrade isn't available, Snapshot upgrade isn't possible neither because the production machine has the ...
DarkCenobyte's user avatar

1
2 3 4 5
15