====== 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 ==== 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) ==== 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 -p[userpassword] [database] > dump.sql restore:# mysql -u -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)