MySQL Potpourri

Linux, Uncategorized, ,

Purpose:

The purpose of this article is to display a set of commonly used MySQL Commands.

MySQL is a popular open-source relational database management system (RDBMS) that utilizes various database engines to manage and store data. A database engine is responsible for handling data storage, retrieval, manipulation, and optimization. MySQL supports several storage engines, each with its own unique features and characteristics. As of my last knowledge update in September 2021, some of the prominent MySQL database engines are:

  1. InnoDB: InnoDB is the default storage engine for MySQL since version 5.5. It provides ACID (Atomicity, Consistency, Isolation, Durability) compliance, which ensures data integrity even in the presence of hardware or software failures. InnoDB supports transactions and offers features like row-level locking, foreign key constraints, and crash recovery mechanisms.
  2. MyISAM: MyISAM was the default storage engine in MySQL prior to version 5.5. It's known for its simplicity and good read performance. However, it lacks support for transactions and doesn't provide the same level of data integrity and crash recovery as InnoDB. MyISAM is suitable for read-heavy workloads but might not be the best choice for systems requiring transactional support.
  3. Memory (Heap): The Memory storage engine stores data in memory rather than on disk. This provides extremely fast access to data but is limited by the amount of available memory. It's commonly used for temporary tables or caching purposes.
  4. Archive: The Archive storage engine is designed for storing and retrieving large amounts of historical data that is rarely updated. It compresses data to save space and is optimized for fast inserts and minimal disk I/O. However, it's not suited for regular querying and updating due to its read-only nature.
  5. NDB (Cluster): Also known as MySQL Cluster, this engine is designed for high availability and scalability. It uses a distributed architecture to store data across multiple nodes and provides automatic data sharding and replication. It's suitable for applications requiring real-time responsiveness and fault tolerance.
  6. CSV: The CSV storage engine stores data in comma-separated value (CSV) format files. It's useful for importing and exporting data between MySQL and other applications, but it lacks support for indexing and transactions.
  7. Blackhole: The Blackhole storage engine accepts data but doesn't store it. Instead, it discards the data, making it useful for replication setups where data needs to be sent to other servers without affecting the local database.
  8. Federated: The Federated storage engine allows you to create a "virtual" table that's connected to a table on a remote MySQL server. This enables distributed querying and data retrieval across different MySQL instances.

It's worth noting that the choice of database engine depends on the specific requirements of your application, such as the nature of your data, the level of transaction support needed, and the desired performance characteristics. Additionally, MySQL's landscape might have evolved since my last update in September 2021, so I recommend checking the official MySQL documentation for the most up-to-date information on available storage engines and their features.

Basic Ops

Check mysqld version shell> 
mysql --version
Check mysqld version mysql shell> 
SELECT VERSION;
Check set values shell> 
my_print_default_mysqld
Show compile options shell> 
mysqlbug
Connect to MySQL server shell> 
mysql -u root -p

Instance Level Ops

Start mysqld shell> 
service mysqld start
Stop mysqld shell> 
service mysqld stop
Gracefull shutdown using mysql admin shell> 
mysqladmin -u root -p shutdown
Check status shell> 
mysqladmin -u -p status
or 
systemctl status mysql>
Check status mysql> 
\s
Show system variables mysql> 
SHOW GLOBAL VARIABLES;
Show status variables mysql> 
SHOW GLOBAL STATUS;
List connected Clients mysql> 
SHOW FULL POCESSLIST;

Database OPS

List existing datbases mysql> 
SHOW DATABASES;
Create a database mysql> 
CREATE DATABASE db1;
Switch database in use mysql> 
USE db1;

Admin Ops

Export to a file mysql> 
SELECT ... INTO OUTFILE 'filename'
Read from a file mysql> 
SET @ @character_set_database=binary;
LOAD DATA INFILE 'filename' INTO TABLE 'table1';
Backup mysql> 
mysqldump [options] -B db1,db2 > dump.sql;
Backup and gzip a table shell> 
mysqldump -u root -p --opt --no-autocommit --single-transaction db1 | gzip -c > /tmp/$(date +%Y.%m.%d).db1.sql.gz
Restore mysql> 
db1 < dump.sql
Defrag mysql> 
OPTIMIZE TABLE table1;
Update Statistics Info mysql> 
ANALYZE TABLE table1;
Switch logs mysql> 
FLUSH LOGS;
Export table mysql> 
FLUSH TABLES [WITH READ LOCK];

Table Ops

List Existing tables mysql> 
SHOW TABLES;
Show table information mysql> 
SHOW TABLE STATUS [LIKE 'table1;];
Check table properties mysql> 
DESC table1
Alternative Check table properties mysql> 
SHOW CREATE TABLE table1 \G
Create table mysql> 
CREATE TABLE table1;
Modify table properties mysql> 
ALTER TABLE table1...

Transaction Ops

Switch off auto commit mysql> 
SET AUTOCOMMIT=0;
Start a transaction myql> 
START TRANSACTION;
BEGIN;
Execute SQL Statement mysql> 
SELECT ... (any SQL Statement);
Commit mysql> 
COMMIT;
Place a save point mysql> 
SAVEPOINT (name of savepoint);
Rollback mysql> 
ROLLBACK;

Allow Remote Access

# By default MySQL only accepts connections from localhost

Edit the mysqld.cnf file 
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
or 
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Change the bind-address to all zeros to allow remote access

# By default we only accept connections from localhost
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
Save the file and restart mysql

Create a new user from a known IP 
CREATE USER 'mydbuser'@'X.X.X.X' IDENTIFIED BY 'SuperSecretPassword';
Grant the new remote user ALL access to mydb Database 
GRANT ALL PRIVILEGES ON mydb.* TO 'mydbuser'@'X.X.X.X';
Create a new user from any IP 
CREATE USER 'mydbuser'@'%' IDENTIFIED BY 'SuperSecretPassword';
Grant the new remote user ALL access to mydb Database 
GRANT ALL PRIVILEGES ON mydb.* TO 'mydbuser'@'%';

Black Ops

Remove a database. Make sure you have a good backup, even for practice. mysql> 
DROP DATABASE db1;
Remove a table make sure you have it backed up. 
DROP TABLE table1;