Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
715
questions
0
votes
0
answers
39
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'...
- 101
0
votes
1
answer
21
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
13
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
37
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
0
answers
23
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 ...
- 363
1
vote
1
answer
40
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 -...
- 11
0
votes
0
answers
74
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 …
$ ...
- 565
0
votes
0
answers
17
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
72
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
80
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
182
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
19
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
54
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 ...
0
votes
0
answers
32
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 ...
- 111
0
votes
1
answer
55
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
79
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
42
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
38
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
676
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 ...
- 525
-1
votes
2
answers
217
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
106
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 ...
- 129
0
votes
1
answer
182
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/...
- 133
0
votes
1
answer
40
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 ...
- 133
0
votes
0
answers
38
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 ...
- 133
0
votes
3
answers
63
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
685
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
33
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
671
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
183
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
153
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
216
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
0
votes
0
answers
25
views
MariaDB export only business data
I'm looking for a switch for mysqldump that will make it export only the business data.
What is business data? Everything except "mysql system data" such as the entire mysql database and ...
- 101
1
vote
1
answer
641
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
43
views
MySQL Table structure and data export - Script sequence is not correct
When I export the data and structure of my MySQL database to a file, I notice that the table sequences in the output file are not correct. For example the script of a child table is put before the ...
- 101
0
votes
0
answers
23
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
740
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,169
0
votes
0
answers
300
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
0
votes
1
answer
2k
views
Why do I get ERROR 1824 (HY000) while importing MySQL dump from 5.7 to 8.0.x?
I have a MySQL 5.7.x dump with about 12GB that I try to import into a newly installed MySQL 8.0.x local instance.
mysql -u username -p test < DB-210508.sql
The import starts but fails with the ...
- 283
0
votes
0
answers
56
views
mysqldump has structure but no data, only when run from cron
I have a script with a mysqldump command that looks like this:
mysqldump ${mysql_auth_args} \
--force \
--single-transaction \
--quick \
--compact \
--extended-...
- 101
0
votes
1
answer
574
views
"Percona-XtraDB-Cluster prohibits use of ALTER command on a table (<example>) that resides in non-transactional storage engine"
I installed a Percona XtraDB cluster with pxc_strict_mode set to ENFORCING, and am trying to import databases from non-Galera MariaDB servers. This works fine for some databases, by dumping them via ...
- 3
0
votes
0
answers
42
views
Very slow loading of MySQL DB-backup
I'm working on a transition of a web-site, which uses MySQL database as a backend.
The transition involves copying the production database -- into another on the same server. We do this by taking a ...
- 134
1
vote
0
answers
30
views
is there a way to dump schema tables that don't exist and ignore the ones that exist?
I am using this command to dump schema directly to a different server because the existing server don't have space left to dump the tables/schemas and then transfer to the other server:
mysqldump -h ...
- 151
1
vote
0
answers
121
views
Faster way to restore MySQL innoDB dump
I have a below MySQL configuration.
# The MySQL server
[mysqld]
....
key_buffer = 384M
max_allowed_packet = 5M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = ...
- 39
0
votes
1
answer
543
views
mysqldump: Got error: 1066: Not unique table/alias: 'temp1' when using LOCK TABLES
When I try to dump a whole database using
mysqldump -htest.com -uabc -p --column-statistics=0 abc > abc.sql
The following error occured.
mysqldump: Got error: 1066: Not unique table/alias: 'temp1' ...
- 111
0
votes
1
answer
22
views
Which folder I use for recovery mysql database
Accidently deleted mysql database and reload again.But ı cant find my old database and no have backup.This is picture that mysql ProgramData folder. Which folder should ı use for recovery? Thanks ...
- 1
1
vote
1
answer
559
views
Cant find the exe of mysqldump
I'm just learning about MYSQL after 6 years focusing only on sql server(dumb me).
I'm studying about how backups works there, but I can't find the mysqldump file to execute the backup. on DOS it says ...
- 7,100
1
vote
1
answer
270
views
Copy mysql_native_password to new server
I did a mysqldump of my entire database mysqldump --defaults-file="/home/ubuntu/.my.conf" -u api --all-databases > full-dump.sql . I setup a new mysql and phpmyadmin and loaded the ...
- 113
1
vote
1
answer
4k
views
mysqldump with --no-tablespaces on InnoDB with index?
Does mysqldump --no-tablespaces dumps index files of InnoDB?
I know that MyIsam uses separate index files *.MYI for index. So if one uses mysqldump with --no-tablespaces, does that mean index is not ...
- 21
0
votes
1
answer
47
views
MySQL import silently failing
I'm trying to import a large (30GB) SQL file (generated using mysqldump) into an RDS instance MySQL 5.7 database, from an EC2 instance on the same VPC.
It's importing most without a problem, but ...
- 219
0
votes
1
answer
173
views
MySql Master Master out of sync
I have 2 MySql nodes with Master-Master Replication set-up.
MySql1 has read and write access and is in current state.
MySql2 has only read access and stopped working due to space issue.
How can I fix ...
- 27