Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [mysqldump]

MySQL's Standard Dump/Backup Utility

121 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
2answers
2k 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
645 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
1answer
5k views

How can I backup/dump MYSQL database with foreign key constraints?

I wanted to take the dump of the MySQL database with all the constraints on that database. The dump (.sql file) I am having right now don't show any reference key(foreign key) relationship. I am ...
3
votes
0answers
996 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 ...
3
votes
2answers
1k views

mysqldbcompare SELECT command denied to user

I am trying to run mysqldbcompare to see the difference between two tables: mysqldbcompare --server1=server1:2016@localhost --server2=server2:2016@localhost user:user --difftype=sql --run-all-tests ...
2
votes
1answer
404 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 ...
2
votes
1answer
124 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
559 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
112 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
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 (...
2
votes
0answers
106 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
553 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
315 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
374 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
126 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
59 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 - ...
1
vote
0answers
34 views

Restoring a mysqldump loses user privileges for routines?

When I try and restore a mysqldump, the user privileges against some routines are missing. From what I can tell that is happeneing the routines that are in schemas that are loaded after the mysql ...
1
vote
1answer
76 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
1answer
122 views

Import Windows MySQL dump on a Debian Linux server

I created a test database on a Windows computer with MySQL 5.7 and filled it with many data records. Now I created a database dump with mysqldump and wanted to import it on my server (Debian Jessie, ...
1
vote
0answers
29 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
2answers
89 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 ...
1
vote
1answer
116 views

Importing a mysqldump from MySQL 5.0.95 to MariaDB 10.0.36: Some tables incomplete

I have an ancient MySQL setup (5.0.95) that I'm trying to shut down. We've mysqldumped the databases we need, and we're trying to import them into MariaDB (10.0.36). We're running into some odd quirks,...
1
vote
0answers
64 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
214 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
557 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
0answers
43 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
162 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
172 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
307 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
303 views

Determine size of mysqldump prior to dumping

This question is a followup on this previous question. Q: How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump? I have tried using the most upvoted ...
1
vote
2answers
388 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
153 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
1answer
103 views

Incorrect backup size

We are storing our everyday full backup in remote storage. Everything seems to be working fine, but every seven day interval the archived folder size is much more than the everyday archived backup ...
1
vote
0answers
64 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
2answers
740 views

Export and Import large database from one rds of amazon to newly created amazon rds

We are facing some issue. We would like to export and import mysql database "xyz" from old rds say xyz-rds to newly created rds say abc-rds (mysql). We are approaching common method taking full ...
1
vote
0answers
415 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
699 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
158 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
257 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 ...
1
vote
1answer
763 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 &...
0
votes
2answers
32 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 ...
0
votes
1answer
12 views

Incomplete MysqlDump Files - Mysqldump Inside For Loop

I am running a Windows 10 Machine with WAMP installed. I have also a Git Bash Installed. I was trying to backup all my databases in each separate file using the code below. for DB in $(mysql -uroot ...
0
votes
0answers
26 views

mysqldump large innodb tables - only importing small number of records

I've inherited a website I didn't originally build. The database includes a users and a bookings table both of which are relatively large (innodb) tables. I'm trying to download a copy of the ...
0
votes
1answer
39 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 ...
0
votes
0answers
99 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: ...