MySQL Group - New Paltz



Table of Contents

1) Install and configure MySQL server on the machine called A

2) Create 5 databases for 5 potential users

3) Manually create 5 users and give them complete access to their database.

4) The users should be able to connect from client machines C1 and C2

5) All MySQL transactions must be logged.

6) Installation of j2sdk-1.3.1

7) Students have only “select” permissions on their records.

8) How do you back up data?

9) How will you transport data?

10) Do you have a back up server?

11) How do you find a process ID?

12) Make sure that the mysql server starts at boot time.

13) Devise and test a way to quickly bring a server on line with all the necessary data and configurations if the original one crashes.

14) Backup everything in TeacherX databases to an sql file.

15) Backup everything in TeacherX databases to another server on another machine.

16) Taking a new database, taking a text file (.txt), and running the source command to enable automatic database generation.

17) Documentation of all activities.

1) We down loaded the Linux version of MySQL and installed it on the machine 192.168.2.12 called “MySQL Server”. The necessary steps for installation were:

Unpack tarball to /usr/local:

>tar xvzf mysql-3.23.51-pclinux-gnu-i686.tar.gz

Create a symbolic link:

>ln –s /usr/local/mysql-3.23.51-pc-linux-gmu-i686 mysql

We successfully configured the software and then created 3 administrators: Devin, Dara and Kevin and set up a password.

a) In order to administer administrative commands you must start up the server. In order to do this we use the command:

safe_mysqld –user=root &

A message will pop up saying:

“Starting mysqld dameon with databases from usr/local/var

b) If we need to stop the server we would use this command:

mysqladmin –p shutdown.

c) Once the server is running, we want to get into administrative mood and begin our process. This is done by typing in:

mysql –u root –p

2) Our next task was to create 5 databases for 5 potential users

3) After creating those 5 users give them complete access to their databases only

4) The user should be able to connect from client machines C1, C2 and anywhere else.

In order to do these 3 steps we did the following steps.

a) show databases; //this command will display

mysql> SHOW DATABASES;

+----------+

| Database |

+----------+

| mysql |

| test |

+----------+

2 rows in set (0.00 sec)

b) Then we did a create option; //this will create all the databases.

mysql> CREATE DATABASE Teaher1;

Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE Teacher2;

Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE Teacher3;

Query OK, 1 row affected (0.00 sec)

mysql> CREATE DATABASE CS1;

Query OK, 1 row affected (0.00 sec)

c) Then we did a use option; //this will let the user use the DB

mysql> use Teacher1

Database changed

mysql> use Teacher2

Database changed

mysql> use Teacher3

Database changed

mysql> use Teacher1

Database changed

The following steps will enable the user to use their database from any remote location:

a) GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON ‘Teacher1’.* to Teacher1@’localhost’

b) IDENTIFIED BY ‘Teacher1’;

c) GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON ‘Teacher1’.* to Teacher1@”%”

d) IDENTIFIED BY ‘Teacher1’;

e) Flush privileges;

If you type in the command show grants for Teacher1@localhost or “%” you will get a listing of all the commands they can do.

5) In order to make sure all transactions are logged you just have to restart the sever and use the command line:

safe_mysqld –user=root –log&

a) if you goto cd /usr/local/var and type ‘ls’, you will see a list of all transactions done by all the users to their databases.

b) If you use the command lines:

Tail io.log //tell you command argument

Tail –f io.log //tells you connection, init DB, Querey

More io.log //gives you everything

c) under [root @io var]# ls –la //will show all root logs

d) tail io.err //shows all errors with the server.\

6) Another process I forgot to mention was the installation of j2sdk-1.3.1 for Linux. We need to add this to the machine so when files are transferred from the HTTP group in Java, we would be able to compile and run them via script commands.

7) Students only have access to their records by entering a social security # for their password. This will only give them access to their grades

GRANT SELECT ON ‘Devin’.* to Teahcer1@’localhost’

IDENTIFIED BY ‘123456789’;

GRANT SELECT ON ‘Devin’.* to Teacher1@”%”

IDENTIFIED BY ‘123456789’;

For steps 8,9,14,15 will handle backing up the data on server A and sending it to backup server B:

If you want to make a SQL level backup, you can use SELECT INTO OUTFILE.

Another way to backup a database is to use the mysqldump program:

1.Do a full backup of your databases:

* 0-23/5 * * * echo Hip >>/root/cronfile

0-59 * * * *

mysqldump --opt -pgepetto Teacher1 | mysql -pgepetto -h192.168.2.11 -c

Teacher1

mysqldump --opt -pgepetto Teacher2 | mysql -pgepetto -h192.168.2.11 -c

Teacher2

mysqldump --opt -pgepetto Teacher3 | mysql -pgepetto -h192.168.2.11 -c

Teacher3

mysqldump --opt -pgepetto Teacher4 | mysql -pgepetto -h192.168.2.11 -c

Teacher4

crontab: installing new crontab

[root@io etc]# # DO NOT EDIT THIS FILE - edit the master and reinstall.

# (/tmp/crontab.7872 installed on Mon Nov 25 18:10:12 2002)

# (Cron version -- $Id: crontab.c,v 2.13 1994/01/17 03:20:37 vixie Exp

$)

0,30 * * * * echo Databases Saved!! >>/root/cronfile2

1,31 * * * * mysqldump --opt -pgepetto Teacher1 | mysql -pgepetto

-h192.168.2.11

-C Teacher1

2,32 * * * * echo Teacher 1 saved!! >>/root/cronfile2

3,33 * * * * mysqldump --opt -pgepetto Teacher2 | mysql -pgepetto

-h192.168.2.11

-C Teacher2

4,34 * * * * echo Teacher 2 saved!! >>/root/cronfile2

5,35 * * * * mysqldump --opt -pgepetto Teacher3 | mysql -pgepetto

-h192.168.2.11

-C Teacher3

6,36 * * * * echo Teacher 3 saved!! >>/root/cronfile2

7,37 * * * * mysqldump --opt -pgepetto Teacher4 | mysql -pgepetto

-h192.168.2.11

-C Teacher4

8,38 * * * * mysqldump --opt -pgepetto Teacher5 | mysql -pgepetto

-h192.168.2.11

-C Teahcer5

9,39 * * * * echo Teacher 4 and 5 saved!! >>/root/cronfile2

the results from the cron job will display:

Databases Saved!!

Teacher 1 saved!!

Teacher 2 saved!!

Teacher 3 saved!!

Teacher 4 and 5 saved!!

2.To do a backup of the server automatically we first made a unix shell script called save2.sh

#!/bin/sh

#This script....

DATE=`date +%d%h-%T`

mysqldump --opt -pgepetto Teacher1 | mysql -pgepetto -h192.168.2.11 -C Teacher1

mysqldump --opt -pgepetto Teacher2 | mysql -pgepetto -h192.168.2.11 -C Teacher2

mysqldump --opt -pgepetto Teacher3 | mysql -pgepetto -h192.168.2.11 -C Teacher3

mysqldump --opt -pgepetto Teacher4 | mysql -pgepetto -h192.168.2.11 -C Teacher4

mysqldump --opt -pgepetto Teacher5 | mysql -pgepetto -h192.168.2.11 -C Teacher5

echo "$DATE""-- ALL DATABASES BACKED UP" >>/root/cronfile2

echo "FINISHED"

once complete we executed the command:

chmod u+x save2.sh

then to run it we used:

[root@io root]# ./save2.sh

FINISHED

Then we had a crontab, to view this you would say crontab -e :

0,30 * * * * ./save2.sh

if you type in more crontab2, you will see our autobackup

03Dec-09:46:59-- ALL DATABASES BACKED UP

03Dec-09:48:00-- ALL DATABASES BACKED UP

03Dec-10:00:01-- ALL DATABASES BACKED UP

03Dec-10:30:00-- ALL DATABASES BACKED UP

03Dec-11:00:01-- ALL DATABASES BACKED UP

03Dec-11:30:00-- ALL DATABASES BACKED UP

03Dec-12:00:01-- ALL DATABASES BACKED UP

03Dec-12:30:00-- ALL DATABASES BACKED UP

03Dec-13:00:01-- ALL DATABASES BACKED UP

03Dec-13:30:00-- ALL DATABASES BACKED UP

03Dec-14:00:01-- ALL DATABASES BACKED UP

03Dec-14:30:00-- ALL DATABASES BACKED UP

03Dec-14:38:35-- ALL DATABASES BACKED UP

03Dec-14:39:58-- ALL DATABASES BACKED UP

[root@io root]#

If you have to restore something, try to recover your tables using isamchk -r first. That should work in 99.9% of all cases.

If isamchk fails, try the following procedure:

1.Restore the original mysqldump backup.

2.Execute the following command to re-run the updates in the update logs:

shell> ls -1 -t -r hostname.[0-9]* | xargs cat | mysql

ls is used to get all the log files in the right order.

You can also do selective backups with SELECT * INTO OUTFILE 'file_name' FROM tbl_name and restore with LOAD

DATA INFILE 'file_name' REPLACE ... To avoid duplicate records, you need a PRIMARY KEY or a UNIQUE key in the

table. The REPLACE keyword causes old records to be replaced with new ones when a new record duplicates an old record

on a unique key value.

10) We created the backup server by using the installations steps from step 1.

11) In order to find a process ID:

[root@io root]# mysqladmin -u root -p processlist

Enter password:

+-----+------+-----------+----+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+-----+------+-----------+----+---------+------+-------+------------------+

| 190 | root | localhost | | Query | 0 | | show processlist |

+-----+------+-----------+----+---------+------+-------+------------------+

[root@io root]#

to find status:

[root@io root]# mysqladmin -u root -p status

Enter password:

Uptime: 703619 Threads: 1 Questions: 967 Slow queries: 0 Opens: 22 Flush ta

bles: 1 Open tables: 13 Queries per second avg: 0.001

[root@io root]#

[root@io root]# mysqladmin -i 5 -r -p status

Enter password:

Uptime: 703704 Threads: 1 Questions: 969 Slow queries: 0 Opens: 22 Flush ta

bles: 1 Open tables: 13 Queries per second avg: 0.001

Uptime: 703709 Threads: 1 Questions: 969 Slow queries: 0 Opens: 22 Flush ta

bles: 1 Open tables: 13 Queries per second avg: 0.001

Uptime: 703714 Threads: 1 Questions: 969 Slow queries: 0 Opens: 22 Flush ta

bles: 1 Open tables: 13 Queries per second avg: 0.001

Uptime: 703720 Threads: 1 Questions: 969 Slow queries: 0 Opens: 22 Flush ta

bles: 1 Open tables: 13 Queries per second avg: 0.001

Uptime: 703725 Threads: 1 Questions: 969 Slow queries: 0 Opens: 22 Flush ta

bles: 1 Open tables: 13 Queries per second avg: 0.001

Uptime: 703730 Threads: 1 Questions: 969 Slow queries: 0 Opens: 22 Flush ta

bles: 1 Open tables: 13 Queries per second avg: 0.001

Uptime: 703735 Threads: 1 Questions: 969 Slow queries: 0 Opens: 22 Flush ta

bles: 1 Open tables: 13 Queries per second avg: 0.001

Uptime: 703740 Threads: 3 Questions: 975 Slow queries: 0 Opens: 22 Flush ta

12) to make sure the server starts at automatically at start up we did:

Connection to 192.168.2.12 closed.

[travis@alpha travis]$ ssh -l root 192.168.2.11

root@192.168.2.11's password:

Last login: Thu Nov 21 10:27:19 2002

[root@localhost root]# ls

anaconda-ks.cfg install.log.syslog MySQL_New_DataBase_Info1.txt~

Desktop mysql MySQL_New_DataBase_Info.txt

evolution MySQL_New_DataBase_Info1.pl Perl.zip

install.log MySQL_New_DataBase_Info1.txt Start at boot time

[root@localhost root]# cd /usr/local/mysql

[root@localhost mysql]# ls

bin COPYING.LIB lib manual.txt share tr.txt

ChangeLog data man mysql-test sql-bench

configure include manual.html README support-files

COPYING INSTALL-BINARY manual_toc.html scripts tests

[root@localhost mysql]# cd support-files/

[root@localhost support-files]# ls

magic my-f mysql-3.23.51.spec mysql.spec

make_mysql_pkg.pl my-f mysql-log-rotate

my-f my-f mysql.server

[root@localhost support-files]#

Then we performed a cp mysql.server/etc/rc.d/init.d/

[root@localhost init.d]# ls

anacron functions isdn mysql.server nscd rhnsd sshd

apmd gpm kdcrotate named ntpd saslauthd syslog

atd halt keytable netfs pcmcia sendmail winbind

autofs httpd killall network portmap single xfs

crond iptables kudzu nfs random snmpd xinetd

firstboot irda lpd nfslock rawdevices snmptrapd ypbind

[root@localhost init.d]# ls -al

total 220

drwxr-xr-x 2 root root 4096 Nov 21 10:17 .

drwxr-xr-x 10 root root 4096 Oct 8 07:14 ..

-rwxr-xr-x 1 root root 941 Aug 28 06:33 anacron

-rwxr-xr-x 1 root root 1458 Jun 23 09:50 apmd

-rwxr-xr-x 1 root root 1176 Jul 24 22:45 atd

-rwxr-xr-x 1 root root 9435 Aug 27 00:49 autofs

-rwxr-xr-x 1 root root 1316 Jul 19 18:51 crond

-rwxr-xr-x 1 root root 1413 Sep 5 17:22 firstboot

-rwxr-xr-x 1 root root 10068 Jul 14 02:39 functions

-rwxr-xr-x 1 root root 1541 Jun 23 13:32 gpm

-rwxr-xr-x 1 root root 5075 Aug 13 22:01 halt

-rwxr-xr-x 1 root root 2366 Sep 4 17:23 httpd

-rwxr-xr-x 1 root root 5636 Aug 7 10:34 iptables

-rwxr-xr-x 1 root root 1152 Jul 9 05:41 irda

-rwxr-xr-x 1 root root 5838 Jul 1 13:27 isdn

-rwxr-xr-x 1 root root 1084 Aug 2 16:09 kdcrotate

-rwxr-xr-x 1 root root 1347 Sep 4 19:06 keytable

-rwxr-xr-x 1 root root 481 Jul 5 17:15 killall

-rwxr-xr-x 1 root root 1919 Sep 3 03:29 kudzu

-rwxr-xr-x 1 root root 2033 Jun 23 08:50 lpd

-rwxr-xr-x 1 root root 4023 Nov 21 10:17 mysql.server

-rwxr-xr-x 1 root root 2398 Aug 7 09:27 named

-rwxr-xr-x 1 root root 5024 Jun 26 05:20 netfs

-rwxr-xr-x 1 root root 6402 Jul 10 04:26 network

-rwxr-xr-x 1 root root 4522 Aug 1 11:06 nfs

-rwxr-xr-x 1 root root 2286 Aug 1 11:06 nfslock

-rwxr-xr-x 1 root root 2066 Sep 5 18:58 nscd

-rwxr-xr-x 1 root root 2409 Aug 31 18:27 ntpd

-r-xr-xr-x 1 root root 4596 Aug 31 00:04 pcmcia

-rwxr-xr-x 1 root root 1901 Aug 6 16:52 portmap

-rwxr-xr-x 1 root root 1516 Jun 26 05:20 random

-rwxr-xr-x 1 root root 2211 Jun 26 05:34 rawdevices

-rwxr-xr-x 1 root root 1782 Sep 9 18:34 rhnsd

-rwxr-xr-x 1 root root 1260 Sep 2 19:48 saslauthd

-rwxr-xr-x 1 root root 2362 Aug 29 15:38 sendmail

-rwxr-xr-x 1 root root 1175 Jul 10 04:58 single

-rwxr-xr-x 1 root root 1160 Aug 31 18:40 snmpd

-rwxr-xr-x 1 root root 1131 Aug 31 18:40 snmptrapd

-rwxr-xr-x 1 root root 2647 Aug 14 00:08 sshd

-rwxr-xr-x 1 root root 1369 Jun 23 20:29 syslog

-rwxr-xr-x 1 root root 1555 Aug 28 12:03 winbind

-rwxr-xr-x 1 root root 2580 Sep 5 23:30 xfs

-rwxr-xr-x 1 root root 2407 Aug 15 16:54 xinetd

-rwxr-xr-x 1 root root 2501 Jun 23 21:50 ypbind

[root@localhost init.d]#

Then to grant access we did:

Chmod atx mysql.server

[root@localhost init.d]# cd /etc/rc3.d/

[root@localhost rc3.d]# ls

K05saslauthd K50snmpd S09isdn S20random S56rawdevices S90xfs

K15httpd K50snmptrapd S10network S24pcmcia S56xinetd S95anacron

K20nfs K74ntpd S12syslog S25netfs S60lpd S95atd

K24irda K95firstboot S13portmap S26apmd S80sendmail S97rhnsd

K35winbind S05kudzu S14nfslock S28autofs S85gpm S99local

K45named S08iptables S17keytable S55sshd S90crond S99mysql

[root@localhost rc3.d]# cd /etc/rc6.d/

[root@localhost rc6.d]# ls

K03rhnsd K15httpd K45named K72autofs K88syslog S00killall

K05anacron K20nfs K50snmpd K74apmd K90network S01reboot

K05atd K24irda K50snmptrapd K74ntpd K91isdn

K05keytable K25sshd K50xinetd K75netfs K92iptables

K05saslauthd K30sendmail K60crond K80random K95firstboot

K10xfs K35winbind K60lpd K86nfslock K95kudzu

K15gpm K44rawdevices K60mysql K87portmap K96pcmcia

[root@localhost rc6.d]#

k60mysql is the server start automatically command.

13,16) In order to take care of this task, we would use a perl script:

[root@localhost root]# ls

anaconda-ks.cfg install.log.syslog MySQL_New_DataBase_Info1.txt~

Desktop mysql MySQL_New_DataBase_Info.txt

evolution MySQL_New_DataBase_Info1.pl Perl.zip

install.log MySQL_New_DataBase_Info1.txt Start at boot time

[root@localhost root]# vi MySQL_New_DataBase_Info1.pl

#!/usr/bin/perl

create database TeacherC;

create database TeacherD;

create database TeacherE;

use database TeacherA;

use database TeacherB;

use database TeacherC;

use database TeacherD;

use database TeacherE;

IDENTIFIED BY 'TeacherA';

IDENTIFIED BY 'TeacherB';

IDENTIFIED BY 'TeacherC';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON 'TeacherD'.* to TeacherD@local

ost

IDENTIFIED BY 'TeacherD';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON 'TeacherE'.* to TeacherE@local

ost

IDENTIFIED BY 'TeacherE';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON 'TeacherA'.* to TeacherA@"%"

IDENTIFIED BY 'TeacherA';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON 'TeacherB'.* to TeacherB@"%"

IDENTIFIED BY 'TeacherB';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON 'TeacherC'.* to TeacherC@"%"

IDENTIFIED BY 'TeacherC';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON 'TeacherD'.* to TeacherD@"%"

IDENTIFIED BY 'TeacherD';

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON 'TeacherE'.* to TeacherE@"%"

IDENTIFIED BY 'TeacherE';

Flush privileges;

Then under mysql> source /root/ MySQL_New_DataBase_Info1.pl

Once executed you can go to cd /usr/local/mysql/data and:

[root@localhost data]# ls

localhost.localdomain.err mysql Teacher2 Teacher4 test

localhost.log Teacher1 Teacher3 Teacher5 Trial

[root@localhost data]#

This reports shows details of our immediate tasks and the necessary steps on achieving them. Examples are also shown for proof of existence. All in all we found database to be the cream of the crop for computer science. We all learned a lot from Dr. Easwaran and could manipulate this whole database with in one business day. That concludes our flight from the city of MySQL. Please return your seats to the up-right position, becareful when exiting the isles, remove all carry on luggage and leave a tip for the 3 pilots at the door. Large donations are welcome.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download