Databases can be created on:
cs480
Databases can be accessed using:
A local database can be created on our workstations using a script call “cs480”
This script will setup and run a database stored in your home directory under your account
When run it will start your mysql server as well as an HTTP server on localhost:8080 with a PHPMyAdmin instance
Usage:
cs480 - Prints help test cs480 setup - Creates needed files cs480 start - Starts servers cs480 stop - Stop server
to access it:
mysql -u root -p
SysAdmin has root db password
databases are created like this:
create database mydatabase; GRANT ALL PRIVILEGES ON mydatabase.* to 'myuser'@'localhost' IDENTIFIED BY 'mypassword' with GRANT OPTION;
most of the time myuser is the same as the database name. mysql users are not at all related to system users.
backups on acmdb are kept in /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]
o [username] - this is your database username
o [password] - this is the password for your database
o [databasename] - the name of your database
o [backupfile.sql] - the file to which the backup should be written.
If you'd like restrict the backup to only certain tables of your database, you can also specify the tables you want to backup.
mysqldump -u [username] -p [password] [databasename] [table1 table2 ....] > [backupfile.sql]
o [username] - this is your database username
o [password] - this is the password for your database
o [databasename] - the name of your database
o [table1 table2 ....] - the names of the tables you want to back up separated by spaces
o [backupfile.sql] - the file to which the backup should be written.
If you want to specify the databases to backup, you can use the –databases parameter followed by the list of databases you would like to backup. Each database name has to be separated by at least one space when you type in the command. So if you have to backup 3 databases, let say Customers, Orders and Comments, you can issue the following command to back them up. Make sure the username you specify has permissions to access the databases you would like to backup.
mysqldump -u [username] -p [password] --databases [database1 database2 ...] > [multibackup.sql]
o [username] - this is your database username
o [password] - this is the password for your database
o [database1 database2 ...] - the names of the databases you want to back up separated by spaces
o [multibackup.sql] - the file to which the backup should be written.
You can automate the backup process by making a small shell script which will create a daily backup file. How do you get cron to back up your database without overwriting the older backup? You can use a tiny shell script to add the date to your backup file. An example of a shell script you could use is shown below. This script will also compress the backup file and it will need to be uncompressed before being used to restore a database.
#!/bin/sh date=`date -I` mysqldump --all-databases | gzip > /var/backup/backup-$date.sql.gz
If you have to re-build your database from scratch, you can easily restore the mysqldump file by using the mysql command. This method is usually used to recreate or rebuild the database from scratch.
Here's the general format you would follow:
mysql -u [username] -p [password] [database_to_restore] < [backupfile]
o [username] - this is your database username
o [password] - this is the password for your database
o [database_to_restore] - the name of your database to restore
o [backupfile] - the file from which to retore