Copying Wordpress installations with phpmyadmin
So this should be fairly easy according to various guides, but it is not.
The goal is to create a test installation of Wordpress on the server, so we can play around with it before moving any changes to the live server.
First step, backup everything!
It is very easy to fuck something up, so you always want to backup stuff. Just in case. To do this, open the FTP and copy all the files. Make sure it was done correctly. One idea is to do it twice and examine the folders. They should be identical (assuming no activity on the site in the meanwhile).
Next you go to the myphpadmin login site. I am using Unoeuro (great host, recommended), so mine is e.g. here: https://mysql29.unoeuro.com
Go to the database (you can only have one on Unoeuro, it is named after the domain). Then click export, and quick settings.
Copying and fixing the database
Wordpress runs of the mySQL database, and two WP installations need their own. To do this, go to the main overview of tables (structure-tab). Then mark the tables WP uses.
Then scroll to the bottom and choose "copy with prefix" in the drop-down menu. Then add the prefix of the tables and the desired prefix of the copies.
You now have a duplicate table set. But wait! In my case, the tables are not copied correctly. The encoding "collation" and type for some reason change to some other stuff. You need to change them back. To do this, go into each table and choose Opernations-tab. Then change the values so they match the tables from the original WP installation. In my case, to utf8_general_ci and MyISAM.
Do this for each table (there were 11 for me).
Now, we are done? Nope! Next you need to edit the table to tell WP on which domain it is located (for use in relative links presumably). These settings are under wp_options. For some reason there are two identical ones "siteurl" and "home". These both need to be change to the new URL. In my case http://openpsych.net/test/OBG from http://openpsych.net/OBG.
No edit rights?
However, it wasn't possible for me to edit them. The error was that no column was set as the uniquely identifying one. To resolve this, go to the SQL-tab and type:
ALTER TABLE `test_journal_OBG_wp_options` ADD UNIQUE (`option_id`)
This sets column "option_id" as the unique column in table "test_journal_OBG_wp_options". Switch these values with yours. You need to do this for the unique column in every table you want to edit. WP did not make it simple because the unique ID column does not have the same name for each table either, but they are easy to identity.
And then you need to edit the two lines in wp_options.
You do not have sufficient permissions to access this page
Aaaand then, you are not done. When I tried to login, I got an error "You do not have sufficient permissions to access this page.". Some googling around (1, 2) did not solve the problem for me, but it did suggest the solution to me. The trouble is that within the tables, WP cross-refers to other tables. But these cross-references have not had the prefix added! So you need to find all of them and add it. You need to do that under wp_usermeta and wp_options. I did not find an automatic search and replace, but there are only a few places it needs to be done. Unless you have a lot of users, since you need to do it a few times for every user. Hope you find the research&replace command!
Uploading the copied Wordpress
Next step is making a copy of the Wordpress folder you want to copy. Then you go to the wp-config.php file and find the line "$table_prefix = 'test_journal_OBG_wp_';". Change this line to the prefix you have chosen and save.
Then upload the WP folder into the correct subfolder on the FTP server and login.
I did not do things in the order above, but it seems the most effective. I may have also forgotten some things I tried. This is just to serve as a reference for those who have similar problems.