intTypePromotion=1
zunia.vn Tuyển sinh 2024 dành cho Gen-Z zunia.vn zunia.vn
ADSENSE
 » 
 » 

Mysql your visual blueprint for creating open source databases- P10

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

Thêm vào BST
Báo xấu
77
lượt xem 5
download
 
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Mysql your visual blueprint for creating open source databases- P10:The show tables command displays a list of tables available in the currently selected database. You can use this command when you are unsure of the exact name of a table within the database.

Chủ đề:

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

Lưu

Nội dung Text: Mysql your visual blueprint for creating open source databases- P10

  1. IMPORT AND EXPORT DATA 8 You can also import a text file you created with the SELECT INTO OUTFILE option in MySQL into Microsoft Access. To import a text file, select Get External Data, and then Import from the File menu. From the file selection dialog box, select Text Files as the file type. Select the text file to import and click OK. Access now starts the Import Text Wizard to prompt you for information about the text file. In the first dialog box, choose between Delimited and Fixed width fields. Choose Delimited for most MySQL export files. You can also choose a starting row for the import. Click Next to display the next dialog box. Choose the character that delimits the fields, typically Tab for MySQL files. You can also choose Text Qualifier if your text file encloses fields in quotation marks. Click Next to continue. In the next dialog box, choose whether to add the data to a new table or an existing table in the current database. Click Next to display another dialog box, which prompts you for the name and data type of each field. The next dialog box lets you choose a field to act as primary key. Finally, enter a table name and click Finish to complete the import. Á From the Export Text ‡ Click Next to continue. ° Select Tab as the ‚ Click Next. Wizard, select the Delimited delimiting character. option. — Click Finish to save the · Select none as the Text file. Qualifier option. s You can now import the resulting text file into MySQL. 167
  2. MySQL BACK UP DATA FROM A TABLE mysqldump -B db1 db2 W hile you can export data to a text file using SELECT and the INTO OUTFILE option, this The mysqldump utility always includes all of the columns process creates a simple text file. MySQL includes of each table. You can specify the format of the text output a separate utility, mysqldump, that you can use from the using several options: --fields-terminated-by, command line to back up a database to a text file. The --fields-enclosed-by, --fields escaped by, output file includes SQL statements and can be used to and --lines-terminated-by. These are the same as the rebuild the database or tables. options for the LOAD DATA command, described earlier in To use mysqldump, specify a database name and one or this chapter. more table names. The following example dumps the mail This utility supports the same -u and -p options as the and address tables from the testdb database: mysql command. You will usually need to use these mysqldump testdb mail address options to specify a username with access to the tables you are dumping and the correct password. If you do not specify table names, mysqldump will dump the entire database. You can also back up more than one The mysqldump utility does not create a text file by itself — database by specifying the -B option and a list of databases. it normally dumps the text to the standard output at the In this case, you cannot specify particular table names. The shell prompt. You can use the > operator from the following example backs up the db1 and db2 databases: command line to save the output to a file instead. BACK UP DATA FROM A TABLE Note: This example uses the testdb Note: You may need to specify the ¤ Type mysqldump testdb ‹ Type mysqldump testdb database and the mail and address username and password with the mail address >tables.txt and >db.txt and press Enter. tables. -u and -p options. press Enter. s This backs up the entire ⁄ From the command line, Note: Under Windows, you may s This backs up both tables database to the db.txt file. type mysqldump testdb mail need to specify the full path to to the tables.txt file. and press Enter. the command, for example, Note: You may need to specify the c:\mysql\bin\mysqldump. username and password with the s This dumps the contents of -u and -p options. the mail table to the screen. 168
  3. IMPORT AND EXPORT DATA 8 RESTORE A BACKUP FILE to create the database or table you are restoring. You may W hen you have created a backup text file using mysqldump, you can use it to re-create the also need to use the -h option to specify a host name. database or tables that were backed up. The text If you are restoring a database or table that has been file includes SQL statements, such as CREATE TABLE and corrupted or updated incorrectly, you should first use the INSERT, to rebuild the backed up databases or tables. To DROP TABLE or DROP DATABASE commands to delete any use the file, you can simply route it through the mysql existing data. command to process the SQL statements. The file created by mysqldump is a standard text file. If The following example uses the mysql command to restore you need to modify the SQL statements in the file before the database backed up in the db.txt file: importing, you can use any text editor. If you have a backup mysql testdb
  4. MySQL MANAGE AUTOMATED BACKUPS The first five options in the entry specify the minute, hour, Y ou can use the mysqldump utility to create a backup of a table, a database, or multiple databases. You can day of month, month, and day of week to execute the use a scheduling utility, such as cron, under UNIX command. You can use a number in each of these fields, or platforms to schedule regular backups using this utility. * to include all of the possible values. Separate the fields with spaces or tabs. For example, the following cron entry To schedule backups, first test the mysqldump command executes the mysqldump command every day at 3:30 AM: you will use to back up the data. For example, the following command backs up the entire testdb database to the 30 3 * * * mysqldump -uuser -ppassword backup.txt file: testdb >backup.txt mysqldump -uuser -ppassword testdb If you are using a shared system, you may need to contact >backup.txt the system administrator to gain access to cron features. Each username has a separate cron table. The username Replace user and password with the username and you use to set up the scheduled backup should have access password. After you have the correct mysqldump to the mysqldump command and permission to create a file. command, you can schedule backups. To use cron, type crontab -e to edit the crontab file. Each line in this file When this command executes daily, it will usually override includes five options for scheduling the command and the the existing file. You may need to rename files regularly or command itself. set up a more complex arrangement of cron events to use different filenames each day. MANAGE AUTOMATED BACKUPS Note: This example uses the testdb s This launches a text editor ¤ Type 30 3 * * * mysqldump › Type > followed by the database. You will need access to and loads the existing table, if and add -u and -p options filename for the backup file. the crontab command. any, into the editor. specifying the username and password for the MySQL Note: You may need to specify a path ⁄ From the UNIX command Note: This example is for UNIX database, if necessary. to the file. prompt, type crontab -e to systems. For Windows, see the Extra edit the cron table. section. ‹ Type the name of the database to back up. 170
  5. IMPORT AND EXPORT DATA 8 You can also use mysqldump to make backups on a Windows-based MySQL server. Under Windows, the utility is mysqldump.exe in the c:\mysql\bin directory. Its options are the same as the UNIX version, and the Windows command prompt also supports the > operator to redirect the output of a command to a file. To automate backups, you can use the at command from the command line under Windows NT, Windows 2000, or Windows XP. Type at /? at the command line to display a list of options for this program. You can also use the Task Scheduler included with most versions of Windows to schedule a backup. To use Task Scheduler, select Scheduled Tasks from the Control Panel. Double-click the Add Scheduled Task entry to add a new task. A wizard prompts you for information about the program that should be run and the schedule it should follow. As an alternative, you can use any standard Windows backup program to back up the MySQL data to tape or to a disk archive. The data is stored in the c:\mysql\data directory. Each database has a subdirectory under data where its data is stored. One backup utility, ntbackup.exe, is included with Windows NT and Windows 2000. ˇ Press Enter to complete Note: These keystrokes are for pico, ‡ Type crontab -l and press s This displays the cron the cron table entry. an editor available on most systems. Enter. table. Verify that your new Use the correct keystrokes for your entry is included. Á Press Ctrl-X and then Y to particular editor. save the file and exit the editor. 171
  6. MySQL BACK UP TABLE STRUCTURE statements. You can then import the file using the mysql T he mysqldump utility includes an option, -d or --no-create-db, that dumps the structure of command. For example, the following command imports the the table but not the data. You can use this option table structure saved to address.txt in the testdb database: to create an empty table with the same structure as the mysql -uuser -ppassword testdb address.txt data of the table, but not the CREATE TABLE statement. After you have dumped the structure of the table to a file, This option is useful if you want to merge the data in the you can edit the file in a text editor to change the name of table into a different table rather than creating a new table. the table and any other options within the CREATE TABLE BACK UP TABLE STRUCTURE Note: This example uses the address Note: In Windows, you may need to ‹ Type cat address.txt and Note: Under Windows systems, use table in the testdb database. specify the full path, for example, press Enter. the command more instead of c:\mysql\bin\mysqldump. cat. ⁄ From the command s This displays the contents prompt, type mysqldump. ¤ Type -d testdb address of the file you have created. >address.txt and press Enter. s If necessary, type -uuser –ppassword, replacing user s The structure of the table and password with the correct is saved to the file you options for your server. specified. 172
  7. IMPORT AND EXPORT DATA 8 CREATE A COPY OF A TABLE This form of the INSERT command uses a standard SELECT I f you need to copy a table's structure or data to a different table, you can use several different methods. statement to select the data to copy. You can optionally One simple way to copy a table is to first create the specify a list of columns to copy, and include a WHERE clause new table with the same structure, and then use an INSERT to select only certain rows to be copied into the new table. query with the SELECT option to copy the data from one An alternate method of copying the table may be easier for table to the other. complex tables. Use mysqldump -d to create a backup of For example, to copy the address table, you would first type the table's structure, as described in the previous section. DESCRIBE address; to view the structure of the table. Next, edit the resulting file and change the table name in the Next, use a CREATE TABLE query using the same column CREATE TABLE command to the name of the new table. types and other information to create the new table. After Use mysql to read the file and create the new table, and the table is created, you can use a single INSERT query to then use an INSERT query like the above to copy the data. copy all of the rows of the table to the new table. The following query copies all of the data in the address table to the address2 table: INSERT INTO address2 SELECT * FROM address; CREATE A COPY OF A TABLE Note: This example uses the address s The structure of the table is › Type name VARCHAR(100), s The new table is created. table in the testdb database. displayed. address VARCHAR(120), city VARCHAR(50), and press Á Type INSERT INTO ⁄ From the MySQL monitor, ‹ Type CREATE TABLE Enter. address2 SELECT * FROM type USE testdb; and press address2 ( and press Enter. address; and press Enter. Enter. ˇ Type state CHAR(2), updatetime TIMESTAMP ); and s The data is now copied to ¤ Type DESCRIBE address; press Enter. the second table. and press Enter. 173
  8. MySQL MYSQL ADMINISTRATION TOOLS and perform other administrative tasks from time to time. A fter you have installed a MySQL server, it runs continuously and requires little intervention. MySQL includes a variety of tools for this purpose. However, you may need to start and stop the server BASIC MYSQL ADMINISTRATION All installations of the MySQL server and client include some basic command-line administration tools. The mysqladmin utility handles many of the administration tasks. The mysqladmin Utility Start and Stop MySQL (UNIX) The mysqladmin utility runs from a client machine and On a UNIX system, you typically start the MySQL server is installed with the MySQL client software. This utility with the mysqld or safe_mysqld programs. mysqld supports a number of different commands for managing is the server program itself, and safe_mysqld is a the MySQL server. To use this utility, type mysqladmin wrapper that determines the correct settings and starts followed by the command name. You may also need to mysqld. These programs are stored in the MySQL specify the -u and -p options with a valid username binaries directory, typically /usr/local/mysql/bin. and password. The table below shows some of the most useful mysqladmin commands. To stop the MySQL server, you can use the mysqladmin shutdown command. This sends a signal COMMAND DESCRIPTION to the server to shut it down. create Create a database Start and Stop MySQL (Windows) drop Drop a database On a Windows system, you can also use mysqld to ping Check whether the server is running start the server and mysqladmin shutdown to shut it down. These commands are typically located in the status Display basic status information C:\mysql\bin directory and can be run from the extended-status Display a detailed status report command prompt. processlist Show a list of the current MySQL On Windows NT, 2000, and XP systems, you can also server processes use the operating system's service management kill Stop one or more server processes features to start and stop the server. MySQL is listed on the Services control panel, and you can start or stop it variables List MySQL variables and their or configure it to start automatically when the system values starts. You can also use the NET START and NET STOP version Display the MySQL server version commands to start and stop the server. number Example: shutdown Shut down the MySQL server NET START mysql password Change the password for the NET STOP mysql current MySQL user Examples: mysqladmin create newdatabase mysqladmin ping mysqladmin shutdown 174
  9. MANAGE THE MYSQL SERVER 9 THE WINMYSQLADMIN UTILITY The Windows installation of MySQL includes a utility select the Show Me option. The right-click menu also called WinMySQLadmin. This utility allows you to display includes an option to start or stop the MySQL server. information about the server and perform many of the same functions as the mysqladmin utility. To Although this utility provides a graphical interface run WinMySQLadmin, start the c:\mysql\bin\ to many MySQL settings, you can also use the command- winmysqladmin.exe program. line mysqladmin utility under Windows. When you start WinMySQLadmin, it briefly displays its The WinMySQLadmin window is divided into a number of window and then hides itself. It adds an icon resembling tabbed sections. Select each tab to display the associated a traffic light to the system tray. To show the page. The pages available are described below. WinMySQLadmin window, right-click the tray icon and Environment Variables The Environment tab displays the host name, user The Variables tab displays a list of MySQL variables and name, operating system, IP address, and the amount of their values. These are the same settings available in the RAM available on the server machine. If you click the my.ini file. Rather than show the contents of this file, Extended Server Status button, detailed statistics for the this tab displays the server's current variable settings. server are displayed. These are the same values shown This is the same information shown in the mysqladmin in the mysqladmin status command. variables command. Start Check Process WinMySQLadmin performs a basic check when it starts The Process tab displays a list of currently running to determine whether the server is running correctly. threads, or processes, on the MySQL server. Each entry The Start Check tab displays the results of this check includes a process ID number, the username and host, and any error messages that were reported. the database in use, the command the thread is running, the thread's current status, and the amount of Server time it has spent processing. This list is the same The Server tab displays a list of MySQL server status produced by the mysqladmin processlist variables and their values. These provide a detailed command. snapshot of the server's current performance. These You can right-click a process and select Kill to stop the values are the same as those listed by the mysqladmin process. This feature is also available from the extended-status command and are described in mysqladmin kill command. Chapter 11. Databases my.ini Setup The Databases tab displays a current list of databases The Windows version of MySQL uses a file called my.ini on the server. If you select a database from the list, the to determine various settings for the server. The my.ini list of tables in the database is displayed. If you select a Setup tab in WinMySQLadmin displays the current table from the list, detailed information about its contents of the my.ini file and allows you to edit the available columns and indexes is displayed. The right- options and save any changes you have made. Be sure click menu allows you to create and drop databases. not to change values in this file unless you are sure what they will affect. The settings available in this file Report are described in Chapter 11. The Report tab creates and displays a detailed report of Err File the MySQL server's configuration and status, including the information in several of the prior tabs. You can The MySQL server maintains a log file that includes any print the report or save it to a file. errors encountered by the server as well as basic status messages created when the server starts up or shuts down. The Err File tab displays the contents of this log file. 175
  10. MySQL CHECK SERVER STATUS As with other mysql client commands, you can specify the I f you or other users of the system are having trouble accessing the MySQL server, the first step is to -h (host) option if necessary to refer to a different MySQL determine whether the MySQL server is running at server. For a complete list of mysqladmin options, type all. One simple way to check this is with the mysqladmin mysqladmin with no options. utility. This utility includes a variety of different options for Sometimes, although the MySQL server is running, it may working with the MySQL server and is installed as part of not be responding quickly or correctly. You can find out a the MySQL client package. bit more about the server's condition with the mysqladmin To test the server, first try the ping option within status command. This displays the amount of time the mysqladmin. This option communicates with the server server has been running, the number of active threads, the and displays a simple message indicating whether it is number of queries since the server started, and other responding. The following is a simple example: information. mysqladmin ping -uuser -ppassword You can use the information displayed by the status option, and the more detailed extended-status option This command tests the default server on the local host. from mysqladmin, to determine the server's current It displays the message "mysqld is alive" if the server performance and learn how performance can be improved. responds. If there is no response from the server, it displays See Chapter 10 for details about optimizing the MySQL an error message that may help you resolve the problem. server. CHECK SERVER STATUS ⁄ From the command line, s The message indicates ¤ Type mysqladmin status s A more detailed status type mysqladmin ping and whether the server is and press Enter. report is displayed. press Enter. responding. Note: You may need to specify a Note: You may need to specify the Note: Under Windows, you may username and password. -u and -p options with the correct need to include the path with username and password values for the command, for example, your server. c:\mysql\bin\ mysqladmin.exe. 176
  11. MANAGE THE MYSQL SERVER 9 CHECK THE MYSQL SERVER VERSION Connection Localhost via UNIX socket T he mysqladmin utility also includes an option to display version information for the MySQL server. You UNIX socket /tmp/mysql.sock can use this option to determine whether your server Uptime: 17 min 24 sec supports certain features or requires an upgrade. To check As with other mysqladmin commands, you must specify a the server version, use the version command with correct username and password with the -u and -p mysqladmin, as shown in this example: options. You can also specify a hostname with the -h mysqladmin version -uuser -ppassword option and a socket file with the -S option, if necessary. This command displays the server version number, the At this writing, the latest version of MySQL 3 is version 3.23. protocol in use, and the amount of time the server has been MySQL 4.0 is also available, although it is currently in alpha running. It also displays information about threads, queries, testing. Visit www.mysql.com to find out information about and other statistics, similar to the output of the status the current version and to download files for a new version command. The following is an example of how the server if necessary. version information appears. This may vary depending on When you start the MySQL monitor with the mysql your specific system. command, a brief message is displayed indicating the Server version 3.23.46 version number of the server and the current connection. Protocol version 10 CHECK THE MYSQL SERVER VERSION ⁄ From the command s The server's version ¤ Type mysql to start the s Notice the version prompt, type mysqladmin information is displayed. MySQL monitor. information displayed before version and press Enter. the prompt. Note: Under Windows, you may Note: You may need to specify a Note: You may need to specify the need to specify the path with username and password. ‹ Type exit and press Enter. -u and -p options with the correct the command, for example, username and password values for c:\mysql\bin\ s This exits the MySQL your server. mysqladmin.exe. monitor. 177
  12. MySQL START AND STOP THE MYSQL SERVER UNDER UNIX To shut down the MySQL server, use the mysqladmin I f the MySQL server is not currently running, you can restart it. You may also need to stop and restart the shutdown command. The following command shuts down server if you have changed its configuration, or if it is the MySQL server on the local host: not responding. On UNIX systems, you can start the MySQL mysqladmin shutdown -uuser -ppassword server using the safe_mysqld command, which starts the mysqld server program. To use the shutdown command, the username you specify must have the correct permissions to shut down the server. The mysqld and safe_mysqld programs are located in On a default installation, only the root user can do this. See the bin directory under the MySQL installation directory. Chapter 11 for information on configuring MySQL users On most systems, this directory is /usr/local/mysql/bin or and passwords. /usr/local/bin. The exact directory depends on how the MySQL server was installed. The following example starts When you use the shutdown command, the server is the MySQL server: immediately shut down. It does not complete any pending queries. While the server is down, clients who attempt to /usr/local/bin/safe_mysqld & connect to it will receive an error message. The & character indicates that the program should run in See Chapter 1 for information about setting up the MySQL the background on most systems. After you have started the server to start automatically when the system boots under server, you can use mysqladmin ping or mysqladmin UNIX and Windows systems. status to verify that it is running. START THE MYSQL SERVER ⁄ From the UNIX command ¤ Type bin/safe_mysqld & ‹ Type mysqladmin ping and Note: If necessary, add the -u prompt, type cd followed by and press Enter. press Enter. and -p options with the correct the directory where the username and password. MySQL server is installed, s This starts the MySQL s This indicates that the typically /usr/local/mysql. server. server is now running. 178
  13. MANAGE THE MYSQL SERVER 9 If you encounter an error when starting the MySQL server, there are a number of potential causes. The first item to check is whether the MySQL server is currently running — you cannot run two copies of the MySQL server at the same time unless you have customized the port and socket settings. Type mysqladmin ping to check whether the server is already running. You can also type ps on a UNIX system to list running processes and check whether mysqld is included. If the server fails to start, you may find some useful information about the error that occurred in the MySQL log files. These files are located in the MySQL data directory, typically /usr/local/var or /usr/local/ mysql/data on UNIX systems. The files are hostname.log and hostname.err, with your server host name in place of hostname. If an error message is displayed indicating that the TCP/IP port or socket is already in use, either MySQL is already running, or some other service is using the port you have selected for MySQL. You need to have the correct permissions to start the MySQL server. If it did not start correctly, log in as root and attempt to start the MySQL server again. STOP THE MYSQL SERVER ⁄ From the UNIX command s This immediately shuts ¤ Type mysqladmin ping and s This indicates that the prompt, type mysqladmin down the MySQL server. press Enter. Add your server is no longer running. shutdown and press Enter. username and password if Note: If necessary, add the -u needed. Note: If other users require the and -p options with the correct MySQL server, be sure to restart username and password. it after you are finished. 179
  14. MySQL START AND STOP THE MYSQL SERVER UNDER WINDOWS in Windows 2000. Find MySQL in the list of services and use I f you are running the MySQL server on a Windows system, you can start and stop the server in a number of the toolbar buttons to start or stop the service. You can also different ways. Under Windows 95, 98, and Me, you can use the Services control panel to set up MySQL to run use the mysqld command from the command prompt to automatically when the system starts. start the server. This is typically located in the C:\mysql\bin An alternative way to control the MySQL service on directory. The following command starts the MySQL server Windows NT, 2000, and XP is to use the NET command on a typical system: from the command prompt. To start the MySQL server, use C:\mysql\bin\mysqld the NET START command. The following command starts the server: You can shut down the MySQL server from the command prompt using the mysqladmin utility, located at C:\ NET START mysql mysql\bin\mysqladmin.exe. The following example To shut down the MySQL server, use the NET STOP shuts down the MySQL server on the local machine: command at the command prompt. The following C:\mysql\bin\mysqladmin shutdown –u root –p command shuts down the MySQL server: password NET STOP mysql On Windows NT, 2000, and XP systems, MySQL is usually You can also use the mysqladmin utility to shut down set up to run as a service, and you can use the standard MySQL when it is run as a service. Choose the most Windows methods to control it. The first of these is the convenient method for your particular system. Services control panel, located under Administrative Tools START AND STOP MYSQL FROM THE COMMAND PROMPT ⁄ From the command s This starts the MySQL ¤ Type NET STOP mysql and Note: Under Windows 95, 98, and prompt, type NET START server. press Enter. Me, type mysqladmin shutdown mysql and press Enter. instead. Note: Under Windows 95, 98, s This shuts down the and Me, type the path to MySQL server. mysqld.exe instead. 180
  15. MANAGE THE MYSQL SERVER 9 If you are unable to start the MySQL server under Windows, be sure your system has the necessary components installed. In particular, MySQL requires the TCP/IP protocol, which you can install using the Network control panel. MySQL also requires that your system support the Winsock 2 standard. This is included in Windows 98 and later; you may need to install an update from Microsoft for Windows 95 systems. On Windows NT, 2000, and XP systems, most users do not have permission to start and stop services by default. If you are unable to start MySQL, log on as Administrator and try again. Although Windows uses the Administrator account, the default administrative user under MySQL is root, as on UNIX systems. You cannot shut down the MySQL server using the Task Manager. If you attempt to shut down the MySQL server in this way, it may lock up your system or fail to shut down. Use the mysqladmin utility or one of the service control methods described in this section instead. If the MySQL server does not start correctly, check the error log for information. This log is stored at c:\mysql\data using the filename mysql.err by default. The WinMySQLadmin utility, described in the section "Using WinMySQLadmin," later in this chapter, also includes an option to view the log file. START AND STOP MYSQL FROM THE SERVICES CONTROL PANEL MySql Started Manual LocalSystem Note: These instructions are for s The display indicates the s The MySql Properties › You can set the Startup Windows NT, 2000, and XP. MySQL server's current dialog box is displayed. type option to Automatic, status. Manual, or Disabled. Use ⁄ Start the Services control ‹ To start or stop the Automatic to start MySQL panel from the Control Panel ¤ Double-click the MySQL MySQL server, use the Start when the computer starts. or Administrative Tools entry. and Stop buttons. window. 181
  16. MySQL VIEW AND MANAGE RUNNING PROCESSES cause a process to lock up and stop responding. When this Y ou can use the mysqladmin utility with the processlist command to display a list of the happens, you can use mysqladmin processlist to processes currently running on the MySQL server. display the list of processes and find the problematic one. This list includes all of the queries currently running for You can then stop the process. clients as well as commands entered from the MySQL To stop a process, use the kill command with mysqladmin. monitor. The following is an example of the processlist To use this command, specify one or more process ID command: numbers. They will be immediately stopped, not mysqladmin processlist -uuser -ppassword completing their queries. The MySQL server will continue to run and process other requests. For example, the For each process currently running, this command lists its following command kills a process with the ID number process ID number, the MySQL user ID that started it, the 3037: host the client connected from, the database used by the query, the command issued to the server, the amount of mysqladmin kill 3037 time the query has been in progress, the current status of If you stop a process that is currently writing data to a table, the process, and the query or command that started the it may leave partial or corrupt data in the table. Because of process. this, you should use the kill command only when you are Occasionally, a database query will take an excessive certain the process will not complete on its own. amount of time, slowing down the server, or an error will VIEW AND MANAGE RUNNING PROCESSES VIEW RUNNING PROCESSES ⁄ From the command s The list of processes is prompt, type mysqladmin displayed. Note: This example assumes that the processlist and press Enter. MySQL server is currently running on the local machine. Note: You may need to add -u and -p options to specify a valid username and password. 182
  17. MANAGE THE MYSQL SERVER 9 The MySQL server can have a number of processes, also known as threads, running concurrently. The number of threads that can run at the same time depends on the operating system and on the configuration of the MySQL server. Chapter 10 explains how you can configure the server for a maximum number of concurrent threads. The status of threads in the process list may indicate Query if a query is in process or Sleep if the thread is currently inactive. The server keeps a number of threads in an inactive state so that it can use them for queries rather than starting a new thread each time. You can use the ps command in most UNIX systems to display a list of processes currently running on the server and their ID numbers. While this will show all of the MySQL threads currently in use, the process numbers used by MySQL are not the same as the system's ID numbers. You can also kill MySQL threads using the operating system's commands. However, using the mysqladmin utility to kill threads has less chance of corrupting data, and is easier because you can use the processlist command to determine the correct thread to kill. KILL A PROCESS s The list of processes is ¤ Type mysqladmin kill ‹ Type mysqladmin displayed. followed by the number of processlist and press Enter. ⁄ From the command the process to kill. prompt, type mysqladmin s The list of processes is processlist and press Enter. s The process is immediately displayed again. Note that stopped. the killed process is no longer listed. 183
  18. MySQL USING WINMYSQLADMIN The Start Check tab runs some tests to verify that the server T he Windows version of the MySQL server includes the WinMySQLadmin utility. This is a graphical utility is running correctly. The Server tab displays a list of statistics that allows you to manage settings and display for the MySQL server. The my.ini Setup tab allows you to statistics for the MySQL server from within Windows. To edit the my.ini file, which stores default settings for the run this tool, start the winmysqladmin.exe program in server. The Err File tab displays the server error log. The the C:\mysql\bin directory. Variables tab displays a list of MySQL variables and their current settings; these are explained in detail in Chapter 11. When WinMySQLadmin is first run, it shows briefly and then hides itself. It remains resident, and an icon appears in The Process tab in WinMySQLadmin shows a list of the system tray. Right-click the icon and select the Show Me processes and their ID numbers, similar to the output of the option to view the utility. mysqladmin processlist command. You can right-click an entry in the list and select Kill Process to stop a process. The WinMySQLadmin display is divided into a number of tabbed sections. The first, Environment, includes The Databases tab allows you to view information about information about the computer MySQL is running on the databases stored on the server, the tables within a and a number of statistics for the server. These are the same database, and the columns of each table. This is similar to values available from the mysqladmin status command. the SHOW DATABASES and SHOW TABLES commands from the MySQL monitor. The right-click menu includes options to create or drop databases and to refresh the current list. USING WINMYSQLADMIN C:/mysql Note: Type c:\mysql\bin\ ⁄ From WinMySQLadmin's ¤ Click the my.ini Setup s This displays the contents winmysqladmin from the command Environment tab, click the tab at the top of the of the initialization file, prompt to start the utility. Right-click Extended Server Status button. WinMySQLadmin which you can edit if needed. the tray icon and select Show Me to dialog box. display the utility. s This displays a detailed set of statistics for the MySQL server. 184
  19. MANAGE THE MYSQL SERVER 9 The final tab of the WinMySQLadmin utility, Report, allows you to display a detailed report about the MySQL server. After you select the Report tab, click the Create the Report button to create the report. The report includes information about the current installation of MySQL, how it was compiled, and the system it is running on. It also lists the contents of the my.ini file, the current status of the server as displayed in the Server tab, the list of variables as shown in the Variables tab, and the error log as listed in the Err File tab. The report is displayed in a text window within the WinMySQLadmin interface. You can use the Print button to print a copy of the report or use the Save As button to save it as a text file. This tab also includes Cut, Copy, Paste, Delete, and Select All buttons that you can use to edit the report or copy it to another program. All of the information in the WinMySQLadmin utility is also available from the command line using the MySQL monitor and the mysqladmin utility. You can use whichever tools you are most comfortable with to manage the server. address name address ‹ Click the Process tab at s This displays a current › Click the Databases tab at ˇ Select a database and a the top of the dialog box. process list for the server. the top of the dialog box. table by clicking their names. s The list of databases on the s The columns and index server is displayed. information for the table are displayed. 185
  20. MySQL MANAGE MYSQL WITH MYSQLGUI functions. These include Ping to check on the server, T he MySQLGUI utility provides another interface to the MySQL server, and includes a variety of useful Refresh to refresh the status display, and Shutdown to administration functions. While most of these are also shut down the server. available from command-line utilities, MySQLGUI provides a The Show variables button displays MySQL's current user-friendly graphic interface. This utility provides some of variable settings. The Display status button displays a the same features as WinMySQLadmin, described in the complete status report, similar to the output of the previous section, but is also available for non-Windows mysqladmin extended-status command. The Show operating systems. See Chapter 1 for information on processes button displays a list of current MySQL threads, obtaining and installing MySQLGUI. similar to the mysqladmin processlist command. To manage the MySQL server using MySQLGUI, select The Flush tables button closes all open tables. The Flush Commands, Tables, and then Administration from the hosts button clears the host cache. The Flush logs button menu. The Administrator Panel option provides a central closes and re-opens the log files. See Chapter 10 for more method of accessing various management functions. Some information about log files. The Flush status command of these functions are also available directly from the clears the status variables. Administration menu. In addition to these options, MySQLGUIs administration The Administrator Panel displays a summary of the server's panel includes options to create and drop databases, and status, similar to the output of the mysqladmin status to change passwords and grant privileges to users. See command, and displays the current time from the MySQL Chapter 11 for more information about securing MySQL server. The panel also includes buttons for administrative by setting up users and passwords. MANAGE MYSQL WITH MYSQLGUI Note: See Chapter 1 for information ⁄ From the MySQLGUI s The administration panel is ¤ Click the Show variables on installing and starting menu, click Commands, displayed. button to display the variable MySQLGUI. Administration, and then list. Administrator Panel. 186
ADSENSE

CÓ THỂ BẠN MUỐN DOWNLOAD

THÔNG TIN
TRỢ GIÚP
HỖ TRỢ KHÁCH HÀNG
Theo dõi chúng tôi

Chịu trách nhiệm nội dung:

Nguyễn Công Hà - Giám đốc Công ty TNHH TÀI LIỆU TRỰC TUYẾN VI NA

LIÊN HỆ

Địa chỉ: P402, 54A Nơ Trang Long, Phường 14, Q.Bình Thạnh, TP.HCM

Hotline: 093 303 0098

Email: support@tailieu.vn

Giấy phép Mạng Xã Hội số: 670/GP-BTTTT cấp ngày 30/11/2015 Copyright © 2022-2032 TaiLieu.VN. All rights reserved.

 

Đồng bộ tài khoản
2=>2