MySQL server


Installation

Required packages

apt-get install mysql-server mysql-client

Your database will use InnoDB instead of the old, deprecated, database file system.

You’ll have to choose a password for MySQL admin [root] user

Configuration

Stop the service (you cannot configure a running service)

service mysql stop
/etc/init.d/mysql stop

Edit configuration file

# mysql 5.6+
vim /etc/mysql/mysql.conf.d/mysqld.cnf

# Old version (mysql up to 5.5)
vim /etc/mysql/my.cnf

Edit the file:

[mysqld]
#bind-address = 127.0.0.1	    # Comment this line to enable remote access

Restart service

service mysql restart
/etc/init.d/mysql restart

Check running service

netstat -tap

you should have something like:

tcp        0      0 *:mysql                 *:*                     LISTEN     3281/mysqld


Enable remote access

Server connection

mysql -u root -p


  • MySQL 5.6
mysql -u root -p
mysql> use mysql;
mysql> select user,host,authentication_string from user;
mysql> update user set host="%" where user="root" and host="localhost";
♠ where 'vks11447' is the server name.

mysql> flush privileges;
mysql> quit;


  • MySQL 5.5
mysql> use mysql;
mysql> select user,host,password from user;
mysql> update user set host="%" where user="root" and host="vks11447";

♠ where 'vks11447' is the server name.

mysql> flush privileges;
mysql> quit;


Restart server

service mysql restart
/etc/init.d/mysql restart


Open your firewall

# MySQL server
$IPTABLES -t filter -A INPUT -p tcp -m state --state NEW --dport 3306 -j ACCEPT


Change root login

Change root login / password

mysql -u root -p

Display all users

mysql > use mysql ;				
mysql > select User,Host,Password from user;

Change root login

mysql > UPDATE user SET user.User = 'admin' WHERE user = 'root';

Change root password

mysql > UPDATE user SET user.Password = password('****') WHERE user = 'admin';

Apply changes

mysql > flush privileges ;
mysql > select User,Host,Password from user ;
mysql > exit;

Check results

mysql –u admin –p