1 / 18

MySQL Administration The Mysql Data Directory

MySQL Administration The Mysql Data Directory. Chapter 11. Location of the Data Directory. mysql> SHOW VARIABLES LIKE 'datadir'; . Structure of the Data Directory. Windows filesystem: Each database has a database directory located under the data directory.

kail
Download Presentation

MySQL Administration The Mysql Data Directory

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. MySQL AdministrationThe Mysql Data Directory Chapter 11 MySql Developer's Library. Paul Dubois, 4th Ed

  2. Location of the Data Directory • mysql> SHOW VARIABLES LIKE 'datadir'; MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  3. Structure of the Data Directory • Windows filesystem: • Each database has a database directory located under the data directory. • Tables, views, and triggers within a database correspond to files in the database directory. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  4. Structure of the Data Directory • The server's process ID (PID) file. • On Start-up the server writes its process ID to this file so that other programs can discover the value if they need to send signals to it. • Status and log files that are generated by the server. • files about server's operation and are valuable for administrators, especially when something goes wrong and you're trying to determine the cause of the problem. • Server-related files, such as the DES key file or the server's SSL certificate and key files. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  5. How the MySQL Server Provides Access to Data • In a client/server setup, all databases under the data directory are managed by a single entity, the MySQL server mysqld. • Client programs never manipulate data directly. • The server provides the sole point of contact through which databases are accessed, acting as the intermediary between client programs and the data they want to use. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  6. How the MySQL Server Provides Access to Data MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  7. How the MySQL Server Provides Access to Data • When the server starts, it opens any logs that you request it to maintain • To access data, a client program establishes a connection to the server, and then communicates requests as SQL statements to perform the desired operations such as creating a table, selecting rows, or updating rows. • The server performs each operation and sends back the result to the client. • The server is multi-threaded and can service multiple simultaneous client connections. • Because update operations are performed one at a time, the server in effect serializes requests so that two clients can never change a given row at exactly the same time. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  8. How the MySQL Server Provides Access to Data • The server does not have exclusive control of the data directory when: • When you run multiple servers on a single data directory. • It's possible to start multiple servers and point them at the same data directory • NOT RECOMMENDED • System must provide good file locking or the servers will not cooperate properly. • You also risk having your logs become a source of confusion if you have multiple servers writing to them at the same time. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  9. How the MySQL Server Provides Access to Data • When you use direct-access maintenance utilities. • Programs such as myisamchk and myisampack are used for MyISAM table maintenance, troubleshooting, repair, and compression operations. • These programs operate directly on the files that correspond to the tables. • Because these utilities can change table contents, using them to operate on tables at the same time the server is doing so can cause table damage. • Stop the server before running these table utilities. • If that is not possible, it's very important to understand how to tell the server not to access a table while you're using a utility that operates directly on the table files • An alternative to myisamchk is to use statements such as CHECK TABLE and REPAIR TABLE • Eliminate the problem of interaction with the server by instructing the server itself to perform the table maintenance operations. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  10. MySql Status and Log Files MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  11. The Process ID File • The MySQL server writes its process ID (PID) into the PID file when it starts, and removes the file when it shuts down. • Other processes can use this file to determine whether the server is running and what its process ID is if so. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  12. The MySQL Logs • MySQL can maintain several types of log files. • Most logging is optional; you use server startup options to enable just the logs you need • Logs can grow quite large, so it's important to make sure they don't fill up your filesystem. • You can expire the logs periodically to keep the amount of space that they use within bounds. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  13. Error log • Contains a record of diagnostic information produced by the server when exceptional conditions occur. • If the server fails to start up or exits unexpectedly, this log is useful because it will often contain the reason why. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  14. General query log • Provides general information about server operation: who is connecting from where and what statements they are issuing. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  15. Binary log • Statements that modify database contents. • Also contains information such as timestamps needed to keep slave servers synchronized when the server is a master server in a replication setup. • The contents of the binary log are written in binary format as "events" that can be executed by providing them as input to the mysql client. • The accompanying binary log index file lists which binary log files the server currently is maintaining. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  16. Binary log • Useful if you have a crash and must revert to backup files • Because you can repeat the updates performed after the backup was made by feeding the log files to the server. • This enables you to bring your databases up to the state they were in when the crash occurred. • Used if you set up replication servers, because it serves as a record of the updates that must be transmitted from a master server to slave servers. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  17. Binary log • columns for date and time, • server thread ID (connection ID), • event category, • event-specific information. MySql Dwvwloper's Library. Paul Dubois, 4th Ed

  18. Secure Log Files • Make sure that your log files are secure and not readable by arbitrary users • May contain the text of statements that include sensitive information such as passwords. • For example, the following log entry displays the password for the root user MySql Dwvwloper's Library. Paul Dubois, 4th Ed

More Related