Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
730
questions
-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 ...
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 ...
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 ...
- 111
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 ...
- 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 ...
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 ...
- 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&...
- 13
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 ...
- 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 (...
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"...
- 121
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 ...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 --...
- 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 ...
- 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. ...
- 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 ...
- 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....
- 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 , ...
-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] ...
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. (...
- 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 &...
- 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 ...
- 101
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 ...
- 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 ...
- 183
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 ...
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/...
- 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 ...
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 ...
- 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 ...
- 261
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
...
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 ...
- 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 ...
- 209
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 ...
- 1,327
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 ...
- 103
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 ...
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 ...
- 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 ...
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 ...
- 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....
- 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 ...
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 ...
- 124
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'...
- 151
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 ...
- 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 ...
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 ...
- 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 -...
- 21
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 …
$ ...
- 605