Mysql

Basic commands

On sql vserver: installation and assignation of a root password

apt-get install mysql-server
mysql -u root
mysql> SET PASSWORD=PASSWORD('sql_long_pwd');
mysql> FLUSH PRIVILEGES;
mysql> QUIT;

Or shorter:

mysqladmin password sql_long_pwd

Now we have to give the pwd each time we manipulate the db:

mysql -u root -p

Edit /etc/mysql/my.cnf to activate the network availability:

bind-address            = <my_ip>

To create a new db and an associated user with full rights on this db

CREATE DATABASE <database>;
GRANT ALL ON <database>.* TO <db_admin>@<host> IDENTIFIED BY '<db_admin_password_in_clear>';

To shutdown mysql

mysqladmin -p shutdown

To delete a table (be careful!!)

mysqladmin -uroot -p drop <my_table>

To backup a database

mysqldump -uadmin_gallery2 -p -h sql --opt gallery2 > gallery2.sql

To change the password of a user (always combined with a host):

SET PASSWORD FOR username@host=PASSWORD('new_password');