Howto use basic MySQL commands used almost every day.

The basic setup involves creating a database and user to access it. If running many DBs and would like a single user to have access to them all, create a dbadmin user on the system and give different mysql and system passwords.

# adduser dbadmin
# passwd dbadmin "new_sys_pass"
# mysql -u root

mysql> CREATE DATABASE mydb;
mysql> grant all on mydb.* to dbadmin@localhost identified by 'new_sql_pass';
mysql> grant all on * . * to dbadmin@localhost identified by 'new_sql_pass';
mysql> FLUSH PRIVILEGES;

Need to change the root password, by default there is none. Open mysql prompt with mysql -u root mysql and enter:

mysql> DELETE from db where HOST="%";
mysql> UPDATE user SET Password=PASSWORD('new_password')
    ->             WHERE user='root';
mysql> FLUSH PRIVILEGES;

Should also remove anonymous accounts and add specific user accounts with specific GRANT privileges.

mysql> DELETE FROM user WHERE User = '';
mysql> FLUSH PRIVILEGS;

mysql> GRANT USAGE ON mydb.* TO 'someuser'@'localhost'
mysql> IDENTIFIED BY 'some_pass';
mysql> FLUSH PRIVILEGES;

In a piece of PHP code, always use hashes to store passwords

$query = "INSERT INTO user VALUES ('UserName',md5('UserPassword'))";

This is a no-no, give them an account to tunnel via ssh instead, but if must - give netmask restriction.

GRANT USAGE ON mydb.* TO 'someuser'@'localhost'
IDENTIFIED BY '12.34.56.78/255.255.255.240';

• Compile –with-libwrap=/usr/local/tcp_wrappers
• add /etc/hosts entries for allowed subnets

User access checked against privilege table that includes

• global privileges
• OR (database AND host privileges)
• OR table privileges
• OR column privileges

mysql> SHOW GRANTS FOR 'user'@'127.0.0.1';

• Privileges: Alter, Delete, Create, Drop, Execute, Select, Update
  • User Table Only: Reload, Shutdown, Process, File, Show, Super, ...

mysql> GRANT ALL ON mydb.* TO 'joe'@'localhost'
    -> IDENTIFIED BY 'joepass'
    -> WITH MAX_QUERIES_PER_HOUR 20
    ->      MAX_UPDATES_PER_HOUR 10
    ->      MAX_CONNECTIONS_PER_HOUR 5
    ->      MAX_USER_CONNECTIONS 2;
mysql> FLUSH PRIVILEGES;

• get mysql user account access on the box
• start mysql with “–skip-grant-tables”
• log in to system and manipulate user table directly, outside of mysql

user@linux:~/files/g2_backup> mysqldump --add-drop-table -h mysqlhostserver
 -u mysqlusername -p databasename (tablename tablename tablename) | bzip2
 -c > g2.bak.sql.bz2

• sharedonline/common install: g2sharedonline, g2wiesefam, md05gallery2
• flyrdie_g2
• nps photos use database “g2sharedonline” but with different prefix!
  • imagemagic needs config - nps, others?

$ mysql -e "show databases" -u dbadmin -p

backup$ mysql -u dbadmin -p -h mysql.hostname.com dbname > dbname.YYYYMMDD.sql

restore$ mysql -u dbadmin -p -h mysql.hostname.com dbname < dbname.YYYYMMDD.sql

• Some notes taken from Matt Caughron’s Certconf 2005 presentation