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