|
|
| (24 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
| + | * [[mysqldump]] (command-line) |
| − | 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
| + | * '''obsolete''': |
| − | #Leave most of the default options. Specifically,
| + | ** [[MySQL Admin]] (GUI utility) - this has been replaced by [[MySQL Workbench]] |
| − | #*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]) |