Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

Filter by
Sorted by
Tagged with
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'...
user avatar
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 ...
user avatar
  • 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-...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 -...
user avatar
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 … $ ...
user avatar
  • 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/...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 *.*...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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, ...
user avatar
  • 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, ...
user avatar
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 ...
user avatar
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 ...
user avatar
-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?
user avatar
  • 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 ...
user avatar
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/...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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=...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 :-...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 (...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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-...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 = ...
user avatar
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' ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 27

1
2 3 4 5
15