MooseLucifer
Well-known member
- 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:
Delete null user:
Delete all users without root:
Rename the root account (for security):
Assign full permissions to a new user:
Detailed authorization for a new user:
Assign full permissions to a new user on a certain database:
Change user password:
Delete user:
Finally reload the user
3. Database operations
Display all databases:
Create database:
Using a database:
Delete a database:
Database optimization:
All Databases:
Single Database:
All the operations below you have to pre-select the database using the command: mysql> USE mydatabase;
Show the entire table:
Display data of table:
Rename table:
Clear table:
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:
or
Column name change:
Select data:
Insert data into the table:
Clear data in table:
Update data in table:
Backup the entire database with the command (note there is no space between -p and the password):
Backup any database:
Restore the entire database with the command:
Restore any database:
Just backup the database structure:
Only backup multiple database structures:
Back up certain tables:
Good luck!
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;
Code:
mysql> DELETE FROM mysql.user WHERE user = '';
Code:
mysql> DELETE FROM mysql.user WHERE NOT (host = "localhost" AND user = "root");
Code:
mysql> UPDATE mysql.user SET user = "mydbadmin" WHERE user = "root";
Code:
mysql> GRANT ALL PRIVILEGES ON *. * TO 'username' @ 'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
Code:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON mydatabase. * TO 'username' @ 'localhost' IDENTIFIED BY 'mypass';
Code:
mysql> GRANT ALL PRIVILEGES ON mydatabase. * TO 'username' @ 'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
Code:
mysql> UPDATE mysql.user SET password = PASSWORD ("newpass") WHERE User = 'username';
Code:
mysql> DELETE FROM mysql.user WHERE user = "username";
Code:
mysql> FLUSH PRIVILEGES;
mysql> exit;Display all databases:
Code:
mysql> SHOW DATABASES;
Code:
mysql> CREATE DATABASE mydatabase;
Code:
mysql> USE mydatabase;
Code:
mysql> DROP DATABASE mydatabase;All Databases:
Code:
$sudo mysqlcheck -o --all-databases -u root -p4. 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;
Code:
mysql> SELECT * FROM tablename;
Code:
mysql> RENAME TABLE first TO second;
or
[QUOTE]mysql> ALTER TABLE mytable rename as mynewtable;
Code:
mysql> DROP TABLE mytable;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;
Code:
mysql> SHOW COLUMNS FROM mytable;
Code:
mysql> UPDATE mytable SET mycolumn = "newinfo" WHERE mycolumn = "oldinfo";
Code:
mysql> SELECT * FROM mytable WHERE mycolumn = 'mydata' ORDER BY mycolumn2;
Code:
mysql> INSERT INTO mytable VALUES ('column1data', 'column2data', 'column3data', 'column4data', 'column5data', 'column6data', 'column7data', 'column8data', 'column9data');
Code:
mysql> DELETE FROM mytable WHERE mycolumn = "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
Code:
mysqldump -u username -pmypass databasename> database.sql
Code:
mysql -u username -pmypass <alldatabases.sql (no space in between -p and mypass)
Code:
mysql -u username -pmypass databasename <database.sql
Code:
mysqldump --no-data --databases databasename> structurebackup.sql
Code:
mysqldump --no-data --databases databasename1 databasename2 databasename3> structurebackup.sql
Code:
mysqldump --add-drop-table -u username -pmypass databasename table_1 table_2> databasebackup.sql
