DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

How to List MySQL Databases Using SHOW DATABASES

Posted on June 14, 2014 Written by Andy Hayes Leave a Comment

Want a list of MySQL databases? In this quick guide, I demonstrate how to show a list of databases in MySQL using SHOW DATABASES.

First, login as user root to your MySQL instance.

mysql -uroot -pYourPassword

Now that you are logged in, you simply need to run the SHOW DATABASES command.

SHOW DATABASES;

Produces the following list.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

As I am logged in as root, I can see all databases. If you run this as another user, you will only see the databases that the user has access to.

You can also look for specific databases which is useful if your server contains many databases.

SHOW DATABASES LIKE 'mysql';
+------------------+
| Database (mysql) |
+------------------+
| mysql            |
+------------------+
1 row in set (0.00 sec)

Or you can use a wildcard to look for databases containing specific text.

SHOW DATABASES LIKE 'my{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}';
+----------------+
| Database (my{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}) |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

How to Show MySQL databases in MySQL Workbench

There are two ways, those listed above as seen here

mysql show databases

And you can also browse a list of available MySQL schemas but this does not include databases such as mysql or performance_schema

mysql list schemas

Filed Under: All Articles, MySQL Administration Tagged With: mysql

How to Resize MySQL Innodb Log Files Without Errors

Posted on June 10, 2014 Written by Andy Hayes 2 Comments

Upon installing MySQL, the default innodb log file size is 5MB. This might be fine for a lot of servers but should you want to resize it, it is not simply a case of changing innodb_log_file_size to the new size and restarting MySQL.

MySQL will not start and produce error log output similar to the following:

140610 20:09:14 InnoDB: The InnoDB memory heap is disabled
140610 20:09:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140610 20:09:14 InnoDB: Compressed tables use zlib 1.2.3.4
140610 20:09:14 InnoDB: Initializing buffer pool, size = 128.0M
140610 20:09:14 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
140610 20:09:14 [ERROR] Plugin 'InnoDB' init function returned error.
140610 20:09:14 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140610 20:09:14 [ERROR] Unknown/unsupported storage engine: InnoDB
140610 20:09:14 [ERROR] Aborting
140610 20:09:14 [Note] /usr/sbin/mysqld: Shutdown complete

MySQL will not start because the configured value in my.cnf is not the same as the current log file size.

How to safely adjust Innodb log file size

(Example demonstrated on a Ubuntu 12.04LTS instance)

1/ Make sure that innodb_fast_shutdown is NOT configured as 2

You may suffer data loss if this is the case. Change to either 0 or 1. Setting as 0 will take longer to shutdown the server so 1 is preferred. Check out more information on this at this link

SHOW VARIABLES LIKE 'innodb_fast_shutdown'

Change to 1 to perform a fast shutdown

SET GLOBAL innodb_fast_shutdown=1;

2/ Safely stop the MySQL service checking for issues

Stop MySQL and check the log file as defined in my.cnf  by the variable “log_error” for any issues.

example :-

log_error = /var/log/mysql/error.log

You want to see a nice clean shutdown

service mysql stop

Check the log, it should show a good, error free shutdown…

tail -10 /var/log/mysql/error.log

140610 20:19:44 [Note] /usr/sbin/mysqld: Normal shutdown
140610 20:19:44 [Note] Event Scheduler: Purging the queue. 0 events
140610 20:19:44  InnoDB: Starting shutdown...
140610 20:19:44  InnoDB: Shutdown completed; log sequence number 1595685
140610 20:19:44 [Note] /usr/sbin/mysqld: Shutdown complete

3/ Move the existing log files out of your MySQL “datadir” folder

You need to move these, don’t delete them. The innodb log files are found in your “datadir” folder as defined in my.cnf

They are ib_logfile0, ib_logfile1 etc

cd /var/lib/mysql
mv ib_logfile0 ib_logfile1 /home/ubuntu

4/ Change innodb_log_file_size variable in my.cnf and start MySQL

Set the size that you need for your server, I’m setting to 64M in this case.

innodb_log_file_size = 64M

Start MySQL, it should start cleanly without error

service mysql start

5/ Now check your servers log file again

It should look similar to the below where the log will show MySQL creating new log files

140610 20:51:17  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait...
140610 20:51:21  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 64 MB
InnoDB: Database physically writes the file full: wait...
140610 20:51:24 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140610 20:51:24  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140610 20:51:24  InnoDB: Waiting for the background threads to start
140610 20:51:25 InnoDB: 5.5.37 started; log sequence number 1596428

Finally

Check that you can query your innodb tables and then it should be safe to remove the old log files which you backed up earlier.

Filed Under: All Articles, MySQL Administration Tagged With: mysql, ubuntu

How to Install MySQL 5.5 on Ubuntu Server 12.04 LTS

Posted on January 26, 2014 Written by Andy Hayes 4 Comments

For this how to, I am going to document how to install MySQL on an installation of Ubuntu 12.04 LTS

Installing it is a very straight forward and quick process and I prefer to use the terminal. If you are running a GUI, you can also install it through the Ubuntu sofware centre. Typically available with the Ubuntu desktop version.

If you are running the desktop version of Ubuntu, fire up a terminal either by clicking on the icon or by pressing Ctrl + Alt + T

It’s a good idea to update your server package cache before proceeding.

sudo apt-get update

Then, run

sudo apt-get install mysql-server-5.5

Once you have entered the password for sudo access, the installation will proceed. The only thing it will ask you is what to set the root password for MySQL as. You SHOULD set a password up, don’t leave it blank with the intention of doing it later. I would recommend you find a decent password generator too.

Once installation has completed, you just need to check that mysql is running. To do this, run

sudo netstat -tap | grep mysql

If MySQL is running you will see output similar to the following:

tcp  0  0 localhost:mysql  *:*  LISTEN  2480/mysqld

Now test that you can login

mysql -uroot -p

Type in the root password directly after the -p switch without spaces.

You can also view what packages have been installed by typing:

dpkg -l | grep mysql

Which should return output similar to the following:

[email protected]:~$ dpkg -l | grep mysql
ii  libdbd-mysql-perl                4.020-1build2                     Perl5 database interface to the MySQL database
ii  libmysqlclient18                 5.5.35-0ubuntu0.12.04.1           MySQL database client library
ii  mysql-client-5.5                 5.5.35-0ubuntu0.12.04.1           MySQL database client binaries
ii  mysql-client-core-5.5            5.5.35-0ubuntu0.12.04.1           MySQL database core client binaries
ii  mysql-common                     5.5.35-0ubuntu0.12.04.1           MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                     5.5.35-0ubuntu0.12.04.1           MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.5                 5.5.35-0ubuntu0.12.04.1           MySQL database server binaries and system database setup
ii  mysql-server-core-5.5            5.5.35-0ubuntu0.12.04.1           MySQL database server binaries

Job done, you have just installed MySQL 5.5 on Ubuntu 12.04 LTS

Filed Under: All Articles, MySQL Administration Tagged With: mysql, ubuntu

Querying INFORMATION_SCHEMA

Posted on July 14, 2012 Written by Andy Hayes 2 Comments

querying information_schema

This post applies to both MySQL and Microsoft SQL Server as both these products allow you to query something called INFORMATION_SCHEMA.

What is INFORMATION_SCHEMA?

It is database metadata. In MySQL, there is a complete virtual database dedicated to it called “information_schema” and in Microsoft SQL Server, there are views which you can query, all prefixed with INFORMATION_SCHEMA in each database.

You can find a full list of the views by opening up the “System Views” node in Management Studio.

In MySQL, you would run the following to get information on tables in a database:

SELECT * FROM information_schema.tables
WHERE table_schema = 'YourDatabaseName';

In SQL Server, the same would be:

USE YourDatabaseName
SELECT * FROM INFORMATION_SCHEMA.tables;

The metadata is automatically updated by the database engine and it is not possible to update the data within but direct user initiated queries

Why use INFORMATION_SCHEMA?

Some of the things I would use it for would include:

  • Finding out how many rows per table you have in your databases
  • Finding out how large your tables and indexes are
  • Finding out when a stored procedure was last updated
  • Assessing index fragmentation (applies to MySQL, you would use a DMV in SQL Server called sys.dm_db_index_physical_stats or legacy DBCC SHOWCONTIG)
  • Finding out which stored procedures contained certain queries (see below example)

A question I get asked as a DBA sometimes is how can I find a stored procedure containing a certain query. I point the developer at INFORMATION_SCHEMA.ROUTINES and to look in the “routine_definition” field for specific keywords, for example I would use this against SQL Server:

USE YourDatabaseName
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE routine_definition LIKE '{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}keyword{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}';

In MySQL you can get this information too but it is not part of INFORMATION_SCHEMA and instead you have to query the mysql.proc table.

There are other ways to get this information, for example in SQL Server, you can load up the Object Explorer in Management Studio to view information on row counts, space used for data and indexes per table but I prefer writing queries to do this.

I think in part because it is a guilty pleasure of mine to query the server using T-SQL or command line tools in MySQL 🙂 and most importantly because there is more information to be had via this method.

For more information, simply start querying it and you will find a wealth of useful stuff inside. Or you can check out these links for both MySQL information_schema and SQL Server information_schema

Filed Under: All Articles, MySQL Administration Tagged With: mysql, sql server

  • « Previous Page
  • 1
  • 2
  • 3
  • 4
  • 5
  • Next Page »

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close