The easiest method is to query the INFORMATION_SCHEMA. For Example: SELECT ENGINE, COUNT(*), SUM(DATA_LENGTH), SUM(INDEX_LENGTH) FROM information_schema.TABLES WHERE TABLE_SCHEMA='menagerie' GROUP BY ENGINE; Additionally here is a potentially faster example to estimate the db size (InnoDB only and if you do not use compressed tables) since it doesn't use INFORMATION_SCHEMA.TABLES : select sum( CLUST_INDEX_SIZE + OTHER_INDEX_SIZE)* @@innodb_page_size / 1024 / 1024 "Estimated size in Mb" … [Read more...] about How to find the size of a MySQL database
The post provides an overview of what features the MySQL Enterprise Monitor provides and how it can be used to improve MySQL administration and performance. Provided as part of MySQL Enterprise, the MySQL Enterprise Monitor is a Virtual DBA assistant that helps MySQL DBAs manage more MySQL servers, tune MySQL servers, and find and fix problems with MySQL database applications before they can become serious problems or costly outages. Running completely within the corporate firewall, the … [Read more...] about What is the MySQL Enterprise Monitor?
Problem When trying to start MySQL using systemctl fails to start, for example: # systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: inactive (dead) Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html # systemctl start mysqld Job for mysqld.service failed because the control process exited with error code. See "systemctl status … [Read more...] about MySQL Fails to Start Using systemctl On systemd Linux Distributions
Problem When a client tries to log into MySQL it may sometimes be rejected and receive an error message saying that there are "too many connections". This means that the maximum number of clients that may be connected to the server has been reached. Either the client will have to wait for another client to log off, or the administrator will have to increase the maximum number of connections allowed. Information about connections to a server can be found using the SHOW STATUS statement: $ … [Read more...] about MySQL Error “Too many connections” and how to resolve it
There are several ways to set a MySQL user password: – CREATE USER...IDENTIFIED BY – GRANT...IDENTIFIED BY – SET PASSWORD – mysqladmin password – UPDATE grant tables (not recommended) For all methods, the privileges required to change a password are: 1. All users can change their own password. 2. Users with UPDATE on mysql database can update passwords for other users using SET PASSWORD or in MySQL 5.7 and later using ALTER USER. 3. If the read_only option is enabled, in addition to … [Read more...] about MySQL : how to set (change) user password
Backing up MySQL database Backup and restoration of MySQL databases play a very important role in a production environment, so here is a simple method to backup a MySQL database. To backup MySQL database(s), the following command can be used: # mysqldump --user=username --password=password --opt DatabaseName > database.sql Note: The above command will only backup the table structure and the data from the existing database. The filename "database.sql" can be changed to suit desired … [Read more...] about How to backup and restore MySQL database
Question: I forgot the MySQL root password. How do I reset it? # mysql -u root Access denied for user 'root'@'localhost' (using password: NO)' Solution: The MySQL root password can be set using the following procedure: 1. Stop the MySQL service: # service mysqld stop Stopping MySQL: [ OK ] 2. Start MySQL in safe mode as it does not ask for root password in safe mode. # /usr/bin/mysqld_safe --skip-grant-tables & Note: mysql_safe is a … [Read more...] about How to reset MySQL database root password
Enabling and Disabling Starting MySQL Server at Boot You can use the chkconfig script to verify whether MySQL has been configured to start when Linux is booted: # chkconfig --list mysql mysql 0:off 1:off 2:on 3:on 4:on 5:on 6:off The numbers 0 through 6 represents the run-levels and on/off signifies whether the service will be automatically started at that run-level. You can configure whether to start MySQL at a specific run level only using the chkconfig … [Read more...] about CentOS / RHEL 6 : How to Start/Stop MySQL Server (mysqld)