BASICS OF MYSQL
There are only a handlful of mysql commands you need to know
to setup and use our software. They all deal with loading up the database
(whatever.sql) files and tables. The first thing you need to do is have a .sql file.
These are all included for your
convenience when you download any of our mysql cgi-scripts. They are named
something like sqltables.sql or revshare.sql or whatever.sql. The .sql extention
denotes that this file will import into MYSQL and create the table structures for the
database for the cgi-scripts to use. All of the cgi-scripts that use MYSQL need
these tables and databases created for the software to work. Its really simple to
setup a MYSQL database. The first thing to do is to create a directory inside of
your mysql installation to load the .sql file into. If this is totally confusing to
you there is a simpler solution. Email your system administrator your .sql file and
ask them to load it up for you. It only takes about 2 minutes.
LOADING UP THE .sql file into the mysql installation
directory
Hopefully, your isp wasn't lazy and they setup the normal
MYSQL installation. The other type of install is called a "binary"
installation.
Telnet to your host and navigate to the MYSQL installation
directory (usually /usr/local/mysql). If you can't figure out where mysql is type
"which mysql" from telnet and if your server is setup correctly it will tell
you.
Create a working directory to store your .sql files in.
For our example here lets call it "sqltables". Let's also assume
mysql is installed in /usr/local/mysql. Create your directory inside of here
(/usr/local/mysql/sqltables) and upload your .sql file(s). Then navigate to this new
directory you created by telnet.
cd
/usr/local/mysql/sqltables
CREATE DATABASE
Staying inside of the /usr/local/mysql/sqltables directory we need
to first CREATE a database for our software. This is simply done by executing the
command:
../bin/mysqladmin -u $mysqlusername
-p create $mysqldatabasename
note that $mysqlusername and $mysqldatabasename need to be your
actual mysql username and a name you choose for your mysqldatabse. Depending on your
isp you may not have the ability to create databases. If this is the case you need
to email the .sql table to your ISP and have them set up your database and upload the .sql
file. If you can create dataabases you would use the above command to create the
database. Let's look at a specific example of creating a database called
"mydatabase" under the mysql username "robert". Thus the command
will be
../bin/mysqladmin -u robert -p
create mydatabase
Mysql will then prompt you to enter your Mysqlpassword to complete
this operation. Now you have created a database. This database has no defined
tables structure (rows/colums) so we need to do that next. That is what the .sql
files are for. The .sql files contain specific parameters for your MYSQL database.
LOAD TABLES INTO MYSQL
Now we need to load up the .sql file itself in order to create the
table structures that you will need for your software. This is also a simple telnet
command.
../bin/mysql -u $mysqlusername
-p$mysqlpassword $mysqldatabasename < $file.sql
AGAIN note that $mysqlusername, $mysqlpassword, $mysqldatabasename
and $file.sql need to be your actual mysql username/password, a name you choose for your
mysqldatabse, and the exact name of the .sql file you uploaded into
/usr/local/mysql/sqltables/
LOGIN TO DATABASE
Sometimes its nice to actually login to the mysql database and poke
around. We are not going into that in detail here except to provide the login
command.
../bin/mysql -u $mysqlusername -p
sqltablesdatabase
BACKING UP AN SQL DATABASE
You should backup your mysql database RELIGIOUSLY. EVERY DAY.
This backsup ALL of the data into a NEW sql file that allows you to easily restore
all of your data in the event of a major server malfunction. These happen. If
you dont backup then thats your choice but its a stupid choice.
../bin/mysqldump -u $mysqlusername
-p$mysqlusername $mysqldatabasename > backup.sql
Note, as usual you need to use your actual mysql username and mysql
database name here. You can call backup.sql whatever you want but its done this way
here for simplicity.
DESTROYING THE MYSQL DATABASE (starting over)
OK, if you need to restore a MYSQL databse from a backup file or for
any other reason want to destroy the entire database, data and start over simply type
../bin/mysqladmin -u $mysqlusername
-p$mysqlpassword drop $mysqldatabase
For comprehensive documentation on MYSQL and all of its glorious
features please visit www.mysql.com
| 