Administration and
Configuration of MySQL
Course Name:
Database Faculty Name:
Administration Ms. Zaiba Khan
with MySQL
Branch- Assistant Professor(CSE)
B.Tech-VI Semester School of
Course Code: Engineering &
Technology
19009300
1
Basic Server Administration
Different command line tool designed specifically to
help administrators perform tasks
◦ Changing the MySQL
◦ Administrator Password
◦ Reloading MySQL Privileges.
This tool is called mysqladmin
Can be found in bin/ directory of your MySQL
installation
For Ex-
[root@host] # /usr/local/mysql/bin/mysqladmin
shutdown
2
Basic Server Administration
Table invoke different useful commands supported by
mysqladmin:
Command What It Does
Status Return information on server
status
Password Changes a user password
Shutdown Shuts down the MySQL Server
Reload Reload the MySQL grant
tables
Refresh Resets all caches and logs
Variables Return values of all server
variables
Version Returns the server version
Processlist Returns a list of all processes
active on the server
Kill Kills an active server process
ping Tests if the server is alive
3
Basic Server Administration
(Shell Prompt)
4
Starting and Stopping the Server
On UNIX , MySQL comes with a start and shutdown
script
This script named as mysql.server available in
support file
[root@host] # /usr/local/mysql/support-files/mysql.server start
On Windows(Windows 95,98 or Me), MySQL comes
with a start and shutdown script
Directly by navigating to your MySQL installation
directory & launching mysqld binary by double
clicking it.
C:> c:\mysql\bin\mysqld.exe
5
Tip: Better Safe Than Sorry
While you can certainly invoke MySQL by directly
running the mysqld from your installation's bin /
directory.
Using the mysqld_safe wrapper is considered a safe
approach
◦ This wrapper takes care of automatically logging errors
and runtime information to a file or of restarting MySQL
daemon in case of unexpected shutdown.
Note: In older versions of MySQL, mysqld_safe is called
safe_mysqld
6
Starting and Stopping the Server
On Windows(Windows NT,2000,XP and 2003), which
will more typically be running MySQL in background
This is accomplished by installing the mysql service
then using the net start command to activate it.
C:> c:\mysql\bin\mysqld --install
Start the Service via Control Panel(Administrative
Tools Services) or with the command:
C:> net start mysql
Depending on the version of Windows running ,MySQL
either launch in the foreground (mysqld ) or start as a
service and run invisibly in the background.
7
Starting and Stopping the
Server(Cont..)
We can also verify if the server is running and this can be done by
using mysqladmin tool with ping command
[root@host]# /usr/local/mysql/bin/mysqladmin ping
mysqld is alive
8
Starting and Stopping the Server
The mysqladmin utility can also be used to reload the
server’s grant tables, as in the following
[root@host]# /usr/local/mysql/bin/mysqladmin reload
Once the server is running, you can shut it down at any
time with mysqladmin utility that ships with MySQL,
by invoking it with the shutdown parameter
[root@host]# /usr/local/mysql/bin/mysqladmin shutdown
9
Starting and Stopping the Server
On Windows, you usually need to open a new DOS
console window or use the Start-> Run dialog box as
follows
C:> c:\mysql\bin\mysqladmin shutdown
On UNIX, you can also use the provided mysql.server
startup/ shutdown script to shut down the server, as
shown below:
[root@host]# /usr/local/mysql/support-files/mysql.server stop
10
Checking MySQL Server Status
You can find out the current state of server
◦ Time
◦ Queries per second
◦ Number of currently open tables and so on
Via mysqladmin tool
[root@host]# /usr/local/mysql/bin/mysqladmin status
• The version command offers a more concise summary
[root@host]# /usr/local/mysql/bin/mysqladmin version
An Equivalent approach is to use the VERSION( ) built-
in function
11
Checking MySQL Server Status
An Equivalent approach is to use the VERSION( ) built-
in function
Extended status information is also available via the
extended-status command to mysqladmin or with
the SHOW STATUS command
12
Checking MySQL Server Status
Extended status information is also available via the
extended-status command to mysqladmin or with
the SHOW STATUS command
13
Checking MySQL Server Status
A great deal of real time status information is provided:
◦ It contains the amount of traffic the server received since it last
started
Including no. of bytes sent and received
The client connection
Together with a breakdown of how many succeeded
How many failed
How many aborted
◦ It also contains statistics on the total no. of queries processed by
the server since startup, together with information on the no. of
the quires in each type(SELECT,DELETE,INSERT,…….)
◦ The number of threads active
◦ The number of current client connections
◦ The number of running queries
◦ The number of open tables
14
Managing MySQL Client Processes
A complete list of all client processes connected to the
server with the SHOW PROCESSLIST command
15
Managing MySQL Client Processes(Cont..)
A “regular” user will only be able to see his or her own threads in
the output of SHOW PROCESSLIST
The PROCESS privilege can, however , see all running threads
Users with the all powerful SUPER privilege can even kill running
threads, with the KILL command.
mysql> KILL 12;
Query OK, 0 rows affected (0.01 sec)
16
Altering The Server Configuration
The Software comes preconfigured by MySQL to meet most
common needs.
However, in case the default configuration doesn’t work , MySQL
exposes a large number of variables whose values can be modified
to meet custom requirements
NOTE:
A thread doesn’t die immediately on receiving a kill signal
Rather, MySQL sets a kill flag for that particular thread
It is checked by the thread once it completes
This approach is considered safer
WHY
Because it allows the thread to complete whatever it’s doing &
release any locks it created
17
Using An Option File
This method of setting MySQL options is through an option file
essentially
MySQL looks for this option file in some standard places when it
starts up:
Windows: My SQL looks for startup option in an file named my.cnf
Alternatively the my.cnf file can be placed in the Windows
directory as my.ini
UNIX: MySQL checks for startup option in /etc/my.cnf, ~ /.my.cnf
and in a filed named my.cnf in your MySQL data directory.
18
Using An Option File(Continue….
MySQL looks in the groups[mysql] and [mysqld] for configuration
options.
19
Using An Option File(Continue….
Note, all these options can be specified on the MySQL command
line as well , simply by prefixing the option name with a double
dash.
Example:
[root@host]# /usr/local/mysql/bin/mysqld_safe
--socket=/usr/tmp/mysql.socket --user=mysql --skip-networking
&
Starting mysqld daemon with databases from /usr/local/mysql/data
In case multiple option files exist or the same option is specified
multiple times with different values
MySQL uses the last found value
Because MySQL reads option files before command line arguments,
this means options specified on the command line take precedence
over options in an option file
20
Using The SET Command
MySQL also lets you modify system and connection variables
while the server is running, using the SET command
The SET command looks like this:
SET variable = value, variable = value,………
Here’s an example in which the SET command is used to set
the default table type for new tables:
mysql> SET table_type = innodb;
Query OK, 0 rows affected (0.00sec)
21
Using The SET Command(Continue….
Variables set using the SET command can be set globally for
all sessions / for the current session
By following example the SET keyword with either the
GLOBAL or SESSION keyword
The following example limits the server to ten client
connections at any time and sets the size of the read buffer to
250 KB:
mysql> SET GLOBAL max_user_connections=10, SESSION
read_buffer_size=250000;
Query OK, 0 rows affected (0.08 sec)
22
Using The SET Command(Continue….
23
Retrieving Variable Value
Once the variable has been SET or through a startup option
Its value can be retrieved using SHOW VARIABLES
command
OR
By invoking mysqladmin with the variable command
Because the output of SHOW VARIABLES is somewhat
prodigious
MySQL lets you filter it down to just the variable you want
with the addition of LIKE clause
24
Retrieving Variable Value
25
Retrieving Variable Value
26
Summary
It gives brief introduction to MySQL database administration
Common tasks are to perform and providing a brief look at
the MySQL tools available to accomplish these tasks
The mysqladmin utility, which makes it possible
◦ to reload or shut down the server,
◦ view a list of active processes,
and
◦ obtain current values of server variables
MySQL administration, including starting and stopping the
MySQL server, configuring the server to start automatically at
boot time, obtaining server status, managing server process ,
altering the server configuration through a configuration file
or the SET command
27
