Few MySQL Commands in Linux

By Anuket Jain On 2 August 2017 In Home

This is a list of handy MySQL commands that I use time and time again. This covers random issues from WordPress slowness, Query Cache Modification, User Creation, Database dumping and importing, resyncing large files across servers and much more. There may be some issues with the kind of quotes you use your data. If you are having difficulties, try using single quotes ( ‘ ) or tricky quotes ( ` ) around your data.

Here wp_options is a table name and option_id is a column name. So here go random MySQL commands in no particular order.

Create Database:
Create a database using below command.

MariaDB [wordpress]> create database databasename;

List All Databases:
List all databases using following command.

# MariaDB [wordpress]> show databases;

Swtich Database:
Switch to a database using following command.

MariaDB [wordpress]> use database_name;

Show Tables:
To see all the tables in the db.

MariaDB [wordpress]> show tables;

Backup of Database.
Run following command to take backup of single database;

mysqldump -u username -p database_name> /tmp/database_name.sql

Import Database:
Run following command to import the database.

mysql -u username -p database_name < database_name.sql

Login Remote server Mysql
Use following command to access remote location database.

# mysql -h hostname -u root -p
Or
# mysql -h IPAddress -u root -p

Drop Database:
Drop database using following command.

MariaDB [wordpress]> drop database database_name;

Delete Database Table:
To delete a database table use following command.

MariaDB [wordpress]> drop table table_name;

List Data in Table:
Show all data in a table using following command.

MariaDB [wordpress]> SELECT * FROM table_name;

Column Information:
Returns the columns and column information pertaining to the designated table.

MariaDB [wordpress]> show columns from table_name;

Search Row with Value:
Show certain selected rows with the value “string”.

MariaDB [wordpress]> SELECT * FROM table_name WHERE field_name = "string";

For example:

MariaDB [wordpress]> SELECT * FROM wp_options WHERE option_name = "widget_media_image";

+-----------+--------------------+--------------------------------+----------+
| option_id | option_name        | option_value                   | autoload |
+-----------+--------------------+--------------------------------+----------+
|       103 | widget_media_image | a:1:{s:12:"_multiwidget";i:1;} | yes      |
+-----------+--------------------+--------------------------------+----------+

List Record with Different strings:
Here we are showing all records containing the option_id “103” AND option_name "widget_media_image".

MariaDB [wordpress]> SELECT * FROM wp_options WHERE option_id = "103" AND option_name = "widget_media_image";

+-----------+--------------------+--------------------------------+----------+
| option_id | option_name        | option_value                   | autoload |
+-----------+--------------------+--------------------------------+----------+
|       103 | widget_media_image | a:1:{s:12:"_multiwidget";i:1;} | yes      |
+-----------+--------------------+--------------------------------+----------+

List Record Starting with Specific String
Show all records starting with the letters ‘widget’ AND the phone number ‘103’.

MariaDB [wordpress]> SELECT * FROM wp_options WHERE option_name like "widget%" AND option_id = '102';

+-----------+--------------------+--------------------------------+----------+
| option_id | option_name        | option_value                   | autoload |
+-----------+--------------------+--------------------------------+----------+
|       102 | widget_media_audio | a:1:{s:12:"_multiwidget";i:1;} | yes      |
+-----------+--------------------+--------------------------------+----------+

List 7 Record Starting with Specific String
To list 7 records starting with specific string.

MariaDB [wordpress]> SELECT * FROM wp_options WHERE option_name like "widget%" AND autoload = 'yes' limit 1,7;

+-----------+--------------------+------------------------------------------------------------------------------------------------------------------------+----------+
| option_id | option_name        | option_value                                                                                                           | autoload |
+-----------+--------------------+------------------------------------------------------------------------------------------------------------------------+----------+
|       101 | widget_calendar    | a:1:{s:12:"_multiwidget";i:1;}                                                                                         | yes      |
|        78 | widget_categories  | a:2:{i:2;a:4:{s:5:"title";s:0:"";s:5:"count";i:0;s:12:"hierarchical";i:0;s:8:"dropdown";i:0;}s:12:"_multiwidget";i:1;} | yes      |
|       102 | widget_media_audio | a:1:{s:12:"_multiwidget";i:1;}                                                                                         | yes      |
|       103 | widget_media_image | a:1:{s:12:"_multiwidget";i:1;}                                                                                         | yes      |
|       104 | widget_media_video | a:1:{s:12:"_multiwidget";i:1;}                                                                                         | yes      |
|        98 | widget_meta        | a:2:{i:2;a:1:{s:5:"title";s:0:"";}s:12:"_multiwidget";i:1;}                                                            | yes      |
|       106 | widget_nav_menu    | a:1:{s:12:"_multiwidget";i:1;}                                                                                         | yes      |
+-----------+--------------------+------------------------------------------------------------------------------------------------------------------------+----------+

List Reocrd with Regular Expression
Use a regular expression to find records. Here we are finding record beginning with a.

MariaDB [wordpress]> SELECT * FROM wp_options WHERE option_id RLIKE "^10"\G;

Show Unique Records

MariaDB [wordpress]> SELECT DISTINCT option_name FROM wp_options;

Record in Ascending or Descending Order
Sorted record in an ascending (asc) or descending (desc).

MariaDB [wordpress]> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Count No of Rows
To count the no of rows in a table, use below command.

MariaDB [wordpress]> SELECT COUNT(*) FROM wp_options;

MySQL Query Cache:
Check current status of query_cache

# mysql -p -e "show variables like 'query_cache_%'"

+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_strip_comments   | OFF     |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

Changing Query Cache size:
We have multiple way to change the query cache size value.

mysql -p -e "SET GLOBAL query_cache_size = 16777216;"
mysql -p -e "SHOW VARIABLES LIKE 'query_cache_size';"

+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

You can also change the query cache size value through MySQL configuration file.

# vim /etc/my.cnf

Add below line in configuration file.

query_cache_size =16777216
query_cache_type = 1 
query_cache_limit = 2M 
query_cache_strip_comments =1

Slow Query for the wp_options table:
Even though our MySQL are heavily InnoDB optimized, I noticed the following slow query in our slow query logfile. It’s related to the WordPress wp_options table:

# MariaDB [wordpress]> SELECT COUNT(*), autoload FROM wp_options GROUP BY autoload;

+----------+----------+
| COUNT(*) | autoload |
+----------+----------+
|        4 | no       |
|      103 | yes      |
+----------+----------+
2 rows in set (0.00 sec)

You can solve the problem for now by adding an index on autoload.

MariaDB [wordpress]> ALTER TABLE wp_options ADD INDEX (`autoload`);

The wp_options table description is as follows:

MariaDB [wordpress]> desc wp_options;

+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| option_name  | varchar(191)        | NO   | UNI |         |                |
| option_value | longtext            | NO   |     | NULL    |                |
| autoload     | varchar(20)         | NO   | MUL | yes     |                |
+--------------+---------------------+------+-----+---------+----------------+

Change User Password
Run below command to change the user password.

# mysqladmin -u dennis -h Host_name/IPAddress -p PASSWORD 'New_Password'

OR
You can also change the password through MySQL prompt.

# mysql -u root -p
MariaDB [wordpress]> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('New_Password');
MariaDB [wordpress]> flush privileges;

Recover root User Password
Some times we forget the MySQL root password or password not works. So to change the MySQL root password use following steps.

# /etc/init.d/mysqld stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
MariaDB [wordpress]> use mysql;
MariaDB [wordpress]> update user set password=PASSWORD("New_Password") where User='root';
MariaDB [wordpress]> flush privileges;
MariaDB [wordpress]> quit
# /etc/init.d/mysqld stop
# /etc/init.d/mysqld start

User Grant Privilages
Give grant access to user to access any databases.

MariaDB [wordpress]> GRANT ALL PRIVILEGES ON wordpress.* TO 'dennis'@'localhost'

Update Any Tables
Run following command to update your tables.

MariaDB [wordpress]> UPDATE wp_options SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where option_id = '105';

Delete Row
Delete row from any tables.

MariaDB [wordpress]> DELETE from wp_options where option_id = 'whatever';

Delete Column

MariaDB [wordpress]> alter table [table name] drop column [column name];

Add New Column
Add a new column to db.

MariaDB [wordpress]> alter table [table name] add column [new column name] varchar (20);

Change Column Name
Change column name.

MariaDB [wordpress]> alter table [table name] change [old column name] [new column name] varchar (50);

Dump All Database
Dump all databases for backup. Backup file is sql commands to recreate all db’s.

# mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump A Database
Dump one database for backup.

# mysqldump -u username -ppassword databasename >/tmp/databasename.sql

Dump A Table
Dump a table from a database.

# mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore Database
Restore database (or database table) from backup.

# mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table

MariaDB [wordpress]> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

MariaDB [wordpress]> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

Enjoy it!

No Responses

Techoism.com

Few MySQL Commands in Linux

Leave a Reply Cancel reply

Related Posts

Leave a Reply Cancel reply