Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

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

Using mysql 8.3 database intro mysql 8.0 causes inability to login in a Laravel app [closed]

I am working on a Laravel app with a weird setup. Initially, it was configured to run on MySQL 8.0.* without docker then we moved to docker with mysql 8.3.*. Now I have to move the database outside ...
Petar Vasilev's user avatar
0 votes
0 answers
49 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
30 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
25 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
39 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
9 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
37 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
41 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 ...
Lorem ipsum's user avatar
0 votes
0 answers
89 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
73 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
149 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
  • 125
0 votes
1 answer
113 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
33 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
0 answers
139 views

mysqldump-like process for Amazon Aurora MySQL?

I believe that mysqldump does not work with Amazon Aurora MySQL. This is a bit of a problem for us as we used use that tool - before moving to Amazon hosting - to extract information across tables to ...
phil's user avatar
  • 153
0 votes
1 answer
39 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
4 votes
2 answers
146 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
  • 211
0 votes
1 answer
195 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
276 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
287 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
10 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
382 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
242 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
2k 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
205 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
138 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
351 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
210 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
32 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
722 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
185 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
73 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
442 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
698 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
261 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 , ...
Raja_mssqlDBA's user avatar
-1 votes
1 answer
742 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
69 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
296 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
156 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
163 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
429 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
323 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
16 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
928 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
13k 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
188 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
370 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
254 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
124 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
306 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

1
2 3 4 5
15