|
|
| (7 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| | ==About== | | ==About== |
| − | This subpage is about porting data into and out of [[MySQL]] databases. | + | 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 are several techniques for doing this.
| |
| − | ==Using phpmyadmin (web-based)==
| |
| − | <font size=-2>(by [[User:Aero|Aero]])</font>
| |
| − | ===Backing up===
| |
| − | #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.
| |
| − | #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'''.
| |
| − | ====Notes====
| |
| − | Some installations of phpmyadmin may have an upper size-limit on imports – e.g. at l48.net it's 2 MB, which basically means I can't use it to restore any of my wikis.
| |
| − | ==Using mysql-admin (GUI utility)==
| |
| − | ===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===
| |
| − | There's a "restore backup" button right under the "backup" button, and I presume the process is also fairly intuitive. Unfortunately, on my system (Ubuntu 4.x), pressing this button instantly causes mysql-admin to close, with no warning (presumably a crash/bug). This may be a problem with Ubuntu, as I have had similar problems with other applications that worked fine in other versions of Linux (I have not tried testing mysql-admin in other Linux distros). --[[User:Woozle|Woozle]] 14:04, 8 May 2006 (EDT)
| |
| − | ==Using mysql (command-line)==
| |
| − | ===Backing Up===
| |
| − | * Official MySQL documentation: [http://dev.mysql.com/doc/refman/5.5/en/backup-and-recovery.html Chapter 6. Backup and Recovery]
| |
| − | From a shell command line:
| |
| − | ;mysqldump --tab=<u>/output/path</u> -h<u>hostname</u> -u<u>username</u> -p<u>password</u> --opt <u>dbname</u>:Saves SQL code to recreate the entire database in an .sql file compatible with the restoring method given below. If you leave off the <u>password</u> (as in the next example), it will prompt you for one. To connect to a MySQL server on localhost, omit -h<u>hostname</u>. '''not tested'''
| |
| − | ;mysqldump --tab=<u>/output/path</u> -h<u>hostname</u> -u<u>username</u> -p --opt <u>dbname</u> <u>tablename</u> {{optargs|<u>anothertablename</u> ...}}:Saves SQL code for particular tables
| |
| − | | |
| − | For more specific backups, the following mysql client commands are relevant:
| |
| − | ;SHOW CREATE DATABASE <u>dbname</u>;:Returns the SQL code which could be used to recreate the database, ''not'' including the contents.
| |
| − | ;SHOW CREATE TABLE <u>tablename</u>;:Returns the SQL code which could be used to recreate <u>tablename</u>, ''not'' including the data.
| |
| − | | |
| − | ===Restoring===
| |
| − | If you are restoring to a different database name (as might happen if you were moving the db to a new server), you will need to edit the .sql file to use the appropriate database name in the line which begins "CREATE DATABASE" and the "USE" line following it. If you know the database has already been created, you can just delete the CREATE DATABASE line.
| |
| − | | |
| − | You might also want to verify that the mysql utility connects successfully before you try the import:
| |
| − | mysql -h<u>server</u> -u<u>username</u> -p<u>password</u>
| |
| − | If that works, then the full command to create a database from a .sql file is:
| |
| − | mysql -h<u>server</u> -u<u>username</u> -p<u>password</u> < <u>path/to/backup/file.sql</u>
| |
| − | Note that the path is relative to the system on which you are running the mysql utility, ''not'' the system running the mysql server (mysqld). Note also that this technique doesn't tell you anything until it finishes processing the .sql file.
| |
| − | You can also specify the database in which the SQL commands should be run:
| |
| − | mysql -h<u>server</u> -u<u>username</u> -p<u>password</u> -D<u>dbname</u> < <u>path/to/backup/file.sql</u>
| |
| | | | |
| | + | There is a choice of tools for doing this. |
| | + | * [[Beekeeper Studio]], but only the paid versions |
| | + | * [[mysqldump]] (command-line) |
| | + | * [[MySQL Workbench]] (GUI utility) |
| | + | * [[PHPMyAdmin]] (web-based) |
| | + | * '''obsolete''': |
| | + | ** [[MySQL Admin]] (GUI utility) - this has been replaced by [[MySQL Workbench]] |
| | ==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]) |