1 / 25

Topics: Overview of Administrative Duties MySQL Data Sirectory

ITN270 Advanced Internet Databases Lecture 14. Introduction to MySQL Administration and the MySQL Data Directory. Topics: Overview of Administrative Duties MySQL Data Sirectory. Overview of Administrative Duties. The MySQL Server mysqld MySQL Clients & Utilities mysql

tiger-henson
Download Presentation

Topics: Overview of Administrative Duties MySQL Data Sirectory

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ITN270 Advanced Internet DatabasesLecture 14. Introduction to MySQL Administrationand the MySQL Data Directory Topics: Overview of Administrative Duties MySQL Data Sirectory 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 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 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 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 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 ITN270.001 Wake Tech

  7. Security • File system security • Server security • grant access priviliges ITN270.001 Wake Tech

  8. Database Repair & Maintenance • Crash recovery • Preventive maintenance ITN270.001 Wake Tech

  9. The MySQL Data Directory • The MySQL Data Directory Stores • databases • status files • log files 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 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 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 ITN270.001 Wake Tech

  13. How the MySQL Server Provides Access to Data 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 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 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 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 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 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 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 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 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 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 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 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 ITN270.001 Wake Tech

More Related