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;