Ubuntu/mysql

Revision as of 14:02, 5 August 2015

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)

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