Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
730
questions
0
votes
0
answers
169
views
MySQL version Upgrade from 5.5 to 8 without no downtime/very less downtime
May I get instructions from anyone on how to upgrade MySQL from version 5.5 to 8 with no downtime/minimum downtime.
Make a plan like that:
Step01# 5.5 (Master)---> 5.7 (Slave): Slave will be ...
0
votes
1
answer
5k
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 ...
- 24.3k
-1
votes
1
answer
41
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 ...
- 3,367
530
votes
4
answers
990k
views
How do you mysqldump specific table(s)?
How can I dump a specific table or set of tables without including the rest of the db tables?
- 123
3
votes
1
answer
2k
views
mysqlpump --set-gtid-purged=OFF still adds @SESSION.SQL_LOG_BIN=0;
Whenever I add --set-gtid-purged=OFF, it still adds @@SESSION.SQL_LOG_BIN=0; to the output.
According to the documentation, shouldn't it get removed?
--set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=...
CommunityBot
- 1
22
votes
5
answers
24k
views
mysql to mariadb: unknown collation utf8mb4_0900_ai_ci
I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:
Error: ...
- 101
1
vote
1
answer
274
views
SQL dump loads with Error 1064
Looks like this issue is just the wrong version of MySQL, need to go to 5.5
So I have a backup of an offsite source SQL database. I am not sure how they are preforming the data dump but I am trying ...
CommunityBot
- 1
2
votes
2
answers
2k
views
Importing a large mysql dump while replication is running
So we have a simple master/slave mysql replication running between two CentOS Servers.
The master has multiple databases. eg..
Database1
Database2
Database3
The issue is we have a mysql dumpfile ...
CommunityBot
- 1
0
votes
2
answers
803
views
Import a large MySQL dump 68GB on RDS instance
I tried to import a large MySQL dump.sql (68GB) on RDS AWS instance MySQL 8.0 (db.t3.2xlarge: 32GB RAM).
The import takes more than six hours:
More than 500 tables
I have 2 large tables: the first ...
CommunityBot
- 1
5
votes
1
answer
3k
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
-1
votes
2
answers
468
views
What is the best practise for MariaDB mysqldump configurations of large databases?
Problem
We have a large database (MariaDB 10.3) and mysqldump takes 2-3 hours to complete a full database dump, and it's also causing downgrade in application performance during the dump process.
...
- 24.3k
1
vote
2
answers
265
views
How can I validate if the MySQL dump was restored correctly?
I am taking a backup on a live system where inserts are going on. The backup size is around 50 GB. I have started the restore of the backup but for some reason I have not enabled the log, and also my ...
- 24.3k
2
votes
1
answer
262
views
What dump file options or MariaDB setting is slowing down the load of data?
Context:
Loading a copy of a legacy DB for a CRUD app at build by Spring Boot. Primary use case is developers testing the CRUD functionality as it is developed.
Problem:
The loading operation is ...
CommunityBot
- 1
3
votes
1
answer
2k
views
MySQL: error 1118 (42000) row size too large
storage_engine = MyISAM
table_type = MyISAM
version = 5.1.25-rc
database_name.sql dump size is 3.3G:
mysqldump -u user -p database_name > database_name.sql
When I try to restore the database (...
CommunityBot
- 1
0
votes
1
answer
244
views
Taking a mysqldump from a specific point in time
Is there a way I can take mysql dump from a specific point in time until the latest?
Let me explain the need of such requirement, maybe someone could suggest a better option:
I took a mysqldump (...
CommunityBot
- 1
1
vote
2
answers
1k
views
Are certain environment variables required for mysql login-path?
I created a bash script that backs up my database, db_backup.sh
It basically just runs these commands:
DB=/home/linuxuser/backups/backupfilename.sql.gz
mysqldump --login-path=local db_name | gzip &...
CommunityBot
- 1
0
votes
1
answer
522
views
mysql diff and add diff records into new database
I have two database on two different hosts:
host A has database A and table A
table A contains records with primary key 1 to 10
host B has database B and table B
table B contains records with ...
CommunityBot
- 1
0
votes
1
answer
256
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 ...
CommunityBot
- 1
40
votes
4
answers
21k
views
mysqldump vs mysqlpump
I just heard about mysqlpump - it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? ...
- 101
3
votes
1
answer
1k
views
Restore MySQL dump to a container fails with ERROR 1114 "The table is full"
I am trying to restore a MySQL dump of size around 18GB to another MySQL server, which is running inside a container using this command:
mysql -h example.com -u user -p matomo < dump.sql
But it ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Restore .dmp file into csv
We have asked our clients for certain data in a csv file format and we just got a .dmp file. I have been trying to open it using MySQL as if it were .dump but it doesn't work.
I have seen other ...
CommunityBot
- 1
1
vote
2
answers
236
views
Grabbing SQL Dump of Master DB Without Downtime
I'm curious whether downtime will be necessary to grab a SQL dump of my master database.
Right now, I'm in the process of rebuilding my one slave. There is actually only one database from master that ...
CommunityBot
- 1
0
votes
1
answer
46
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 ...
2
votes
1
answer
586
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 -...
CommunityBot
- 1
0
votes
1
answer
3k
views
Error: ERROR 1047 (08S01) at line 6955: WSREP has not yet prepared node for application use
While restoring a dump from a CloudSQL instance into a node of a Percona xtradb cluster, I am getting this error. The line 6955 in the .sql dump file is in bold below:
DROP TABLE IF EXISTS `...
CommunityBot
- 1
1
vote
0
answers
53
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 ...
- 111
5
votes
3
answers
8k
views
How to take mysqldump with Generated column?
I am getting below error while importing the dump,
ERROR 3105 (HY000) at line 82: The value specified for generated
column 'column_name' in table 'table_name' is not allowed.
When I describe the ...
- 1,143
1
vote
1
answer
2k
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/...
CommunityBot
- 1
0
votes
1
answer
677
views
MySQL - Dumping 1TB database with SED to replace definer
Im going to dump a large database more than 1TB using MySQLDUMP (unfortunately I have to stick with mysqldump, I can't use mydumper or any other backup tools)
And I want to replace the definer on the ...
CommunityBot
- 1
0
votes
1
answer
1k
views
mysqldump from AWS RDS slowing down
Im taking a db backup from AWS RDS (MySQL) slave from an EC2 (m5.large - 2c/8GB) in same AZ using -
mysqldump -hxx -u xx -p --extended-insert --single-transaction --max_allowed_packet=1G db > ...
CommunityBot
- 1
0
votes
1
answer
285
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 ...
CommunityBot
- 1
0
votes
0
answers
7
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 ...
- 101
1
vote
1
answer
108
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 ...
- 3,367
47
votes
8
answers
92k
views
Is it possible to mysqldump a subset of a database required to reproduce a query?
Background
I would like to provide the subset of my database required to reproduce a select query. My goal is to make my computational workflow reproducible (as in reproducible research).
Question
...
1
vote
1
answer
839
views
MySQLdump Exclude master-data
Is it possible to exclude the master data from a mysql dump? From the manual it seems like the only options are commented out or present. I don't need the data at all because it is going to a static ...
CommunityBot
- 1
0
votes
1
answer
324
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 ...
CommunityBot
- 1
1
vote
1
answer
333
views
Why do I have two collations in mysqldump, under stored procs?
When I run mysqldump --routines I'm seeing multiple collations for my stored procs. Neither my DB nor columns use utf8mb (I understand the benefits of utf8mb over utf8 but let's leave it for now).
/*...
CommunityBot
- 1
0
votes
1
answer
369
views
MySQL upgrade via mysqldump
We want to upgrade to MySQL 8.0 from MySQL 5.6 but upgrade manual seems to cumbersome and risky to follow doing an in-place upgrade so we have decided to do it via MySQL Workbench export (mysqldump) ...
CommunityBot
- 1
0
votes
1
answer
9k
views
mysqldump Unknown table 'COLUMN_STATISTICS' in information_schema (1109)
I am having some difficulty with my MySQL database.
It appears the server suffered from a power outage during the night. As a result, MySQL wouldn't start when it reboots.
I viewed the error log and ...
CommunityBot
- 1
1
vote
1
answer
366
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&...
- 80.3k
0
votes
0
answers
53
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 ...
- 155
1
vote
1
answer
350
views
MySQLdump leads to exceeding max connections. Skip-Quick as a solution?
Every night I do a full mysqldump of a 17G DB in MySQL 5.7.32. This worke for years, now I am doing some heavy load on inserts during the night which caused at exactly the backup time the connections ...
CommunityBot
- 1
0
votes
1
answer
1k
views
mysqldump waiting for table fush for long time
MySQL version: MySQL Commercial 8.0.16 OS: RHEL 7.6
Following is my mysqldump command
mysqldump --defaults-extra-file=/mysql_data/config/extra_options.cnf --single-transaction --flush-logs --master-...
CommunityBot
- 1
0
votes
1
answer
361
views
How speed up mysql dump without data?
There are a lot of questions (1,2,3) about speed up MySql restoring. But all of them speed up data import. Is there a way to speed up schema tables creation? For example dump iundepended table in ...
CommunityBot
- 1
0
votes
1
answer
441
views
mysql database migration of schemas with updated data and structures
I am a newbie with MySQL database dumps and am currently trying to work with data migration between a source schema and a target schema.
Assuming the source schema is defined by (...
CommunityBot
- 1
0
votes
1
answer
2k
views
Automysqlbackup fix error "Skipping the data of table mysql.event. Specify the --events option explicitly"
I need to fix the mysqldump warning "-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.".
I found that its because of not specifying whether or not to add ...
CommunityBot
- 1
1
vote
2
answers
2k
views
how to dump via mysqldump hugh table into chunks
i have very hugh table , around 100 M records and 100 GB in a dump file , when i try to restore it it to a different DB i get sql query lost connection , i want to try and dump this table into chunks ...
CommunityBot
- 1
2
votes
2
answers
577
views
Large MYSQL DB on single file
I have a large MYSQL innodb database (115GB) running on single file mode in MySQL server.
I NEED to move this to file per table mode to allow me to optimize and reduce the overall DB size.
Im ...
CommunityBot
- 1
0
votes
1
answer
82
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 ...
- 74.9k
1
vote
1
answer
88
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 (...
- 179k