Use the following SQL to list out all database users in MySQL.
select host, user from mysql.user;
Done =)
Reference: Alvin Alexander – MySQL show users – how to show the users in a MySQL database
MySQL – Show all database users
Leave a reply
Category Archives: Database
MySQL – Drop all tables without dropping the database
You could use the following command to drop all the tables of a MySQL DB.
- mysqldump -u<user> -p<pwd> –add-drop-table –no-data <db> | grep ^DROP | mysql -u<user> -p<pwd> <db>
Just replace the <user>, <pwd> and <db> with your MySQL username, password and database name. That’s all.
Done =)
Reference: MySQL drop all tables command
MariaDB – Installation on Ubuntu Precise
Next: Run phpMyAdmin on Nginx in Ubuntu Precise
It’s time to give up MySQL and move on to MariaDB. Here is the steps for MariaDB installation on Ubuntu Precise (Ubuntu 12.04.1 LTS) with root privilege.
1. Create the following file.
/etc/apt/sources.list.d/MariaDB.list
# http://downloads.mariadb.org/mariadb/repositories/ deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main deb-src http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main
MAMP – Where is the MySQL config file/my.cnf?
If you want to modify the MySQL configuration in MAMP, just create the my.cnf file by the following command Continue reading
MySQL – MySQL server has gone away
When i try to import a database, the following error is thrown and the import fails.
- Warning: MySQL server has gone away query: INSERT xxx…
MySQL – Import and Export Database with mysqldump
Export a database which is called Test:
mysqldump -u <username> -p Test > Test.sql
The sql file will be generated after you input the password. Continue reading
MySQL – Data Type Conversion
Sometimes we need to convert the selected data into other content type. Here is an example.
Assume we got a table with a VARCHAR column which stores number only.
mysql> SELECT * FROM demo; +----------+ | a_string | +----------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | 11 | | 12 | | 13 | +----------+ 13 rows in set (0.00 sec)
PostgreSQL – Check Running SQLs
Recently, there is a performance issue on the application which i am supporting and i find that the bottleneck is related to the SQL query speed. Therefore, my SA suggests me to add an index on the table to increase the query speed.
But before i could add the index, i have to find out the SQL which runs slow. In PostgreSQL, you can query the recent SQLs by the pg_stat_get_backend_activity() function. Here shows you an example. Continue reading
SQL – Select Random Records From Table
I found a good article which the author shows you how to select random records from table among different kind of databases.
Here are the example for SQLite, MySQL and PostgreSQL.
Continue reading
PostgreSQL – Initialize Database At Different Location
PostgreSQL version: 8.3
By default, the PostgreSQL database files is located at /var/lib/pgsql/data. Initialize the database can be done by the following command.
Continue reading
