mysql.md

MySQL and MariaDB

Log in to MySQL interpreter

If you are using socket authentication, mysql relies on your linux user authentication. This requires you to run the command as root.

sudo mysql -u root

If you are using password authentication, you will need to supply the -p flag. This will cause the interpreter to prompt you for your password.

mysql -u root -p

This also applies to mysqldump.

Enable autocomplete

rehash

or

\#

or, in .my.conf

[mysql]
auto-rehash

Can be disabled by passing the --disable-auto-rehash flag. This could be necessary if pasting queries that contain tab characters.

Alternatively, use mycli, which is reported to have better autocomplete than the mysql command.

Select a DB

SHOW DATABASES;
USE db_name;

Create a DB dump

sudo mysqldump -u root db_name -r db_dump.sql

You may need to use the -p flag depending on your configuration.

Load a DB dump

To load the dump into a new database,

CREATE DATABASE new_db;
SOURCE db_dump.sql;

Or, to overwrite an existing database,

> DROP DATABASE existing_db;
> CREATE DATABASE existing_db;
> SOURCE db_dump.sql;

You can also do the SOURCE step from outside the mysql shell.

sudo mysql -u root new_db < db_dump.sql

Copy a DB

First you need to create the new database

CREATE DATABASE db_copy;

Then, dump the old database and pipe it into the new one

sudo mysqldump -u root <db_name> | sudo mysql -u root <db_copy>