Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
728
questions
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 ...
- 261
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 ...
- 199
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 ...
- 1,297
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 ...
- 124
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'...
- 111
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 -...
- 21
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 ...
- 103
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 ...
- 111
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, ...
- 11
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 ...
- 1
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 ...
- 594
-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 ...
- 139
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 ...
- 83
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 ...
- 111
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