Ubuntu/mysql

Latest revision as of 21:10, 9 August 2022

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

[edit] mysql login

mysql -u <user> -p

will then prompt you for a password

[edit] mysqladmin login

mysqladmin -u <user> -p

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

status

[edit] change MySQL root pass

sudo dpkg-reconfigure mysql-server-5.1

[edit] verify MySQL is running

sudo netstat -tap | grep mysql

[edit] 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)

[edit] 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

[edit] view all databases

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

[edit] 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)

[edit] select a database

mysql> USE wikidb;

[edit] show tables in a database

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

[edit] 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)

[edit] 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)

[edit] delete (drop) a database

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

[edit] 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)

[edit] delete a user

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

[edit] 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';

[edit] Create Table

Create a super basic table with a couple of columns.

mysql> CREATE TABLE test (id INT NOT NULL, name VARCHAR(100) NOT NULL);

[edit] Insert into table

Insert values into table

mysql> INSERT INTO test (id, name) VALUES ('1','John');

[edit] Create Database

creating a db called temp.

mysql> CREATE DATABASE temp;

[edit] Create a new user

creating a new user called user with a password of password

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

If you choose to create a new user with '%', but then cannot login locally with the username/password you just created, this may be because of a mysql entry in the user table that precedes your new user, @'localhost'/
You can fix this issue by dropping that user from the user table.

mysql> select * from user where host='localhost' and user=''\G;
mysql> DROP USER ''@'localhost';
mysql> FLUSH PRIVILEGES;

[edit] Grant access to database

granting access to database for user

mysql> grant all privileges on artifactorydb.* to 'artifactory'@'%';
Query OK, 0 rows affected (0.00 sec)

[edit] Show Grants

show grants for a user

MariaDB [(none)]> SHOW GRANTS FOR 'grafana'@'%';