MySQL Commands you need to know to work with MySQL on a dedicated server?

coredump

Member
Registered
Joined
Jun 20, 2016
Messages
32
Points
0
I can create MySQL databases easily on my hosting via a hosting control panel but it's possible to do same functions with commands on SSH?
What are MySQL Commands do we need to know to work with MySQL on a dedicated server?
 

FerdieQO

Well-known member
Joined
Jul 15, 2016
Messages
213
Points
28
Hello Coredump,

Here's the list of MySQL Commands. When running these commands, you need to log into MySQL as root (MySQL root account, not root account for managing your VPS) or the account with full permissions. All these actions will be performed on a VPS CentOS.

Login to MySQL

You use this command

Code:
mysql -u root -p
1. the folder contains database

On CentOS, all raw databases stored in /var/lib/mysql

2. Managing accounts and permissions

show all users:
Code:
mysql> SELECT * FROM mysql.user;
Delete null user:
Code:
mysql> DELETE FROM mysql.user WHERE user = ' ';
Delete all users that not root:
Code:
mysql> DELETE FROM mysql.user WHERE NOT (host="localhost" AND user="root");
Change root account name
Code:
mysql> UPDATE mysql.user SET user="mydbadmin" WHERE user="root";
Assign full permissions for a new user
Code:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
Add detailed permission for an user
Code:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass';
Assign full permissions for an user on a specific database
Code:
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
Change password of an user/account
Code:
mysql> UPDATE mysql.user SET password=PASSWORD("newpass") WHERE User='username';
Delete user:
Code:
mysql> DELETE FROM mysql.user WHERE user="username";
3. Database Commands

Show all databases:
Code:
mysql> SHOW DATABASES;
Create database:
Code:
mysql> CREATE DATABASE mydatabase;
Using a database:
Code:
mysql> USE mydatabase;
Delete a database:
Code:
mysql> DROP DATABASE mydatabase;
Optimize database:

All Databases:
Code:
$ sudo mysqlcheck -o --all-databases -u root -p
Single Database:

Code:
$ sudo mysqlcheck -o db_schema_name -u root -p
4. Work with tables

To work with tables on a database, you need to use this command first:

Code:
mysql> USE mydatabase;
show all tables:
Code:
mysql> SHOW TABLES;
show database of a table:
Code:
mysql> SELECT * FROM tablename;
change table name:
Code:
mysql> RENAME TABLE first TO second;
or
Code:
mysql> ALTER TABLE mytable rename as mynewtable;
Delete table:
Code:
mysql> DROP TABLE mytable;
5. Commands with Columns and rows

Show columns in a table:
Code:
mysql> DESC mytable;
or
Code:
mysql> SHOW COLUMNS FROM mytable;
Change column name:
Code:
mysql> UPDATE mytable SET mycolumn="newinfo" WHERE mycolumn="oldinfo";
Select data from a table:
Code:
mysql> SELECT * FROM mytable WHERE mycolumn='mydata' ORDER BY mycolumn2;
Insert data into a table:
Code:
mysql> INSERT INTO mytable VALUES('column1data','column2data','column3data','column4data','column5data','column6data','column7data','column8data','column9data');
Delete data in a table
Code:
mysql> DELETE FROM mytable WHERE mycolumn="mydata";
Update data in a table:
Code:
mysql> UPDATE mytable SET column1="mydata" WHERE column2="mydata";
6. Export and restore database

Backup all databases with this command
Code:
mysqldump -u root -pmypass --all-databases > alldatabases.sql
Backup a database
Code:
mysqldump -u username -pmypass databasename > database.sql
Restore all databases
Code:
mysql -u username -pmypass < alldatabases.sql (no space in between -p and mypass)
Restore a database
Code:
mysql -u username -pmypass databasename < database.sql
Backup the structure of a database:
Code:
mysqldump --no-data --databases databasename > structurebackup.sql
Backup the structure of multiple databases:
Code:
mysqldump --no-data --databases databasename1 databasename2 databasename3 > structurebackup.sql
Backup a table
Code:
mysqldump --add-drop-table -u username -pmypass databasename table_1 table_2 > databasebackup.sql
 

LJSHost

Well-known member
Hosting Provider
Registered
Joined
Jul 5, 2016
Messages
1,031
Points
63
Excellent post FerdieQO

Very useful for anyone new to MySQL management.
 

GswHosting

Well-known member
Registered
Joined
Aug 23, 2016
Messages
233
Points
0
That is very useful information You shared with us, FredieQO.
 

Rackend

New member
Registered
Joined
Aug 31, 2016
Messages
8
Points
0
For sure you should know that commands, but I'd recommend to install web user interface for managing MySQL databases like phpmyadmin, it would be much faster, especially when your db size and quantity grows
 

GswHosting

Well-known member
Registered
Joined
Aug 23, 2016
Messages
233
Points
0
GswHosting
You are absolutely right about that, but with phpmyadmin you get open for hackers, and it is wonderful thing to know manage everything with commands.
 
Newer Threads
Replies
0
Views
2,124
Replies
1
Views
2,421
Replies
5
Views
8,324
Recommended Threads

Latest Hosting OffersNew Reviews

Sponsors

Tag Cloud

You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.

Top