MySQL administration

how to kick user from MySQL server

1. get his connection id with SHOW PROCESSLIST 2. KILL 21;(connection id instead of 21)

list all the views in the classicmodels database

> SHOW FULL TABLES WHERE table_type = 'VIEW';

Sometimes, you want to see the tables in the database that you are not connected to. In this case, you can use the FROM clause of the SHOW TABLES statement to specify the database from which you want to show the tables.

> SHOW TABLES FROM mysql LIKE 'time%';

unlock the user

ALTER USER 'brad'@'localhost' ACCOUNT UNLOCK;

Change MySQL user password using ALTER USER statement

ALTER USER dbadmin@localhost IDENTIFIED BY 'littlewhale';

lock the user account dolphin@localhost

ALTER USER dolphin@localhost ACCOUNT LOCK;

How to update the key distributions the key distributions can be sometimes inaccurate e.g., after you have done a lot of data changes in the table including insert, delete, or update. If the key distribution is not accurate, the query optimizer may pick a bad query execution plan that may cause a severe performance issue.

ANALYZE TABLE payments;

Checking integrity of database tables Something wrong can happen to the database server e.g., the server was shutdown unexpectedly, error while writing data to the hard disk, etc. These situations could make the database operate incorrectly and in the worst case, it can be crashed.

CHECK TABLE orders;

Creating roles

CREATE ROLE crm_dev, crm_read, crm_write; Suppose you develop an application that uses the CRM database. To interact with the CRM database, you need to create accounts for developers who need full access to the database. In addition, you need to create accounts for users who need only read access and others who need both read/write access. To avoid granting privileges to each user account individually, you create a set of roles and grant the appropriate roles to each user account.

Creating a locked account

CREATE USER david@localhost IDENTIFIED BY 'Secret!Pass1' ACCOUNT LOCK;

The definer of the procedure

DELIMITER $$ CREATE DEFINER=fx PROCEDURE GetPayments() SQL SECURITY DEFINER BEGIN SELECT * FROM payments; END$$ DELIMITER ; The definer of the procedure is fx and SQL SECURITY specifies that the procedure will execute with the privileges of the definer. When you rename fx to sth else, you need to manually change the definer in the stored procedure GetPayments() and save it.

In practice, you use the ... statement which is a shorthand of the DESCRIBE statement. For example, the following statement is equivalent to the DESCRIBE above:

DESC orders;

Removing roles

DROP ROLE crm_read, crm_write;

Stop MySQL Server on Windows

First, launch the Command Prompt by pressing Windows+R to open the Run box and type cmd and press Enter. Second, navigate to the bin folder of the MySQL if it is not in the Window path environment. Third, use the following command to stop MySQL Server: mysqladmin -u root -p shutdown Enter password: ******** It prompts for a password of the root account. You need to enter the password and press the Enter keyboard.

Start MySQL Server on Windows

First, open the Run dialog by pressing Windows+R keyboards: Second, type cmd and press Enter: Third, type mysqld and press Enter: If the bin folder is not in the Windows path environment, you can navigate to the bin folder e.g., C:\Program Files\MySQL\MySQL Server 8.0\bin\ and use the mysqld command.

In this example, bob@localhost can select data from four columns employeeNumber, lastName, firstName, and email and update only the lastName column in the employees table.

GRANT SELECT (employeeNumner,lastName, firstName,email), UPDATE(lastName) ON employees TO bob@localhost;

The following statement grants all privileges to crm_dev role

GRANT ALL ON crm.* TO crm_dev;

grant all privileges to fx

GRANT ALL ON *.* TO fx;

In this example, bob@localhost can delete rows from the table employees in the database classicmodels.

GRANT DELETE ON classicmodels.employees TO bob@localhsot;

grant the EXECUTE privilege to the rfc@localhost:

GRANT EXECUTE ON classicmodels.* TO rfc@localhost;

In this example, bob@localhost can execute the stored procedure CheckCredit in the current database.

GRANT EXECUTE ON PROCEDURE CheckCredit TO bob@localhost;

Database privileges apply to all objects in a database. To assign database-level privileges, you use the ... syntax, for example: In this example, bob@localhost can insert data into all tables in the classicmodels database.

GRANT INSERT ON classicmodels.* TO bob@localhost;

The following statement grants INSERT, UPDATE, and DELETE privileges to crm_write role:

GRANT INSERT, UPDATE, DELETE ON crm.* TO crm_write;

The following example grants UPDATE, DELETE, and INSERT privileges on the table employees to bob@localhost:

GRANT INSERT, UPDATE, DELETE ON employees TO bob@localhost;

In this example, alice@localhost assumes all privileges of root.

GRANT PROXY ON root TO alice@localhost; Proxy user privileges allow one user to be a proxy for another. The proxy user gets all privileges of the proxied u

This example grants the SELECT privilege on the table employees in the sample database to the user acount bob@localhost

GRANT SELECT ON employees TO bob@localhost;

Global privileges apply to all databases in a MySQL Server. To assign global privileges, you use the ... syntax, for example The account user bob@localhost can query data from all tables in all database of the current MySQL Server.

GRANT SELECT ON *.* TO bob@localhost;

The following statement grants SELECT privilege to crm_read role:

GRANT SELECT ON crm.* TO crm_read;

The following statement grants the crm_rev role to the user account crm_dev1@localhost:

GRANT crm_dev TO crm_dev1@localhost;

Copying privileges from a user account to another copy privileges from the crm_dev1 user account to crm_dev2 user account

GRANT crm_dev1@localhost TO crm_dev2@localhost;

The following statement grants the crm_read role to the user account crm_read1@localhost:

GRANT crm_read TO crm_read1@localhost;

The following statement grants the crm_read and crm_write roles to the user accounts crm_write1@localhost and crm_write2@localhost

GRANT crm_read, crm_write TO crm_write1@localhost, crm_write2@localhost;

Restart MySQL Server on Windows

If MySQL installed as a Window service, you follow these steps to restart the MySQL Server: First, open the Run window by using the Windows+R keyboard. Second, type services.msc and press Enter

How to defragment a table While working with the database, you do a lot of changes such as insert, update and delete data in the table that may cause the physical storage of the table fragmented. As a result, the performance of database server is degraded.

OPTIMIZE TABLE orders;

rename these two users

RENAME USER jill@localhost TO jin@localhost, hill@localhost TO hank@localhost;

rename user john@localhost

RENAME USER john@localhost TO doe@localhost;

allows you to repair some errors occurred in database tables. MySQL does not guarantee that the ... statement can repair all errors that the tables may have.

REPAIR TABLE employees;

revoke all privileges of the rfc@localhost user account by using

REVOKE ALL, GRANT OPTION FROM rfc@localhost;

revoke the UPDATE and INSERT privileges from rfc@localhost

REVOKE INSERT, UPDATE ON classicmodels.* FROM rfc@localhost;

Revoking privileges from roles For example, to temporarily make all read/write users read-only, you change the crm_write role as follows To restore the privileges, you need to re-grant them as follows:

REVOKE INSERT, UPDATE, DELETE ON crm.* FROM crm_write; GRANT INSERT, UPDATE, DELETE ON crm.* FOR crm_write;

revoke the PROXY privilege from the rfc@localhost

REVOKE PROXY ON root FROM rfc@localhost;

to list all users in a MySQL database server, you use

SELECT user FROM mysql.user;

to show users and other information such as host, account locking, and password expiration status, you use the following query

SELECT user, host, account_locked, password_expired FROM user;

get user's current role

SELECT current_role();

Setting default roles

SET DEFAULT ROLE ALL TO crm_read1@localhost; Now if you connect to the MySQL using the crm_read1 user account and try to access the CRM database: The statement issued the following error message: ERROR 1044 (42000): Access denied for user 'crm_read1'@'localhost' to database 'crm' This is because when you granted roles to a user account, it did not automatically make the roles to become active when the user account connects to the database server.

Change MySQL user password using the SET PASSWORD statement

SET PASSWORD FOR 'dbadmin'@'localhost' = bigshark;

Setting active roles

SET ROLE NONE; The following statement set the active role to NONE, meaning no active role. SET ROLE ALL; To set active roles to all granted role, you use SET ROLE DEFAULT; To set active roles to default roles that set by the SET DEFAULT ROLE statement, you use

The more flexible way to get a list of columns in a table is to use

SHOW COLUMNS FROM orders;

To get more information about the column, you add the ... keyword to the SHOW COLUMNS command as follows

SHOW FULL COLUMNS FROM table_name;

shows the number of attempts to connect to MySQL Server using a locked account.

SHOW GLOBAL STATUS LIKE 'Locked_connects'; When a locked account attempts to log in, MySQL increases the Locked_connects status variable by 1.

To verify the role assignments, you use the SHOW GRANTS statement as the following example:

SHOW GRANTS FOR crm_write1@localhost USING crm_write; To show the privileges that roles represent, you use the USING clause with the name of the granted roles as follows:

show the privileges assigned to super@localhost user by using

SHOW GRANTS FOR super@localhost;

Using MySQL SHOW GRANTS to display the privileges granted for the current user

SHOW GRANTS; SHOW GRANTS FOR CURRENT_USER; Both CURRENT_USER and CURRENT_USER() return the current user.

Change MySQL user password using UPDATE statement

USE mysql; UPDATE user SET authentication_string = PASSWORD('dolphin') WHERE user = 'dbadmin' AND host = 'localhost'; FLUSH PRIVILEGES; Notice that the PASSWORD() function computes the hash value from a plain text.

Locked accounts are...

are forbidden to login into

create a new user called bob:

create user bob@localhost identified by 'Secure1pass!'; The hostname part of the account name is optional. If you omit it, the user can connect from any host. The CREATE USER statement creates one or more user accounts with no privileges. It means that the user accounts can log in to the MySQL Server, but cannot do anything such as selecting a database and querying data from tables.

go to the session of the user root and create a new database called bobdb select the database bobdb create a new table called lists grant all privileges on the bobdb to bob user???

grant all privileges on bobdb.* to bob@localhost;

SHOW FULL TABLES;

lists all tables in a database and shows whether it's a view or a base table

Fifth, open a second session and log in to the MySQL as bob:

mysql -u bob -p Enter password: ********

First, connect to the MySQL Server using the mysql client tool:

mysql -u root -p Enter password: ********

remove two user accounts api@localhost and remote using

mysql> drop user api@localhost, remote; remote% can have any host

drop the user dbadmin@localhost by using

mysql> drop user dbadmin@localhost

disconnect from the MySQL Server

mysql> exit

Restoring an SQL dump file

mysql>drop database mydb; mysql>source c:\backup\mydb.sql

the following statement makes a backup of the database structure of the database classicmodels

mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\classicmodels-ddl.sql --no-data --databases classicmodels The mysqldump tool is located in the root/bin directory of the MySQL installation directory. To access the mysqldump tool, you navigate to the root/bin folder and use the mysqldump command with the following options.

Creating a backup of a database

mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\classicmodels.sql --databases classicmodels The mysqldump tool is located in the root/bin directory of the MySQL installation directory. To access the mysqldump tool, you navigate to the root/bin folder and use the mysqldump command with the following options.

the following command makes a backup of the classicmodels and world databases

mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\classicmodels_world.sql --databases classicmodels world The mysqldump tool is located in the root/bin directory of the MySQL installation directory. To access the mysqldump tool, you navigate to the root/bin folder and use the mysqldump command with the following options.

to make a backup of the employees table from the classicmodels database, you use the following command

mysqldump --user=root --password=Supe!rPass1 --result-file=c:\backup\employees.sql classicmodels employees The mysqldump tool is located in the root/bin directory of the MySQL installation directory. To access the mysqldump tool, you navigate to the root/bin folder and use the mysqldump command with the following options.

Second, show users from the current MySQL Server:

select user from mysql.user;