How to view a list of MySQL users and their privileges

If you are running a multi-user MySQL database, handy commands that show a list of all existing MySQL users and their privileges may be on your cheat sheet. To find out all MySQL users and the permissions granted to each user, log in to your MySQL server, and run the following MySQL commands.Read more »

How to backup a MySQL server

If you are running a MySQL server with critical information in its databases, you may want to back up the databases periodically. A Linux command-line tool called mysqldump allows you to back up MySQL databases without needing to shut down a MySQL server. mysqldump generates as output a text file containing a series of MySQL commands that represent a current snapshot of MySQL databases being backed up. The mysqldump output file can easily be compressed and/or encrypted as needed. The following is how to backup a MySQL server with mysqldump.Read more »

How to run MySQL query in a shell script

When you are writing a shell script as part of some batch processing, there are cases when the script needs to process data stored in a separate MySQL server. The general-purpose script languages such as Perl and Python have separate MySQL modules or interfaces to use, but shell script languages do not have such an interface for MySQL. However, there are ways to run some simple MySQL queries, and process the result even in a shell script.Read more »

How to allow remote access to MySQL server

If you have installed MySQL fresh (e.g., "sudo apt-get install mysql-server" on Debian/Ubuntu, and "sudo yum install mysql-server mysql" on CentOS/Fedora/RedHat), MySQL server binds on local loopback address (i.e., 127.0.0.1) by default. This means that you can access MySQL server only from the local host, but not from any remote host. You can verify this by running netstat as follows.Read more »

How to reset root password in MySQL

If you forgot the root password in MySQL, and would like to reset it, do the following as root.  In this example, replace "NewPassword" with your own password.Read more »