As of May 31, 2023, we have updated our Code of Conduct.

Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
-1 votes
1 answer
41 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
0 votes
1 answer
46 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
53 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
0 votes
0 answers
7 views

Change replication name when importing dump with master log position

I exported data from an existing database to bootstrap replication. When setting --master-data to 1 or 2 for mysqldump it writes the log position to the dump file. However the generated command does ...
thi gg's user avatar
  • 101
1 vote
1 answer
108 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
53 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
366 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
82 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
88 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
2 votes
1 answer
286 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
82 views

When restoring a mysql.daily.dump file in plesk, it only recreates the SQL server but without the table data or structure

The issue began when I removed a subscription from a plesk server that was a staging site for the main site that was also on the same server. However unaware to me the wp-config for the live site had ...
thomasjenkerson's user avatar
0 votes
0 answers
79 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
23 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
337 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
  • 113
0 votes
0 answers
75 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
36 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
223 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
1 vote
0 answers
304 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
  • 11
0 votes
0 answers
26 views

Mysqldump - Backup and restore concept misunderstanding

I have problem to understand the backup/restore solution with combination of Mysqldump and binlogs. I will give an example of a possible situation, maybe someone can help me to understand. I have a ...
TimLer's user avatar
  • 159
0 votes
1 answer
95 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
555 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 , ...
Raja_mssqlDBA's user avatar
-1 votes
1 answer
298 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] ...
Raja_mssqlDBA's user avatar
0 votes
0 answers
49 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
92 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
84 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
139 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
205 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
0 answers
169 views

MySQL version Upgrade from 5.5 to 8 without no downtime/very less downtime

May I get instructions from anyone on how to upgrade MySQL from version 5.5 to 8 with no downtime/minimum downtime. Make a plan like that: Step01# 5.5 (Master)---> 5.7 (Slave): Slave will be ...
fahad_dba's user avatar
0 votes
1 answer
86 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
14 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
346 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
5k 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
122 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
186 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
210 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
0 votes
1 answer
56 views

Stuck at importing data directly from the staging machine to the development machine in MySQL/MariaDB

My development database is exactly similar to the staging (and production) database. Table by table, field by field. I know that I can use this command to connect to the remote database: mysql -u user ...
Saeed Neamati's user avatar
0 votes
1 answer
162 views

mysqldump won't accept combination of --socket, --all-databses, --user, --password

Linux, 2 mariadbs each with their own .sock. Dump as root works fine: [root@host]$ mysqldump --socket=/tmp/mysqlsecond.sock --all-databases > $sqlfile Dump as the non-root backup user requires ...
bluesquare's user avatar
0 votes
1 answer
85 views

Understanding slow queries from mysqldumpslow

I'm maintaining a system which sporadically (no exact time) but only at night causes the entire server to crash for a few seconds and the users seeing "Error Connection Refused" until the ...
Marcus Christiansen's user avatar
2 votes
2 answers
3k views

Skip views when using mysqldump

I want to create an command line script that backs up just the tables from my database. Is there an option to do this? I thought about specifying just the tables using SELECT table_name FROM ...
Paul's user avatar
  • 143
2 votes
1 answer
760 views

How to exceed max_execution_time when running a mysqldump?

Is there any way to exceed max_execution_time when running mysqldump, without changing global max_execution_time? My database is in AWS RDS and I can't change these parameters for the sake of the ...
matheus.nagatsuka's user avatar
0 votes
0 answers
1k views

How to set max_execution_time during mysqldump

I am trying to take a backup of my database in a CI before database schema change are made and for local development. However recently we ran into an issue where some tables are to large to dump, we ...
Kay's user avatar
  • 111
1 vote
2 answers
511 views

Load huge mysqldump (500G)

I have a 500G mysqldump to load and it seems taking forever. My server is with Intel i9-10920X @ 3.5GHz and 128GB RAM. The database is set up in a HDD harddisk. I have the mysql (Ver 8.0.29-0ubuntu0....
cccfran's user avatar
  • 13
0 votes
1 answer
176 views

Finding the MySQL binlog positions to restore replication

I have a large database where replication became broken (a query to delete rows ran on the slave directly by mistake) The decision was taken to restore the slave from the master. I've made an export ...
VagrantPaladin's user avatar
0 votes
0 answers
162 views

mysqldump Silently Fails to Backup One Table

I'm using an application that uses MySQL Server 5.7.16.0 on a Windows Server 2012 R2 server. I cannot update mysql, because the app depends on this version and handles updates of it. I use mysqldump ...
Dusty Vargas's user avatar
5 votes
1 answer
3k views

MySQL Workbench mysqldump: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

When using workbench on Mysql 8, I get the following error: mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces I'...
teamcrisis's user avatar
0 votes
1 answer
285 views

How do MySQL-Workbench and mysqldump return dumps with vastly different file-sizes?

I observed that the dump of my DB done "by hand" in the CLI is a lot bigger (>800MB) than the one produced by the Workbench (v8.0) (<500MB) and I am trying to figure out why that's the ...
Wolfone's user avatar
  • 113
0 votes
1 answer
324 views

mysqldump stuck at connecting to localhost

System Info: mySQL version: Ver 8.0.27 for Win64 on x86_64 (MySQL Community Server - GPL) windows 10 version: 20H2 19042.1586 I am trying to create a backup of a database. When I try to run the ...
MissingMarvin's user avatar
0 votes
1 answer
59 views

Parse mysql client version

I need to programmatically call mysqldump with certain parameters. I try to use either the --source-data option or the --master-data option. There is the following phrase in the documentation: From ...
Ivan's user avatar
  • 485
2 votes
1 answer
586 views

Why MySQL dump consume memory after finished

Hi I have a cron job to backup a lot of my database. I create a script to run the dump command for each database every night, I created it like this. mysqldump --defaults-extra-file=$DEFAULT_FILE -e -...
Felix Gani H's user avatar
1 vote
0 answers
777 views

Is it possible to update Mac mysqldump version to same version as what's available for Ubuntu?

I run the following command on Mac Big Sur mysqldump --column-statistics=0 -v -h my host -u myuser -P 3306 -pmypass mydb my_table > /tmp/dump.sql Which uses the following version of mysqldump … $ ...
Dave's user avatar
  • 605

1
2 3 4 5
15