MySQL notes

As it usually happens with SQL databases, there are two main components:

By default MySQL server accepts connection requests from the network on tcp/3306, but it may not accept network requests at all due to config option:

bind-address = 127.0.0.1

Check /etc/mysql/my.cnf file and (only if you really need this!) set

bind-address = 0.0.0.0

Restart MySQL server to activate changes. Remember that this config is not safe, and you should carefully protect your database server with firewall.

Startup/Shutdown

You can handle MySQL server using standard Linux cmds:

systemctl status mysql

systemctl start mysql

systemctl stop mysql

systemctl restart mysql

or

service mysql status

service mysql start

service mysql stop

service mysql restart

Old style:

/etc/init.d/mysql status

/etc/init.d/mysql start

/etc/init.d/mysql stop

/etc/init.d/mysql restart

Connecting

Let's assume that inet_logs is a database, and MySQL server is running. You can connect to this database using the following command:

mysql inet_logs -u root -p

(-u specifies a username, -p means "prompt for password")

When user successfully connects, MySQL client displays its prompt:

mysql>

To disconnect and exit:

mysql> exit

You can connect to MySQL server without specifying a database name:

mysql -u root -p

or

mysql --user=root mysql -p

In general, to connect to a database at the localhost:

mysql db_name -u user -p

To connect to a remote database, you must specify the remote host name or IP address, e.g.:

mysql -h 192.168.0.4 -u avatar -p

Creating databases, users, tables, etc

To create a new database named test_db:

mysql> create database test_db;

or

mysql> create database test_db character set utf8 collate utf8_general_ci;

In MySQL, max length of identifiers (database name, table name, column name, etc) is 64 chars, except Alias which can be 256, and Compound Statement Lablel which is limited to 16.

Each MySQL database corresponds to a specific directory within the data directory. Each table corresponds to at least one file within the database dir (maybe more, depending on the storage engine). Triggers also correspond to files. As a result, the case sensitivity of the underlying OS affects the case sensitivity of databases, tables, and trigger names. Shortly speaking, in Windows, names are case-insensitive, in Linux, names are case-sensitive. The last is true for the most varieties of Unix, though MacOS is an exception.

Some useful cmds:

mysql> show databases;

mysql> use test_db;

mysql> show tables;

To see the creation parameters of an existing database:

mysql> show create database inet_logs;

To create a local user (e.g., home PC, client and server in the same system):

mysql> CREATE USER 'avatar'@'localhost'
IDENTIFIED BY 'dmsa72n9pf';

mysql> GRANT ALL PRIVILEGES ON site2.*
TO 'avatar'@'localhost' WITH GRANT OPTION;

or

mysql> grant all privileges on site2.*
to avatar@localhost identified by 'dmsa72n9pf';

To create a new user with remote access and limited privileges (e.g. SELECT on all tables in test_db database):

mysql> CREATE USER 'my_guest'@'%' IDENTIFIED BY 'ndmif8gh43';

mysql> GRANT SELECT ON test_db.* TO 'my_guest'@'%';

Superuser account:

mysql> GRANT ALL PRIVILEGES ON *.*
TO 'adm'@'localhost' WITH GRANT OPTION;

SQL scripts

can be used to create databases, tables, indexes, triggers, etc. A typical SQL script is a text file with .sql extension containing SQL statements and comments. Here is an example of SQL script named userlist.sql which is supposed to create a database table userlist and fill it with a couple of records:

# Table 'userlist';
# Created : 2019-Feb-28

CREATE TABLE userlist (
  empno INT(5) NOT NULL auto_increment,
  ename VARCHAR(40) NOT NULL,
  dept VARCHAR(36),
  job VARCHAR(36),
  PRIMARY KEY (empno))
  ENGINE=InnoDB;

INSERT INTO userlist(ename, dept, job)
    VALUES ('Adam Jones', 'HR', 'Manager');
INSERT INTO userlist(ename, dept, job)
    VALUES ('James Brown', 'HR', 'Assistant');

MySQL supports three comment styles:

To run the above SQL script on behalf of user max:

mysql test_db -u max -p < userlist.sql

If mysql client is already running and connected to a database, you can execute SQL script using source cmd:

mysql> source userlist.sql;

The script must be in the current dir, otherwise you should specify path.

An example of a session

If you start mysql client without specifying the database, you can select database with:

mysql> use test_db;

This cmd also allows you to change the current database at any moment. If you lost the track and want to know what is your current database,

mysql> select database();

Note that database() is a function.

A typical session may look like this:

mysql test_db -u max -p

mysql> show tables;

mysql> desc userlist;

mysql> select count(*) from userlist;

mysql> select empno, ename from userlist;

mysql> insert into userlist (
ename,dept,job)
values ('Alex Marshall','IT','Admin');

mysql> update userlist
set job = 'Manager' where empno = 204;

mysql> delete from userlist where empno > 500;

mysql> exit

To list available databases:

mysqlshow -u root -p

To shutdown MySQL database:

mysqladmin -u root -p shutdown

To erase the database (think twice before doing it):

drop database test_db;

Database backup

This is the simplest way to perform an online backup:

mysqldump -u root -p -A > mysql-bkp.sql

With -A (or --all-databases) this utility writes all databases to a specified file as a sequence of SQL statements that later can be used to re-create all database objects and to re-insert all records.

Alternatively, you can shutdown database cleanly (!) and backup database files using tar or some other appropriate application.

Setting MySQL root password in Ubuntu

If you cannot connect as root (because root password is not set in Ubuntu by default), restart MySQL with special options:

sudo stop mysql

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

In this mode you can connect MySQL without a password:

mysql -u root

Exec the following cmds (use some good pass of your choice):

mysql> FLUSH PRIVILEGES;

mysql> USE mysql;

mysql> UPDATE user
SET password = PASSWORD('mtsk9u3pat')
WHERE host = 'localhost' AND user = 'root';

This is for local access; if you want to access MySQL from anywhere, then this statement must be slightly different:

mysql> USE mysql;

mysql> UPDATE user
SET password = PASSWORD('mtsk9u3pat')
WHERE host = '%' AND user = 'root';

Restart MySQL in normal mode:

sudo stop mysql

or

sudo /etc/init.d/mysql stop

Use

ps ax|grep mysql

to be sure that /usr/sbin/mysqld (!) is not running. If you cannot stop it decently, use soft kill (SIGTERM), wait ~10 sec before checking; if it fails twice, send SIGKILL. When you're sure that MySQL is stopped, go on:

sudo start mysql

Now you should be able to connect as root using that password:

mysql -u root -p