|
|
| (23 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| − | [[Category:Techniques]]There are several techniques for doing this.
| + | ==About== |
| − | ==Using phpmyadmin== | + | 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 |
| − | <font size=-2>(by [[User:Aero|Aero]])</font>
| + | |
| − | ===Backing up===
| + | There is a choice of tools for doing this. |
| − | #Make sure you've got [http://www.phpmyadmin.net PHPMyAdmin] running and that's all happy. I won't cover this part here as it varies with your set up.
| + | * [[Beekeeper Studio]], but only the paid versions |
| − | #Login and choose '''Export''' from the main page
| + | * [[mysqldump]] (command-line) |
| − | #Leave most of the default options. Specifically,
| + | * [[MySQL Workbench]] (GUI utility) |
| − | #*Format: SQL
| + | * [[PHPMyAdmin]] (web-based) |
| − | #*Select the database(s) you want in the listbox
| + | * '''obsolete''': |
| − | #*etc.
| + | ** [[MySQL Admin]] (GUI utility) - this has been replaced by [[MySQL Workbench]] |
| − | #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 this hasn't been working; at first glance, it looks like it could be a permissions problem. (If you retrieve email from your localhost POP3 server, you will see error messages from the cron scheduler if it couldn't run something it was supposed to run.)
| |
| − | #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]) |