?? mysql-admin.html
字號:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0//EN" "http://www.w3.org/TR/REC-html40/strict.dtd"><html><head><title>Basic MySQL Administration</title>
<link rel="stylesheet" href="mysql-admin_files/home.css" type="text/css">
<link rel="stylesheet" href="mysql-admin_files/webpub.css" type="text/css"></head>
<body bgcolor="#ffffff">
<a href="http://www.washington.edu/"><img id="toplogo" src="mysql-admin_files/UWlogo150p.gif" alt="University of Washington" border="0"></a>
<div id="toolbar">
<span class="l1text"><a href="http://www.washington.edu/home/search.html">Search</a> |
<a href="http://www.washington.edu/home/directories.html">Directories</a> |
<a href="http://www.lib.washington.edu/research/">Reference
Tools</a></span></div>
<!--Section Banner Table -->
<div class="bannerwrapper">
<div id="topbanner"><img src="mysql-admin_files/rib0.gif" alt="" height="16" width="6"></div>
</div>
<div id="crumbs"><span class="l1text"><a href="http://www.washington.edu/">UW Home</a> > <a href="http://www.washington.edu/uwin/">UWIN</a> > <a href="http://www.washington.edu/computing/">Computing and Networking</a> > <a href="http://www.washington.edu/computing/web/">Web</a> > <a href="http://www.washington.edu/computing/web/publishing/">Web Publishing</a> > <a href="http://www.washington.edu/computing/web/publishing/mysql.html">Using MySQL</a></span> </div><div class="forceclear"></div>
<table style="table-layout: fixed;" align="right" bgcolor="#ffffcc" border="1" cellpadding="5" cellspacing="0">
<tbody><tr>
<td width="120">
<p>This article applies to the following UW Web servers:</p>
<center>
<a href="http://faculty.washington.edu/">faculty</a><br>
<a href="http://staff.washington.edu/">staff</a><br>
<a href="http://students.washington.edu/">students</a><br>
<a href="http://courses.washington.edu/">courses</a><br>
<a href="http://depts.washington.edu/">depts</a><br>
</center>
</td>
</tr>
</tbody></table>
<h1>Basic MySQL Administration</h1>
<h4>Summary</h4>
<p>This article covers the basics of MySQL administration. You will
learn how to stop and restart MySQL safely and how to configure and
administer your database server.</p>
<p><b>Note:</b> This article assumes that you have followed the instructions for <a href="http://www.washington.edu/computing/web/publishing/mysql-install.html">installing and starting MySQL</a> on the UW servers.</p>
<p><i>Included on this page:</i></p>
<ul>
<li><a href="#1">Determining Whether Your MySQL Server Is Running</a>
</li><li><a href="#2">Stopping and Restarting Your MySQL Server</a>
</li><li><a href="#3">Using cron to start Your MySQL Server Automatically After a System Restart</a>
</li><li><a href="#4">Logging Client Interaction With Your MySQL Server</a>
</li><li><a href="#5">References</a>
</li></ul>
<h4><a name="1">Determining Whether Your MySQL Server Is Running</a></h4>
<p>In order to use or administer MySQL, your MySQL server needs to be
running. To verify that this is the case, follow these instructions:</p>
<ol>
<li> <p>Log in to your Homer or Dante account with <a href="http://www.washington.edu/computing/software/uwick/teraterm">Tera Term</a>
or another terminal emulator.</p>
</li><li> <p>Press the O key for Other.</p>
</li><li> <p>Press the W key to drop into the <a href="http://www.washington.edu/computing/web/publishing/environs.html">Web development environment</a> and <tt>cd</tt> into your <tt>mysql</tt> directory:
</p><p><span class="code">cd mysql</span></p>
<p><b>Note:</b> Stay in your <tt>mysql</tt> directory; at no point in this installation should you <tt>cd</tt> to <tt>public_html</tt> or <tt>student_html</tt>. Doing so will cause errors if you attempt to stop or restart MySQL.</p>
</li><li> <p>Maximize the size of your terminal window, and type the following at the command line:</p>
<p><span class="code">ps -ef | grep mysql | grep <i>accountname</i></span></p><p>
</p><p>Substitute your UW NetID or the account name for <i>accountname</i>.</p>
<p>If MySQL is running, you will see output similar to the following:</p>
<p></p><table class="code">
<tbody><tr><td>
<i>accountname</i> 66060 53816 4 11:15:59 pts/44 0:00 grep mysql<br>
<i>accountname</i> 68046 53816 0 11:15:46 pts/44 0:00 sh ./bin/safe_mysqld<br>
<i>accountname</i> 79940 68046 0 11:15:46 pts/44 0:00
/rc34/d82/<i>accountname</i>/mysql/bin/mysqld
--defaults-extra-file=/rc34/d82/<i>accountname</i>/mysql/data/my.cnf
--basedir=/rc34/d82/<i>accountname</i>/mysql
--datadir=/rc34/d82/<i>accountname</i>/mysql/data
--pid-file=/rc34/d82/<i>accountname</i>/mysql/data/ovid01.pid
</td></tr>
</tbody></table>
<p>Specifically, you will see that a process called <tt>"./bin/safe_mysqld"</tt> is running.
This is the command you typed to start your MySQL server.
If MySQL is not running, you will only see a reponse similar to this:</p>
<p><span class="code"><i>accountname</i> 75994 53816 2 11:11:32 pts/44 0:00 grep mysql</span></p>
</li></ol>
<h4><a name="2">Stopping and Restarting Your MySQL Server</a></h4>
<ul>
<li> <p> To stop your MySQL server, make sure you are in your <tt>mysql</tt> directory, and then type </p>
<p><span class="code">./bin/mysqladmin -u root -p shutdown</span></p>
<p> This lets the <tt>mysqladmin</tt> utility know that you want to shut down MySQL running as user root (<tt>-u root</tt>), and that you will enter a password (<tt>-p</tt>). </p>
<p><b>Tip:</b> <tt>mysqladmin</tt>
is a utility that lets you perform various administrative functions
from a command line. It can also be used to retrieve version, process,
and status information from the server. For more information about <tt>mysqladmin</tt>, see <a href=""><tt>mysqladmin</tt>, Administering a MySQL Server</a>. </p>
</li><li> <p> To start up your MySQL server again, make sure you are in your <tt>mysql</tt> directory, and then type </p>
<p><span class="code">./bin/safe_mysqld &</span></p>
<p>See <a href="http://www.washington.edu/computing/web/publishing/mysql-install.html#step10">Step 10 of Installing and Starting MySQL</a> for a more detailed explanation about starting MySQL.</p>
</li></ul>
<h4><a name="3">Using cron to Start Your MySQL Server Automatically After a
System Restart</a></h4>
<p>Normally, you would have to start your MySQL server up again after the
system it is running on is restarted. Cron is a program that can start it for
you, so you don't have to log in and start it again before using your database
after a system restart.</p>
<p>To add a cron job, type the command</p>
<p><span class="code">crontab -e</span></p>
<p>This will open your crontab file in your default editor(vi, unless you've
changed it). You will then need to insert this line of text:</p>
<p><span class="code">0,5,10,15,20,25,30,35,40,45,50,55 * * * * /ux01/tools/mysql.starter</span></p>
<p>Then save the file and exit. You can check the contents of your crontab
file with the command</p><p>
</p><p><span class="code">crontab -l</span></p>
<p>To delete all of your cron jobs, type the command</p>
<p><span class="code">crontab -r</span></p>
<h4><a name="4">Logging Client Interaction With Your MySQL Server</a></h4>
<!-- As a security measure or for archival reference, you can log all connections and queries made to your MySQL server. logging will tell you the portthat your MySQL server is running on, the path to your socket file, the time and location of a connection, and the SQL commands that were run during each connection to the server. -->
<p>Your MySQL server can log all information about all connections and
queries made to it. To start up your MySQL server with logging enabled,
first make sure your server is stopped, and then
start it in the following way:</p>
<p><span class="code">./bin/safe_mysqld --log="<i>logfilepath</i>" &</span></p>
<p>Replace <tt><i>logfilepath</i></tt> with the entire path to a log file
whose name you make up yourself. Since all other MySQL logs are kept in the
<tt>data</tt> directory, it might be a good idea to use something like
"<tt><i>/rc34/d82/accountname</i>/mysql/data/connections.log</tt>", with
<tt><i>/rc34/d82/accountname</i></tt> replaced with the actual path to your home directory.</p><p>
</p><p> To view the most recent activity on your MySQL server, simply type the following command:</p>
<p><span class="code">tail -20 ~/mysql/data/<i>connections.log</i></span></p>
<p>If necessary, replace <tt><i>connections.log</i></tt> with the name of your log file. You will see something like this:</p>
<table class="code"><tbody><tr><td>
<pre>/rc34/d82/<i>accountname</i>/mysql/bin/mysqld, Version: 4.0.15-log, started with:
Tcp port: XXXX Unix socket: /rc34/d82/<i>accountname</i>/mysql.sock
Time Id Command Argument
020807 16:34:47 1 Connect root@localhost on
020807 16:34:52 1 Query SHOW DATABASES
020807 16:34:57 1 Init DB John_Doe
020807 16:35:02 1 Query SHOW TABLES
020807 16:35:13 1 Query SHOW COLUMNS FROM friends
020807 16:35:18 1 Quit
020807 16:36:23 2 Connect root@veron01.u.washington.edu on
2 Init DB mysql
2 Quit
020807 16:47:56 3 Connect root@localhost on
3 Shutdown</pre>
</td></tr>
</tbody></table>
<p>Your log file will tell you the port that your MySQL server is running on, the path to your socket file,
the time and location of each connection to your MySQL server, and the SQL commands that were run during each connection.</p>
<p>If you do choose to keep MySQL logs, be sure to monitor them. Log
files can get large very quickly and need to be flushed on a regular
basis.</p>
<!--<p>The output above shows all activity that occurred during the MySQL server's(very short) uptime. The first connection was made using the command-line MySQLclient; we know this because the user was <tt>root</tt> and he was connectingfrom <tt>localhost</tt> (the server on which his MySQL server is running). Heasked to see the databases available to him, to use the database John_Doe, toshow the tables in John_Doe, and then to show the columns from the friendstable. He then quit. The second connection was made from a UW Web server;<tt>veron01</tt> is the Web server that hosts faculty, staff, courses, anddepartmental Web sites. The database <tt>mysql</tt> was initialized through aweb script, and then the connection to the server expired when the user left theWeb page. The third and final connection was again made from the MySQL hostserver - this time the user <tt>root</tt> shut down the server with the<tt>mysqladmin</tt> utility.</p>-->
<h4><a name="5">References</a></h4>
<p> See the main <a href="http://www.mysql.com/doc/">MySQL Documentation</a> for more help. The following links may also be useful for configuring, administering, and troubleshooting your MySQL server:</p>
<a href="http://dev.mysql.com/doc/mysql/en/Server-Side_Scripts.html">MySQL Server-Side Scripts</a><br>
<a href="http://dev.mysql.com/doc/mysql/en/Client-Side_Scripts.html">MySQL Client-Side Scripts Page</a><br>
<a href="http://dev.mysql.com/doc/mysql/en/Log_Files.html">MySQL Log Files</a>
<p><b>Tip:</b> <a href="http://www.phpmyadmin.net/">phpMyAdmin</a> is a web-based PHP utility that provides a simple graphical interface for many MySQL administrative tasks. See <a href="http://www.washington.edu/computing/web/publishing/phpmyadmin.html">Installing phpMyAdmin</a> for information on how to set up and use phpMyAdmin.</p>
<div id="footer"><div id="footerseal">
<a href="http://www.washington.edu/"><img src="mysql-admin_files/footersealW.gif" alt="UW Seal"></a>
</div>
<div id="addressright"><address>
A service provided by<br>
<a href="http://depts.washington.edu/cac/"><img src="mysql-admin_files/CnC_full_UW_Purple-1.gif" alt="Computing & Communications" border="0" height="14" width="202"></a>
</address>
</div>
<div id="address"><address>
<a href="http://depts.washington.edu/cac/">Computing
& Communications</a><br>
help@cac.washington.edu<br>
Modified: May 16, 2005
</address>
</div>
</div>
<!--Created by chtml on Sep 15, 2006 11:44am--></body></html>?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -