Ubuntu/mysql

Revision as of 01:53, 6 May 2016

mysql is one of the most popular database applications on the market today

mysql login

mysql -u <user> -p

will then prompt you for a password

mysqladmin login

mysqladmin -u <user> -p

you can also run other commands after -p, such as:
see if the database is running

status

change MySQL root pass

sudo dpkg-reconfigure mysql-server-5.1

verify MySQL is running

sudo netstat -tap | grep mysql

determine MySQL version

~$ mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for debian-linux-gnu (i486) using readline 6.1

or
mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.1.73-0ubuntu0.10.04.1 |
+-------------------------+
1 row in set (0.00 sec)

or
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| protocol_version        | 10                      |
| version                 | 5.1.73-0ubuntu0.10.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | i486                    |
| version_compile_os      | debian-linux-gnu        |
+-------------------------+-------------------------+
5 rows in set (0.00 sec)

backup wiki mysql database

sudo -s
mysqldump -u user -p db.name > wikidatabasebackup.sql
i.e. mysql -u wikiuser -p wikidb > /var/www/mediawiki/backup/wikidatabasebackup10_4_12.sql

view all databases

~$ mysql -u root -p
~mysql>SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema | 
+--------------------+
3 rows in set (0.00 sec)

determine which database is selected

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

"NULL" will be shown if no DB is selected

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| wikidb     |
+------------+
1 row in set (0.00 sec)

select a database

mysql> USE wikidb;

show tables in a database

mysql> SHOW TABLES;
+--------------------+
| Tables_in_wikidb   |
+--------------------+
| archive            |
| category           |
| categorylinks      |
| change_tag         |
+--------------------+
49 rows in set (0.00 sec)

show structure of table

mysql> DESCRIBE category;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| cat_id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| cat_title   | varbinary(255)      | NO   | UNI | NULL    |                |
| cat_pages   | int(11)             | NO   | MUL | 0       |                |
| cat_subcats | int(11)             | NO   |     | 0       |                |
| cat_files   | int(11)             | NO   |     | 0       |                |
| cat_hidden  | tinyint(3) unsigned | NO   |     | 0       |                |
+-------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

select all data from a table

mysql> SELECT * FROM user;
+---------+------------+-----------------+----------------------------------------------+----------------------------------------------+-------------------+-------------------------------+----------------+----------------------------------+--------------------------+----------------------------------+--------------------------+-------------------+----------------+
| user_id | user_name  | user_real_name  | user_password                                | user_newpassword                             | user_newpass_time | user_email                    | user_touched   | user_token                       | user_email_authenticated | user_email_token                 | user_email_token_expires | user_registration | user_editcount |
+---------+------------+-----------------+----------------------------------------------+----------------------------------------------+-------------------+-------------------------------+----------------+----------------------------------+--------------------------+----------------------------------+--------------------------+-------------------+----------------+
|       1 | Root       |                 | :B:m1t398gt:r4389dhsdh39fgkfndf39fgd9 |                                              | NULL              | [email protected] | 20120814155951 | c6f49hdf94hf9dfc39021dsjkfd49843 | NULL                     | d53fh29dsalmoamlc293023dml2399db | 20120821162812           | 20120811233239    |              2 |
1 row in set (0.00 sec)

delete (drop) a database

~mysql> DROP DATABASE IF EXISTS <db name>;
Query OK, 11 rows affected (0.04 sec)

list all users

~mysql> SELECT User,Host FROM mysql.user;
+------------------+-----------------+
| User             | Host            |
+------------------+-----------------+
| root             | 127.0.0.1       |
| root             | ::1             |
| root             | mysite |
| debian-sys-maint | localhost       |
| root             | localhost       |
+------------------+-----------------+
5 rows in set (0.00 sec)

delete a user

~mysql> DROP USER 'wordpress'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Change a mysql user's password.

Login as root.

~$ mysql -u root -p

From the mysql console, select the mysql db (this is where mysql users are stored, like root)

~mysql> use mysql; 

Update the password. Make sure to replace your_new_password and changed_user with the appropriate values.

~$mysql> update user set password=PASSWORD('your_new_password') where User='changed_user';