Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

139 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
6
votes
0answers
4k 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: ...
3
votes
1answer
2k views

Database size twice as large after mysql dump and re-import

After exporting and re-importing a mysql database, the size of the database on disk appears to be quadrupling in size. Any ideas why this my be? This is occurring when importing the sql dump right ...
3
votes
1answer
1k 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 (...
3
votes
2answers
3k views

fix `mysqldump: Error 2013: Lost connection` on “cloud” server

I have 6 months of backups left to perform and a single day can take up 200MB in raw mysqldump --extended-insert=false --complete-insert form. If I try to dump more than a day worth of data I get ...
3
votes
0answers
780 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 ...
3
votes
0answers
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 ...
2
votes
1answer
75 views

Restore Mysql dump to a mysql container fails with ERROR 1114 (HY000) at line 7238: The table is full

I am trying to restore a mysql dump of size around 18GB to other mysql server which is running inside a container using this command: mysql -h example.com -u user -p matomo < dump.sql But it fails ...
2
votes
0answers
135 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 ...
2
votes
3answers
4k views

mysqldump still fails even with mysqldump --column-statistics=0

mysqldump still fails even when using mysqldump --column-statistics=0 # mysqldump --column-statistics=0 Just returns # mysqldump: [ERROR] unknown variable 'column-statistics=0' Am I missing ...
2
votes
0answers
344 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 - ...
2
votes
2answers
126 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 ...
2
votes
1answer
460 views

Proper Way to Remove Mysql Tables

I was going to ask why mysqldump was trying to write dropped tables but I found that answer from this question, How do you remove reported non existing tables from mysql?. Removing the .frm files ...
2
votes
0answers
1k 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 `...
2
votes
1answer
154 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 ...
2
votes
2answers
1k 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 ...
2
votes
0answers
125 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
0answers
2k 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 ...
2
votes
0answers
701 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 ...
2
votes
0answers
348 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 ...
2
votes
0answers
400 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 ...
2
votes
1answer
141 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 ...
1
vote
0answers
17 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 ...
1
vote
0answers
24 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 = ...
1
vote
1answer
54 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 ...
1
vote
1answer
29 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 ...
1
vote
0answers
24 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 ...
1
vote
0answers
58 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 ...
1
vote
1answer
17 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?
1
vote
0answers
64 views

How to prevent MariaDB mysqldump from prepending database name in CREATE VIEW?

I am having the exact same problem as this person here: How can I prevent mysqldump from prepending the database name in CREATE VIEW? I am using MariaDB 10.4.7 x64 on Windows. For just ONE of my ...
1
vote
0answers
225 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=...
1
vote
1answer
127 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
0answers
36 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 ...
1
vote
0answers
72 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
0answers
265 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 ...
1
vote
0answers
723 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
2answers
88 views

mysqldump file restore while sql is still being dumped

Im using MaridaDB 10, but my question applies to all mysql variants. I have large dataset (70GB uncomressed when dumped to file). The mysqldump process takes 2-3hours. The restore process takes even ...
1
vote
0answers
51 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 ...
1
vote
0answers
209 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'" "'...
1
vote
0answers
201 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] ...
1
vote
0answers
374 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 ...
1
vote
2answers
465 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 ...
1
vote
1answer
216 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 ...
1
vote
0answers
68 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 ...
1
vote
0answers
465 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 ...
1
vote
0answers
792 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 ...
1
vote
0answers
1k views

Updating +150,000 rows using .sql file causes ERROR 2013 Lost connection to MySQL server during query

I am using ubuntu 12.04. I have a .sql file that is around 5 megabytes. When I use the command: mysql -u -p -v database query.sql The server prints out the content of the file (because of --verbose) ...
1
vote
0answers
1k views

MySQL dump error (#1005 - Can't create table 'novephp_cz_bv.jos_jquarks_customsets' (errno: 150))

I´m trying dump mysql database from MySQL 5.1. to MySQL 5.5. But I´m getting an error "#1005 - Can't create table 'novephp_cz_bv.jos_jquarks_customsets' (errno: 150)". Problematic part of script is ...
1
vote
0answers
162 views

SQL Error while restoring big database in Mysql

I have successfully created a MYSQL dump on RHEL 6 machine. The dump file is having 40 GB. When I try to restore it in an UBUNTU machine I got this error.
1
vote
0answers
1k views

restore using mysqlbinlog failing

While doing restore from a MySQL binary log file, it is failing with the following error . mysqlbinlog mysql-bin.000036 | mysql -u root -p asdb ERROR 1032 (HY000) at line 48: Can't find record in '...
1
vote
0answers
274 views

mysqldump issues with DreamHost

Background: I sought to rename a database on Dreamhost web hosting VPS account. I also wanted to rename a database user for the database to be created above. I followed the approached detailed at ...