Questions tagged [mysqldump]
MySQL's Standard Dump/Backup Utility
133
questions with no upvoted or accepted answers
4
votes
1
answer
940
views
Why does PhpMyAdmin put the indexes at the end of a dump?
When I export a MySQL database with Adminer or with mysqldump, it produces a .sql file with each table definition followed by (if the table is non-empty) an INSERT statement (or multiple statements ...
- 169
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 ...
- 131
3
votes
0
answers
2k
views
Are `--events` and `--routines` really needed to get a `mysqldump --all-databases` of an entire mysql instance?
Several fine answers on here have already exquisitely argued that
mysqldump --hex-blob --events --routines --triggers [...] --all-databases
should be all - modulo storage-engine considerations - ...
- 131
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=...
- 133
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 (...
- 31
3
votes
0
answers
914
views
How do I restore a mysql dump that was created in xml format?
I have a rather big XML formatted MySQL dump
It was created with the --xml param (-X) param of mysqldump.
I need to import it in.
This article from 2010 indicates that there was no "built in" way ...
- 1,104
3
votes
0
answers
1k
views
How do I safely import MySQL data in a directory to a running MariaDB server?
We're integrating another office into ours. That includes some of their data.
I have a new installed MariaDB 5.5 server. The data dir = /home/data/MariaDB. I've done a new/fresh install into that ...
- 31
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 -...
- 21
2
votes
0
answers
231
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 = ...
- 49
2
votes
0
answers
245
views
What kind of complications would I face downgrading from MySQL 8 to MySQL 5?
Let's assume I wanted to downgrade to from MySQL 8 to MySQL 5.7. I know this isn't supported, but what kind of complications am I likely to encounter.
The docs say,
The only supported alternative ...
- 60.4k
2
votes
2
answers
204
views
mysql dump best practices for development
I have an application that has a large database that includes, among many other tables, a table called content and a table called users.
I would like to set up a development environment for this ...
- 129
2
votes
0
answers
2k
views
Prevent mysqldump from writing column-level collate and charset?
Using mariadb 10.x, it looks like the mysqldump utility adds column-level collate even if the table was created without them. Is there a way to suppress this behavior?
Table created:
CREATE TABLE `...
- 215
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 ...
- 121
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 ...
- 21
2
votes
0
answers
185
views
Unable to create backup with mysqlbackup.exe (MEB)
I am not able to create a backup with the mysqlbackup.exe
The command:
Z:\path\mysql-5.7.11-winx64\bin>mysqlbackup.exe -u dba --host=127.0.0.1 --backup-image=C:\test\backup --backup-dir=%temp%\...
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 ...
- 21
2
votes
0
answers
3k
views
mysqldump show Table doesn´t exist when doing LOCK TABLES
Our server had power outage and after start some of mysql tables was inaccessible. I tried many things but nothing helped and currently i am at freeze point.
I used command :
mysqldump database ...
- 121
2
votes
0
answers
937
views
mysqldump with --skip-grant-tables
It appears the files in my /var/lib/mysql/mysql are borked, and I can only start mysqld with --skip-grant-tables.
Now I want to export all tables with mysqldump --all-databases, wipe and reinstall ...
- 175
2
votes
0
answers
405
views
mysqldump restore error
I am getting the error below, while restoring a dump to RDS MySQL 5.6. Dump is taken from MySQL 5.5 originally. I tried restoring twice but same error. Can anyone help
ERROR 1064 (42000) at line ...
- 357
2
votes
0
answers
426
views
Are ZRM (Zmanda Recovery Manager) incremental backups reliable?
I had an issue while restoring incremental backups and I'm wondering if they are reliable. I'm making weekly full backups and daily incremental backups and I needed to restore a single database after ...
- 169
2
votes
1
answer
158
views
MySQL replication backup
How to take MySQL Master-Master and Master-Slave replication backup?. I have setup Master-Master replication with a separate slave for each master on Ubuntu.
What happens if,
I issue or schedule ...
- 165
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
1
vote
0
answers
223
views
Mysqldump crashes mysql with out of memory error
I am trying to run mysqldump to dump mysql for another replica.
I am running the following command
mysqldump --quick --all-databases --master-data | gzip > dump.sql.gz
But when I run it, I get ...
- 111
1
vote
0
answers
304
views
MariaDB 10 SQL-Dump Error: mysqldump: unknown option '--no-tablespaces'
I'm using a MariaDB 10 installed on a QNAP TS-435XEU from QNAP-AppCenter. It is shown as V1.0.4.32.
My ERP-Software is designed to work with a MySQL-Database and I've already passed some issues, e. g. ...
- 11
1
vote
0
answers
777
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 …
$ ...
- 605
1
vote
0
answers
138
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
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/...
- 143
1
vote
0
answers
228
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-...
- 111
1
vote
0
answers
170
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
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 ...
- 293
1
vote
0
answers
451
views
MySQL Why does mysqldump break/just stop? Improve my mysqldump command
I use the following command to backup my MySQL (with mysqldumpl) two times a day:
/usr/local/mysql/bin/mysqldumpl --lock-tables=false --default-character-set=utf8 -h'host' -u'user' -p'pass' -f -v ...
- 111
1
vote
0
answers
92
views
Getting error while starting slave on MySQL slave./ Backups in MySQL 8.0.20
I want to set up below command for daily MySQL database backup.suppose We have more than 50 MySQL database in server.
I am using below command to take backup of all databases on server.
Note: I am ...
- 133
1
vote
1
answer
48
views
mysqldump locks all tables even when specifically grabbing a few?
I'm using a mysqldump command pulling data from 3 tables of 7. Are the other 4 locked as well?
- 11
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 ...
- 149
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 ...
- 163
1
vote
0
answers
2k
views
how to dump mysql schema without keys and without constraint ? and keep all of them to alter afterwards
i am using ubuntu 16.04 and want to dump large schema with hounded of tables from AWS rds, remove all keys and foreign key , load data and then add back the keys , is there any automatic tool that ...
- 163
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 ...
1
vote
0
answers
62
views
How to fully dump mySQL database via workbench?
I am exporting database in SQL file using workbench. Export is done successfully, but when I import data and compare record counts with older database, some of the records are missing. Not all records ...
- 111
1
vote
0
answers
83
views
Mysql dump is not full
I have installed fresh ubuntu 16.04 with apache2.4 and php7.0 on it and then installed mariadb. Here is version: mysql Ver 15.1 Distrib 10.0.35-MariaDB, for debian-linux-gnu (x86_64) using readline 5....
1
vote
0
answers
317
views
My node js server stops listening on mysql db
I implemented a listener on a table in a MySQL database with a MySQL event that is based on listening on binlog.
However, the problem is that after a while my node.js server stops listening on the ...
- 11
1
vote
0
answers
1k
views
mysqldump incremental backup -- slight confusion
I am Avinash,
I have a slight confusion about incremental backups.
I am going through the MySQL 5.7 Docs to study the backup process using mysqldump.
I am not using any master-slave configuration. I ...
1
vote
0
answers
56
views
mysqldump dies on the same row. Analyze is fine. Timeouts are long
I have an innodb table with about 70M rows. Recently a cron job to back it up with mysqldump started failing at the same row.
Mysqlcheck also fails.
Mysqlanalyze runs fine and prints OK.
Alter ...
- 11
1
vote
0
answers
283
views
Bash Script gives error in a loop passing thru multiple Mysqldump
The script
I'm experiencing an issue with this bash script:
dump_all_dbs(){
$DOMAIN="mysql.domain.com"
$DBS_FOLDER_BK="$HOME/_backup/"
$DATA=("'1' 'dbname' 'dbuser' 'dbpassword' 'Yes'" "'...
- 11
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).
/*...
- 433
1
vote
0
answers
240
views
Attempt to use mysqldump results in usage suggestions only
I attempted the following:
mysqldump -u root -p sa -n -d -t --routines --skip-triggers --all-databases > C:\backups\MySQLStoredProc.sql
but only received back;
Usage: mysqldump [OPTIONS] ...
- 11
1
vote
0
answers
487
views
Loading MySQL Dump From one Character Set to Another
As you can see from these configuration differences, the MySQL server is configured with latin1, while the MariaDB server is using utf8mb4:
Variable MySQL Server MariaDB Server
...
- 143
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 ...
- 19
1
vote
0
answers
79
views
MySQL master to slave dump fails when done over SSH pipe
When we need to [re]build a MySQL-5.5 slave, our usual approach is this (CHANGE MASTER TO / START SLAVE omitted for clarity):
master$ mysqldump -uroot -p --all-databases --master-data > dump.sql
...
- 111
1
vote
0
answers
576
views
To configure MySQL Enterprise Workbench for Incremental Backup with Online Backup option
Could not acquire management access for administration
RuntimeError:Could not initialize WMI interface: Workbench.wmiOpenSession():Could not connect to target machine.
When i am trying to configure ...
- 6,299
1
vote
0
answers
858
views
mysqldump with --single-transaction seems to be "fixing" InnoDB tables with page corruption - why?
I'm trying to find out what exactly is happening here. I've been intentionally corrupting index pages in InnoDB tables, changing bytes in user record areas that actually contain information (largest ...
- 11