MySql Dump Import Is Incomplete

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.
sharingsunshineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sharingsunshineAuthor Commented:
No change.  It is the same size as the other dumps that have no products.
0
Julian HansenCommented:
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)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sharingsunshineAuthor Commented:
I was incorrectly using mysqldump on the import too.  Which is incorrect as you have in your example.  Thanks for your help.
0
Julian HansenCommented:
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?
0
sharingsunshineAuthor 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.
0
Julian HansenCommented:
Ok, glad you got sorted out.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.