Installing and configuring MySQL server on Ubuntu

You can install the MySQL server and client in Ubuntu/Debian like this:

sudo apt-get install mysql-server mysql-client

The directory with configuration files is located at /etc/mysql.
The database storage directory is /var/lib/mysql.

To allow remote access to the mysql server, in the configuration file /etc/mysql/my.cnf you need to comment out the line bind-address = 127.0.0.1 (put the # character in front of it) and restart the mysql server to apply the changes:

sudo service mysql restart

You can set the root password, delete the test database, deny remote root access to the user and configure other security recommendations by running the command:

sudo mysql_secure_installation

I will show some examples of commands.
Connect to mysql via command line:

mysql -u USERNAME -p

View available databases:

show databases;

We open the necessary database:

use DATABASENAME;

We look at the tables in the database:

show tables;

Example of creating a new database:

create database DATABASENAME;

Database removal:

drop database DATABASENAME;

User Creation:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

We give the user full privileges over the desired database:

GRANT ALL PRIVILEGES ON databasename.* to 'username'@'localhost';

Creating a database dump:

mysqldump -u USERNAME -p DATABASENAME > mysqldump.sql

Import dump to the desired database:

mysql -u USERNAME -p DATABASENAME < mysqldump.sql

For help, you can type: help

For security, delete the standard test database:

DROP DATABASE test;

We look at privileges for an empty user and if it exists, then delete:

SHOW GRANTS FOR ''@'localhost'
DROP USER "";

We look at what users exist and what their privileges are:

use mysql;
select * from user;

You can check if the server is running as follows:

sudo netstat -tap | grep mysql

We look at the connections on port 3306:

netstat -na | grep 3306

You can look at packets on port 3306 when there are problems connecting to mysql:

tcpdump port 3306
tcpdump port 3306 -i eth0

See also my articles:
IPTables rules for MySQL
Restoring MySQL tables
Other

Did my article help you? How about buying me a cup of coffee as an encouragement? Buy me a coffe.

Leave a comment

Leave a Reply