MySQL databases, phpMyAdmin, exporting and importing = CORRUPTED :(

AdventureTime

Hi,

I tried to export all of the MySQL databases using phpMyAdmin as an SQL file.
After downloading it, I did an SCP of it to the new server which is a local to remote transfer.

Now, when I am trying to import it, I am receiving an error message that it cannot be imported.

What do I do next? Is it possible to export the SQL file as a compressed file, then store it in the old server. Then I will just do an SCP remote to remote transfer. Is it possible?

Thanks.

Mun

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

geekalot

@AdventureTime: Make sure the data is not corrupted (truncated) when you did the dump.

I was doing this for a customer recently with data hosted by a 3rd party and it took multiple attempts before I was able to get ALL the data dumped properly; no idea why. And, doing it via commandline was not an available option with their setup :-(

AdventureTime

@Mun said:
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Okay, so I have to check everything first then optimize them all afterwards what to do next? So what you are suggesting is that, it is better to use the CLI instead of using phpMyAdmin?

@geekalot said:
AdventureTime: Make sure the data is not corrupted (truncated) when you did the dump.

I was doing this for a customer recently with data hosted by a 3rd party and it took multiple attempts before I was able to get ALL the data dumped properly; no idea why. And, doing it via commandline was not an available option with their setup :-(

Thanks for the tip. But how? I prefer to use phpMyAdmin?

Mun

Yes, there might be a limit on the download for phpmyadmin, and I have had that issue before.

The reason for the CLI line is because if it is corrupt that is the way to fix it.

AdventureTime

Is there a way to set phpMyAdmin exporting the actual SQL file and save it directly in the server instead of making the user download it?

mikho

also make sure that you are allowed to upload the complete file. if it is to large, the import will break.

geekalot

@AdventureTime said:
Thanks for the tip. But how? I prefer to use phpMyAdmin?

If you have phpMyAdmin, my point is just make sure that your data dump is not being truncated based on the options you choose in phpMyAdmin. It can produce the problem you have described.

If you have SSH access, you can do the dump like this (for example):

time mysqldump -uroot -p --master-data --opt DBNAME > DBDUMP.sql

• "time" is optional (to time it)
• "--master-data" is optional (if you are using it to setup mysql replication). And if you are using it to setup replication then do this as well immediately after:
  

mysql -uroot -p --execute="SHOW MASTER STATUS \G;"

It will come in very handy.

• Do "man mysqldump" for complete list of options

PrincessOfCats

@AdventureTime

On origin

mysqldump -uroot -p --all-databases >backup.sql

On new server

mysql -uroot -p <backup.sql

If you prefer to do one db at a time, replace --all-databases with db name

AdventureTime

geekalot said: Do "man mysqldump" for comple

@mikho said:
also make sure that you are allowed to upload the complete file. if it is to large, the import will break.

For uploading the file, I use SCP for it.

@geekalot said:
If you have phpMyAdmin, my point is just make sure that your data dump is not being truncated based on the options you choose in phpMyAdmin. It can produce the problem you have described.

If you have SSH access, you can do the dump like this (for example):

time mysqldump -uroot -p --master-data --opt DBNAME > DBDUMP.sql
"time" is optional (to time it)
"--master-data" is optional (if you are using it to setup mysql replication). And if you are using it to setup replication then do this as well immediately after:
mysql -uroot -p --execute="SHOW MASTER STATUS \G;"
It will come in very handy.

Do "man mysqldump" for complete list of options

Thank you, but I have no idea what those commands mean. It's really not that user-friendly

@StartledPhoenix said:
AdventureTime

On origin

If you prefer to do one db at a time, replace --all-databases with db name

Thank you, I'll try this one.

msg7086

How do you export? By PHPMyAdmin.

Then how do you import it?

Outputting by PMA and then importing by mysql command line, you suffer the risk of breaking something on character set.

AdventureTime

Update:

The dumping took more than 30 minutes. Then I saw an error message:

-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.

Another thing, doing the dump uses the "root" username right?
How about restoring the dump in a different server that uses a different MySQL username?

AdventureTime

@msg7086 said:
How do you export? By PHPMyAdmin.

Then how do you import it?

Outputting by PMA and then importing by mysql command line, you suffer the risk of breaking something on character set.

Importing and Exporting were both done via phpMyAdmin.

mikho

AdventureTime said: For uploading the file, I use SCP for it.

I ment php_upload_limit, memory_limit or whats it called and all other settings in php for uploading files since thats what phpmyadmin uses. Or are you not using phpmyadmin on the new server?

PrincessOfCats

@AdventureTime said:
Update:

The dumping took more than 30 minutes. Then I saw an error message:

How about restoring the dump in a different server that uses a different MySQL username?

Thats a warning, not a error. You don't need that table.

If you don't have root access on target, do something like below for each user, changing userdb1/2/etc to whatever

mysqldump -uroot -p --databases userdb1 userdb2 >backup_for_user.sql

Importing...

mysql -uuserhere -p < backup_for_user.sql

AdventureTime

@mikho said:
I ment php_upload_limit, memory_limit or whats it called and all other settings in php for uploading files since thats what phpmyadmin uses. Or are you not using phpmyadmin on the new server?

I don't upload files using the web-based interface since there is a 2 MB limit. I usually store it in the tmp folder and let phpMyAdmin find it.

The new server comes with phpMyAdmin since I installed zPanel.

@StartledPhoenix said:

mysql -uuserhere -p < backup_for_user.sql

Thank you isn't there suppose to be a space after -u then enter the username?

mikho

AdventureTime said: isn't there suppose to be a space after -u then enter the username?

don't have to be

drazilox

If you can, install Heidi SQL or DBeaver on your computer. Better solution than PHPMyAdmin. Also much faster to use. Can recommend.

PrincessOfCats

Fixed a typo above as well, somehow didnt remove --all-databases when typing new command

joereid

If dump took 30 minutes, the sql file is too big to import via pma. Use mysqldump command on source server and mysql < file.sql on destination server.

AdventureTime

@drazilox said:
If you can, install Heidi SQL or DBeaver on your computer. Better solution than PHPMyAdmin. Also much faster to use. Can recommend.

Does it work on a Mac?

@StartledPhoenix said:
Fixed a typo above as well, somehow didnt remove --all-databases when typing new command

I did the restoration thing right now. The old server uses "root" as the username and the new server uses a different one. So, I just followed the instructions and I received the following message:

ERROR 1044 (42000) at line 22: Access denied for user '(INSERT USERNAME HERE)'@'%' to database 'blog'

So, what I used is the generic login "root" and I was able to restore everything. Now, I don't know how to transfer it from one specific database to another user's mysql account in a specific database.

@joereid said:
If dump took 30 minutes, the sql file is too big to import via pma. Use mysqldump command on source server and mysql < file.sql on destination server.

That is what I did and I am so happy about it.

PrincessOfCats

@AdventureTime said:
That is what I did and I am so happy about it.

Forgot to mention you need to create the dbs and add privs for mysql users to each db if you want to import using the user

So, what I used is the generic login "root" and I was able to restore everything. Now, I don't know >how to transfer it from one specific database to another user's mysql account in a specific >database.

There is no 'other account' to place mysql databases in.

However, you can add privs for mysql users to the dbs they are supposed to have. Something like

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'passwordhere';

Side note: That allows users to access using socket/port at localhost only

AdventureTime

@StartledPhoenix said:
Side note: That allows users to access using socket/port at localhost only

I just realized if ever that is possible then I would have to edit each and every one config.php or some config files of the current websites to match the username that I wanted instead of using "root" right?

PrincessOfCats

Using root in a config.php is dangerous.

NEVER EVER DO THIS.

If a person compromises one app on your server, they will manage to access everything on the mysql server - all databases, your mysql user table, and other data.

AdventureTime

Okay, so how can I do this?

GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'passwordhere';

How can I make it like to grant the specific user to all of the databases so that I will be avoiding root?

mikho

Run the same command for each database?

4n0nx

AdventureTime said: How can I make it like to grant the specific user to all of the databases so that I will be avoiding root?

If you give one user access to all databases, you might as well just use root. (?)

AdventureTime

Okay, well I've tried to use zPanel and VestaCP. The problem with them is that they put "prefixes" which is annoying. Also, creating a user means you have to create a specific database and will just some sort of map them or to connect them with each other.

What happened was, I was able to do the restoration of the databases with the username "root" since that is the same username from the other server. I used everything that you guys taught me, through the command line interface. It actually worked.

However, it corrupted some integrated settings, like for example phpMyAdmin and the current MySQL databases were overwritten as well.

So, what I did is to do a MySQL Dump for each of the needed databases. However, when I am restoring them it seems that I would need to create a database for each and every one of them. I guess the only thing exported are the tables.

I had to do a few research and came up with GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost'; which is from http://stackoverflow.com/questions/5016505/mysql-grant-all-privileges-on-database

I logged into phpMyAdmin and I was able to create the needed databases manually, without any prefixes (at last). Then I imported the tables of each databases through the SQL files that are stored in the server.

Still, it doesn't work. WordPress works, I edited the configuration and entered the proper MySQL login credentials.. The location of the WordPress website is domain.com/blog/ but the problem with that is the links to the pages doesn't work. I have checked the .htaccess file and it looks fine.

The main issue here is the Joomla based website which resides at the root directory of the domain. I edited out the configuration.php but everything just went black. I checked on the permissions, the groups stuff chown and chmod but they were okay and was able to match the sample pages of the control panel software. But it still didn't work. For the WordPress thing, at least it is easy to transfer with less modifications, but for the Joola and SugarCRM based websites are really difficult to transfer. A complete database backup and the actual files must be made, which is what I did. Still it didn't work.

The last thing that I did is to use the grant privileges thingy and then restore the databases and the good thing about this is that it uses a different username. I thought everything will turn out great because phpMyAdmin is still working. WordPress works great even though there is the .htaccess directory blahblah issue but Joomla and SugarCRM are really not working. They just love to display blank pages even though I have entered the proper MySQL logins in each and every one of the configuration files.

I actually don't know what to do. My plan is just to stay with the current server, update and upgrade everything and just install Varnish.

P.S

I even tried the optimization of MySQL databases but that didn't work either. I just followed this website http://stackoverflow.com/questions/4582832/repair-all-tables-in-one-go

PrincessOfCats

@4n0nx said:

However, contrary to a root account, that one user cannot create new dbs, modify users/etc

rokok

still has access to old server & running? and new server to move on? just copy phpmyadmin folder ;p but lot things to do

bsdguy

@AdventureTime

By no means desiring to sound brutal but: Use managed hosting.

You are using wordpress and phpmysqladmin and quickly get lost on the command line. That clearly indicates that you simply don't have the required knowledge. Don't be fooled by friendly answers suggesting ways how to make phpmysqladmin work (I'd prefer to not tell the reason for that advice because it would strongly dis-please quite some people here).

Don't get me wrong. There is nothing wrong with not knowing those technical things and just wanting something simple "click and go". That's why there are managed hosters and even "just enter your stuff and click-bang your wordpress site is online" providers.

I hate things like phpmysqladmin. Because they lie to you, they give you a "no understanding needed, just click" feeling.
The sad truth, however, is: Things like phpmysqladmin are not a solution. They are a confession.