MySql Dump Import Is Incomplete

sharingsunshine
sharingsunshine used Ask the Experts™
on
I am moving over my production DB to a test server and this is holding up my progress to get cut over.

Mysqldump of a mysql db isn't complete.  When I import to a Maria DB many of the tables aren't populated like they are in the source.  One obvious table is the products table.  After the import it shows 0 products.  In the source there are over a 1,000 products.

This is the command I am using mysqldump -u root -p --opt [source db] > [dumpname.sql]

I am on a Centos 7 target system and a Centos 6 source system.

Please tell me a way to get my complete MySQL DB imported into a Maria DB.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Have you tried this

mysqldump --databases <SOURCE_DB_NAME> -u <SOURCE_DB_USER> -p > <SOURCE_DB_NAME.YYYYMMDD>.sql
<SOURCE_DB_PASSWORD>

Open in new window

WHERE:
<SOURCE_DB_NAME> => name of the database
<SOURCE_DB_USER> => user for DB_NAME
<SOURCE_DB_PASSWORD> => password for DB_NAME when prompted
<YYYYMMDD> => A convention I use for naming my dumps - replace with date values

Open in new window

That should get you your dump.

Edit the dump to change the default database

1. vi <SOURCE_DB_NAME.YYYYMMDD>.sql
2. Find the CREATE DATABASE line and remove
3. Find the USE line and replace the target db with <TARGET_DB_NAME>

Then on the target system
mysql -h localhost -u <TARGET_DB_USER> -p
<TARGET_DB_PASSWORD>
use <TARGET_DB_NAME>
source <SOURCE_DB_NAME.YYYYMMDD>.sql

Open in new window

Replace <TARGET_DB_****> with your TARGET DB values

Author

Commented:
No change.  It is the same size as the other dumps that have no products.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I use that process all the time - several times a week, cloning and moving sites. Works perfectly.

There is nothing wrong with the process so I am guessing there is something wrong with the DB.

Either the User account you are using doesn't have permissions on the table
Or
The table is corrupt (try repairing it)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I was incorrectly using mysqldump on the import too.  Which is incorrect as you have in your example.  Thanks for your help.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You are welcome.

Just to clarify
Which is incorrect as you have in your example.
Did you mean "correct" or did I get something wrong in my post?

Author

Commented:
No, your post was perfect.  Instead of just putting mysql and adding the import syntax.  I was putting mysqldump and then adding the import syntax which is why it was incomplete.
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Ok, glad you got sorted out.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial