Neil Padgett's picture

Running the latest LAMP on Virtualbox. With Adminer, I can't see any links/buttons to use to import a database.

Am I missing something, or isn't import available with Adminer 3.3.3?

Thanks.

Forum: 
Jeremy Davis's picture

When you do that, in the right pane you will see a big text box where you can copy/paste SQL commands. Directly below that there is a box to upload a SQL file; below that there is an option to execute a file that already exists on your server.
Lee Crawford's picture

I'm attempting to export a wordpress database from an older Turnkey Wordpress appliance and import it into a freshly deployed app.  My exported SQL db is about half a GB, so I need to upload it.  I have so far been unable to find the upload box as described.  Adminer 4.2.5 I go to "SQL Command" on the left, I see the box to type SQL commands, and beneath it I only see "Execute" (button), "Limit Rows:" (form textbox), "Stop on error" (checkbox), and "Show only errors" (checkbox).  No box for uploading here where you (as well as the guy on the Adminer forums) describe.

If I  click "Import" instead of "SQL Command", I  have a "Choose files" button to facilitate a small  upload ("< 16MB") and a button to "run file" (with un-editable text indicating I have to use the filename "adminer.sql[.gz]".  I have used WinSCP & SFTP to upload my obediently-named adminer.sql and even adminer.sql.gz, and pressing that "Run file" button only gives me the red text: "File does not exist."  I  have tried placing the file(s) into root, /etc/, and /etc/adminer/, and it doesn't seem to find the file.  I also supposed that maybe it simply wasn't showing up in Chrome, so I tried Firefox, with the same results.

Jeremy Davis's picture

As I don't have Adminer in front of me right now, I can't be 100% sure, but it does sound like things have changed since my previous post. It's sounds like the options are now under "Import"?! The limitation to 16MB, is most likely coming from the PHP config, so updating to a (much) larger "max upload" (and restarting the webserver) may well allow you to upload your file (although perhaps Adminer also creates a limit).

Re using the Adminer "run" functionality, a few things come to mind. First up, judging by the filename, I assume that you have gzipped the file? If not, please change the name back to .sql.

Next up, for security, Adminer runs under the webserver user account (www-data). So the file will need to be somewhere where Adminer can look (i.e. that www-data has access to), as well as have the appropriate permissions to do something with it. By default, files you upload using the root account will be owned by root.

Personally, I'd recommend that you just use the commandline. But I get that many aren't super keen to do that. If that is an option for you and assuming that you are using a v15.x TurnKey appliance, and depending on how you created the sql dump, something like this should work:

mysql -u root db_name 

If for some reason that fails, please feel free to post back with the command you used and the result. Also if you can tell me which appliance this relates to, the version you are coming from and the version you are going to, hopefully I should be able to point you in the right direction. In the meantime, I just did a quick google and turned up this. The question relates to MySQL commandline on Windows, but a quick browse through the answers suggests that commands are generic and should work on Linux too.

You may also need to make some other adjustments to get everything to work (including adding a user, updating passwords, etc). So for assistance with that, again, I'll need more info...

If you'd rather persevere with Adminer, then please read on...

As a general rule, the www-data user has full access to the /var/www directory and all sub-directories below that. So that's probably the easiest place to put your file. Although please note that if you are using the LAMP, Nginx or LigHTTPd appliances (or any appliance that has been configured to have access to /var/www as it's doc root), that file will now be downloadable by anyone! If that's the case, putting it somewhere else (and ensuring that www-data has full access) would be a better idea. If that's the case, then possibly the best bet is to rename your file to a random name so it's unlikely that anyone will stumble across it. E.g.

mv /var/www/adminer.sql.gz /var/www/$(mcookie).sql.gz

Then to get the new filename:

ls /var/www/*.sql.gz

You'll also need to ensure that the www-data user has access to the file. The easiest way to do that is transfer ownership to that user. From the commandline:

chown www-data /path/to/file.sql.gz

Hope that helps... If not, please post back and I'll respond ASAP. If you want to give me some furhter background on exactly what you're up to so I can give more specific advice, it's probably best to open a new thread (feel free to post a link to your new thread in a post here).

Lee Crawford's picture

I only didn't go with the CLI approach because I'm not fluent with doing MySQL imports.  Now that you've posted the command, I could just as easily do that.  I did export the existing database as both .SQL and .SQL.GZ, just in case one worked better.  I tried putting my imports into the /var/www/ folder and every subfolder inside, and even checked that www-data also had permissions to the files.  Nothing I tried made the Import button work, so let's try that command:

<code>mysql -u root < /adminer.sql (yeah, I stuck the import in root)</code>

After a brief pause while it imported, Adminer shows all the database tables that existed on my existing Wordpress appliance.  The default webpage is still default, but rebooting the appliance...  well, broke the Wordpress part, ("Error establishing a database connection"), so I probably should have imported one db at a time.  Good thing it's an easily redeployable appliance!

My question here was specifically how to get the import to work, and you've done a beautiful job of explaining the better way to accomplish the task and I'm confirming it worked, so any further questions or comments I may have around the Wordpress appliance do belong in a separate thread, once I've got some free time to poke at it and report back.  Thanks again!

Jeremy Davis's picture

As you loaded the DB via the root account, my guess is that the error is because the wordpress DB user doesn't have permission to access it. That is easily fixed, should be something like this:

mysql --user=root  --execute "GRANT ALL PRIVILEGES ON dbname.* TO dbuser"

Assuming TurnKey defaults; the database is called "wordpress" and the username is also "wordpress". It should be something like this (using the short options this time):

mysql -u root  -e "GRANT ALL PRIVILEGES ON wordpress.* TO wordpress"

Obviously, you'll need to make sure to adjust the DB name and DB user if they aren't both "wordpress".

If you continue to have issues connecting to the DB, ensure that it is working and the next thing to try is resetting the DB user password (both in MySQL and in WordPress config).

If this is from a TurnKey WordPress appliance (or at least a TurnKey appliance that shipped with MySQL/MariaDB) then you can at least partially use the following snippet to regenerate a random user password. This is assuming default TurnKey WordPress DB username ('wordpress') and WordPress install path (/var/www/worpress). You can adjust those if need be:

# set random password
PASSWORD=$(mcookie)
# set config file location
CONF=/var/www/wordpress/wp-config.php
# leverage TurnKey inithooks to reset the "wordpress" DB user password
/usr/lib/inithooks/bin/mysqlconf.py --user=wordpress --pass="$PASSWORD"
# update the password in the WP config file
sed -i "s/\(.*\)$1\(.*\)');/define('$1', '$2');/;" $CONF
Neil Padgett's picture

Thanks for your help.

Jeremy Davis's picture

Please read my post above on how to import with Adminer...

Add new comment