All the pages I am running at the moment is using MySQL. This is (in my oppinion) good and reliable database server and I can recommend this as a good server for small and medium sized web applications. Haven’t tried this for pages with high volume of traffic / requests towards the database. For this purpose I have used MSSQL with success. MySQL might work for this too.

The feature list for this server is comprehensive and covers all the requirements I have to a data engine.

Link to Oracles MySQL page: http://www.oracle.com/us/products/mysql/index.html


TOC:


to the top
Some simple Database Scripts:
Script – Load file into a Temp Table


to the top
A Simple Database Backup:

You can use mysqldump to create a simple backup of your database using the following syntax.

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

    • [username]– this is your database username
    • [password]– this is the password for your database
    • [databasename]– the name of your database
    • [backupfile.sql] – the file to which the backup should be written.

The resultant dump file will contain all the SQL statements needed to create the table and populate the table in a new database server. To backup your database ‘Customers’ with the username ‘sadmin’ and password ‘pass21’ to a file custback.sql, you would issue the command:

mysqldump -u sadmin -p pass21 Customers > custback.sql

You can also ask mysqldump to add a drop table command before every create command by using the option –add-drop-table. This option is useful if you

would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

mysqldump –add-drop-table -u sadmin -p pass21 Customers > custback.sql

to the top

Restore Database with MySQL

If you are moving your data to a new server, or you have removed the old database completely you can restore it using the code below.  This will only work if the database does not already exist:

mysql - u user_name -p your_password database_name < file_name.sql

If your database already exists and you are just restoring it, try this line instead:

mysqlimport -u user_name -p your_password database_name file_name.sql

 


to the top
Create a new Database

I would at any time recommend to use one of the many tools to do SQL database management to create new databases and to get a clear overview of your database structures during development. I’m using “DreamCoder for MySQL” provided by MENTAT Technologies Get it Here



to the top

Create a new Table

Log into “mysql>” via telnet or a command prompt and type something like this:

mysql> CREATE TABLE example_autoincrement

(

 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

data VARCHAR(100)

);

However if this is not absolutly nessesary to do from the promt, then use one of the SQL database management tools available.
to the top

Temp Tables

The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be knows for temporary tables is that they will be deleted when the current client session terminates.

Temporary tables where added in MySQL version 3.23. If you use an older version of MySQL than 3.23 you can’t use temporary tables, but you can use heap tables.

As stated earlier temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQl database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy  the table.

Here is an Example of using and Temporary Table
Dropping Temporary Tables:

By default all the temporary tables are deleted by MySQL when your database connection gets terminated. Still you want to delete them in between then you do so by issuing DROP TABLE command.