1

for years I've been using mysqldump without much trouble. Recently I joined a project that is using "MySQL dump 10.14 Distrib 5.5.52-MariaDB, for Linux (x86_64)" and when I attempt to do a dump it includes all of the create table statements, and the routines if I include --routines, but it doesn't contain any of the data. There are no INSERT statements in the file at all.

I've tried on multiple databases within this DBMS instance, and I've tried using several different user accounts, including root. I've checked that the users I'm using have access to the data, and if I log directly into mysql from those usernames I can browse the data.

I've been unable to find any help information on the web because all posts seem to assume that the insert data will just be there (which I think it right). Has anyone seen something like this before or is there a suggestion of what I might try to get a real database backup?

I recognize this is a very old MariaDB version, I'm new to the team and I'm willing to update it, but not until after I know the data is successfully backed up somewhere.

Thanks in advance for your help, this is really confusing me.

Here's what I'm trying: 

/usr/bin/mysqldump -u root -p --all-databases  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql

/usr/bin/mysqldump -u root -p -d databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
diginize
  • 13
  • 2

2 Answers2

2

Answer is very simple. -d stands for NO DATA !!!

Here is the MariaDB Documentation:

Do not write any table row information (that is, do not dump table contents). This is useful if you want to dump only the CREATE TABLE statement for the table (for example, to create an empty copy of the table by loading the dump file).

Just get rid of the -d and you are good to go.

UPDATE 2019-09-24 11:04 EDT

Let me clarify why you are seeing what you see,

  • When you used --databases, the line USE databasename; was added before all INSERT INTO statements for each named database. (Please review MySQL Docs on --databases)
  • When you used --all-databases, the line USE databasename; was added before all INSERT INTOs for each given database in the MySQL instance (Please review MySQL Docs on --all-databases)
  • When you used -d, you were telling mysqldump not to dump the data, whether you name a database or not. Even if you use --database or all-databases, no data will come out. (Please view MySQL Docs on --no-data)
Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

Before posting my question I did just a few more tweaks to see what might be going wrong. I may have stumbled into an answer. So I posted the question anyway and I'm going to post a potential answer here in hopes that it helps the next person.

The -d option in the second row I thought was short for --databases, but it appears to do something else. I don't see it in the docs (https://mariadb.com/kb/en/library/mysqldump/) but when I changed that to --databases I started getting results with the INSERT INTO statements.

So, instead of: 

/usr/bin/mysqldump -u root -p -d databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql

Use: 

/usr/bin/mysqldump -u root -p --databases databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql

Or (without naming the option at all): 

/usr/bin/mysqldump -u root -p databasename  > dbbackup-dump-$(date +\%Y-\%m-\%d).sql
diginize
  • 13
  • 2