mysql:general
Table of Contents
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
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
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 |
Show status of variables
MariaDB> SHOW STATUS WHERE variable_name = 'threads_connected'; +-------------------+-------+ | Variable_name | VALUE | +-------------------+-------+ | Threads_connected | 1 | +-------------------+-------+ 1 ROW IN SET (0.004 sec) MariaDB>
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 >
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)
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
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)
Users
Create User
mysql> create user 'dbuser'@'localhost' identified by 'dbuser'; Query OK, 0 rows affected (0.08 sec)
Update user's password
mysql> UPDATE mysql.user SET Password=PASSWORD('new-password-here') WHERE USER='user-name-here' AND Host='host-name-here';
Delete User
Grant permissions
mysql> GRANT ALL PRIVILEGES ON mydatabase.* to dbuser@'localhost' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.02 sec)
Set GRANT permissions on tables
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>
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>
mysql> revoke SELECT ON `database`.`table` FROM 'user1'@'10.0.0.0/255.0.0.0'; Query OK, 0 rows affected (0.01 sec)
Show Grants
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>
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>
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 = 'databse'; +----------+ | COUNT(*) | +----------+ | 51 | +----------+ 1 row in set (0.00 sec) mysql>
Count number of rows in table
mysql> SELECT COUNT(*) FROM database.vpn_config; +----------+ | COUNT(*) | +----------+ | 1710 | +----------+ 1 row in set (0.09 sec) mysql>
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: 148MB 0:00:34 [5.67MB/s] [> ] 0% ETA 3:10:01
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 (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
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>
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
SQL for datetime related stuff
> 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)
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:
MariaDB [(NONE)]> SET GLOBAL event_scheduler = ON; Query OK, 0 ROWS affected (0.001 sec)
Then as a regular user, add the event to schedule:-
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)
Check what's there:-
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)
mysql/general.txt · Last modified: by andrew