Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
0 votes
0 answers
15 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
0 answers
56 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 ...
0 votes
0 answers
19 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
42 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
57 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 ...
0 votes
1 answer
23 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 ...
0 votes
1 answer
19 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 ...
0 votes
1 answer
26 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 ...
1 vote
2 answers
517 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 ...
  • 133
1 vote
1 answer
129 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
203 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
104 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
0 answers
40 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
31 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 ...
0 votes
0 answers
21 views

Merge 2 database data with same schema but different items

I have 2 databases: CloudDB and LocalDB, turns out that ClouDB have more records in some tables and vice versa with LocalDB. I need to create third database where all the missing values are merged. I ...
1 vote
0 answers
1k 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'...
0 votes
1 answer
37 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
0 answers
18 views

MySQLDump stops after 664 of 741 tables - only structure

I have a PHP-script to dump the "structure only" of a database with a total of 741 tables. Im calling "shell_exec" which works fine. The reason is, sadly... I do not have any shell-...
  • 101
0 votes
1 answer
136 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
43 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 ...
  • 433
2 votes
1 answer
240 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 -...
0 votes
0 answers
286 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 … $ ...
  • 515
0 votes
0 answers
27 views

MySQL 8 Mysqldump is slow after updating from mysql 5.1

We recently upgraded from MySQL 5.1 to 8.0.26. We are currently running the following command for our backup: mysqldump -F -A -R --source-data=2 --single-transaction -u $myuser -p$mypw -P 3306 -S /var/...
  • 1
0 votes
2 answers
110 views

How to optimize a big table for backup

I have a table that is not too much big but still, it takes 30GB of storage. When I try to make a backup with mysqldump it takes a lot of time and resources and I want to optimize it but I don't know ...
1 vote
1 answer
260 views

Why store /var/lib/mysql when you can just create SQL dumps for backup purposes?

Tools like mariabackup offer the possibility to create full and incremental file system backups of /var/lib/mysql but I don't quite understand what additional safety layer this would give me if I ...
1 vote
1 answer
554 views

Why does mysqldump not backup procedures?

I am running MySQL 8.0.23 and try to backup/restore procedures. Therefore I created a user "dump@localhost" with the following rights: > Grants for dump@localhost > GRANT PROCESS ON *.*...
  • 283
0 votes
0 answers
53 views

Mysql dump and live data syncing

I was hoping someone here could help me figure out a solution. I have a MYSQL database that logs live data. This comes from an API that continuously updates the database and this data is being fed ...
  • 1
1 vote
1 answer
221 views

How to take mysqldump of specific tables across all databases and restore them to a a single database?

I need to take a mysqldump of specific tables across all databases in a cluster. In this case the cluster has more than 300+ databases. And I need to take the dump of table1 and table2 from each of ...
  • 111
0 votes
0 answers
37 views

mysqldump slow alter table issue

I notice mysqldump always put the command ALTER after INSERT. CREATE TABLE ...; INSERT table ....; ALTER TABLE ...; Is it possible to change the sequence to ALTER table first? CREATE TABLE ...; ALTER ...
  • 121
0 votes
1 answer
127 views

MariaDB: Periodic copy of data from local database to remote database

I need to periodically copy the outcome of a view on our local database to another remote database. I've tried using mysqldump which works fine when copying data from tables to tables, but i need to ...
2 votes
1 answer
137 views

MariaDB table is broken after failed dump process

I need to dump MariaDB InnoDB-database consists of several tables. One table that causes problems has nearly 13 Million rows. A fresh install of XAMPP(V.3.2.2) dump process was successful, after that, ...
  • 21
1 vote
0 answers
67 views

mysqldump increases SQL server process memory footprint

i mentioned my initial problem here in this question (config is still the same): mariaDB with a 3 node galera cluster is eating more and more RAM Now I have narrowed the problem down. I found out, ...
0 votes
1 answer
59 views

Importing MySQL dumps to multiple MySQL instances in one machine

In case of title is not clear, I would like to explain myself further. I have 6 MySQL instances in my server. Let's call them, M1, M2, .., M6. The reason I have 6 MySQL instances here, I have also ...
6 votes
1 answer
1k views

Do I need --skip-lock-tables with --single-transaction?

I am performing regular backups using mysqldump on a schema consisting entirely of InnoDB tables. The mysqldump documentation has this to say about --single-transaction: The --single-transaction ...
-1 votes
2 answers
585 views

How can I mysqldump all databases except the mysql schema In Windows [closed]

I want to backup all 200+ databases on a MySQL server to all-dbs.sql. I want to exclude the mysql schema. How can I do that IN WINDOWS?
  • 11
1 vote
1 answer
237 views

mysql - import only data from full dump?

I have a full dump from database A. I want to import only the data of this dump into database B. Database B's structure is the same as database A only it misses some tables. How can I do this? The ...
1 vote
1 answer
702 views

Will mysql import overwrite my database

So if I take a mysqldump of my production database called production_database, but I've made changes to by test environment database already, called work_database, adding function/stored_procedures/...
  • 143
0 votes
1 answer
50 views

MySQLDump understanding

How come when I write the following line in my CMD: mysqldump -hHOST -uAdmin -p TestDatabase --single-transaction --routines --triggers > "C:\testdump_backup\testdump.sql" Then the file ...
  • 143
0 votes
0 answers
59 views

Backup a large database with MySqlDump / Import useing MySql

So I'm going to make a backup system for my database and wanted to know if there was something specific I have to know before doing too much. I've read that I mught have to change some database ...
  • 143
0 votes
3 answers
70 views

Need Advises on Updating MySQL Database(Both software and hardware)

I have three in production MySQL database (5.7) with about 160GB of Data. The largest table is about 20GB, and the largest schema is about 40GB. I think all tables are innodb table. Currently I don't ...
  • 105
0 votes
1 answer
1k views

How can I create a mysql database (if it doesn't exist) or replace it (if it exists)?

My dump: mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt --databases mydatabase > out.sql My restore mysql --host=...
  • 157
1 vote
1 answer
86 views

How to restore one mysql database with mysqldump

to make a backup I run: mysqldump --host=localhost --user=root --password=whatever --single-transaction --routines --triggers --log-error=error.txt mydatabase > out.sql Is it possible to do the ...
  • 157
0 votes
2 answers
1k views

MySQL Dump only views, triggers, events and procedures

how are you? I need to generate a file with all triggers, events, procedures from a server's databases and tried the two commands below mysqldump -u root -p --all-databases --host=127.0.0.1 --no-data ...
3 votes
2 answers
321 views

Importing a 16 GB MySQL file silently fails

I've looked at a lot answers here MySQL any way to import a huge (32 GB) sql dump faster? and on Server Fault but haven't found a solution to not being able to import a 16 GB MySQL file completely; it ...
0 votes
1 answer
247 views

Wait state: Statistics

We have 200 databases in one instance of MariaDB with a total of 370.000 tables, since we upgraded to MariaDB 10.5.11, we see most of time passed by mysql is in wait states: Statistics. According ...
1 vote
2 answers
378 views

How do I get a list of all tables in a mysql db, ordered by foreign key dependencies?

I want a list of tables, ordered by foreign key. So if table "Book" has a FK dependency on table "Author", Author would be listed first and Book second. I have no cyclic dependency ...
1 vote
1 answer
1k views

Mysql Innodb Error Number 24 Means 'too Many Open Files'?

I was in the middle of restoring my database using mysqldump and it errored out this ERROR 1030 (HY000) at line 637885: Got error 168 from storage engine And when i check the logs, i am seeing this :-...
0 votes
0 answers
29 views

The user privileges I have assigned don't work when someone imports my .sql database file on mySQL

I'm new to SQLs and mySQL, I and my colleagues who are also new to SQLs are working on a database project and I have successfully queried its structures and inserted data into them. I then created ...
  • 1
1 vote
2 answers
1k views

How to check the last time a table has been modified?

I regularly backup tables individually via mysqldump. To avoid unnecessary backup, I would like to check if a table has been modified since the last backup. By "modified", I mean any change (...
  • 4,281
0 votes
0 answers
363 views

Data truncated after importing a MySQL 5.7 dump into MySQL 8

I am trying to import a 12G MySQL 5.7 dump (on ubuntu 16.04) into a MySQL 8.0 database (on ubuntu 20.04). Half way through the import exits and returns the following error: ERROR 1265 (01000) at line ...
  • 283

1
2 3 4 5
15