Ubuntu/mysql
(Created page with ";mysql is one of the most popular database applications on the market today <br\> mysql login mysql -u <user> -p will then prompt you for a password <br\> mysqladmin login m...") |
|||
(21 intermediate revisions by one user not shown) | |||
Line 1: | Line 1: | ||
;mysql is one of the most popular database applications on the market today | ;mysql is one of the most popular database applications on the market today | ||
<br\> | <br\> | ||
− | mysql login | + | ==mysql login== |
mysql -u <user> -p | mysql -u <user> -p | ||
will then prompt you for a password | will then prompt you for a password | ||
<br\> | <br\> | ||
− | mysqladmin login | + | ==mysqladmin login== |
mysqladmin -u <user> -p | mysqladmin -u <user> -p | ||
you can also run other commands after -p, such as:<br\> | you can also run other commands after -p, such as:<br\> | ||
see if the database is running | see if the database is running | ||
status | status | ||
− | change MySQL root pass | + | ==change MySQL root pass== |
sudo dpkg-reconfigure mysql-server-5.1 | sudo dpkg-reconfigure mysql-server-5.1 | ||
− | verify MySQL is running | + | ==verify MySQL is running== |
sudo netstat -tap | grep mysql | sudo netstat -tap | grep mysql | ||
+ | ==determine MySQL version== | ||
+ | <nowiki>~$ 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)</nowiki> | ||
+ | ==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== | ||
+ | <nowiki>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)</nowiki> | ||
+ | |||
+ | ==select a database== | ||
+ | mysql> USE wikidb; | ||
+ | |||
+ | ==show tables in a database== | ||
+ | <nowiki>mysql> SHOW TABLES; | ||
+ | +--------------------+ | ||
+ | | Tables_in_wikidb | | ||
+ | +--------------------+ | ||
+ | | archive | | ||
+ | | category | | ||
+ | | categorylinks | | ||
+ | | change_tag | | ||
+ | +--------------------+ | ||
+ | 49 rows in set (0.00 sec)</nowiki> | ||
+ | |||
+ | ==show structure of table== | ||
+ | <nowiki>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)</nowiki> | ||
+ | |||
+ | ==select all data from a table== | ||
+ | <nowiki>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)</nowiki> | ||
+ | |||
+ | ==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) | ||
+ | ----<br> | ||
+ | ==Change a mysql user's password.== | ||
+ | Login as root. | ||
+ | <nowiki>~$ mysql -u root -p</nowiki> | ||
+ | From the mysql console, select the mysql db (this is where mysql users are stored, like root) | ||
+ | <nowiki>~mysql> use mysql;</nowiki> | ||
+ | Update the password. Make sure to replace '''your_new_password''' and '''changed_user''' with the appropriate values. | ||
+ | <nowiki>~$mysql> update user set password=PASSWORD('your_new_password') where User='changed_user';</nowiki> | ||
+ | ----<br> | ||
+ | |||
+ | ==Create Table== | ||
+ | Create a super basic table with a couple of columns. | ||
+ | <nowiki>mysql> CREATE TABLE test (id INT NOT NULL, name VARCHAR(100) NOT NULL);</nowiki> | ||
+ | |||
+ | ==Insert into table== | ||
+ | Insert values into table | ||
+ | <nowiki>mysql> INSERT INTO test (id, name) VALUES ('1','John');</nowiki> | ||
+ | |||
+ | ==Create Database== | ||
+ | creating a db called temp. | ||
+ | <nowiki>mysql> CREATE DATABASE temp;</nowiki> | ||
+ | |||
+ | ==Create a new user== | ||
+ | creating a new user called user with a password of password | ||
+ | <nowiki>mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';</nowiki> | ||
+ | If you choose to create a new user with <code>'%'</code>, 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, <code>''@'localhost'</code>/<br> | ||
+ | You can fix this issue by dropping that user from the user table. | ||
+ | <nowiki>mysql> select * from user where host='localhost' and user=''\G; | ||
+ | mysql> DROP USER ''@'localhost'; | ||
+ | mysql> FLUSH PRIVILEGES;</nowiki> | ||
+ | |||
+ | ==Grant access to database== | ||
+ | granting access to database for user | ||
+ | <nowiki>mysql> grant all privileges on artifactorydb.* to 'artifactory'@'%'; | ||
+ | Query OK, 0 rows affected (0.00 sec)</nowiki> | ||
+ | |||
+ | ==Show Grants== | ||
+ | show grants for a user | ||
+ | <nowiki>MariaDB [(none)]> SHOW GRANTS FOR 'grafana'@'%';</nowiki> |
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'@'%';