Linux Ask!

Linux Ask! is a Q & A web site specific for Linux related questions. Questions are collected, answered and audited by experienced Linux users.

Home
Subscribe
Ask a Question
Advertise
About Us

Show the current privileges of a MySQL user

MySQL No Responses »
Sep 242011

Show the current privileges of a MySQL user

Answer:

To show the current privileges of a MySQL user, you can use the SHOW GRANTS statement

E.g.

mysql> SHOW GRANTS;

+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*****************************************' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
Posted by Linux Ask! at 1:28 pm Tagged with:

Show the current user in MySQL

MySQL No Responses »
Sep 162011

Show the current user in MySQL

Answer:

Like in the shell, we can execute the command whoami to find out the current user name, we can use the function CURRENT_USER();

E.g.

mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
Posted by Linux Ask! at 1:37 pm Tagged with:

Disable MySQL server from listening for TCP/IP connections

MySQL No Responses »
Aug 252011

Disable MySQL server from listening for TCP/IP connections

Answer:

If you only connect to the MySQL server from localhost and you might want to disable TCP/IP networking feature so the server is more secure.

To do so, edit the MySQL configurations, e.g. /etc/my.cnf

..
skip-networking
..

Don't forget to restart MySQL to take effect.

# /sbin/service mysqld restart

Posted by Linux Ask! at 1:51 pm Tagged with:

Show the storage used by a database in MySQL

MySQL No Responses »
Aug 112011

Show the storage used by a database in MySQL

Answer:

In previous article, we have already discussed how to list the database size in your MySQL server.

But if you want to have a more detail view on an individual database, you can try the following (crazy) SQL.

SELECT s.schema_name,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))
/1024/1024,2),0.00),"Mb") total_size,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb")

data_used,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free,
IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))
/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,
COUNT(table_name) total_tables
FROM INFORMATION_SCHEMA.SCHEMATA s
LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema
WHERE s.schema_name = "mydatabase"
GROUP BY s.schema_name
ORDER BY pct_used DESC\G

It will give you something like the following..

 schema_name: mydatabase
  total_size: 165.09Mb
   data_used: 12.09Mb
   data_free: 153.00Mb
    pct_used: 7.33
total_tables: 133
Posted by Linux Ask! at 1:32 pm Tagged with:

How to change the MySQL temporary directory

MySQL No Responses »
Jul 172011

How to change the MySQL temporary directory

Answer:

To change the temporary directory of your MySQL server to use a non-default directory, you can change the tmpdir parameter in your my.cnf

..
tmpdir = /data/tmp
..

Don't forget to change the permission of that temporary directory

# sudo chmod mysql:mysql /data/tmp

Then restart MySQL to take effect.

# /sbin/service mysqld restart

Posted by Linux Ask! at 4:55 am Tagged with:
Older Entries