Archive for the ‘MySQL’ Category

Find locks in MySQL

In MySQL sometimes one transaction blocks another. The tables that contain information about InnoDB transactions and data locks enable you to determine which transaction is waiting for another, and which resource is being requested. Table used to check locks is information_schema. We can get locks information using following two methods: Method1: Using the SHOW FULL […]

ERROR 1040 (HY000): Too many connections MySQL

Following error occurs when connection limit reaches the maximum limit as defined in the configuration file. The variables holding this value is max_connections. If you want connect MySQL server either kill some of existing connections or login as root and then increase the max_connections limits. ERROR 1040 (HY000): Too many connections To check the current […]

Show running processes in MySQL

We can get the current running processes in MySQL using ‘show processlist’. This will give us the username, connected database, CPU, query running by user. show processlist; mysql> show processlist; +—-+———+———–+———+———+——+———-+——————+ | Id | User | Host | db | Command | Time | State | Info | +—-+———+———–+———+———+——+———-+——————+ | 26 | root | localhost […]

How to save MySQL Query results to csv file

In this tutorial, we are going to export the MySQL query results to csv file. CSV stands for comma separated values. We can often use the CSV file format to exchange the data between applications such as Microsoft Excel, Open Office, Google Docs, etc. Here we are going to export the table ‘film’ from the […]

How to get MySQL Version

Using this tutorial, we can find the MySQL Version with different methods. We can find MySQL Server using MySQL command or Linux based commands. Following are easy methods 1. By running following MySQL command. SELECT version(); or SELECT VERSION(); or select @@version; Output: mysql> select version(); +————————-+ | version() | +————————-+ | 5.7.20-0ubuntu0.16.04.1 | +————————-+ […]

How to check MySQL Server Status on Linux

This article explains to get MySQL Server status information on Linux using mysqladmin command or the mysql init script. The information return uptime of the MySQL Server,Version, Open Tables, Socket type,.. Method 1:Using Status option mysqladmin -u root -p status Output Enter password: Uptime: 98350 Threads: 1 Questions: 18 Slow queries: 0 Opens: 107 Flush […]

Remove Duplicates in MySQL

Duplicates are nothing but, repeated data of database objects(table,view). Find the duplicates in MySQL. Be careful about removing duplicates. Before deleting records(duplicates) of table, its better to run the query on copy of the table. Following table named ’emp1′ contains duplicates in the column empno and empname

Find Duplicate Rows in MySQL

Duplicates are nothing but, repeated data of database objects(table,view). We can find duplicates in different ways. Following table named ’emp1′ contains duplicates in the column empno and empname

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

Powered by k2schools