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

coredump · Sep 8, 2016

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 · Sep 8, 2016

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 · Sep 8, 2016

Excellent post FerdieQO

Very useful for anyone new to MySQL management.

GswHosting · Sep 8, 2016

That is very useful information You shared with us, FredieQO.

VeyraHost · Sep 8, 2016

Awesome post, thanks Fredie!

FerdieQO · Sep 9, 2016

VeyraHost said:
Awesome post, thanks Fredie!

GswHosting said:
That is very useful information You shared with us, FredieQO.

LJSHost said:
Excellent post FerdieQO

Very useful for anyone new to MySQL management.

You're welcome!

If you have any helpful Mysql commands, please add to my list. I would to learn more command to control Mysql server better.

Rackend · Sep 12, 2016

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

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

coredump

Member

FerdieQO

Well-known member

LJSHost

Well-known member

GswHosting

Well-known member

VeyraHost

Member

FerdieQO

Well-known member

Rackend

New member

GswHosting

Well-known member

Latest posts New threads

Latest posts

New threads

Latest Hosting Offers New Reviews

Latest hosting offers

Popular Contributors - Past 30 Days

Sponsors

Tag Cloud