Currently many CMS or content management using MySQL database, http://www.mysql.com/ . Data base Mysql database is one of the most users. Preceded by a pure open source, which later was bought by SUN launched after Enterpise version that is not 100% longer an open source software.
On this occasion, we will publish how to maximize (tuningisasi) MySQL associated with memory, logging, recovering, database maintenance, etc. so that MySQL will run with maximum and well in our server. And we will also introduce a small script to check the settings associated with the memory that we set in our MySQL.
Logging and determine the condition of the MySQL settings
the most important thing we do is the first tuning, keep track of all queries that run in a log. It is important to know what kind of query processes are running on MySQL. Is it true, MySQL we need a special tuning or settings so that the conditions that now need to be optimized. From these log records, we can also find out what kind of query is often done, so that when we adjust the settings with respect to any memory we will be more appropriate to do so. In any MySQL query sorting, select, insert, update tables, etc.. Each of these queries can be set how much memory is required, so the priority of any provision of memory we can do. Technically to record logs in MySQL is very easy. Add option -
# safe_mysqld - log = $ logfile
If you use Fedora or Redhat or derivatives thereof, find script file to run MySQL on $ logfile is the name of the log and place that you want. Example is
- log = / var / log / mysql.log
.
Or if you just want to run with the command that is simpler, then you can do as follows.
# safe_mysqld - log
With this option the query logs will be stored in the directory / var / lib / mysql .
Checking MySQL queries
Now let us run the following command:
# mysqlshow So, is listed in the log that we have set earlier as following,
8:32:49 020 819 15 Connect root @ localhost on 15 Query show databases 15
Quit Now look who is connect to our MySQL, and query what the user is doing. From this log, of course, we can find out if if there is an attack or an intruder who entered early as possible.
Therefore we can also log MySQL monitor with a specific script when there is an incoming query that we never do.
Checking MySQL process running
After the log has been well documented, not enough to know 100% how the course of MySQL on our server. Therefore, there are things we must do to find out that MySQL runs well. First we have to do is check whether MySQL runs with a fair process. Perform the following commands. # ps-auxxw with ease. With a script that is to send the MySQL if there are problems to your email. Does it need to be given a sample script?
There are other commands you can do, if you want to know more detail the condition of MySQL.
That is, with the following command.
# mysqladmin status
result is as follows,
[root @ ns3 mye] # mysqladmin status
Uptime: 4034920 Threads: 1 Questions: 15649633 Slow queries: 38 opens: 227439 Flush tables: 1 Open tables: 64 Queries per second avg: 3879 By simple we will explain the meaning of each word above,
Uptime : Long seconds after running MySQL.
Threads: the number of Threads used
Questions
: the number of queries that are made from since MySQL runs
Slow queries: the number of queries in a long time
opens
: the number of tables that opened since MySQL runs Flush tables
: the number of flush, refresh, reload the run
Open tables: the number of table that is opened Queries per second avg: average per-second query response
If you want a more complete condition with respect to your MySQL, run The following command,
# mysqladmin extended-status
Because the above command when run, the result is very long at all, it was not raised in this paper. Is there anything that has not been understood?
0 comments:
Post a Comment