Difference between revisions of "Sysadmin:Sysadmin Info"
Greg.Fuller (talk | contribs) (→Restoring or Migrating a Database) |
Greg.Fuller (talk | contribs) (→Move [root]/jfa/backups offsite) |
||
| (6 intermediate revisions by the same user not shown) | |||
| Line 7: | Line 7: | ||
* Setup cache, use memcache? $wgMainCacheType = | * Setup cache, use memcache? $wgMainCacheType = | ||
* Setup email on jfawiki provisional production. | * Setup email on jfawiki provisional production. | ||
| − | |||
| − | |||
| − | |||
== Handy Links == | == Handy Links == | ||
| Line 56: | Line 53: | ||
This is untried, and comes from here: https://stackoverflow.com/questions/44015692/access-denied-you-need-at-least-one-of-the-super-privileges-for-this-operat | This is untried, and comes from here: https://stackoverflow.com/questions/44015692/access-denied-you-need-at-least-one-of-the-super-privileges-for-this-operat | ||
| − | ==== | + | ==== Migrating a Database from Production to Development ==== |
| − | |||
| − | |||
| − | |||
| − | |||
| − | + | Here is a sample sequence for the common activity of dumping the production database and loading into the development database. <span style="color:red">Never go in the opposite direction! </span>(unless it's an emergency situation) You will need the production database password and the development database password to perform these tasks. The "--default-character-set=binary" parameter on the dump is critical. | |
| − | + | Note: The production database is named ''jfawiki'' and the development database is named ''djfawiki''. | |
Steps: | Steps: | ||
| Line 72: | Line 65: | ||
* Backup the production database | * Backup the production database | ||
| − | ** >mysqldump -h localhost -u jfawiki - | + | ** >mysqldump -h localhost -u jfawiki --password=thePRODUCTIONdbpassword --default-character-set=binary jfawiki > jfawiki-mysqldump-YYYY-MM-DD.sql |
* Fix the sql dump file | * Fix the sql dump file | ||
| − | ** >sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i jfawiki-mysqldump- | + | ** >sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i jfawiki-mysqldump-YYYY-MM-DD.sql |
*Restore into DEVELOPMENT | *Restore into DEVELOPMENT | ||
| − | ** >mysql -u djfawiki - | + | ** >mysql -u djfawiki --password=theDEVELOPMENTdbpassword djfawiki < jfawiki-mysqldump-YYYY-MM-DD.sql |
=== Configure the Parsoid Server for the Visual Editor === | === Configure the Parsoid Server for the Visual Editor === | ||
| Line 88: | Line 81: | ||
=== Move [root]/jfa/backups offsite === | === Move [root]/jfa/backups offsite === | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
Latest revision as of 09:31, 3 March 2019
To Do
- Write instructions for backing up database via ssh into [root]/jfa/backups/db with date suffix "-yyyy-mm-dd". See below howtos.
- Write and install cron job to backup database daily into [root]/jfa/backups/db with date suffix "-yyyy-mm-dd".
- Setup cache, use memcache? $wgMainCacheType =
- Setup email on jfawiki provisional production.
Handy Links
- Webfaction Failover https://blog.gingerlime.com/2012/webfaction-fail-over/
How To
SSH
>ssh username@web542.webfaction.com >cd /home/gregfuller/webapps/jfawiki/ (for wiki.justiceforanimals.org)
Dump XML pages
>cd /home/gregfuller/webapps/jfawiki/maintenance >php72 dumpBackup.php --current >/home/gregfuller/webapps/jfawiki/jfa/backups/xml/jfawikidump-yyyy-mm-dd.xml FTP the file down to localhost
Database Backup and Restore
Backup Dababase into [root]/jfa/backups/db
Use mysqldump to back up the database. Do not use phpMyAdmin to backup the database
Change directory to jfa/backups/db so the dump will go into this directory, or change the command below
mysqldump -h localhost -u dbuser -p --default-character-set=binary dbname > dbname-mysqldump-YYYY-MM-DD.sql
For production
- dbname is jfawiki and the user is jfawiki.
For dev
- dbname is djfawiki and the user is djfawiki
See this: https://www.mediawiki.org/wiki/Manual:Backing_up_a_wiki
Preparing the Database Dump for import
If you do not do this step before importing, you will get
"ERROR 1227 (42000) at line 1909: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
You must remove the "DEFINER=" statement from the dump—each entire line. You can do this with a text editor, or with a sed:
sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i oldfile.sql
This is untried, and comes from here: https://stackoverflow.com/questions/44015692/access-denied-you-need-at-least-one-of-the-super-privileges-for-this-operat
Migrating a Database from Production to Development
Here is a sample sequence for the common activity of dumping the production database and loading into the development database. Never go in the opposite direction! (unless it's an emergency situation) You will need the production database password and the development database password to perform these tasks. The "--default-character-set=binary" parameter on the dump is critical.
Note: The production database is named jfawiki and the development database is named djfawiki.
Steps:
- Change directory to where you want the sql dump to go (here it's put it in the development site for more convenient FTP downloading)
- >cd /home/gregfuller/webapps/djfawiki/jfa/backups/db
- Backup the production database
- >mysqldump -h localhost -u jfawiki --password=thePRODUCTIONdbpassword --default-character-set=binary jfawiki > jfawiki-mysqldump-YYYY-MM-DD.sql
- Fix the sql dump file
- >sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i jfawiki-mysqldump-YYYY-MM-DD.sql
- Restore into DEVELOPMENT
- >mysql -u djfawiki --password=theDEVELOPMENTdbpassword djfawiki < jfawiki-mysqldump-YYYY-MM-DD.sql
Configure the Parsoid Server for the Visual Editor
Place this in the LocalSettings.php file:
$wgVirtualRestConfig['modules']['parsoid'] = array ( 'url' => 'http://server3.mywikis.net:8142', );Move [root]/jfa/backups offsite
