Transcript ITN270 Advanced Internet Databases

ITN270 Advanced Internet Databases
Lecture 14. Introduction to MySQL Administration
and the MySQL Data Directory
•Topics:
–Overview of Administrative Duties
–MySQL Data Sirectory
ITN270.001 Wake Tech
1
Overview of Administrative Duties
• The MySQL Server
– mysqld
• MySQL Clients & Utilities
– mysql
• allows you to send SQL to the server & get results
– mysqladmin
• an administrative program
• shutting down
• checking status
ITN270.001 Wake Tech
2
Overview of Administrative Duties
• MySQL Clients & Utilities
– mysqlcheck
– isamchk
– myisamchk
• table analysis
• table optimization
• crash recovery
– mysqldump
• backing up databases
ITN270.001 Wake Tech
3
Overview of Administrative Duties
• The server’s language
– SQL
• The MySQL data directory
– structure
• where the files located
– contents
• file system
ITN270.001 Wake Tech
4
Administrative Duties
• Server startup & shutdown
– mysqld start/stop
• User account maintenance
– grant
• Log file maintenance
– it may fill up your system
• Database backup & copying
– mysqldump
ITN270.001 Wake Tech
5
Administrative Duties
• Database replication
– two servers have the same database
– changes made in one propagate to the other
• Server configuration & tuning
– retrieval
– updates
• Multiple servers
– each group may have its own server
• MySQL software updates
ITN270.001 Wake Tech
6
Security
• File system security
• Server security
– grant access priviliges
ITN270.001 Wake Tech
7
Database Repair & Maintenance
• Crash recovery
• Preventive maintenance
ITN270.001 Wake Tech
8
The MySQL Data Directory
• The MySQL Data Directory Stores
– databases
– status files
– log files
ITN270.001 Wake Tech
9
Location of the Data Directory
• Install from a source distribution
– /usr/local/mysql/var
• Install from a binary distribution
– /usr/local/mysql/data
• Install from a RPM file
– /var/lib/mysql
• MS Windows
– c:\mysql\data
ITN270.001 Wake Tech
10
Location of the Data Directory
• Specification of the location of data
directory
--datadir = dir_name
• Ask the server for location
– mysqladmin variables
– or
ITN270.001 Wake Tech
11
Structure of the Data Directory
• Contains all the databases & tables
• Tree structure
– each database has a sub directory
– tables in a database - files in the
subdirectory
• Status & Log files
ITN270.001 Wake Tech
12
How the MySQL Server Provides Access to Data
ITN270.001 Wake Tech
13
How the MySQL Server Provides Access to Data
• When the server does not have exclusive
control of the data directory
– multiple servers on a single data directory
– when run the table repair utilities
ITN270.001 Wake Tech
14
How the MySQL Represents
Databases in the File System
• Each database is a subdirectory under
c:\mysql\data
• Show DATABASES
– dir in Windows
– ls in unix/Linux
• CREATE DATABASES db_name
– cd DATADIR
– mkdir db_name
– chmod u=rwx,go-rwx dbname
ITN270.001 Wake Tech
15
How the MySQL Represents
Databases in the File System
• DROP DATABASE db_name
– in UNIX
• cd DATADIR
• rm -rf db_name
– in Windows
• cd DATADIR
• del /s db_name
ITN270.001 Wake Tech
16
How the Tables are represented
• ISAM Tables
– *.frm - description file for the format
– *.ISD - data file for the contents
– *.ISM - index information
• MyISAM Tables
– *.frm - format
– *.MYD - data
– *.MYI - index files
ITN270.001 Wake Tech
17
How the Tables are represented
• MERGE Tables
– *.frm
– *.MRG
• BDB Tables
– *.frm - description
– *.db - data & index
• InnoDB Tables
– *.frm
– tablespace
ITN270.001 Wake Tech
18
OS constrains on Database &
Table Naming
• Alphanumeric characters, _, $
• 64 characters long
• other characters can be used by quoting
the name in backticks `odd@name`
• case sensitivity
ITN270.001 Wake Tech
19
Factors that Affect Maximum
Table Size
• Internal Limits
–
–
–
–
ISAM: .ISD & .ISM <=4GB
MyISAM: .MYD & .MYI <= 4GB
BDB: 2 TetraBytes
InnoDB: 4Billion x 16KB
• OS limits:
– 2GB
• Data Type of AUTO_INCREMENT
ITN270.001 Wake Tech
20
Implications of Data Directory
Structure for System Performance
• Multiple files for one table
• Table opening time increases with the
number of tables
• TIME VS SPACE
– Combine tables need more space
– More tables need more time
• Security
– More user access same table if combined
ITN270.001 Wake Tech
21
Status & Log Files
• Process ID file:
– created when mysql starts
– removed when shuts down
• MySQL Log files
– General Log file
• Who is connecting
• From Where
• What queries thay are issuing
ITN270.001 Wake Tech
22
Status & Log Files
• MySQL Log files
– Update Log file
• queries that update the databases
• useful when there is a crash
– Make sure they will not use up your file
system space
ITN270.001 Wake Tech
23
Relocating Data Directory Contents
• Relocation Methods
– Specify your data directory at start up
– Move the data & create a link
• Assessing the effects of relocation
– disk space
• Relocating the entire data directory
ITN270.001 Wake Tech
24
Relocating Data Directory Contents
• Relocating individual databases
– shut down the server
– copy or move database directory to
ite new location
– Remove the original database
directory
– Create a symlink
– Restart the server
ITN270.001 Wake Tech
25