Advanced MySQL
This lesson is an addition to MySQL Command Line /Database Setup. Please look there first for MySQL fundamentals.
Backing Up Your Database
Since our customers will start to store important data, it's good to have an idea of how to 'back up' this data. We can run a command called mysqldump, which will 'dump' (copy) the SQL statements required to reconstruct all the tables in our database with a single command.
mysqldump
Once you are OUT of MySQL you can "dump" (copy) a database to a separate file. You could do this if you are moving between servers or wanted to save a copy of your database. Run the following from the command line, outside of MySQL:
mysqldump -u MySQLUserName -p --opt myDatabase > /PathToMyServerSpace/backup.sql;
This creates a copy of a database named "myDatabase" and deposits it at the root of your server space, as a file named "backup.sql".
On Zephir we use the same name (horsey01) for the database, the MySQL user and the folder where our files go in UNIX. Therefore the above command for Zephir would then be:
mysqldump -u horsey01 -p --opt horsey01 > /home/classes/horsey01/backup.sql;
Remember, mysqldump can only be run from OUTSIDE of mysql! You would be at the UNIX command line, but NOT logged in to MySQL!
Now you can open your file named 'backup.sql' to see how MySQL would use SQL commands to re-create your database tables. This file can be run at the command line to restore your database to a previous state, or to move the database tables to a new server. Remember you may lose all of your data if you run a backed up version of your database against your current data!
Restoring A Database/Running a MySQL Batch File
To restore the database (and wipe out all data created in the meanwhile) or to run any MySQL script as a 'batch' from a file, login to MySQL, select the database then, run the command using the source command:
mysql> use horsey01;
Database changed
mysql> source backup.sql;
Be careful, if you run your backup sql script against your current database, all new data added SINCE the backup could be wiped out!
You can also run a SQL batch file from outside MySQL (at the shell, or UNIX prompt):
mysql -u MySQLUserName -p myDatabase < fileName.sql
After which you'll be prompted for a password. The fileName.sql file is assumed to be at the root of your UNIX space. To run this command in Zephir:
[horsey01@zephir2 ~]$ mysql -u horsey01 -p horsey01 < backup.sql
Add the switch -vvv to watch the SQL commands run:
[horsey01@zephir2 ~] $mysql -vvv -u horsey01 -p horsey01 < backup.sql
For more info about running MySQL statements as 'batch' files, see MySQL Batch Mode .
If you have access to phpMyAdmin (on your own host, not Zephir), there is a 'backup' feature you can use to tailor a backup file via the program. phpMyAdmin that will prompt you for a location to backup your MySQL file. View directions on how this is done via phpMinAdmin below:
Backup/Restore with phpMinAdmin
To backup/restore a database (or any tables) in phpMinAdmin, find the following:
DUMP >> OUTPUT:SAVE >> FORMAT: SQL
Under Database, choose DROP,CREATE
Click EXPORT and save the SQL file with a significant name, like: horsey01-08242009.sql
In which you name the database (or application) plus the date.
To re-install on a new server (or try it, to be sure you can restore your data):
SQL COMMAND >> FILE UPLOAD:BROWSE (identify the file) then click EXECUTE
If you make an insignificant (but easy to find) change in your database after you backup, but before you restore, you can test the restoration to see the change goes back to it's previous state. Be sure NOT to do this on a working database with customers!
UPDATE: phpMinAdmin is now called Adminer! Go get the new version!
MySQL Backup Strategies
Backing up the database is one of the most critical things to do for our customers. To do it once is trivial enough, and can be done freely with several programs, or at the command line, but an effective backup strategy happens at proper intervals, and includes off – server storage in case of hardware failure on the server. Please see the following handout for details: MySQL Backup Strategies
Indexes
We can speed up the retrieval of data if we carefully add indexes to columns in our database. An index can be thought of like an index in the back of a text book. It provides an alternate way to find a word in the book. An index for a table approximately doubles the size of the data for the table!
There is a default (physical) index on the ID (primary key) field, since the data must be stored in a particular physical order.
A field that is searched on frequently, and has instances of unique data (like a Foreign Key field) could benefit from having a properly set index. Fields that have a very high degree of similarity (like a State field) would NOT have an index!!
Improper setting of indexes can slow down a database. Indexes also slow down updates, adds & deletes, since the index is re-created whenever the data is changed. As a rule of thumb, we will not set an index until we are sure it is needed, and that it will speed up a particular type of search the customer is likely to use!
For more info on indexes in MySQL see the following: Optimizing MySQL Indexes
