MySQL

by Scott, Revised on March 13, 2017 (DatabasesLinux)

Main config file on Debian/Ubuntu: /etc/mysql/my.cnf

MySQL has 3 default users in the mysql database:

  • root at localhost w/o password
  • root at host (for network connections) without password
  • debian-sys-maint with password

Login and set passwords for root on localhost and host

mysql -u root
UPDATE user SET Password=PASSWORD('xxxx') WHERE user='root';
FLUSH PRIVILEGES;

May want to edit .mysql_history and remove the above password from the command history (as it will be visible).

To allow access from the network connections edit /etc/mysql/my.conf and comment out: #bind-address = 127.0.0.1

Now to connect to server with password:

mysql -u root -p
mysql -u root -h host -p

Create a database user:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'pw';

Create a database and grant rights to a user for use on web site for a particular database on same machine:

CREATE DATABASE db;
GRANT SELECT, INSERT, UPDATE, DELETE, INDEX ON db.* TO 'user'@'localhost';
GRANT ALL PRIVILEGES ON db.* TO 'user'@'localhost';

Backup and Restore

mysqldump -u user -p database > db_backup.sql
mysqldump -u user -p --all-databases > mysql.sql
mysql -u user -p database < db_backup.sql

One idea for backups is to create a backup user and only give the proper permissions for the databases he needs privileges for (grant usage to allow access, then refine privileges per database.

GRANT USAGE ON *.* TO 'dump_user'@'localhost' IDENTIFIED BY 'dump_user_password';
GRANT SELECT, LOCK TABLES ON `database_1`.* TO 'dump_user'@'localhost';
GRANT SELECT, LOCK TABLES ON `database_2`.* TO 'dump_user'@'localhost';

To run the backup in a cron will not work as a password is promted for. The solution is to pass the password in with the initial command (two options below, note there is no space in the first option).

mysqldump -u dump_user -pdump_user_password database > db_backup.sql
mysqldump -u dump_user --password=dump_user_password database > db_backup.sql

Tags: DatabasesLinux