Database Reference
In-Depth Information
secure
TINYINT
DEFAULT
0
,
location
ENUM
(
'on-site'
,
'off-site'
,
'both'
),
tables_include
VARCHAR
(
255
) );
Now that we've created the
backup_policies
table, let's insert data in it related to
INSERT INTO
backup_policies
(
backup_name
,
file_format_prefix
,
frequency
,
days
,
start_time
,
secure
,
location
,
tables_include
)
VALUES
(
'rookery - full back-up'
,
'rookery-'
,
2
,
1
,
'08:00:00'
,
0
,
2
,
"all
tables"
),
(
'rookery - bird classification'
,
'rookery-class-'
,
1
,
2
,
'09:00:00'
,
0
,
1
,
"birds, bird_families, bird_orders"
),
(
'birdwatchers - full back-up'
,
'birdwatchers-'
,
2
,
1
,
'08:30:00'
,
1
,
2
,
"all tables"
),
(
'birdwatchers - people'
,
'birdwatchers-people-'
,
1
,
2
,
'09:30:00'
,
1
,
1
,
"humans, birder_families, birding_events_children"
),
(
'birdwatchers - activities'
,
'birdwatchers-activities-'
,
1
,
2
,
'10:00:00'
,
0
,
1
,
"bird_sightings, birding_events, bird_identification_tests,
prize_winners, surveys, survey_answers, survey_questions"
);
In addition, we will need another table in the
server_admin
database. We'll call it
backup_reports
and store reports in it that will be generated by the program that
we'll create. The SQL statement to create this table is as follows:
CREATE TABLE
backup_reports
(
report_id
INT
AUTO_INCREMENT
KEY
,
report_date
DATETIME
,
admin_name
VARCHAR
(
100
),
report
TEXT
);
This is a simple table containing a key, the date of the report, the name of the administrat-
or generating the report, and a
TEXT
column to store the report, which will be generated
by the program we'll create in this section. Because we will be using theadmin_backup
user account, we will need to give that account user privileges to access the
serv-
er_admin
database. We can do that by executing this SQL statement:
GRANT SELECT
,
INSERT ON
server_admin
.*
TO
'admin_backup'
@
'localhost'
;
We're now ready to create the program for the backupadministrator.
