Ubuntu/mysql

From r00tedvw.com wiki
Jump to: navigation, search
mysql is one of the most popular database applications on the market today


Contents

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';


Create Database

creating a db called temp.

mysql> CREATE DATABASE temp;

Create a new user

creating a new user called user with a password of password

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
Personal tools
Namespaces

Variants
Actions
Navigation
Mediawiki
Confluence
DevOps Tools
Ubuntu
Oracle Linux
AWS
Windows
OpenVPN
Grafana
Pivotal
osTicket
OTRS
phpBB
WordPress
VmWare ESXI 5.1
Crypto currencies
HTML
CSS
Python
Java Script
PHP
Raspberry Pi
Canvas LMS
Kaltura Media Server
MetaSploit
Zoneminder
Photoshop CS2
Fortinet
Uploaded
Certifications
General Info
Games
Meal Plans
NC Statutes
Toolbox