Support Home | Knowledge Base Home | Submit Article | Search Articles | Browse Articles  Backing Up a mySQL Database Via the Commandline With mysqldump  Description: A fairly detailed explanation of how to log onto a [hosted] server and use mysqldump to backup a mySQL database. Author: bleak Date: Fri Aug 01, 2003 9:10 pm Type: HowTo Keywords: mySQL, mysqldump, database, backup, commandline, SSH, shell Category: Administrating Backing Up a mySQL Database Via the Commandline With mysqldump Overview: This article is an attempt at a detailed explanation of how to backup a phpBB database via your server/host's commandline. This article is focused mainly on *nix (linux/unix) users, but may prove useful to others as well. Please note that a lot of hosting services do not allow access to their servers' commandlines, and many more make this service part of a "premium" package that usually costs more money per billing cycle. Therefore, those of you who pay this extra money, and those who run their own servers, will benefit most from this article. Also note that this article is geared specifically toward users of mySQL. Those of you who use Postgre, Oracle, etc, or ODBC drivers to go through MS Access will require a bit of adaptation. What You Will Need: For the procedures described below, you will need: 1.) A way in which to access the commandline of your [host's] server. This can be accomplished using direct access to the server terminal, telnet, or SSH. SSH is usually the preferred method. Some hosts even disable telnet access altogether in favor of SSH. As of this writing, the latest version of SSH is 3.2.5 and it can be downloaded from the SSH website. You will want to download the client program. Logging Onto the Server Using SSH Once you have downloaded and installed SSH (or, if you already have it installed), run the SSH executable. A shortcut ("SSH Secure Shell Client") is usually made on the desktop during the install process. If a shortcut was not created, you should be able to find the program under "Start -> Programs -> SSH Secure Shell -> Secure Shell Client". Once you have the program window in front of you, the quickest way to connect is by clicking on "Quick Connect" in the top left-hand corner of the window (under the menu bar). At this point you will be prompted for the Host Name to which you are trying to connect and the User Name you wish to connect as. The default port number and authentication method will work in most cases. If your site is hosted by a 3rd-party, then you will have to contact your host and get the host name and User name information from them (also the port number and authentication method, if it's other than the normal default). After you have correctly entered your information, click the "Connect" button. Now you will prompted for your password, which should also be obtained via your hosting provider, if your site is hosted. Enter your password and click "OK" or just press "Enter". At this point, you should now be logged onto the server. NOTE: If you think you will be using the commandline frequently and don't want to input the Host Name and User Name information every time you want to connect, you can make a profile for it by click on "Profiles", which is located to the right of "Quick Connect". You will then be presented with a drop-down list box, from which you will click on "Add Profile...". You will then be prompted to give the profile a name, once done click the "Add the Current Connection to Profiles" button. From now on, whenever you wish to connect to this particular server, you just click the "Profiles" button and select the profile name from the list. Logging Onto the Server Using Telnet Once again, most providers do not allow this and will, instead, want you to use SSH. For Windows 98: Click "Start -> Run" Type "telnet" and click "OK" or press "Enter" After the program window has spawned click (from the menu bar) "Connect -> Remote System" Enter the Host Name where prompted (the Port can usually be left as the default, but check with your hosting provider to make sure. Term Type should be left as the default 99.9% of the time.) Click "Connect" Enter your username when prompted and press "Enter" Enter your password when prompted and press "Enter" You should now be connected to the server. For Windows XP: Click "Start -> Run" Type "telnet" and click "OK" or press "Enter" This is where XP differs from 98 and previous versions: a DOS-looking window will now be spawned which expects text commands, instead of the GUI window provided by previous versions. At the prompt, type "open <hostname>" <hostname> being whatever your hosting service provided you with(e.g. for me, it would be flyingpenguins.org) Once connected, you will be asked for your username and password as above; enter them each when prompted and you will be logged onto the server. Logging Onto the Server Using the Server Terminal Sit down in your nice, comfy chair Enter your username and password You are now logged on. If an Error Occurs These are a few simple solutions for those whose sites are hosted by a 3rd-party. For such, it would be beneficial to contact your host in case of any problems and verify the following information. This will also be a good starting point for those who run their own servers. Make sure your username and password are both correct. (*nix usernames and passwords are CaSe sensitive, so be sure you are entering them correctly.) Make sure the Host Name is correct Make sure you are using the correct Port Number. Most people use port 22 for SSH connections, however there are the odd people out there that change this for their system: find out if your provider is one of them. Port 23 is the usual default for telnet connections. Make sure the authentication method is correct Backing Up Your mySQL Database With mysqldump The easiest and [usually] most pain free way of backing up your mySQL database, once logged onto the server, is via the commandline using the "mysqldump" program. The mysqldump program has many possibilities as to the way you actually backup your database. The simplest method is to just a full backup, as such: Code: mysqldump -u [username] -p[password] [database] > [backup_file] Example: Code: mysqldump -u bleak -pb133q phpbb > phpbb_backup-07302003.sql This will create a full backup of your mySQL database, which can then be restored later, if needed. However, restoring a backup made in this way is a somewhat painful process, as you will need to login to the mySQL monitor and drop your current database before the backup file can be reloaded (and the majority of providers don't allow users to drop databases, anyway). The solution to this is to make sure that if backed up tables already exist, when attempting to upload a backup file, said tables are deleted as the backup is restored. This is accomplished by adding another option switch to the mysqldump command: Code: mysqldump --add-drop-table -u [username] -p[password] [database] > [backup_file] Example: Code: mysqldump --add-drop-table -u bleak -pb133q phpbb > phpbb_backup-07302003.sql Now all the CREATE TABLE queries are preceded by a query that says to drop those tables, if they already exist, before attempting to re-create them (DROP TABLE IF EXISTS [table_name];). You are not, however, limited to creating just full backups of your database (backups which include all tables). mysqldump allows you to be selective about which tables are backed up. To accomplish this, just list the tables you want backed up, following the database name, like so: Code: mysqldump --add-drop-table -u [username] -p[password] [database] [table_name] [table_name] ... > [backup_file] Example: Code: mysqldump --add-drop-table -u bleak -pb133q phpbb phpbb_users phpbb_config phpbb_auth_access > phpbb_backup-07302003.sql You may backup as many, or as few, tables in this manner as you want. So, now all that must be done to restore a backup is to feed the following command to mySQL: Code: mysql -u [username] -p[password] [database] < [backup_file] Example: Code: mysql -u bleak -pb133q phpbb < phpbb_backup-07302003.sql There are ways of backing up and restoring databases while actually logged into mySQL, but they are bit more difficult as they actually require you to login to the mySQL monitor and have a pretty thorough working knowledge of mySQL queries. There are also many, many options that may be used with mysqldump, for the full list you can type either mysqldump --help or man mysqldump from the commandline. NOTE: If your site is hosted and you are unsure of what your username and password are, then you will need to contact your provider and obtain the information from them. Username: Password: News | Features | Demo | Downloads | Support | Community | Styles | Mods | Links | Merchandise | About | Home  © Copyright 2002 The phpBB Group.