Archive for the ‘MySQL’ Category

How to stop and start MySQL server

To Stop MySQL Server #sudo mysqld stop #service mysql stop #/etc/init.d/mysql stop #sudo service mysql stop #sudo /etc/init.d/mysql stop #/usr/local/mysql/bin/mysqladmin -u root -p shutdown To Start MySQL Server #sudo mysqld start # service mysql start # /etc/init.d/mysql start# # sudo service mysql start # sudo /etc/init.d/mysql start To restart MySQL Server without running stop and […]

Largest Tables in MySQL

Largest Tables in MySQL: Following query will give largest tables of entire MySQL. SELECT table_schema as ‘Database’, table_name AS ‘Table’, round(((data_length + index_length) / 1024 / 1024), 2) ‘Size in MB’ FROM information_schema.TABLES ORDER BY (data_length + index_length); Output

How to get table size in MySQL

How to get table size in MySQL: We can get this information from the schema ‘information_schema’. Query: SELECT table_name AS ‘Table’, round(((data_length + index_length) / 1024 / 1024), 2) ‘Size in MB’ FROM information_schema.TABLES WHERE table_schema = “sakila” AND table_name = “rental”;

Difference between delete and truncate

Delete: Whenever we are using delete from tablename, then automatically deleted data internally stored in buffer. We can get it back by using rollback command.

How to execute script file in MySQL

script file contains list of commands saved in with .sql/.txt or other extension. To import or export file in MySQL, use must be granted FILE permission. Steps to execute script file in MySQL: 1. Create a file(.sql extension): We are going to get list of users in MySQL using the file prvr@ubuntu:~$ cat > getlistofusers.sql […]

Rename table in MySQL

RENAME TABLE is used to change the name of table. We must have ALTER and DROP privileges for the original table, and CREATE and INSERT privileges for the new table.

Copy a table structure in MySQL

We can clone or copy a table structure in MySQL using LIKE or AS operators. Using LIKE: We can use CREATE TABLE … LIKE to create an empty table based on the definition of another table, including any column fields and indexes defined in the original table: Syntax: CREATE TABLE new_table LIKE original_table;

Change USER password in MySQL

We can the user password in MySQL, using UPDATE, SET, ALTER command. To change password, current login user must have granted permissions on user table of mysql database and must have UPDATE, SET and ALTER permissions on user table. To reset password in MySQL 5.6 or older versions(This statement not works in MySQL 5.7 or […]

Powered by k2schools