MySQL - Administration 30 Functions
CHECK TABLE
Allows you to check the integrity of database tables
OPTIMIZE TABLE
Allows you to optimize the table to avoid this defragmenting problems.
REPAIR TABLE
Allows you to repair some errors occurred in database tables
CREATE TABLE
Create a new table within a database.
SHOW CHARACTER SET
Display all of the charset on the server.
UPDATE user password
EX: USE mysql; UPDATE user SET password = PASSWORD('dolphin') WHERE user = 'dbadmin' AND host = 'localhost'; FLUSH PRIVILEGES;
SHOW TABLES FROM
Ex: SHOW TABLES FROM 'dbname' LIKE 'time%'; Show tables from a specified database LIKE tables starting with 'time%'.
ANALYZE TABLE
If the key distribution is not accurate, after you have done a lot of data changes in the table including insert, delete, or update, the query optimizer may pick a bad query execution plan that may cause a severe performance issue. To solve this problem use this optimizer.
SHOW DATABASES
List all databases on the local MySQL database server.
SHOW FULL TABLES
List all the tables and their table_type.
SHOW SCHEMAS
List all the tables in a database.
SHOW TABLES
List all the tables in a database.
SHOW FULL TABLES WHERE
List all the views in a database. SHOW FULL TABLES WHERE table_type = 'VIEW'
SHOW CURRENT LOG USER
List all users that are currently logged in the MySQL database server. EX: SELECT user, host, db, command FROM information_schema.processlist;
SHOW COLUMNS
List of columns in a table
SHOW USERS
MySQL does not have a "SHOW USERS" command instead you run a query on the user table of the database server. SELECT user FROM mysql.user;
REVOKE
Revoke privileges from a user account
SHOW CURRENT USER
SELECT current_user();
DESCRIBE table
Show all columns of a table. Also you can use DESC.
SHOW TABLES LIKE
Shows all tables in a database that start with the a define search pattern or parameter. Ex: SHOW TABLES LIKE 'p%'; Will show all tables in a db that start with the letter 'p'. Ex: SHOW TABLES LIKE '%es'; Will show all tables in a db that ends with the letters 'es'.
USE
Switch to a different database. Ex: mysql > USE 'desired databasename';
BACKUP data only
To backup a MySQL database data ONLY, use option -no-create-info. EX: mysqldump -u [username] -p[password] -no-create-info [database_name] > [dump_file.sql]
BACKUP
To backup a MySQL database. EX: mysqldump -u [username] -p[password] [database_name] > [dump_file.sql]
BACKUP all databases
To backup all MySQL databases into a single file, use the option -all-database. EX: mysqldump -u [username] -p[password] -all-database > [dump_file.sql]
BACKUP multiple databases
To backup multiple MySQL databases into a single file, just separate database names by the command in the [database_name] EX: mysqldump -u [username] -p[password] [dbname1,dbname2,...] > [dump_file.sql]
ALTER USER
To change a user password. ALTER USER dbadmin@localhost IDENTIFIED BY 'littlewhale';
SET PASSWORD
To change a user password. EX: SET PASSWORD FOR 'dbadmin'@'localhost' = bigshark;
CREATE USER
To create a new user account
CREATE ROLE
To create certain access levels, create roles with full, read, or write permission. EX: crm = databasename CREATE ROLE crm_dev, crm_read, crm_write; GRANT ALL ON crm.* TO crm_dev = grants full privileges GRANT SELECT ON crm.* TO crm_read; = grant select privileges. GRANT INSERT, UPDATE, DELETE ON crm.* TO crm_write; = grants INSERT, UPDATE and DELETE privileges.
GRANT
To grant privileges to a user account
DROP USER
To remove one or more user accounts. To remove a user, you specify the account name in the 'user_name'@'host_name' format after the DROP USER clause. If you want to remove multiple users at a time, you use a list of comma-separated users.
