|
|
| (25 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| [[Category:Techniques]]
| | ==About== |
| There are several techniques for doing this.
| | This subpage is about migrating data between [[MySQL]] databases, i.e. backing up data to independent files, and restoring the data from those files back into a database |
| ==Using phpmyadmin <font size=-2>(by | | |
| [[User:Aero|Aero]])</font>== | | There is a choice of tools for doing this. |
| ===Backing up===
| | * [[Beekeeper Studio]], but only the paid versions |
| #Make sure you've got [http://www.phpmyadmin.net PHPMyAdmin] running
| | * [[/CLI/]]: command-line interface |
| and that's all happy. I won't cover this part here as it varies with
| | * [[MySQL Workbench]] (GUI utility) |
| your set up.
| | * [[PHPMyAdmin]] (web-based) |
| #Login and choose '''Export''' from the main page
| |
| #Leave most of the default options. Specifically,
| |
| #*Format: SQL
| |
| #*Select the database(s) you want in the listbox
| |
| #*etc.
| |
| #Checkmark 'Add DROP TABLE'. This will make sure that when you restore
| |
| from a backup, it will delete existing tables before restoring from the
| |
| backed up ones.
| |
| #Checkmark 'Save to file' at the bottom, or it will just dump it to
| |
| your browser window, and that's no fun.
| |
| #*You probably also want to use compression. These won't be huge files,
| |
| but a 1.5mb database compresses to about 150kb. So, y'know, whatever.
| |
| Wiggy recommends BZIP.
| |
| #Click '''Go'''.
| |
| ===Restoring from backup===
| |
| ''I'm writing this from memory, so I might be wrong, but I '''think'''
| |
| it's right.''
| |
| #Click '''Databases''' from the PHPMyAdmin main page
| |
| #Click the name of your databse (it should already exist; have you
| |
| [re]installed [http://www.mediawiki.org MediaWiki] yet? Do that first.) | |
| #Click the '''SQL''' tab
| |
| #Where it says '''Location of textfile:''', browse to your latest
| |
| backup, then click '''Go'''.
| |
| ==Using mysql-admin <font size=-2>(by
| |
| [[User:Woozle|Woozle]])</font>== | |
| ===Backing up===
| |
| #Choose a place on your hard drive for storing the file; create any
| |
| folders needed.
| |
| #Run mysql-admin (this can be done from the command line)
| |
| #Connect to mysql. You'll need the server address, a username with read
| |
| permissions, and the password for that user.
| |
| #*Note: mysql-admin seems to have trouble actually using passwords
| |
| you've stored, so you may have to type in the password each time.
| |
| Hopefully they will fix this soon.
| |
| #*Another note: the current version of mysql-admin doesn't like
| |
| connecting to mysql versions prior to 4.0, but it did connect and
| |
| backup my v3.23. It appears prone to sudden crashing on certain
| |
| operations, so avoid poking around in the menu while backing up or
| |
| restoring.
| |
| #Press the "backup" icon/button on the left.
| |
| #Check the 'Add DROP TABLE' box on the "Output File Options" box on the
| |
| "Advanced Options" tab, as with the phpmyadmin backup.
| |
| #Steps from that point on are pretty intuitive, except for the
| |
| scheduler (which still has me confused). Apparently mysql-admin can set
| |
| itself up to do backups automatically, using the cron scheduler, but I
| |
| don't yet know if this works or if I did it right.
| |
| #The first time through, you will probably want to save the backup
| |
| configuration to a Backup Project, for quick reuse later. I had trouble
| |
| once, however, with the "Backup Now" button greying out after doing
| |
| this; I'm not sure what caused it.
| |
| ===Restoring===
| |
| I haven't tried this yet, but there's a "restore backup" button right
| |
| under the "backup" button, and I presume the process is also fairly
| |
| intuitive.
| |
| ==Related Resources== | | ==Related Resources== |
| *[http://members.lycos.co.uk/wipe_out/automysqlbackup/ AutoMySQLBackup] | | * [https://sourceforge.net/projects/automysqlbackup/ AutoMySQLBackup] ([https://web.archive.org/web/20081218005549/http://members.lycos.co.uk/wipe_out/automysqlbackup/ origin story]) |
About
This subpage is about migrating data between MySQL databases, i.e. backing up data to independent files, and restoring the data from those files back into a database
There is a choice of tools for doing this.
