MySQL notes
As it usually happens with SQL databases, there are two main components:
mysqld
- MySQL server (Linux daemon);mysql
- MySQL client (cmdline app);
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:
- # some text ... (one line, from # to EOL);
- -- some text ... (one line, from -- to EOL);
- /* some text ... */ (multiline, C-style);
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