This page is the continuation of my blog post on MySQL commands

I currently use the MariaDB client on Debian GNU/Linux. I have kept mysql > prompts below.

As root, setup users and create databases

As root, create a database and grant permissions to a new user To connect to the mysql client as root, in the shell

sudo mysql 
# or
mysql -u root -p

Create a new user

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Create a database

mysql> create database databasename;

Connect to a database

mysql> connect databasename;

Grant all permissions to a user on all databases

mysql> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Grant all permissions to a user on a specific database

mysql> GRANT ALL PRIVILEGES ON databasename. * TO 'newuser'@'localhost';

Grant read permission to a user on a specific database

mysql> GRANT SELECT ON databasename. * TO 'newuser'@'localhost';

Then you can log out of mysql

mysql> quit;

Graphical clients

On Ubuntu, I used mysql navigator by shi bok jang. On Debian, I used mysql workbench

Lately I mostly use the command line client.

User operations

Change password, as the user itself or as root:

SET PASSWORD FOR 'Karl'@'localhost' = PASSWORD('cleartext password');

As root , list all users

SELECT User FROM mysql.user;

As root , delete a user

drop user Rasdfas@localhost;

MySQL documentation on adding user accounts.

Connecting to a database

To connect to the mysql client as a user

mysql -u username -p

If you have configured ~.my.cnf, you can simply call

mysql

Then in the mysql client:

mysql> connect databasename;

If you have the correct privileges, you can create a database with a pipe. How to create a database

echo "create database databasename" | mysql -u username -p

Configuration file my.cnf

It’s not desirable to share user name and password in software disseminated over the internet. User name, password and database names can be placed under groups in the configuration file. For example for a given project enter this group in ~/.my.cnf

[project_name]

user = user
password = password
host = localhost
database = dbname

Then simply specify group=project_name to the client that accesses the database.

Table operations

Rename a table

mysql> rename table tbl1 to tbl1;

Database operations

mysqldump and mysqladmin commands below work if your shell user name is the same as your mysql user name. If it’s different add -u username to the command. If you have configured ~/.my.cnf (see below) you don’t even need the -p switch.

Show information about databases, tables, columns

List all databases (in mysql client)

mysql> show databases;

Show all tables in a database

mysql> show tables;

Show column information

mysql> show columns from tablename;

Show the size of a table:: select TABLE_NAME, round((DATA_LENGTH + INDEX_LENGTH)/1024/1024) AS Size (MB) from information_schema.TABLES where TABLE_SCHEMA = “tradeflows” and TABLE_NAME = “vld_comext_monthly”;

Drop

Delete a database (in the mysql client)

mysql> DROP database databasename;

Delete a table

mysql> connect databasename;
mysql> DROP tablename;

Dump

Rename a database (in the shell)

$ mysqladmin -p create new_database
$ mysqldump -p old_database | mysql -p new_database

After you have verified that everything is in order

mysql> drop database old_database

Move a table from one database to another (in the shell)

$ mysqldump -p database_1 table_name | mysql -p database_2

Back up only part of a database with the where option

$ mysqldump -p -w"productcode=440799" tradeflows raw_flow_yearly > sawnwood99raw.sql

Back up only the structure of a database (not the data)

$ mysqldump -p -d tradeflows > tradeflows.sql

Load a dump into a database :

$ cat file.sql | mysql -u username -p databasename

And if you have a ~/.my.cnf user configuration file setup, simply

$ cat file.sql | mysql databasename

Database engines

Show database engines available:

mysql> show engines;

MySQL Commands