Using MySQL

Some general information on using MySQL.

See the Legend for an explanation of the symbols and conventions used on this page.

View database settings and status

Show settings:

> show variables;

Show current status (useful to compare with settings):

> show status;

List all connections (processes):

> show processlist;

View table attributes and status

Show table attributes:

> use <dbname>;
> describe <tablename>;

Show table status:

> show table status;

To show the status of a single table:

> show table status like 'tablename';

Show table size

The 7th column of "show table status", "Data_length", shows the size of each table in bytes.

Here is some SQL code to determine the size taken by a table in a more human-readable format.

Assuming that the database name is 'emp_database' and table name is 'emp_master':

SELECT table_schema,
table_name,
ROUND(data_length/1024/1024,2) total_size_mb
FROM information_schema.tables
WHERE table_name = 'emp_master' AND table_schema = 'emp_database';

The result will look like this:

---------------------------------------------
table_schema | table_name | total_size_mb
---------------------------------------------
emp_database | emp_master | 2.57

To see the sizes of all tables in a database, just remove table_name from the WHERE clause.

Change password

To set root password for the first time:

$ mysqladmin -uroot password "newpassword"

Change/update an existing root password:

$ mysqladmin -uroot -p "newpassword"

Change another user's password:

$ mysqladmin -u<username> -p "newpassword"

Dump a database/table

$ mysqldump [-h<address>] -u<user name> -p <database name> [table name] > <dump file>

• [database name]: The name of the database that we would like the mysqldump utility to backup. Instead of specifying one single database name, we could use either --databases (separate database names with spaces) or --all-databases to backup every single database on our MySQL server.
• [table name]: You can optionally backup specific tables rather than the whole database (space separated list).

• --add-drop-database — Add a DROP DATABASE statement before each CREATE DATABASE statement.
• --add-drop-table — Add a DROP TABLE statement before each CREATE TABLE statement.
• --ignore-table=db_name.table_name — Exclude the table db_name.table_name from the dump. To ignore multiple tables, use this option multiple times.
• -v — Verbose mode. Useful when making a long dump.

• Backing Up Your MySQL Databases With MySQLDump
• Backuping and restoring a single table using mysqldump
• Extract a Single Table from a mysqldump File

Dump a single table

Resources:

• Backuping and restoring a single table using mysqldump
• mysqldump - A Database Backup Program

Dump schema only

$ mysqldump -uroot -p --no-data dbname > dumpfile.sql

Restore a dump

$ mysql [-h<hostname>] -u<user> -p [db name] < <dumpfile>

If the dump is compressed, you can import without manually decompressing first, e.g.:

$ bunzip2 -k < dumpfile.sql.bz2 | mysql -uroot -p databasename

Resources:

• Backing Up and Restoring Your MySQL Database
• Dumping and Restoring MySQL Database

Working with permissions

MySQL permissions are managed separately from the system user permissions. Different permissions can be granted on a database or table basis, to a username connecting from a specified address.

Wildcards ("%") and netmasks may be used in the address to specify a range of sources for connections.

If you are assigning permissions to connect to a local database, it'll be easier in the long run to assign the same permissions to both localhost and 127.0.0.1, as MySQL does not automatically assume that they are the same. Remember that it is assumed that you are connecting locally if you don't use a --host or -h parameter.

For basic use, it's fine to grant all rights on a database at once. For greater security, you should look into using more fine-grained permissions.

Syntax:

> grant all on databasename.tablename to 'username'@'address' identified by 'password';
> flush privileges;

Examples:

> grant all on db.* to user@"%" identified by "password";
> grant all on db.* to user@"%.mydomain.com" identified by "password";
> grant all on db.* to user@"192.168.1.%" identified by "password";
> grant all on db.table to user@"192.168.1.0/24" identified by "password";

The MySQL server must be configured to permit connections over the network. Open your my.cnf file (usually in /etc/ or /etc/mysql/) and look for the following:

• skip-networking should not be set
• bind-address should be either set to your network IP address, or not set at all

Caching

Clear the query cache:

> RESET QUERY CACHE;

You need the RELOAD privilege to run this command.

Miscellaneous

• The Rows Holding the Group-wise Maximum of a Certain Column

Resources

• MySQL Startup Guide