Difference between revisions of "MySQL/migration"
< MySQL
Jump to navigation
Jump to search
m (belated post-import cleanup) |
|||
| Line 1: | Line 1: | ||
| − | [[Category:Techniques]] | + | [[Category:Techniques]]There are several techniques for doing this. |
| − | There are several techniques for doing this. | + | ==Using phpmyadmin== |
| − | ==Using phpmyadmin <font size=-2>(by | + | <font size=-2>(by [[User:Aero|Aero]])</font> |
| − | [[User:Aero|Aero]])</font> | ||
===Backing up=== | ===Backing up=== | ||
| − | #Make sure you've got [http://www.phpmyadmin.net PHPMyAdmin] running | + | #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. |
| − | and that's all happy. I won't cover this part here as it varies with | ||
| − | your set up. | ||
#Login and choose '''Export''' from the main page | #Login and choose '''Export''' from the main page | ||
#Leave most of the default options. Specifically, | #Leave most of the default options. Specifically, | ||
| Line 12: | Line 9: | ||
#*Select the database(s) you want in the listbox | #*Select the database(s) you want in the listbox | ||
#*etc. | #*etc. | ||
| − | #Checkmark 'Add DROP TABLE'. This will make sure that when you restore | + | #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. |
| − | from a backup, it will delete existing tables before restoring from the | + | #Checkmark 'Save to file' at the bottom, or it will just dump it to your browser window, and that's no fun. |
| − | backed up ones. | + | #*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. |
| − | #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'''. | #Click '''Go'''. | ||
===Restoring from backup=== | ===Restoring from backup=== | ||
| − | ''I'm writing this from memory, so I might be wrong, but I '''think''' | + | ''I'm writing this from memory, so I might be wrong, but I '''think''' it's right.'' |
| − | it's right.'' | ||
#Click '''Databases''' from the PHPMyAdmin main page | #Click '''Databases''' from the PHPMyAdmin main page | ||
| − | #Click the name of your databse (it should already exist; have you | + | #Click the name of your databse (it should already exist; have you [re]installed [http://www.mediawiki.org MediaWiki] yet? Do that first.) |
| − | [re]installed [http://www.mediawiki.org MediaWiki] yet? Do that first.) | ||
#Click the '''SQL''' tab | #Click the '''SQL''' tab | ||
| − | #Where it says '''Location of textfile:''', browse to your latest | + | #Where it says '''Location of textfile:''', browse to your latest backup, then click '''Go'''. |
| − | backup, then click '''Go'''. | + | ==Using mysql-admin== |
| − | ==Using mysql-admin <font size=-2>(by | + | <font size=-2>(by [[User:Woozle|Woozle]])</font> |
| − | [[User:Woozle|Woozle]])</font> | ||
===Backing up=== | ===Backing up=== | ||
| − | #Choose a place on your hard drive for storing the file; create any | + | #Choose a place on your hard drive for storing the file; create any folders needed. |
| − | folders needed. | ||
#Run mysql-admin (this can be done from the command line) | #Run mysql-admin (this can be done from the command line) | ||
| − | #Connect to mysql. You'll need the server address, a username with read | + | #Connect to mysql. You'll need the server address, a username with read permissions, and the password for that user. |
| − | 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. |
| − | #*Note: mysql-admin seems to have trouble actually using passwords | + | #*Another note: the current version of mysql-admin doesn't like connecting to mysql versions prior to 4.0, but it did connect and |
| − | you've stored, so you may have to type in the password each time. | + | backup my v3.23. It appears prone to sudden crashing on certain operations, so avoid poking around in the menu while backing up or |
| − | 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. | restoring. | ||
#Press the "backup" icon/button on the left. | #Press the "backup" icon/button on the left. | ||
| − | #Check the 'Add DROP TABLE' box on the "Output File Options" box on the | + | #Check the 'Add DROP TABLE' box on the "Output File Options" box on the "Advanced Options" tab, as with the phpmyadmin backup. |
| − | "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.) |
| − | #Steps from that point on are pretty intuitive, except for the | + | #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. |
| − | scheduler (which still has me confused). Apparently mysql-admin can set | ||
| − | itself up to do backups automatically, using the cron scheduler, but | ||
| − | |||
| − | #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=== | ===Restoring=== | ||
| − | I haven't tried this yet, but there's a "restore backup" button right | + | 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. |
| − | 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] | *[http://members.lycos.co.uk/wipe_out/automysqlbackup/ AutoMySQLBackup] | ||
Revision as of 01:58, 21 December 2005
There are several techniques for doing this.
Using phpmyadmin
(by Aero)
Backing up
- Make sure you've got PHPMyAdmin running and that's all happy. I won't cover this part here as it varies with your set up.
- 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 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
(by Woozle)
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.