MySQL
From SobellWiki
Contents |
Jumpstart MySQL
Make database, put in tables, columns and get data out.
Log In
$ mysql -u [user] -p
Will prompt for a password and log [user] into mysql if the password hashes match. If you have configured ~/.my.cnf or equivalent file, -p is not necessary. If your Ubuntu username matches your MySQL username, the mysql interface can be started by typing mysql and nothing further.
.my.cnf
To configure local options for a user, the ~/.my.cnf file can be used. A .my.cnf file could read:
$ cat ~/.my.cnf [client] password="password"
Taken from MySQL 5.1 Reference Manual
On Unix, MySQL programs read startup options from the following files.
File Name Purpose /etc/my.cnf Global options /etc/mysql/my.cnf Global options (as of MySQL 5.1.15) SYSCONFDIR/my.cnf Global options $MYSQL_HOME/my.cnf Server-specific options defaults-extra-file The file specified with --defaults-extra-file=path, if any ~/.my.cnf User-specific options
Simple DB Management
Once in the mysql utility, mysql> will show as the command prompt. All commands end with ";". Exit using Ctrl-D or "quit".
mysql> SHOW DATABASES;
Will list all databases. To create a database, use:
mysql> CREATE DATABASE [database];
To delete a database, use the DROP syntax. Note: Be very careful with DROP. There is no warning prompt and you can easily delete all of your data.
mysql> DROP DATABASE [database]; mysql> DROP TABLE [table];
My favorite comic about DROP TABLES; To use a specific database use the command below:
mysql> USE [database];
Each database contains tables which can be used for things such as a list of users.
mysql> SHOW TABLES IN [database];
Will list the tables in the [database]. Tables can be described (their columns listed) using:
mysql> DESCRIBE [table];
Columns in a user table would probably include "user", "password" and various permissions.
Basic Syntax
SELECT columns, IN database.table WHERE criteria (can use AND, OR)
Example
We will create a simple database to keep track of users:
Create the Table
Having created the database already, use root (or a user with sufficient GRANT permissions) to create a user and give him or her privileges on the database they will have to edit. It is possible to create a user and define their permissions at the same time using GRANT. Note: If the user referenced by this command does not exist, the user will be created and the user and db tables will both be updated accordingly.
mysql> GRANT ALL PRIVILEGES
-> ON maxdb.* TO newUser@localhost
-> IDENTIFIED BY 'newPass'
-> WITH GRANT OPTION;
First, select the database and create the table with the appropriate columns:
mysql> USE my_first_db; mysql> CREATE TABLE users ( user VARCHAR(20), password CHAR(41), created DATE, readperm BOOL, writeperm BOOL, executeperm BOOL );
We can see the table we've created by describing it:
mysql> DESCRIBE users; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | user | varchar(20) | YES | | NULL | | | password | char(41) | YES | | NULL | | | created | date | YES | | NULL | | | readperm | tinyint(1) | YES | | NULL | | | writeperm | tinyint(1) | YES | | NULL | | | executeperm | tinyint(1) | YES | | NULL | | +-------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
Notice that BOOL was changed to tinyint(1). MySQL does not have native (bit) boolean support. 0 will evaluate to false and 1-255 will evaluate to true. We are using a full 4 bits for this boolean field.
Modify Table
Suppose we've decided that readperm, writeperm and executeperm should default to 0 but we've already created the table and don't want to delete it:
mysql> ALTER TABLE users MODIFY readperm BOOL DEFAULT 0, MODIFY writeperm BOOL DEFAULT 0, MODIFY executeperm BOOL DEFAULT 0; mysql> DESC users; +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | user | varchar(20) | YES | | NULL | | | password | char(41) | YES | | NULL | | | created | date | YES | | NULL | | | readperm | tinyint(1) | YES | | 0 | | | writeperm | tinyint(1) | YES | | 0 | | | executeperm | tinyint(1) | YES | | 0 | | +-------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
Enter Data
Because this represents an empty database, we can load information from a tab-delineated file. Create one entry per row and do not end the file with a newline character. \N represents a null character.
$ cat ~/users max \N 2008-02-29 1 1 1 zach \N 2009-03-19 1 1 0 sam \N 2009-01-09 1 0 0
mysql> LOAD DATA LOCAL INFILE '/home/max/users' INTO TABLE users; Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
Alternatively, we can insert values into the table using:
mysql> INSERT INTO users
-> VALUE ('topsy',NULL,CURDATE(),1,1,1);
Query OK, 1 row affected (0.03 sec)
Or using specific columns:
mysql> INSERT INTO users (user,created,readperm)
-> VALUES ('bailey',CURDATE(),1), ('percy',CURDATE(),0);
mysql> SELECT * FROM users;
+--------+----------+------------+----------+-----------+-------------+
| user | password | created | readperm | writeperm | executeperm |
+--------+----------+------------+----------+-----------+-------------+
| max | NULL | 2008-02-29 | 1 | 1 | 1 |
| zach | NULL | 2009-03-19 | 1 | 1 | 0 |
| sam | NULL | 2009-01-09 | 1 | 0 | 0 |
| topsy | NULL | 2009-12-02 | 1 | 1 | 1 |
| bailey | NULL | 2009-12-02 | 1 | 0 | 0 |
| percy | NULL | 2009-12-02 | 0 | 0 | 0 |
+--------+----------+------------+----------+-----------+-------------+
6 rows in set (0.00 sec)
Because the default values for readperm, writeperm and executeperm are 0, we do not have to specify that data and it will be non-null.
Delete Data
We can use the DELETE FROM syntax to delete rows which meet certain criteria:
mysql> DELETE FROM users where user='bailey' OR user='percy';
Using the LIKE syntax, we can select all rows which have "m" in them. The % operators on either side are wildcards.
mysql> SELECT * FROM users WHERE user LIKE '%m%'; +------+----------+------------+----------+-----------+-------------+ | user | password | created | readperm | writeperm | executeperm | +------+----------+------------+----------+-----------+-------------+ | max | NULL | 2008-02-29 | 1 | 1 | 1 | | sam | NULL | 2009-01-09 | 1 | 0 | 0 | +------+----------+------------+----------+-----------+-------------+ 2 rows in set (0.00 sec)
Update Syntax
Now to add a password for the user max, we can use the UPDATE syntax:
mysql> UPDATE users SET password=PASSWORD("mypass") WHERE user='max';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Queries
Lets check through the table to see if any user has no password and execute permissions using the IS NULL and AND syntax:
mysql> SELECT user,password,executeperm FROM users WHERE password IS NULL AND executeperm=true; +-------+----------+-------------+ | user | password | executeperm | +-------+----------+-------------+ | topsy | NULL | 1 | +-------+----------+-------------+ 1 row in set (0.00 sec)
We can repeat this process for the other users (with different passwords).
mysql> SELECT * FROM users; +-------+-------------------------------------------+------------+----------+-----------+-------------+ | user | password | created | readperm | writeperm | executeperm | +-------+-------------------------------------------+------------+----------+-----------+-------------+ | max | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | 2008-02-29 | 1 | 1 | 1 | | zach | *14E65567ABDB5135D0CFD9A70B3032C179A49EE7 | 2009-03-19 | 1 | 1 | 0 | | sam | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 | 2009-01-09 | 1 | 0 | 0 | | topsy | *8E5E773736B8F836F58A31694266F9A0F48BB8A4 | 2009-12-02 | 1 | 1 | 1 | +-------+-------------------------------------------+------------+----------+-----------+-------------+ 4 rows in set (0.00 sec)
Now we have a full table. Because the PASSWORD() function is a one-way function, we cannot retrieve the plaintext password from the password hash. However, we can check for specific things. Suppose we want to make sure no users used their username as their password.
mysql> SELECT * FROM users WHERE password=PASSWORD(user); +-------+-------------------------------------------+------------+----------+-----------+-------------+ | user | password | created | readperm | writeperm | executeperm | +-------+-------------------------------------------+------------+----------+-----------+-------------+ | topsy | *8E5E773736B8F836F58A31694266F9A0F48BB8A4 | 2009-12-02 | 1 | 1 | 1 | +-------+-------------------------------------------+------------+----------+-----------+-------------+ 1 row in set (0.00 sec)
Oops, looks like topsy should change his password. We'll do it for him:
mysql> UPDATE users SET password=NULL WHERE password=PASSWORD(user); Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT user,password FROM users WHERE password IS NULL; +-------+----------+ | user | password | +-------+----------+ | topsy | NULL | +-------+----------+ 1 row in set (0.00 sec)
MySQL
MySQL is a popular open source database. It is the M in LAMP (Linux, Apache, MySQL, php). Many programming languages provide an interface to MySQL.
Installing MySQL
On Ubuntu Linux:
$ sudo apt-get install mysql-server mysql-client /* do we need this? -> */ mysql-admin
And a useful config tool, phpmyadmin:
$ sudo apt-get install phpmyadmin
When installing mysql-server, you will be prompted for a root password. This password is similar to the root password used in your operating system. The database user root has access to all the information and commands in the database. Set this password to something you will remember.
Installing phpMyAdmin
https://help.ubuntu.com/community/phpMyAdmin
On Ubuntu 9.04 and later, use:
$ sudo apt-get install phpmyadmin
Important: do not use aptitude because it will install the package differently. Using apt-get gives a series of install configurations that aptitude does not.
Choose "Yes" at the first prompt. This will take you through some very simple install instructions. Enter to root database user's password (see below for resetting the root password). The second password screen can be left blank -- phpmyadmin uses this password internally to interface with the database. At the next screen, choose apache2 (if you are not running lighttpd as your webserver).
phpMyAdmin can now be accessed using: http://localhost/phpmyadmin. Use your mysql account/password to login.
Options
Flags
-h host, --host=host
By default MySQL connects to localhost (127.0.0.1). If you need to connect to a remote server, set the host flag to the remote server's address. However, most remote servers have phpmyadmin installed for database management.
-ppassword, --password=password
By default MySQL uses no password. Specify your password here -- there is no whitespace inbetween -p and the password. Note: It is recommended not to type your password here as it will be easily visible in ~/.bash_history (see example below). Instead, use only -p followed by whitespace. You will be prompted for a password after the flag is passed to MySQL.
$ cat /home/max/.bash_history | grep mysql mysql -u crackme -powned
-P port, --port=port
Specify a port on the host to connect to. Note: This does not seem to be working -- I can connect to MySQL on any port on localhost.
--reconnect
Reconnect to the server if the connection is dropped. This is enabled by default and can be disabled with --disable-reconnect
-u user, --user=user
Specify the user who will connect to the database. By default this is the linux user. If the two are the same, you can connect to the mysql database using mysql -p. You will be prompted for a password.
-v, --verbose
Be more verbose.
Configuring MySQL
phpMyAdmin
phpMyAdmin is a very useful utility written in php. It is a set of scripts that provides a GUI on top of your databases. Its freely available here: phpMyAdmin homepage
Command Line
Users and Permissions
MySQL automatically sets up a 'root' user, who has all privileges to all databases by default.
To create a user and define his/her permissions later, use CREATE USER:
mysql> CREATE USER 'user'[@host] IDENTIFIED BY 'password';
To list the users in a MySQL database:
mysql> SELECT user FROM mysql.user;
The permissions for the user can be determined later using GRANT:
mysql> GRANT ALL PRIVILEGES
-> ON [database, *.* for all] TO 'user'[@host];
GRANT allows the admin to grant permissions to individual users. Appending WITH GRANT OPTION to the end of this command allows the user to grant the same permissions he/she has been granted to another user. The options allowed with GRANT are:
ALL PRIVILEGES FILE RELOAD ALTER INDEX SELECT CREATE INSERT SHUTDOWN DELETE PROCESS UPDATE DROP REFERENCES USAGE
It is possible to create a user and define their permissions at the same time using GRANT. Note: If the user referenced by this command does not exist, the user will be created and the user and db tables will both be updated accordingly.
mysql> GRANT SELECT, INSERT, UPDATE, DELETE
-> ON *.* TO newUser@localhost
-> IDENTIFIED BY 'newPass'
-> WITH GRANT OPTION;
The user newUser can now grant SELECT, INSERT, UPDATE and/or DELETE to an existing user (newUser cannot create a user because she does not have create user priviledges.
$ mysql -u newUser -p
Enter password:
mysql> GRANT SELECT
-> ON *.* TO max@localhost;
Conversely, the REVOKE command:
mysql> REVOKE ALL PRIVILEGES ON *.*
-> FROM newUser@localhost;
Use FLUSH PRIVILEGES; to reload the privileges data after a change has been made.
Note: This does not delete a user. To delete a user, use DROP USER:
mysql> DROP USER newUser@localhost;
Passwords
We could choose to use the mysql database and then issue commands directly to the user table without the preceding mysql..The result of this command shows far more information than we need. Use to obtain the field names in mysql.user use the command below and then SELECT only the ones you wish to view:
mysql> DESC mysql.user;
To change a user's password, use the MySQL PASSWORD('password') hashing function which, as of mysql 4.1, is sufficiently strong (40 bytes + a leading "*").
mysql> SET PASSWORD FOR crackme = PASSWORD('crackpass');
It is possible to retrieve the password hash created by the PASSWORD() function:
mysql> SELECT user, password FROM mysql.user WHERE user='crackme'; +---------+-------------------------------------------+ | user | password | +---------+-------------------------------------------+ | crackme | *681AFE8C52CCB97F7752C672CFF487E7F932D91D | +---------+-------------------------------------------+
The password can then be verified by checking the two hashes are the same.
mysql> SELECT PASSWORD('crackpass');
+-------------------------------------------+
| password('crackpass') |
+-------------------------------------------+
| *681AFE8C52CCB97F7752C672CFF487E7F932D91D |
+-------------------------------------------+
Here we can see the one-way hashing function in action. PASSWORD('crackpass') produces the same hash every time. However, it is impossible to efficiently derive the password from the password hash. Likewise, the probability of having two hashes "collide" (produce the same hash value for two different inputs) is very small. On login, MySQL compares the hash generated by the input password with the hash in the mysql.user table. If they match, then the same word must have been used to generate both passwords and thus the user is granted access.
Forgotten root Password
Modified slightly from http://www.pantz.org/software/mysql/mysqlcommands.html
"Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server."
$ sudo /etc/init.d/mysql stop $ sudo mysqld_safe --skip-grant-tables
In a new terminal window:
$ mysql -u root
mysql> update mysql.user set password=PASSWORD("newrootpassword") where user='root';
mysql> flush privileges;
mysql> quit
$ sudo /etc/init.d/mysql stop
$ sudo /etc/init.d/mysql start
Here we can't use the SET PASSWORD script discussed above because we are running MySQL with the --skip-grant-tables option, so we must manually update the root password using MySQL's built in password hashing function.
Backing up a MySQL DB
phpMyAdmin
Using phpMyAdmin, this is easily done through the GUI.
- Choose Export on the main page
- Select the tables you'd like to export
- Check the Save as file box
- Select the compression type (none, zipped, gzipped, bzipped)
- Choose a file name
- Click Go
At this point, the download will proceed like any other download. Firefox will give you a choice of saving or opening the file. Choose save, and select a location.
Command line
The process is most easily initialized from outside the MySQL utility:
$ mysqldump -u [user] -p [dbname] | {zip|gzip|bzip} -{1-10} > [backupfile.sql.gz]
Where user is your username, dbname is the database to back up, 1-10 is the compression ratio and backupfile.sql.gz is the name of the file to be stored in your current directory.
The mysqlhotcopy utility is written in Perl. It will lock the MySQL tables and then use scp or cp (user can specify which) to copy the tables. The utility then unlocks and flushes the tables. This utility only works if the source and destination are on the same machine and if the tables are MyISAM or ARCHIVE.
$ mysqlhotcopy -u [user] --password=[password] db_name [/path/to/new/dir]
Note: It is highly recommended to configure a .my.cnf file so as to avoid sending your password in plaintext. mysqlhotcopy will not prompt for a password given the -p option.
If only given a new database name, mysqlhotcopy assumes /var/lib/mysql (example below). This utility can be used to back up a live site because it locks the tables beforehand, preventing users from writing to the database for the duration of the copy.
/var/lib/mysql$ sudo mysqlhotcopy -p=pass my_first_db my_first_db_backup Locked 2 tables in 0 seconds. Flushed tables (`my_first_db`.`artist`, `my_first_db`.`table1`) in 0 seconds. Copying 7 files... Copying indices for 0 files... Unlocked tables. mysqlhotcopy copied 2 tables (7 files) in 0 seconds (0 seconds overall). /var/lib/mysql$ ls debian-5.0.flag ibdata1 ib_logfile0 ib_logfile1 my_first_db my_first_db_backup mysql mysql_upgrade_info
If the user's password is already contained in a .my.cnf file, then the -p option can be omitted. This is recommended for security so as not to send a user's password in plaintext.
Restoring a MySQL Database
The MySQL databases on a system are stored by default in /var/lib/mysql/ and owned by the mysql user.
mysql -u root -p pen_test < pen_test_db.sql
Restores pen_test_db.sql to the pen_test database using the root MySQL user.
