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

root@mysql-A:~# export MYSQL_PS1="mysql-A [\d]> "
root@mysql-A:~# mysql -u root -p 
Enter password: 
 
mysql-A [(none)]> 

This can be set in a my.sql config file, but the disadvantage is that the server requires a restart to activate it:-

# nano /etc/my.cnf
[mysql]
prompt=\\u@\\h [\\d]>\\_

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 :-

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

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
  • \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

mysql> show processlist;
+-------+-------------+---------------------+-------------+---------+------+----------------------+----------------------+-----------+---------------+-----------+
| Id    | User        | Host                | db          | Command | Time | State                | Info                 | Rows_sent | Rows_examined | Rows_read |
+-------+-------------+---------------------+-------------+---------+------+----------------------+----------------------+-----------+---------------+-----------+
|  7581 | fp6         | 10.100.220.29:58303 | expose6     | 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 | expose6     | Sleep   |    1 |                      | NULL                 |         1 |             1 |         1 |

Databases

Show databases available

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

Create Database

mysql> create database mydatabase;
Query OK, 1 row affected (0.06 sec)
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| mydatabase         | 
+--------------------+
1 rows in set (0.00 sec)

Tables

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

Drop table from database

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)

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)

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

Users

Create User

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

Delete User

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

Grant permissions

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

Using localhost or 127.0.0.1?

localhost should resolve to 127.0.0.1, but connections to localhost rather than 127.0.0.1 may fail as the MySQL client will try to connect to a UNIX socket, 127.0.0.1 always uses TCP.

Revoke permissions

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> 

Reload permissions

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

Show users and permissions

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> 

Import into database from file

To import sql statements from a file use:-

-bash-4.1$ mysql -u myuser -p stats < db.sql 
Enter password: <hidden>
-bash-4.1$

Note that there is no on screen output if successful.

Import data from sql file with progress counter

Use pipe viewer (pv) which can show a progress bar:-

root@com5-prod-db:/var/lib/mysql# pv /tmp/system5.sql | mysql -u root -p
Enter password: <hidden>
 148MB 0:00:34 [5.67MB/s] [>                                                      ]  0% ETA 3:10:01

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)

Show database sizes

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> 

Count number of tables in database

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

Count number of rows in table

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

Import data from .csv file

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

Copy table to new table

CREATE TABLE appdb.users_backup LIKE appdb.users; 
INSERT appdb.users_backup SELECT * FROM appdb.users;

Basic backup and restore

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.

Timeouts for network connections

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

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)

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

vi /etc/mysql/my.cnf
interactive_timeout = 7200
wait_timeout        = 7200

mysql/general.txt · Last modified: 17/04/2018 20:31 by andrew