Rainsbrook

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== MySQL General Info ======

 
===== Log in to mysql monitor =====

Using mysql monitor:-

  # mysql -h 127.0.0.1 -u dbuser -pletmein

Note, if you are giving a password on the command line, do not add a space between -p and the password.

-h is not necessary if you are logging in to the same machine you are working on.

===== Change prompt in mysql monitor =====

<code bash>
root@mysql-A:~# export MYSQL_PS1="mysql-A [\d]> "
root@mysql-A:~# mysql -u root -p 
Enter password: 

mysql-A [(none)]> 
</code>

This can be set in a my.sql config file, but the disadvantage is that the server requires a restart to activate it:-
 
<code bash>
# nano /etc/my.cnf
[mysql]
prompt=\\u@\\h [\\d]>\\_
</code>

Or:-


This way, the mysql server does not need a restart and individual users can set a different prompt is required. Also add to /etc/skel/.bashrc to allow new users to gain from this. \\
In users ~/.bashrc, set :-

<code bash>
#Set MySQL prompt
export MYSQL_PS1="\\h:[\\d] mysql> "
</code>

==== Generic variables: ====


  * \S displays semicolon
  * \’ displays single quote
  * \” displays double quote
  * \v displays server version
  * \p displays port
  * \\ displays backslash
  * \n displays newline
  * \t displays tab
  * \ displays space (there is a space after \ )
  * \d displays default database
  * \h displays default host
  * \_ displays space (there is a underscore after \ )
  * \c displays a mysql statement counter. keeps increasing as you type commands.
  * \u displays username
  * \U displays username@hostname accountname

==== Date related variables: ====


  * \D displays full current date (as shown in the above example)
  * \w displays 3 letter day of the week (e.g. Mon)
  * \y displays the two digit year
  * \Y displays the four digit year
  * \o displays month in number
  * \O displays 3 letter month (e.g. Jan)
  * \R displays current time in 24 HR format
  * \r displays current time in 12 hour format
  * \m displays the minutes
  * \s displays the seconds
  * \P displays AM or PM


===== Running Processes =====

<code mysql>
mysql> show processlist;
+-------+-------------+---------------------+-------------+---------+------+----------------------+----------------------+-----------+---------------+-----------+
| Id    | User        | Host                | db          | Command | Time | State                | Info                 | Rows_sent | Rows_examined | Rows_read |
+-------+-------------+---------------------+-------------+---------+------+----------------------+----------------------+-----------+---------------+-----------+
|  7581 | fp6         | 10.100.220.29:58303 | exp6     | Sleep   |    4 |                      | NULL                 |         1 |             1 |         1 |
|  7582 | fp6         | 10.100.220.29:58305 | fp6         | Sleep   |    4 |                      | NULL                 |         0 |             0 |         0 |
|  7638 | fp6         | 10.100.220.29:58771 | exp6     | Sleep   |    1 |                      | NULL                 |         1 |             1 |         1 |
</code>


===== Show status of variables =====


<code sql>
MariaDB> show status where variable_name = 'threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 1     |
+-------------------+-------+
1 row in set (0.004 sec)

MariaDB>

</code>

<code>
MariaDB > show processlist;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host      | db   | Command | Time | State | Info             | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 74 | sse  | localhost | NULL | Query   |    0 | Init  | show processlist |    0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
1 row in set (0.001 sec)

MariaDB >
</code>

===== Show databases available =====

<code mysql>
mysql> show databases;
+------------+
| Database   |
+------------+
| MyDatabase |
+------------+
1 rows in set
</code>   



===== Create Database =====

<code mysql>
mysql> create database mydatabase;
Query OK, 1 row affected (0.06 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| mydatabase         | 
+--------------------+
1 rows in set (0.00 sec)
</code>

===== Select query =====

  mysql> select * from city where name='Coventry';
  +-----+----------+-------------+----------+------------+
  | ID  | Name     | CountryCode | District | Population |
  +-----+----------+-------------+----------+------------+
  | 466 | Coventry | GBR         | England  |     304000 |
  +-----+----------+-------------+----------+------------+

Select with "like" and percent % wildcard:-

  mysql> select * from city where name like 'Co%';
  +------+-------------------------+-------------+--------------------+------------+
  | ID   | Name                    | CountryCode | District           | Population |
  +------+-------------------------+-------------+--------------------+------------+
  |   37 | Constantine             | DZA         | Constantine        |     443727 |
  |  466 | Coventry                | GBR         | England            |     304000 |
  |  521 | Colchester              | GBR         | England            |      96063 |
  | 4006 | Costa Mesa              | USA         | California         |     108724 |
  | 4052 | Compton                 | USA         | California         |      92864 |
  +------+-------------------------+-------------+--------------------+------------+

select with "and":-

  mysql> select * from city where name like 'Co%' and CountryCode='GBR';
  +-----+------------+-------------+----------+------------+
  | ID  | Name       | CountryCode | District | Population |
  +-----+------------+-------------+----------+------------+
  | 466 | Coventry   | GBR         | England  |     304000 |
  | 521 | Colchester | GBR         | England  |      96063 |
  +-----+------------+-------------+----------+------------+
  2 rows in set (0.00 sec)

Using Less than test:-

  mysql> select * from city where Population < '90000' and CountryCode='USA';
  +------+------------+-------------+----------------+------------+
  | ID   | Name       | CountryCode | District       | Population |
  +------+------------+-------------+----------------+------------+
  | 4062 | Kenosha    | USA         | Wisconsin      |      89447 |
  | 4063 | Elgin      | USA         | Illinois       |      89408 |
  | 4064 | Odessa     | USA         | Texas          |      89293 |
  | 4065 | Carson     | USA         | California     |      89089 |
  | 4066 | Charleston | USA         | South Carolina |      89063 |
  +------+------------+-------------+----------------+------------+
  5 rows in set (0.00 sec)





===== Create Table =====

  mysql> create table PERFORMANCE (showid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, showname varchar(100),created TIMESTAMP DEFAULT NOW());
  Query OK, 0 rows affected (0.15 sec)
  mysql> show tables;
  +-------------------------+
  | Tables_in_TICKETBOOKING |
  +-------------------------+
  | EVENTS                  |
  | PERFORMANCE             |
  | VENUE                   |
  +-------------------------+
  3 rows in set (0.00 sec)
  
  mysql>
  

Show how the table was created with:-
  mysql-A [world]>  SHOW CREATE TABLE Country;

Show table columns and types:-
  mysql-A [world]> describe Country;
  +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
  | Field          | Type                                                                                  | Null | Key | Default | Extra |
  +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
  | Code           | char(3)                                                                               | NO   | PRI |         |       |
  | Name           | char(52)                                                                              | NO   |     |         |       |
  | Continent      | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO   |     | Asia    |       |
  ...edited...
  +----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
  15 rows in set (0.00 sec)
  
  mysql-A [world]>


===== Add new column to table =====


  mysql> ALTER TABLE teams ADD displayname VARCHAR(20);
  Query OK, 3 rows affected (0.09 sec)
  Records: 3  Duplicates: 0  Warnings: 0
  
  mysql> SELECT * FROM teams ORDER BY team;
  +---------+--------------+-------------+
  | team    | teampassword | displayname |
  +---------+--------------+-------------+
  | desktop | desktop      | NULL        | 
  | network | network      | NULL        | 
  | server  | server       | NULL        | 
  +---------+--------------+-------------+
  3 rows in set (0.00 sec)

Position of column can be specified with:-

  mysql> ALTER TABLE teams ADD displayname VARCHAR(20) AFTER team; 
  
New column will be NULL, so add some fields:-

  mysql> UPDATE TEAMS SET displayname="Desktop" WHERE team="desktop";
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> select * from TEAMS order by team;
  +---------+--------------+-------------+
  | team    | teampassword | displayname |
  +---------+--------------+-------------+
  | desktop | desktop      | Desktop     | 
  | network | network      | NULL        | 
  | server  | server       | NULL        | 
  +---------+--------------+-------------+
  3 rows in set (0.00 sec)


===== Rename Column =====

  ALTER TABLE tableName CHANGE oldcolname newcolname datatype(length);
  > ALTER TABLE password CHANGE user_id  username_id varchar(255);


===== Insert new rows into table =====

  mysql> INSERT INTO TEAMS(team, teampassword, displayname) VALUES("security","security","Security");
  Query OK, 1 row affected (0.02 sec)


===== Delete row from table =====


  mysql> select * from city where Name like 'Gib%';
  +------+-----------+-------------+-----------+------------+
  | ID   | Name      | CountryCode | District  | Population |
  +------+-----------+-------------+-----------+------------+
  |  915 | Gibraltar | GIB         | Gibraltar |      27025 |
  | 4085 | Gibraltar | GBR         | Gibraltar |      30000 |
  +------+-----------+-------------+-----------+------------+
  2 rows in set (0.00 sec)
  
  mysql> delete from City where id=4085;
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select * from city where Name like 'Gib%';
  +-----+-----------+-------------+-----------+------------+
  | ID  | Name      | CountryCode | District  | Population |
  +-----+-----------+-------------+-----------+------------+
  | 915 | Gibraltar | GIB         | Gibraltar |      27025 |
  +-----+-----------+-------------+-----------+------------+
  1 row in set (0.00 sec)

===== UPDATE field in table =====

  mysql> select team from TEAMS  where team="Network Team";
  +--------------+
  | team         |
  +--------------+
  | Network Team | 
  +--------------+
  1 row in set (0.00 sec)
  
  mysql> UPDATE TEAMS
      -> SET team="network"
      -> WHERE team="Network Team";
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> select * from TEAMS;
  +--------------+--------------+
  | team         | teampassword |
  +--------------+--------------+
  | network      | network      | 
  | Server Team  | server       | 
  | Desktop Team | desktop      | 
  +--------------+--------------+
  3 rows in set (0.00 sec)




===== Delete Column from table =====


  mysql> alter table FORWARDZONE drop octet1;
  Query OK, 8 rows affected (0.10 sec)
  Records: 8  Duplicates: 0  Warnings: 0


===== Change Column type =====


  mysql> alter table ZONEINFO modify ns4 VARCHAR(20);
  Query OK, 0 rows affected (0.07 sec)
  Records: 0  Duplicates: 0  Warnings: 0
  

===== Drop table from database =====

<code mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| countries      |
| newtable       |
+----------------+
2 rows in set (0.00 sec)

mysql> drop table countries;
Query OK, 0 rows affected (0.00 sec)
</code>


===== Users =====


==== Create User ====

<code mysql>
mysql> create user 'dbuser'@'localhost' identified by 'dbuser';
Query OK, 0 rows affected (0.08 sec)

</code>

===== Update user's password =====

<code mysql>
mysql> UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE USER='user-name-here' AND Host='host-name-here';
</code>

==== Delete User ====


<code mysql>
mysql> drop user 'nagios'@'10.10.10.10';
Query OK, 0 rows affected (0.02 sec)
</code>

==== Grant permissions ====

<code mysql>
mysql> GRANT ALL PRIVILEGES ON mydatabase.* to dbuser@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)
</code>


==== Set GRANT permissions on tables ====

<code mysql>
db-s1:[(none)] mysql> create user 'user1'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.05 sec)

db-s1:[(none)] mysql> grant select on database.table to 'user1'@'localhost';
Query OK, 0 rows affected (0.06 sec)

db-s1:[(none)] mysql> grant create,delete,insert,select,update on database.table_template to 'user1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

db-s1:[(none)] mysql>
</code>


==== Revoke permissions ====


<code mysql>
mysql> revoke delete on `table6`.`template` from 'user23'@'10.0.0.0/255.0.0.0';
Query OK, 0 rows affected (0.07 sec)

mysql> 
</code>

<code mysql>
mysql> revoke SELECT ON `database`.`table` FROM 'user1'@'10.0.0.0/255.0.0.0';
Query OK, 0 rows affected (0.01 sec)
</code>


===== Show Grants =====

<code mysql>
db-s1:[(none)] mysql> show grants for 'user1'@'10.0.0.0/255.0.0.0';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for user1@10.0.0.0/255.0.0.0                                                                                   |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'10.0.0.0/255.0.0.0' IDENTIFIED BY PASSWORD '*79C10D794370254500D5931B7D02F94A4561365A' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `database`.`table_template` TO 'user1'@'10.0.0.0/255.0.0.0'       |
| GRANT SELECT ON `database`.`site` TO 'user1'@'10.0.0.0/255.0.0.0'                                                  |
| GRANT SELECT ON `database`.`spider_verify` TO 'user1'@'10.0.0.0/255.0.0.0'                                         |
| GRANT SELECT ON `database`.`spider` TO 'user1'@'10.0.0.0/255.0.0.0'                                                |
| GRANT SELECT ON `database`.`spider_verify_results` TO 'user1'@'10.0.0.0/255.0.0.0'                                 |
+--------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

db-s1:[(none)] mysql>
</code>

==== Reload permissions ====

<code mysql>
mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.02 sec)

</code>


==== Show users and permissions ====

<code mysql>
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| slave_user       | %         |
| mysql-B          | 1.1.1.2   |
| root             | 127.0.0.1 |
...edited...
+------------------+-----------+

mysql> show grants for 'mysql-B'@'1.1.1.2';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for mysql-B@1.1.1.2                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'mysql-B'@'1.1.1.2' IDENTIFIED BY PASSWORD '*EBB77A62CD91DADE2ACDF516B08FCFD10125DE3C' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'user'@'10.0.0.0/255.0.0.0';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for user@10.0.0.0/255.0.0.0                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'10.0.0.0/255.0.0.0' IDENTIFIED BY PASSWORD '4A4561365A'                                    |
| GRANT SELECT, INSERT, UPDATE, CREATE ON `db01`.`table01` TO 'user'@'10.0.0.0/255.0.0.0'                                  |
| GRANT SELECT ON `db01`.`table02` TO 'user'@'10.0.0.0/255.0.0.0'                                                          |
| GRANT SELECT, INSERT, UPDATE ON `db01`.`table03` TO 'user'@'10.0.0.0/255.0.0.0'                                          |
+--------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> flush PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

mysql> 
</code>

  
===== Show database sizes =====

<code mysql>
mysql> SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+------------+
| Database name      | Size (MB)  |
+--------------------+------------+
| db1                | 0.45312500 |
| db2                | 2.07812500 |
| db3                | 0.64062500 |
| mysql              | 0.63744450 |
+--------------------+------------+
4 rows in set (0.06 sec)

mysql> 
</code>

  
===== Count number of tables in database =====

<code mysql>
mysql> SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'databse';
+----------+
| COUNT(*) |
+----------+
|       51 |
+----------+
1 row in set (0.00 sec)

mysql> 
</code>



===== Count number of rows in table =====

<code mysql>
mysql> SELECT COUNT(*) FROM database.vpn_config;
+----------+
| COUNT(*) |
+----------+
|     1710 |
+----------+
1 row in set (0.09 sec)

mysql> 
</code>
===== Import data from sql file with progress counter =====

Use pipe viewer (pv) which can show a progress bar:-
<code>
root@com5-prod-db:/var/lib/mysql# pv /tmp/system5.sql | mysql -u root -p
Enter password: 
 148MB 0:00:34 [5.67MB/s] [>                                                      ]  0% ETA 3:10:01
</code>


===== Import data from .csv file =====

<code mysql>
LOAD DATA INFILE '/backup/myfile.csv' IGNORE INTO TABLE \
appdb.mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' \
ESCAPED BY '"' LINES TERMINATED BY '\n';
</code>




===== Copy table to new table =====

<code mysql>
CREATE TABLE appdb.users_backup LIKE appdb.users; 
INSERT appdb.users_backup SELECT * FROM appdb.users;
</code>
===== Basic backup and restore (mysqldump) =====


  backup: # mysqldump -u <username> -p[userpassword] [database] > dump.sql
  
  restore:# mysql -u <username> -p[userpassword] [database] < dump.sql

Note, no space between ''-p'' and the password.


===== Optimize table =====

<code mysql>
mysql> optimize table search_page_report_new;

+------------------------------------+----------+----------+-------------------------------------------------------------------+
| Table                              | Op       | Msg_type | Msg_text                                                          |
+------------------------------------+----------+----------+-------------------------------------------------------------------+
| database.search_page_report_new    | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| database.search_page_report_new    | optimize | status   | OK                                                                |
+------------------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (8 hours 19 min 5.01 sec)

mysql>
</code>

===== Timeouts for network connections =====


The default timeout is 8hrs which is far too long for most uses (this is not an inactive connection timeout)

<code mysql>
mysql> show global variables like 'interactive_timeout%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 14400 |
+---------------------+-------+
1 row in set (0.00 sec)


mysql> show global variables like 'wait_timeout%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 14400 |
+---------------+-------+
1 row in set (0.00 sec)


mysql> set global interactive_timeout = 7200;
Query OK, 0 rows affected (0.00 sec)


mysql> set global wait_timeout = 7200;
Query OK, 0 rows affected (0.00 sec)
</code>

Don't forget to update my.cnf to make this permanent. 

<code>
vi /etc/mysql/my.cnf
interactive_timeout = 7200
wait_timeout        = 7200
</code>

===== SQL for datetime related stuff =====

<code sql>
> describe user_model;
+------------------+--------------+------+-----+---------------------+----------------+
| Field            | Type         | Null | Key | Default             | Extra          |
+------------------+--------------+------+-----+---------------------+----------------+
| id               | int(11)      | NO   | PRI | NULL                | auto_increment |
| user_id          | varchar(255) | NO   | UNI | NULL                |                |
| date_last_update | timestamp    | YES  |     | current_timestamp() |                |
| login_status     | tinyint(1)   | NO   |     | NULL                |                |
| cookie_expiry    | timestamp    | NO   |     | 0000-00-00 00:00:00 |                |
+------------------+--------------+------+-----+---------------------+----------------+
5 rows in set (0.001 sec)

</code>

Select rows where timestamp is in the past:-

> SELECT * FROM `user_model` WHERE `cookie_expiry` < NOW();




===== Run scheduled tasks =====

First, as the root user or appropriate, enable the event scheduler:

<code sql>
MariaDB [(none)]> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.001 sec)
</code>

Then as a regular user, add the event to schedule:-

<code sql>
MariaDB [test]> create event expire_status on schedule every 1 minute do update usr_model set status=false WHERE `cookie_expiry` < NOW() AND `status`=true;
Query OK, 0 rows affected (0.005 sec)
</code>

Check what's there:-

<code>
MariaDB [test]> show events\G;
*************************** 1. row ***************************
                  Db: test
                Name: expire_status
             Definer: test@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: MINUTE
              Starts: 2023-07-13 09:49:49
                Ends: NULL
              Status: ENABLED
          Originator: 1
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.001 sec)
</code>