Difference between revisions of "ISFDB:How To Create a Public Backup File"

From ISFDB
Jump to navigation Jump to search
(+MediaWiki 1.35+ instructions)
(Removed the line about MySQl 4.1-compatible backups since we no longer generate backups in that format)
 
(One intermediate revision by the same user not shown)
Line 3: Line 3:
 
# Download the latest compressed backup file from the live ISFDB server to your development server. You will need a live server account with sufficient privileges to do this.
 
# Download the latest compressed backup file from the live ISFDB server to your development server. You will need a live server account with sufficient privileges to do this.
 
# Unzip the backup file using a standard unzip program.
 
# Unzip the backup file using a standard unzip program.
−
# Install MySQL 5.5 on your development server.
+
# Install MySQL 8.0 on your development server.
 
# Start your MySQL instance.
 
# Start your MySQL instance.
 
# Run the following MySQL commands in order to restore the database from the backup:
 
# Run the following MySQL commands in order to restore the database from the backup:
Line 11: Line 11:
 
#* source C:/ISFDB/Backups/dbdump; [where C:/ISFDB/Backups is the drive/directory where you put the unzipped ISFDB backup file]
 
#* source C:/ISFDB/Backups/dbdump; [where C:/ISFDB/Backups is the drive/directory where you put the unzipped ISFDB backup file]
 
# Run the following commands in order to delete private data:
 
# Run the following commands in order to delete private data:
−
#* drop table mw_archive;
+
drop table mw_archive;
−
#* drop table mw_blobs;
+
drop table mw_blobs;
−
#* drop table mw_brokenlinks;
+
drop table mw_bot_passwords;
−
#* drop table mw_categorylinks;
+
drop table mw_brokenlinks;
−
#* drop table mw_cur;
+
drop table mw_category;
−
#* drop table mw_externallinks;
+
drop table mw_categorylinks;
−
#* drop table mw_hitcounter;
+
drop table mw_change_tag;
−
#* drop table mw_filearchive;
+
drop table mw_change_tag_def;
−
#* drop table mw_image;
+
drop table mw_comment;
−
#* drop table mw_imagelinks;
+
drop table mw_content;
−
#* drop table mw_interwiki;
+
drop table mw_content_models;
−
#* drop table mw_ipblocks;
+
drop table mw_cur;
−
#* drop table mw_ipblocks_old;
+
drop table mw_externallinks;
−
#* drop table mw_job;
+
drop table mw_filearchive;
−
#* drop table mw_langlinks;
+
drop table mw_image;
−
#* drop table mw_links;
+
drop table mw_imagelinks;
−
#* drop table mw_linkscc;
+
drop table mw_interwiki;
−
#* drop table mw_logging;
+
drop table mw_ip_changes;
−
#* drop table mw_math;
+
drop table mw_ipblocks;
−
#* drop table mw_objectcache;
+
drop table mw_ipblocks_old;
−
#* drop table mw_oldimage;
+
drop table mw_ipblocks_restrictions;
−
#* drop table mw_page;
+
drop table mw_iwlinks;
−
#* drop table mw_page_restrictions;
+
drop table mw_job;
−
#* drop table mw_pagelinks;
+
drop table mw_l10n_cache;
−
#* drop table mw_protected_titles;
+
drop table mw_langlinks;
−
#* drop table mw_querycache_info;
+
drop table mw_links;
−
#* drop table mw_querycache;
+
drop table mw_linkscc;
−
#* drop table mw_querycachetwo;
+
drop table mw_log_search;
−
#* drop table mw_recentchanges;
+
drop table mw_logging;
−
#* drop table mw_redirect;
+
drop table mw_math;
−
#* drop table mw_revision;
+
drop table mw_module_deps;
−
#* drop table mw_searchindex;
+
drop table mw_objectcache;
−
#* drop table mw_site_stats;
+
drop table mw_oldimage;
−
#* drop table mw_templatelinks;
+
drop table mw_page;
−
#* drop table mw_text;
+
drop table mw_page_props;
−
#* drop table mw_trackbacks;
+
drop table mw_page_restrictions;
−
#* drop table mw_transcache;
+
drop table mw_pagelinks;
−
#* drop table mw_user_newtalk;
+
drop table mw_protected_titles;
−
#* drop table mw_user_rights;
+
drop table mw_querycache;
−
#* drop table mw_validate;
+
drop table mw_querycache_info;
−
#* drop table mw_watchlist;
+
drop table mw_querycachetwo;
−
#* delete from license_keys;
+
drop table mw_recentchanges;
−
#* delete from submissions;
+
drop table mw_redirect;
−
#* update mw_user set user_email='',user_real_name='',user_password='',user_newpassword='',user_options='',user_touched='',user_token='',user_email_authenticated=NULL,user_email_token=NULL,user_email_token_expires=NULL,user_registration=NULL,user_newpass_time=NULL,user_editcount=NULL;
+
drop table mw_revision;
−
#* delete from mw_user_groups;
+
drop table mw_revision_comment_temp;
−
#* delete from votes;
+
drop table mw_searchindex;
−
#* delete from user_sites;
+
drop table mw_site_identifiers;
−
#* delete from user_preferences;
+
drop table mw_site_stats;
−
#* delete from user_languages;
+
drop table mw_sites;
−
# Run the following command to create a MySQL 4.0-compatible backup file (substitute the location of your MySQL directory and your MySQL password as needed):
+
drop table mw_slot_roles;
−
#* "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" --password=XXX --user=root --compatible=mysql40 isfdb > C:\ISFDB\Backups\backup-MySQL-40-YYYY-MM-DD
+
drop table mw_slots;
−
# Run the following command to create a MySQL 5.5-compatible backup file (substitute the location of your MySQL directory and your MySQL password as needed):
+
drop table mw_templatelinks;
−
#* "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" --password=XXX --user=root isfdb > C:\ISFDB\Backups\backup-MySQL-55-YYYY-MM-DD
+
drop table mw_text;
−
# Zip up the backup files and upload them to an internet location where they can be accessed
+
drop table mw_trackbacks;
 +
drop table mw_updatelog;
 +
drop table mw_uploadstash;
 +
drop table mw_user_former_groups;
 +
drop table mw_user_newtalk;
 +
drop table mw_user_properties;
 +
drop table mw_user_rights;
 +
drop table mw_validate;
 +
drop table mw_watchlist;
 +
drop table mw_watchlist_expiry;
 +
delete from license_keys;
 +
delete from submissions;
 +
update mw_user set user_email='',user_real_name='',user_password='',user_newpassword='',user_touched='',user_token='',user_email_authenticated=NULL,user_email_token=NULL,user_email_token_expires=NULL,user_registration=NULL,user_newpass_time=NULL,user_editcount=NULL,user_password_expires=NULL;
 +
delete from mw_user_groups;
 +
delete from votes;
 +
delete from user_sites;
 +
delete from user_preferences;
 +
delete from user_languages;
  
−
== Future changes for MediaWiki 1.35+ ==
+
* Run the following command to create a MySQL 5.5-compatible backup file (substitute the location of your MySQL directory and your MySQL password as needed):
−
 
+
** "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" --password=XXX --user=root isfdb > C:\ISFDB\Backups\backup-MySQL-55-YYYY-MM-DD
−
The MySQL commands to delete private data in a MediaWiki 1.35+ database are as follows:
+
* Zip up the backup files and upload them to an internet location where they can be publicly accessed
−
 
 
−
* drop table mw_archive;
 
−
* drop table mw_blobs;
 
−
* drop table mw_bot_passwords;
 
−
* drop table mw_brokenlinks;
 
−
* drop table mw_category;
 
−
* drop table mw_categorylinks;
 
−
* drop table mw_change_tag;
 
−
* drop table mw_change_tag_def;
 
−
* drop table mw_comment;
 
−
* drop table mw_content;
 
−
* drop table mw_content_models;
 
−
* drop table mw_cur;
 
−
* drop table mw_externallinks;
 
−
* drop table mw_filearchive;
 
−
* drop table mw_image;
 
−
* drop table mw_imagelinks;
 
−
* drop table mw_interwiki;
 
−
* drop table mw_ip_changes;
 
−
* drop table mw_ipblocks;
 
−
* drop table mw_ipblocks_old;
 
−
* drop table mw_ipblocks_restrictions;
 
−
* drop table mw_iwlinks;
 
−
* drop table mw_job;
 
−
* drop table mw_l10n_cache;
 
−
* drop table mw_langlinks;
 
−
* drop table mw_links;
 
−
* drop table mw_linkscc;
 
−
* drop table mw_log_search;
 
−
* drop table mw_logging;
 
−
* drop table mw_math;
 
−
* drop table mw_module_deps;
 
−
* drop table mw_objectcache;
 
−
* drop table mw_oldimage;
 
−
* drop table mw_page;
 
−
* drop table mw_page_props;
 
−
* drop table mw_page_restrictions;
 
−
* drop table mw_pagelinks;
 
−
* drop table mw_protected_titles;
 
−
* drop table mw_querycache;
 
−
* drop table mw_querycache_info;
 
−
* drop table mw_querycachetwo;
 
−
* drop table mw_recentchanges;
 
−
* drop table mw_redirect;
 
−
* drop table mw_revision;
 
−
* drop table mw_revision_comment_temp;
 
−
* drop table mw_searchindex;
 
−
* drop table mw_site_identifiers;
 
−
* drop table mw_site_stats;
 
−
* drop table mw_sites;
 
−
* drop table mw_slot_roles;
 
−
* drop table mw_slots;
 
−
* drop table mw_templatelinks;
 
−
* drop table mw_text;
 
−
* drop table mw_trackbacks;
 
−
* drop table mw_updatelog;
 
−
* drop table mw_uploadstash;
 
−
* drop table mw_user_former_groups;
 
−
* drop table mw_user_newtalk;
 
−
* drop table mw_user_properties;
 
−
* drop table mw_user_rights;
 
−
* drop table mw_validate;
 
−
* drop table mw_watchlist;
 
−
* drop table mw_watchlist_expiry;
 
−
* delete from license_keys;
 
−
* delete from submissions;
 
−
* update mw_user set user_email='',user_real_name='',user_password='',user_newpassword='',user_touched='',user_token='',user_email_authenticated=NULL,user_email_token=NULL,user_email_token_expires=NULL,user_registration=NULL,user_newpass_time=NULL,user_editcount=NULL,user_password_expires=NULL;
 
−
* delete from mw_user_groups;
 
−
* delete from votes;
 
−
* delete from user_sites;
 
−
* delete from user_preferences;
 
−
* delete from user_languages;
 

Latest revision as of 10:10, 14 July 2023

The following steps will let you create a publicly available ISFDB backup file on a Windows-based development server.

  1. Download the latest compressed backup file from the live ISFDB server to your development server. You will need a live server account with sufficient privileges to do this.
  2. Unzip the backup file using a standard unzip program.
  3. Install MySQL 8.0 on your development server.
  4. Start your MySQL instance.
  5. Run the following MySQL commands in order to restore the database from the backup:
    • drop database isfdb;
    • create database isfdb;
    • connect isfdb;
    • source C:/ISFDB/Backups/dbdump; [where C:/ISFDB/Backups is the drive/directory where you put the unzipped ISFDB backup file]
  6. Run the following commands in order to delete private data:
drop table mw_archive;
drop table mw_blobs;
drop table mw_bot_passwords;
drop table mw_brokenlinks;
drop table mw_category;
drop table mw_categorylinks;
drop table mw_change_tag;
drop table mw_change_tag_def;
drop table mw_comment;
drop table mw_content;
drop table mw_content_models;
drop table mw_cur;
drop table mw_externallinks;
drop table mw_filearchive;
drop table mw_image;
drop table mw_imagelinks;
drop table mw_interwiki;
drop table mw_ip_changes;
drop table mw_ipblocks;
drop table mw_ipblocks_old;
drop table mw_ipblocks_restrictions;
drop table mw_iwlinks;
drop table mw_job;
drop table mw_l10n_cache;
drop table mw_langlinks;
drop table mw_links;
drop table mw_linkscc;
drop table mw_log_search;
drop table mw_logging;
drop table mw_math;
drop table mw_module_deps;
drop table mw_objectcache;
drop table mw_oldimage;
drop table mw_page;
drop table mw_page_props;
drop table mw_page_restrictions;
drop table mw_pagelinks;
drop table mw_protected_titles;
drop table mw_querycache;
drop table mw_querycache_info;
drop table mw_querycachetwo;
drop table mw_recentchanges;
drop table mw_redirect;
drop table mw_revision;
drop table mw_revision_comment_temp;
drop table mw_searchindex;
drop table mw_site_identifiers;
drop table mw_site_stats;
drop table mw_sites;
drop table mw_slot_roles;
drop table mw_slots;
drop table mw_templatelinks;
drop table mw_text;
drop table mw_trackbacks;
drop table mw_updatelog;
drop table mw_uploadstash;
drop table mw_user_former_groups;
drop table mw_user_newtalk;
drop table mw_user_properties;
drop table mw_user_rights;
drop table mw_validate;
drop table mw_watchlist;
drop table mw_watchlist_expiry;
delete from license_keys;
delete from submissions;
update mw_user set user_email=,user_real_name=,user_password=,user_newpassword=,user_touched=,user_token=,user_email_authenticated=NULL,user_email_token=NULL,user_email_token_expires=NULL,user_registration=NULL,user_newpass_time=NULL,user_editcount=NULL,user_password_expires=NULL;
delete from mw_user_groups;
delete from votes;
delete from user_sites;
delete from user_preferences;
delete from user_languages;
  • Run the following command to create a MySQL 5.5-compatible backup file (substitute the location of your MySQL directory and your MySQL password as needed):
    • "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" --password=XXX --user=root isfdb > C:\ISFDB\Backups\backup-MySQL-55-YYYY-MM-DD
  • Zip up the backup files and upload them to an internet location where they can be publicly accessed
Retrieved from "https://isfdb.org/wiki/index.php?title=ISFDB:How_To_Create_a_Public_Backup_File&oldid=667131"