itn270.001 wake tech1 itn270 advanced internet databases lecture 14. introduction to mysql...
TRANSCRIPT
![Page 1: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/1.jpg)
ITN270.001 Wake Tech 1
ITN270 Advanced Internet DatabasesLecture 14. Introduction to MySQL Administration
and the MySQL Data Directory
•Topics:–Overview of Administrative Duties–MySQL Data Sirectory
![Page 2: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/2.jpg)
ITN270.001 Wake Tech 2
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
![Page 3: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/3.jpg)
ITN270.001 Wake Tech 3
Overview of Administrative Duties
• MySQL Clients & Utilities– mysqlcheck
– isamchk
– myisamchk
• table analysis
• table optimization
• crash recovery
– mysqldump• backing up databases
![Page 4: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/4.jpg)
ITN270.001 Wake Tech 4
Overview of Administrative Duties
• The server’s language– SQL
• The MySQL data directory– structure
• where the files located
– contents• file system
![Page 5: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/5.jpg)
ITN270.001 Wake Tech 5
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
![Page 6: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/6.jpg)
ITN270.001 Wake Tech 6
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
![Page 7: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/7.jpg)
ITN270.001 Wake Tech 7
Security
• File system security
• Server security– grant access priviliges
![Page 8: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/8.jpg)
ITN270.001 Wake Tech 8
Database Repair & Maintenance
• Crash recovery
• Preventive maintenance
![Page 9: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/9.jpg)
ITN270.001 Wake Tech 9
The MySQL Data Directory
• The MySQL Data Directory Stores– databases– status files– log files
![Page 10: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/10.jpg)
ITN270.001 Wake Tech 10
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
![Page 11: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/11.jpg)
ITN270.001 Wake Tech 11
Location of the Data Directory
• Specification of the location of data directory--datadir = dir_name
• Ask the server for location– mysqladmin variables– or
![Page 12: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/12.jpg)
ITN270.001 Wake Tech 12
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
![Page 13: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/13.jpg)
ITN270.001 Wake Tech 13
How the MySQL Server Provides Access to Data
![Page 14: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/14.jpg)
ITN270.001 Wake Tech 14
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
![Page 15: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/15.jpg)
ITN270.001 Wake Tech 15
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
![Page 16: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/16.jpg)
ITN270.001 Wake Tech 16
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
![Page 17: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/17.jpg)
ITN270.001 Wake Tech 17
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
![Page 18: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/18.jpg)
ITN270.001 Wake Tech 18
How the Tables are represented
• MERGE Tables– *.frm
– *.MRG
• BDB Tables– *.frm - description
– *.db - data & index
• InnoDB Tables– *.frm
– tablespace
![Page 19: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/19.jpg)
ITN270.001 Wake Tech 19
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
![Page 20: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/20.jpg)
ITN270.001 Wake Tech 20
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
![Page 21: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/21.jpg)
ITN270.001 Wake Tech 21
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
![Page 22: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/22.jpg)
ITN270.001 Wake Tech 22
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
![Page 23: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/23.jpg)
ITN270.001 Wake Tech 23
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
![Page 24: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/24.jpg)
ITN270.001 Wake Tech 24
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
![Page 25: ITN270.001 Wake Tech1 ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory Topics: –Overview](https://reader030.vdocuments.us/reader030/viewer/2022032802/56649e195503460f94b0685d/html5/thumbnails/25.jpg)
ITN270.001 Wake Tech 25
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