Important MySQL Commands you should know

MooseLucifer

Well-known member
Registered
Joined
May 20, 2016
Messages
149
Points
28
When running these commands, you need to login to MySQL with the root account (MySQL root, not the root account that manages VPS) or an account with full permissions. All the actions I perform on VPS CentOS

Log in to MySQL using the command: mysql -u root -p

1. Directory containing database
On CentOS, all raw database files are stored in /var/lib/mysql directory

2. Account management and authorization
Show all users:
Code:
mysql> SELECT * FROM mysql.user;
Delete null user:
Code:
mysql> DELETE FROM mysql.user WHERE user = '';
Delete all users without root:
Code:
mysql> DELETE FROM mysql.user WHERE NOT (host = "localhost" AND user = "root");
Rename the root account (for security):
Code:
mysql> UPDATE mysql.user SET user = "mydbadmin" WHERE user = "root";
Assign full permissions to a new user:
Code:
mysql> GRANT ALL PRIVILEGES ON *. * TO 'username' @ 'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
Detailed authorization for a new 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 to a new user on a certain database:
Code:
mysql> GRANT ALL PRIVILEGES ON mydatabase. * TO 'username' @ 'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
Change user password:
Code:
mysql> UPDATE mysql.user SET password = PASSWORD ("newpass") WHERE User = 'username';
Delete user:
Code:
mysql> DELETE FROM mysql.user WHERE user = "username";
Finally reload the user
Code:
mysql> FLUSH PRIVILEGES;
mysql> exit;
3. Database operations
Display 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;
Database optimization:
All Databases:
Code:
$sudo mysqlcheck -o --all-databases -u root -p
Single Database:
$sudo mysqlcheck -o db_schema_name -u root -p
4. Table Operations
All the operations below you have to pre-select the database using the command: mysql> USE mydatabase;

Show the entire table:
Code:
mysql> SHOW TABLES;
Display data of table:
Code:
mysql> SELECT * FROM tablename;
Rename table:
Code:
mysql> RENAME TABLE first TO second;
or
[QUOTE]mysql> ALTER TABLE mytable rename as mynewtable;
Clear table:
Code:
mysql> DROP TABLE mytable;
5. Column and row operations
All the operations below you have to pre-select the database using the command: mysql> USE mydatabase;

Display columns in the table:
Code:
mysql> DESC mytable;
or
Code:
mysql> SHOW COLUMNS FROM mytable;
Column name change:
Code:
mysql> UPDATE mytable SET mycolumn = "newinfo" WHERE mycolumn = "oldinfo";
Select data:
Code:
mysql> SELECT * FROM mytable WHERE mycolumn = 'mydata' ORDER BY mycolumn2;
Insert data into the table:
Code:
mysql> INSERT INTO mytable VALUES ('column1data', 'column2data', 'column3data', 'column4data', 'column5data', 'column6data', 'column7data', 'column8data', 'column9data');
Clear data in table:
Code:
mysql> DELETE FROM mytable WHERE mycolumn = "mydata";
Update data in table:
mysql> UPDATE mytable SET column1 = "mydata" WHERE column2 = "mydata";
6. Backup and restore operations
Backup the entire database with the command (note there is no space between -p and the password):
Code:
mysqldump -u root -pmypass --all-databases> alldatabases.sql
Backup any database:
Code:
mysqldump -u username -pmypass databasename> database.sql
Restore the entire database with the command:
Code:
mysql -u username -pmypass <alldatabases.sql (no space in between -p and mypass)
Restore any database:
Code:
mysql -u username -pmypass databasename <database.sql
Just backup the database structure:
Code:
mysqldump --no-data --databases databasename> structurebackup.sql
Only backup multiple database structures:
Code:
mysqldump --no-data --databases databasename1 databasename2 databasename3> structurebackup.sql
Back up certain tables:
Code:
mysqldump --add-drop-table -u username -pmypass databasename table_1 table_2> databasebackup.sql
Good luck!
 
Older 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