MySQL Administrator's Bible- P3

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

138
lượt xem 44
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

MySQL Administrator's Bible- P3: Với tập trung đặc biệt vào việc phát hành lớn tiếp theo của MySQL, nguồn tài nguyên này cung cấp một khuôn khổ vững chắc cho bất cứ ai mới vào MySQL hoặc chuyển từ một nền tảng cơ sở dữ liệu, cũng như các quản trị MySQL kinh nghiệm. Các bộ đôi tác giả cao hồ sơ cung cấp bảo hiểm thiết yếu của các nguyên tắc cơ bản của phạm vi bảo hiểm cơ sở dữ liệu quản lý, bao gồm cả cách tiếp cận độc đáo MySQL của các tính năng cơ sở...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: MySQL Administrator's Bible- P3

Accessing MySQL 3 the server host and the mysqld instance. If you experience any problems SQLyog has very thor- ough, context-sensitive help. Figure 3-1 shows this connection dialog. FIGURE 3-1 SQL query creation SQLyog has two methods of SQL query creation. With the ﬁrst method you type the query in a query building window. SQLyog provides two tools to assist you — the auto-completion feature and SQL templates. mysql does not have tab completion on Windows (see the previous section on mysql features). With SQLyog you can use the Tab key to complete table or column names or even functions, no matter what operating system you are using. SQL templates help with the proper syntax for the various SQL commands. First, right-click in the query building window. This will bring up a menu where you left-click Insert Templates to use a template. Figure 3-2 shows the list of available templates with the CREATE TRIGGER statement template selected. At this point you just click the Insert button and SQLyog will create the template for you to ﬁll in with your speciﬁc data. The second method of query writing is using the query builder to create the query. This is a more graphical method of query building where you drag the appropriate tables from the schema objects to a blank window. You can then do the common query actions of joining tables, sorting, ﬁltering, and grouping based on your chosen criteria. SQLyog will then build the SQL command for you to execute without actually having to type any SQL code. Figure 3-3 shows how the query builder looks with a simple query built from the sakila database. 67
Part I First Steps with MySQL FIGURE 3-2 FIGURE 3-3 68
Accessing MySQL 3 After building the query you can copy the query over to the Query tab and then execute it with just a couple of clicks. Database management SQLyog has numerous features designed for database management. Among the tasks you can perform are: ■ Create new tables ■ Manage table indexes ■ Manage users ■ Synchronize databases ■ Back up and restore databases ■ Create triggers, views, events, and stored procedures Database synchronization One of the best features of SQLyog is the database synchronization with its powertool. This tool allows you to synchronize two different databases by creating the corresponding SQL commands needed to match both the structure and data of the two database tables. There is a wizard that will guide through each step of the process. The process is simple — just select the databases and tables to synchronize and SQLyog takes care of everything else. Resolving a data synchro- nization problem by hand is incredibly difﬁcult, and SQLyog reduces the complexity to a few simple clicks. phpMyAdmin phpMyAdmin is a free set of PHP scripts that are used to manage a MySQL server via a web interface. Because phpMyAdmin is based on these PHP scripts it does require a web server to run. Though this might be seen as a drawback, it also means that you can access your MySQL servers from any computer that has a web browser. phpMyAdmin can be used to manage an entire MySQL server or a single database. You can ﬁnd more information about phpMyAdmin, including how to download the program, at http://www.phpmyadmin.net/. We are not going to show how to install phpMyAdmin for your server setup because it varies depending on your operating system. Typically, it is not very difﬁcult. Once done with the installation of phpMyAdmin, access the system by simply going to the following URL in your browser: http://yourserverip/phpmyadmin. You will need to replace yourserverip with the hostname or IP address of your web server. You will be presented with a login screen where you will input your MySQL server login credentials. 69
Part I First Steps with MySQL Figure 3-4 shows the opening screen for phpMyAdmin once you log in. FIGURE 3-4 You will use the left-hand frame in phpMyAdmin for navigation. Initially you will see the databases on the server displayed in this area. Tables will also be displayed under each database as they are created. In addition to the database navigation area on the left side, a number of options are available on the right-hand side of the split screen. Creating a database If you need to create a database there is a Create Database option on the main screen. You sim- ply enter the new database name and click the Create button. Creating a table in your database Creating a table is almost as easy as creating a database. Just click the database you created in the navigation frame and a new window will appear on the right-hand side of the split screen. To create a table you just use the Create New Table option. You should enter the name of the new table into the Name ﬁeld, and the number of expected ﬁelds for the table into Fields. At this point, after you click Go, you just ﬁll in the information for the columns in your new table. Once you are done with conﬁguration, clicking the Save button will create your new table. 70
Accessing MySQL 3 Data insertion into a table Once your table is conﬁgured you can ﬁll it with data using phpMyAdmin. On the screen that is displayed after you create a table there is an Insert tab. Click this and another screen should appear. This screen is your data entry screen. When you are done with a record just click Save and it will be saved to the table. After you click Save, the record is saved to the testing table, and the data entry screen is shown again. You can insert more records by selecting Insert again. If you are going to be inserting multiple records it is faster if you select the Insert Another New Row radio button on the input form. Database backup Backing up databases is easy with phpMyAdmin. You select the database you want to back up by clicking the database name in the left-hand navigation bar. Next you click the Export button and then make sure all tables are highlighted that you want to back up. You can then specify the options you want under Export. Make sure that you enter a ﬁlename so you can save the output. In the background phpMyAdmin is using the mysqldump program, which is covered in Chapter 13. The phpMyAdmin program has a fairly user-friendly interface. Because it is used by a number of web-hosting providers, many administrators are at least somewhat familiar with it. Although it has a modest range of abilities, it does have some ﬂexibility in that you can access the program from anywhere with a web browser. It should be pointed out, as we discuss in Chapter 20, that running both a web server and a database server on the same server increases your security risk. Sharing resources means there will be fewer available for mysqld. Think very carefully before installing phpMyAdmin on the same machine as mysqld. MySQL Query Browser The Query Browser is part of the GUI bundle that MySQL has available for free download from http://dev.mysql.com/downloads/gui-tools. Also included in this bundle are the MySQL Administrator tool (discussed in the following section) and the MySQL Migration Tool, which we will not be covering. The Query Browser and other associated tools are available for Windows, Macintosh OS X, and Linux so it allows for a good degree of cross-platform usage. The Query Browser is a GUI tool for creating, executing, and optimizing queries. The Query Browser includes the following components: ■ Query Toolbar: Create small queries and execute them. Browse old queries. ■ Script Editor: Useful for editing more complex queries. 71
Part I First Steps with MySQL ■ Results Window: Displays query results. ■ Database Explorer: Navigate, create, and drop tables and ﬁelds. The central interface of the Query Browser provides access to all of the preceding functionality. This window is also customizable by allowing you to control which toolbars are present. The Query History functionality combined with navigation buttons allows you to browse your query history and re-execute queries with or without editing them. To begin a Query Browser session you have to establish a connection to your server. When you start Query Browser you see the screen in Figure 3-5. FIGURE 3-5 One of the biggest shortcomings of Query Browser is that it does not allow for the direct con- nection to a server using an ssh session. In many organizations, administrator access to mysqld is restricted to connecting only from the local server. In order to use Query Browser you must establish an ssh tunnel manually, which can be a tricky operation. It is also frustrating to have to set up an ssh tunnel every time you want to use MySQL Query Browser. In contrast, the pre- viously discussed SQLyog program allows direct secure connections using ssh tunneling very easily. Once you have established your connection to the server you are presented with the main Query Browser screen, which is shown in Figure 3-6. Looking on the right-hand side of the screen you can see the databases on the connected server. In this case you can see the information_schema and mysql databases (used by the MySQL server) and a test database. Also there is the sakila database, which you will be using for the example. 72
Accessing MySQL 3 FIGURE 3-6 To work with the sakila database a single mouse-click on the arrow to the left of the sakila database icon will display all the tables. Just enter the query to execute and click the Execute button to the right of query entry screen. Once you enter the query and execute it the results are shown in the results pane on the bottom left of the screen, as shown in Figure 3-7. After you have entered multiple queries you can use the Go Back and Next buttons to browse through the history of the already executed queries. Under the Tools menu you can start MySQL Administrator, the mysql command-line client, the Windows command line (if you are on Windows), a text importer (that uses regular expressions), and the connections manager, where you can save connection information. You can also manage stored routines very easily with Query Browser. This might be one of the best features of Query Browser because it is very difﬁcult to manage a stored routine from the MySQL command line (see Chapter 7 for more information about stored routines). Though a procedure or function can be programmed from the command line, it is awkward at best. A graphical environment such as Query Browser makes it much easier to work with stored procedures and functions. 73
Part I First Steps with MySQL FIGURE 3-7 To begin a new stored procedure, make sure you have a database selected and then just click Script and then Create Stored Procedure/Function. Once you have created the stored procedure or function you will be presented with the editing screen as shown in Figure 3-8. All of the initial setup is already done for you. Just write your query and click the Execute but- ton to store it on the server. You can also examine and edit stored procedures and functions that are already created. To do this just click Script on the menu bar and select Edit All Stored Pro- cedures/Functions. Figure 3-9 shows what this looks like. Now you just edit the appropriate procedure and click Execute to reload the procedures. The Query Browser program is well rounded with only one major weakness: Though it is more specialized than the other programs covered here, it is designed to work in tandem with the MySQL Administrator program covered in the next section. MySQL Administrator The MySQL Administrator is included in the same bundle with the Query Browser. It is a cross-platform tool that runs on Windows, Linux, and the Mac OS X. MySQL Administrator is a GUI console that enables you to administer a mysqld server instance. 74
Accessing MySQL 3 FIGURE 3-8 FIGURE 3-9 75
Part I First Steps with MySQL Some of the functionality that MySQL Administrator allows includes: ■ User management ■ Monitoring database health ■ Performing backups and restores ■ Managing server logs ■ Monitoring server connections The connection dialog is the same as for Query Brower. Once you have established your connec- tion to a server the main screen will look similar to what is shown in Figure 3-10. FIGURE 3-10 This is the central control panel used to manage your servers. On the left-hand panel you can see the icons that are used to access the screens for speciﬁc functions. 76
Accessing MySQL 3 User administration MySQL Administrator makes creating users, dropping users, granting privileges, and seeing priv- ileges quite easy. Using an intuitive form you can add users, set passwords, and specify more information about the user. Figure 3-11 shows the user administration screen. FIGURE 3-11 As you can see there are two tabs in the right-hand pane. The ﬁrst tab (User Information) is used to enter the basic login credentials and details about the user such as name and e-mail. The Schema Privileges tab is used to both assign and remove global, database, and table/column-level privileges. There is a list of the system users in the lower-left part of the screen. To view and edit the current information on a user just click the username. Then you will be able to edit the properties on the right-hand side of the screen. Once you are done just click Apply Changes. With a reasonable layout this screen makes it very easy to edit a user’s password or schema privileges. 77
Part I First Steps with MySQL Health monitoring The health monitoring screen gives you a view into the activity of your MySQL server. Very nice graphing functionality shows you: ■ The percentage of memory used ■ The number of current connections ■ The number of SQL queries being executed This provides both an immediate view of system activity, plus a more long-term view as it keeps track of statistics as long the Administrator is running. Figure 3-12 shows the Health monitoring screen. FIGURE 3-12 78
Accessing MySQL 3 Although this is a nice feature it does require that the MySQL Administrator program be con- nected to the database server for extended periods of time if you want to gather long-term statis- tics. Chapter 19 covers various programs that can be used for monitoring your database servers. Backup and recovery With the backup and recovery screen, you can create backups and restore data using a graphi- cal interface. You can choose to back up an entire database or one or more tables by clicking on check boxes. This screen looks fancy, but it is actually simply providing a nice interface to the mysqldump program, which is covered in Chapter 13. We also cover general background about why it’s important to back up your servers and other topics such as how often you should back up. Figure 3-13 shows the backup screen with the sakila database chosen to backup. FIGURE 3-13 79
Part I First Steps with MySQL Server information The server information screen shows basic server and client information, including status, ver- sion numbers, and host information. Replication status The replication status screen allows you to view the network topology of your replication master and slaves if the slaves are conﬁgured with the report-host option in the MySQL server conﬁgu- ration ﬁle and you are connected to the master server with the MySQL Administrator program. Server log management On the server log management screen, you can view all the log ﬁles — error logs, binary logs, and slow query logs. This can help you when troubleshooting server issues. Service control When your database servers are running on Microsoft Windows, MySQL Administrator allows you to control service creation and automatically creates, installs, and conﬁgures services through the service control screen. You can even use MySQL Administrator to start and stop your server. Catalogs The catalogs screen allows you to browse your databases and corresponding table information available about columns and indexes, views, and even stored procedures. From this screen you can perform basic table maintenance such as optimizing and checking tables. Figure 3-14 shows this screen for the sakila database. Overall the MySQL Administrator provides a decent program to help with server administra- tion. The Query Browser is the stronger of the two related programs. Both programs are avail- able from http://dev.mysql.com/downloads/gui-tools. MySQL Workbench MySQL Workbench is a visual data modeling and database design tool. Schema creation and change scripts can be automatically created from the database designs. There are two versions of MySQL Workbench: an open source Community Edition and a commercial Standard Edition (SE). The SE version includes enhancements for reverse and forward engineering of databases, synchronization to and from live databases, documentation, and model validation. As an example of what can be done with MySQL Workbench we reverse engineered the sakila database. This entire process took about ﬁve minutes. Figure 3-15 is what you see when you start up the Workbench program. 80
Accessing MySQL 3 FIGURE 3-14 FIGURE 3-15 81
Part I First Steps with MySQL FIGURE 3-16 Now that you have the program started, select Database from the menu and then Reverse Engineer. This will bring up the connection screen where you have to ﬁll in the hostname, user- name, and password used to access the server. You need to make sure that you have permission to access the server you are trying to reverse engineer. Figure 3-16 shows the connection screen. Once you have established the connection to the server, the next screen will allow you to select (by default) all the tables or any number of individual tables. Once you have selected the desired tables it is just a matter of having MySQL Workbench construct the Entity Relationship (ER) diagram. As the server builds the diagram it will ask if it should automatically place the tables on the diagram. We recommend that you choose this option. Once the initial diagram is built you can then rearrange them to your heart’s content. You can print the ER diagram directly or export to a PDF ﬁle. Figure 3-17 shows the sakila database after being reverse engineered. 82
Accessing MySQL 3 FIGURE 3-17 Reverse engineering a database is easy with the proper tools. If you have a signiﬁcant number of tables it might take some time to arrange the tables in your ER diagram as you want, but that this is much faster than building the ER diagram by hand. In the preceding ﬁgure, the lines on the diagram represent the foreign key relationships between the tables. Summary Any database administrator should to take the time ﬁrst to learn the mysql client, because it is the default access client and will always be available. The other tools, both GUI-based and command-line–oriented, certainly have a place in many environments. Some tasks can be much more difﬁcult with command-line tools or even impossible, such as with ER diagramming. This chapter covered: ■ MySQL command-line tools including: ■ The mysql client ■ The mysladmin tool ■ The mysqlshow tool 83
Part I First Steps with MySQL ■ GUI tools for MySQL administration including: ■ SQLyog ■ phpMyAdmin ■ MySQL Query Browser ■ MySQL Administrator ■ MySQL Workbench 84
Developing with MySQL IN THIS PART Chapter 4 How MySQL Extends and Deviates From Standard SQL Chapter 5 MySQL Data Types Chapter 6 MySQL Index Types Chapter 7 Stored Routines, Triggers, and Events Chapter 8 MySQL Views Chapter 9 Transactions in MySQL