To totally unlock this section you need to Log-in
One of the most common activities for live sites, WordPress for example, is usually create a copy on a local server to test new themes, plugins, or do development testing. This allows you to set up your theme with all your content and test all the features without worrying about breaking your site. Many users copy their site to local server to practice their WordPress and coding skills with actual site data.
In case something does not work for you, then you can always manually move your live site to local server. The first thing you would need is to back up your website manually, and one of the most important thing is to do a database backup, using for example phpMyAdmin ,to properly export your WordPress database.
To export your live site’s WordPress database, you will need to log into web hosting dashboard and click, it depends on the web hosting platform, on phpMyAdmin (if available). Once entered in phpMyAdmin you will need to select the database you want to export and then click on the Export tab available on the top.
phpMyAdmin will now ask you to choose either quick or custom export method. We recommend using custom method and choosing zip as the compression method. Sometimes WordPress plugins can create their own tables inside your WordPress database, but always with the same pre-defined table prefix, by default wp_ (but this can be different if specified at WordPress installation phase).
If you are not using that plugin anymore, then the custom method allows you to exclude those tables. Leave rest of the options as they are and click on the Go button to download your database backup in zip format.
Under the custom method we recommend to check/enable the Disable foreign key checks option, to avoid possible issues during the importing process on the local system, and check/enable the Add CREATE DATABASE / USE statement option, to be able to import the SQL/ZIP database file even if we have not already pre-defined the new database in which we will import the data.
Once you have downloaded your database backup, the next step is to download your WordPress files. To do that you need to connect to your WordPress site using an FTP client like Filezilla (or WinSCP or Cyberduck, as alternatives).
Once connected to your FTP/FTPS server with your web hosting specifications (each web hosting service will have their own connection details, so none of them will be inserted in this article), you will have to download all your WordPress files and folder to your target system (this could be a laptop, a workstation, a Raspberry Pi, etc.). Downloading all your files may take a while depending on how much data you have on your website.
After downloading your WordPress files, if you downloaded all your files and not just the wp-content directory then simply copy paste these files in your local server folder where you want to transfer the live WordPress site.
In Windows, navigate to the folder where you installed XAMPP. By default, that’s C:\xampp. It should be something similar or identical for you. Then, in that folder, find the htdocs subfolder:
Now, create a new folder called whatever you want for this project. A recommendation would be to keep the name of the folder the same as the name of the database. This makes managing a ton of local installs, if needed much easier.
For this example, if you make the folder testsite, you’ll access your site by going to http://localhost/testsite (for this kind of access you will not require any change to VirtualHosts in Apache web server).
Once you create the folder, extract the WordPress folders/files file you downloaded from your web hosting into it; for example, under C:\xampp\htdocs\testsite path, in which will reside all WordPress folders and files, like wp-admin, wp-content, wp-includes, etc.
MySQL Database Preparation
On your local server, you need to create a database for your WordPress site (unless you have checked the Add CREATE DATABASE / USE statement option during the export process).
To do that you need to open http://localhost/phpmyadmin/ in your web browser and create a new database. After creating the database click on the Import tab on top. On the next screen you need to click on the Choose File button to select the WordPress database export file you downloaded earlier, and then click on the Go button. phpMyAdmin will now import your database and will show you a success message when it is done.
You will see the result indicating how many lines are changed in the tables after the successful execution of the query.
Once the importing is completed you will be redirected to the database page which shows all the tables migrated into your localhost mySQL server.
After the database import, we will need also to create and link a proper user to manage the new imported DB. So we will need to go, always in phpMyAdmin, after we have clicked on our new database on the left menu, to Privileges tab and then click on Add user account (in this way we will create a specific login only that database:
Once done you will get, in the new window, an option similar to Grant all privileges on database {database}.: check this option and click Go. Once done that we will have a new user, linked only to our database, with the following privileges (thay are good for our local environment).
NOTE: obiously, username and password of this Native MySQL authentication will need to be the same as specified in our wp-config.php for WordPress to correctly access the database.
XAMPP - Uploading large .sql database dump file
To be able to upload large .sql files into XAMPP platform we need to change PHP and phpmyAdmin config files to allow long execution times and large file size; now let's apply in php.ini configuration file, for example, the following modifications (on modern laptops/desktops systems you should not have any problem changing and applying the following values):
post_max_size = 750M upload_max_filesize = 750M max_execution_time = 5000 max_input_time = 5000 memory_limit = 1000M
Then restart XAMPP for the changes to take effect: it will take few seconds.
If you get following error:
Script timeout passed. If you want to finish import please resubmit same zip file and import will resume. OR Fatal error: Maximum execution time of 300 seconds exceeded in C:\xampp\phpMyAdmin\libraries\import\sql.php on line 129
We will have to go in phpMyAdmin folder, and search and modify the file phpMyAdmin\libraries\config.default.php as the following:
/** * maximum execution time in seconds (0 for no limit) * * @global integer $cfg['ExecTimeLimit'] */ $cfg['ExecTimeLimit'] = 0;
Once done, just refresh the phpMyAdmin portal and retry the import of the large .sql dump file of your database.
XAMPP - phpMyAdmin - Error (near "ON" at position 25)
During the import of "large" databases, greater than 100MB, it could happen that phpMyAdmin will raise the following exception, or something similar (and will block the import process, leaving only a portion of all the tables that should be imported into the target local database): phpMyAdmin - Error (near "ON" at position 25).
In this case we will need to adjust only one line in my.ini (mySQL database engine configuration file), by changing the configuration file at C:\xampp\mysql\bin\my.ini or clicking on Config button for MySQL role in XAMP > MySQL > my.ini and finally change (or add) the following parameter and value, under [mysqld] section::
max_allowed_packet = 50M // the value depends on the size of the .sql files you are trying upload.
Modifying internal URLs in the mySQL DB
Now that your database is all set up, you need to update the URLs inside your WordPress database referencing to your live site.
You can do this by running a SQL query, using always phpMyAdmin. Make sure you have selected your local site’s database and then click on SQL. In phpMyAdmin’s SQL screen copy and paste the following code, make sure that you have replaced example.com with your live site’s URL and localhost/test-site with the local server URL of your site and also https:// with http:// if you will not enable TLS in localhost (and this make sense as you are doing a backup and/or development copy, and you will not do something so stupid as publishing it on Internet, right?) and/or the DB prefix wp_ with your own prefix, obviously.
UPDATE wp_options SET option_value = replace(option_value, 'http://www.example.com', 'http://localhost/test-site') WHERE option_name = 'home' OR option_name = 'siteurl'; UPDATE wp_posts SET post_content = replace(post_content, 'http://www.example.com', 'http://localhost/test-site'); UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.example.com','http://localhost/test-site');
This query will replace refences to your live site’s URL from database and replace it with the localhost URL.
The final step is to update your local site’s wp-config.php file. You need to go to the folder where you installed WordPress on your local server and open wp-config.php file in a text editor like Notepad or Notepad++.
Replace database name with the one you created in phpMyAdmin on your localhost (unless you have left the same DB name, as usually should be, to not introduce so many differences between your backup and live site). Replace the username with your local mysql username, usually it is root (but it is highly recommend to dedicate a specific user to the DB of the site and not using root user. If you have set password for mySQL user root on your localhost, then enter that password. Otherwise leave it empty and save your changes.
/** The name of the database for WordPress */ define('DB_NAME', 'database_name_here'); /** MySQL database username */ define('DB_USER', 'username_here'); /** MySQL database password */ define('DB_PASSWORD', 'password_here'); /** MySQL hostname */ define('DB_HOST', 'localhost');
That’s all your live site is now copied to your local server, and you are ready to test drive it.
Test the WordPress Localhost Website
Hit the localhost url or the virtual-domain url on your browser, you should see the wordpress site successfully migrated. Test the following things to make sure the migration is successful.
- Home Page
- Image URL Testing
- Page and Post URL’s
- Admin Login
Moving a live WordPress site on localhost can be very useful for development and testing, and specially for backup (never to forget) – https://www.heelpbook.net/2020/manually-move-wordpress-site-on-localhost-xampp/ #heelpbook #wordpress #howto