New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
MySQL databases, phpMyAdmin, exporting and importing = CORRUPTED :(
AdventureTime
Member
in Help
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.
Comments
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
@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 :-(
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?Thanks for the tip. But how? I prefer to use phpMyAdmin?
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.
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?
also make sure that you are allowed to upload the complete file. if it is to large, the import will break.
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):
It will come in very handy.
@AdventureTime
On origin
On new server
If you prefer to do one db at a time, replace --all-databases with db name
For uploading the file, I use
SCP
for it.Thank you, but I have no idea what those commands mean. It's really not that user-friendly
Thank you, I'll try this one.
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.
Update:
The dumping took more than 30 minutes. Then I saw an error message:
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?
Importing and Exporting were both done via phpMyAdmin.
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?
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
Importing...
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.
Thank you isn't there suppose to be a space after
-u
then enter the username?don't have to be
If you can, install Heidi SQL or DBeaver on your computer. Better solution than PHPMyAdmin. Also much faster to use. Can recommend.
Fixed a typo above as well, somehow didnt remove --all-databases when typing new command
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.
Does it work on a Mac?
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:
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.
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
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
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?
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.
Okay, so how can I do this?
How can I make it like to grant the specific user to all of the databases so that I will be avoiding root?
Run the same command for each database?
If you give one user access to all databases, you might as well just use root. (?)
Okay, well I've tried to use
zPanel
andVestaCP
. 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 currentMySQL 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-databaseI 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 theSQL
files that are stored in the server.Still, it doesn't work.
WordPress
works, I edited the configuration and entered the properMySQL login credentials
.. The location of theWordPress
website isdomain.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 stuffchown
andchmod
but they were okay and was able to match the sample pages of the control panel software. But it still didn't work. For theWordPress
thing, at least it is easy to transfer with less modifications, but for theJoola
andSugarCRM
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 butJoomla
andSugarCRM
are really not working. They just love to display blank pages even though I have entered the properMySQL
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
andupgrade
everything and just installVarnish
.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-goHowever, contrary to a root account, that one user cannot create new dbs, modify users/etc
still has access to old server & running? and new server to move on? just copy phpmyadmin folder ;p but lot things to do
@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.