SSL Connection Error: SSL is required but the server doesn’t support it.

When users using connecting MYSQL using MySQL Workbench they received following error

By default MySQL Workbench, use SSL and users may receive above error. It is recommended to use certificates to avoid above error. In case, if you do not have the certificates then you can follow below steps to skip the certificate and connect to the MySQL Database

 



Option 1:

• Open MySQL workbench
• Right click on MySQL instance and select "Edit Connection"
• Go to the "SSL" tab under Connection Method
• Choose "If Available" instead of "Required" in the drop-down for the "Use SSL"
• Click the "Test Connection" and you should connect without errors.

Option 2:

• 
  
• Right click on MySQL instance and select "Edit Connection"
• Go to “Advanced” tab and enter "useSSL=0" in the 'Others' tab
• Click the "Test Connection" and you should connect without errors

Option 3:

 MySQL Workbench version 8.0.26 and below doesn’t use SSL Options by default

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Failed to connect to MySQL at with user root

When users connecting to MySQL using Workbench or from command prompt they may receive the error when they don’t have access to connect from the machine.

It is mainly due to security setup that allows to connect to “root” from specific IP address or localhost



In this scenario, ask your admins to create a new user account and setup the required access instead of using “root”

If the root account is not configure to authenticate with a password, you may need below command

MySQL> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';


If user need to login as root, you they can follow the steps to grant access to local host and or IP Address

MySQL> GRANT ALL ON [DB_Name].* to 'root'@'127.0.0.1' IDENTIFIED BY '[PASSWORD]';
MySQL> GRANT ALL ON [DB_Name].* to 'root'@ ‘IP Address’ IDENTIFIED BY '[PASSWORD]';

Instead of using “GRANT ALL ON”, use required privileges only.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Jumpstart Your Success with Oracle MySQL Database Service

MySQL is the preferred database of developers to build applications. It is easy to use, highly scalable, and cost-effective. Which is why MySQL Database Service is the strategic provider of our customer’s technology infrastructure.
MySQL invites you to join this virtual session as we run through the steps, and demonstrate how easy it is, to set-up your MySQL Cloud instance before launching your applications. Acquire the knowledge you need to easily:

• Provision your MySQL Cloud instance
• Save time by automating time-consuming tasks, such as patching and backup
• Lower operation costs by trying the only managed database service which is 3.6 less expensive than Amazon RDS

This step-by-step introduction will establish the steps to get started on MySQL Database Service and get your questions answered by MySQL experts. Save your spot today and register!

Don't miss the opportunity and Register Today using below
https://go.oracle.com/LP=102674?elqCampaignId=275257

Thanks & Regards

OracleRACExpert.com

Convert the database from MyISAM to innoDB

When we are trying to use application tool to convert MySQL DB engine from MyISAM to innoDB it failed and received below error message.

Error: Pre-conversion step failed: unable to restart database: exit status 5

So I have tried converting manually using below steps.

1. Before you convert make sure you stop the application or web proxy

2. Run below command to generate a “alter table” command to convert to InnoDB engine.

mysql -u root -p -N -e "select concat('alter table ', table_name, ' engine =InnoDB;') from information_schema.tables where table_schema = 'TimeDB'" > TimeDB_innodb.sql

3. Run below command to start conversion processes. Note that the below command will run sequentially and may take some time.

mysql -u root -p TimeDB < TimeDB_innodb.sql

4. After the conversion completed bring up the application or web proxy

Thanks & Regards,
Satishbabu G, Oracle ACE
http://www.oracleracexpert.com

ERROR 1827 (HY000): The password hash doesn't have the expected format

When creating a user with Grant option we have received below error message

ERROR 1827 (HY000): The password hash doesn't have the expected format
That means you have to use hash password. Here is the simple solution that you can use

Enter the password that you want to set and you will get HASH password.
mysql> select password(‘enter password you want');
+-------------------------------------------+
| password(‘enter password you want') |
+-------------------------------------------+
| *B535BN128KK03E74BE2AC0EE23D07ABX6AD8165E |
+-------------------------------------------+
1 row in set (0.00 sec)

Use the has password while creating the user

mysql> GRANT ALL ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*B535BN128KK03E74BE2AC0EE23D07ABX6AD8165E ' WITH GRANT OPTION;

Query OK, 0 rows affected (0.01 sec)

Thanks
Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

MySQL: Too many connection errors.

When user trying to connect MySQL Database, he got below error

ERROR 1040 (hy000): Too many connections
By looking the error we can see that the max_connections got exhausted. You can check the connection info by running below commands.

-- To find max_connections value run below command
SHOW VARIABLES LIKE '%max_connections%';

-- To see the all processes and connections run below command
SHOW FULL PROCESSLIST;

Note that when you get this error you will be able to login using root. Becoz My SQL by default will consider maximum allowed connections as MAX_CONNECTIONS +1 for super user.

But if you have used root to connect to any other app you will not able to connect to run the above commands. That means you should use root for only Administration purpose only.

You can run below command to change the MAX_CONNECTIONS

SET GLOBAL max_connections = 200;
But note that you need to find out is there any application issue which is creating more connections and causing this issue and fix that later.

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

MySQL thread_stack overrun error

When user working with trigger code in MySQL he receives below error.

Thread stack overrun: 8304 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.


I see that MYSQL have stack value of 128K, which seems to be less and receiving the error

mysql> show variables where `Variable_name` = 'thread_stack';
+---------------+--------+
| Variable_name | Value |

+---------------+--------+

| thread_stack | 131072 |

+---------------+--------+

We have updated 'thread_stack' in my.cnf to 256k and restart the MySQL and everything working started working fine.

Thanks
Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

InnoDB: Unable to lock ./ib_logfile0, error: 11

One of our MySQL servers got restarted and after restart MySQL instance was not coming online.

I see below error message in mysqld.log

InnoDB: Unable to lock ./ib_logfile0, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Error in opening ./ib_logfile0
160712 19:16:34 [ERROR] Plugin 'InnoDB' init function returned error.
160712 19:16:34 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
160712 19:16:34 [ERROR] Unknown/unsupported storage engine: InnoDB
160712 19:16:34 [ERROR] Aborting
160712 19:16:34 [Note] /u01/mysql/5.5.22/bin/mysqld: Shutdown complete
I receive below error message when trying to access the database from command line

# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/u01/mysql/5.5.22/mysqld.sock' (2) 

It looks like the sudden server crash didn’t update the logfile or datafile header properly and not able to lock to bring the instance up.

After research I was able to fix this error by following below steps

1. Go to the data directory and backup the ./ib_logfile0 and copy a new one
$ mv ib_logfile0 ib_logfile0.bak
$ cp -a ib_logfile0.bak ib_logfile0

2. Restart the mySQL database

Thanks,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Are you attending Oracle OpenWorld?

Oracle OpenWorld presents the world's largest, most in-depth selection of sessions by Oracle experts, customers, and partners over a course of five days.

Oracle OpenWorld 2015- October 25- 29, 2015

In Oracle Open World, you are sure to discover new ways to optimize your systems, new solutions that will help you take your business to the next level and new practices that will make you more successful in your job and in your industry.

At Oracle Open World you are going to get -
• 2500+ Technology sessions
• Labs & demos
• Exhibitors and partners
• Opportunities to meet with industry experts
• Fun at the Welcome Reception and Appreciation event at the end.

And lot more…!

Click here for Oracle Open World Agenda

Exhibit, Sell, Win – At Oracle Open World. This is the great opportunity to launch and demo your new products and solutions, meet people face to face with prospects. Follow the Three easy steps to Exhibit.

Click here to lean “How to Exhibit: Three Easy Steps”

Learn at Oracle OpenWorld
Oracle Open World provides more educational and networking opportunities than any other Oracle conference. Last year Oracle OpenWorld attracted 60,000+ visitors and this year you can expect more.

Whether you are an experienced developer or DBA, a novice technologist, or business manager or executive, you'll find how-tos, overviews, best-practice sessions, and customer panels targeted to your job role and level of expertise at Oracle OpenWorld.

Oracle Open World Streams and Tracks
• Applications
• Database
• Engineered Systems
• Enterprise Manager
• Industries
• Middleware
• MySQL
• Operating Systems
• Servers and Storage
• Support and Services
• Virtualization

Let Your Manager Know Why You Should Attend
Oracle OpenWorld offers thousands of sessions geared toward helping Oracle users get the most out of their IT infrastructure and investments—and helping them make the most cost-effective and strategic IT decisions for the future.

Oracle OpenWorld is also a great showcase for the latest developments in cloud, big data, security, governance, data center optimization, and other issues that we’re dealing with. Through presentations and hands-on sessions the conference offers hundreds of opportunities to learn money- and time-saving techniques.

Nowhere else you could get the detailed best practices learned by other companies facing challenges similar to yours. You can get advice directly from the people who know the technology best, and can participate in question-and-answer sessions and other roundtable meetings—getting access to high-value information from experts, peers, partners, and industry leaders.

If you still need some help convincing your manager to let you attend Oracle OpenWorld, Download a Customizable Justification Letter.

There are many packages in Oracle OpenWorld 2015 and see which one best suite your needs. Note that Early Bird and Group Registrations receive discounts.

Here is the available packages info.
https://www.oracle.com/openworld/register/index.html

Please see below link to compare benefits
https://www.oracle.com/openworld/register/compare.html#compare

Some of the sessions will be covering in Oracle Open world are:
• Technology- Oracle’s complete technology portfolio
• Applications - Enablement offerings, initiatives and service development options
• Engineered Systems - Ways to transform IT into more cost effective operation
• Industry - Discover ways to successfully position and deliver solutions to clients
• Cloud - Learn how to go to market and implement innovative cloud solutions

Click here to View all the sessions.

Become Part of the Experience, Meet up with your peers, find out when and where our leaders and most exciting events will occur, and participate in the discussion.

Click here to Register for Oracle OpenWorld 2015

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Install and Configure MySQL using Generic Unix or Linux Binaries

Oracle provides the Generic binaries in the form of tar files. Using generic binaries you will be able to control the configuration and layout.

The MYSQL compressed tar files have names of the form mysql-version-OS.tar.gz.

Generic Unix/Linux Binaries Installation layout
Directory  Contents of Directory
bin            Client programs and the mysqld server
data          Log files, databases
docs         Manual in Info format
man          Unix manual pages
include     Include (header) files
lib            Libraries
scripts      mysql_install_db scripts
share        Miscellaneous support/sample configuration/Install files, including error messages
sql-bench Benchmarks

Before you install, make sure that there are no previous versions of mysql, unless you want to use older version of MySQL.

Command to find old rpms installed on the system
# rpm -qa | grep -i ^mysql
Command to find old mysql directories/files
# Find . –name *mysql* -print
Command to find mysql server process
# ps –ef | grep mysql

Follow the steps to Install and configure MySQL

1. Create mysql user and group
# groupadd -g 100 mysql
# useradd -u 100 -s ksh -d /u01/mysql -r -g mysql mysql
# chown -R mysql:mysql /u01/mysql

2. Login as Mysql and unpack the distribution
# su – mysql
$ tar zxvf /u01/mysql-5.0.45.tar.gz
Create a soft link to 5.0.45
# ln -s /u01/mysql/mysql-5.0.45 5.0.45

3. Create a profile and set the profile
-----.profile file----
MYSQL_BASE=/u01/mysql; export MYSQL_BASE
MYSQL_HOME=/u01/mysql/5.0.45; export MYSQL_HOME
PATH=$MYSQL_HOME/bin:$PATH ; export PATH
Set the profile
$ . ./.profile

4. Copy sample configuration file and edit the file according to your environment.
$ cp /u01/mysql/5.0.45/support-files/my-small.cnf $MYSQL_HOME/my.cnf
-----my.cnf file----
[mysqld]
datadir=/u01/mysql/data
socket=/u01/mysql/mysql.sock

innodb_data_home_dir=/u01/mysql/innodata
innodb_data_file_path=ibdata1:10M:autoextend:max:3000M
innodb_log_group_home_dir=/u01/mysql/innologs
user=mysql

[mysqld_safe]
log-error=/u01/mysql/mysqld.log
pid-file=/u01/mysql/mysqld.pid
[client]
socket=/u01/mysql/mysql.sock

5. Create mysql database files for mysql server.
$ scripts/mysql_install_db --datadir=/u01/mysql/5.0.45/data --basedir=$MYSQL_HOME

This command creates the default database schemas

6. Start the MySQL Server
$ mysqld_safe --defaults-file=$MYSQL_HOME/my.cnf &

Here we have placed the my.cnf under MYSQL_HOME. You can copy the configuration file to any location and use the path while starting the mysql server.

7. Verify the Mysql process and logs
$ ps –ef  | grep mysql

Verify the error log (log-error=/u01/mysql/mysqld.log) under the path that you mentioned in the my.cnf

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

Backup and Restore MySQL Database

The mysqldump is a backup program used to dump a database or collection of database for a backup
Syntax: Table level
$ mysqldump [options] db_name [tbble_name ...]
Database level
$ mysqldump [options] --databases database_name ...
All Databases
$ mysqldump [options] --all-databases

The mysqlimport is used to import the data into database.
Syntax:
$mysqlimport [options] db_name dumpfile1 [dumpfile2 ...]

You can load the dumpfile into server using mysql command.

Backup Mysql Database using mysqldump1. Take a backup of single database on the same server
$mysqldump -u user_name -d database_name –p > database.sql

2. Backup single database from remote server
$ mysqldump -u user_name -h host_name -d database_name –p > database.sql

3. If you are running more than one MYSQL instance on different ports them you need to mention the port to backup database
$ mysqldump -u user_name -h host_name -P port -d database_name –p > database.sql

4. Backup a table from a database
$ mysqldump -u username database_name table_name > table.sql

5. Backup all databases on the server as ROOT
$ mysqldump -u root --all-databases > all_databases.sql

Restore MySQL Database using “mysql” command
If you are moving the database from one server to another server or remotely then use below commands

1. Restore a database on the same server
$mysql -u user_name database_name –p < database.sql
Or
$ mysql -e "source /tmp/database.sql " database_name

2. Restore database from remote server
$ mysql -u user_name -h host_name database_name –p < database.sql

3. If you are running more than one MYSQL instance on different ports them you need to mention the port of Instance to restore
$ mysql -u user_name -h host_name -P port database_name –p < database.sql

4. Restore a table from a database
$ mysql -u username database_name < table.sql

5. Restore all databases from the dump as ROOT
$ mysql -u root < all_databases.sql

Restore database using mysqlimportThe database should exist in order to import dump using mysqlimport.
$ mysqlimport -u user_name -p password database_name /tmp/dumpfile.sql

Write MySQL table data to a file

Use below syntax to write table data/contents to a file
mysql> SELECT * FROM . ORDER BY DESC LIMIT 0,99000 INTO OUTFILE '/tmp/table_name.txt'

If you don’t have access to write from MySQL to a file then you will receive below error.
ERROR 1045 (28000): Access denied for user 'xxxxxxx'@'%' (using password: YES)

If you dont have permission the use below alternate option

$ echo "SELECT * FROM database_name.table_name" mysql -u -h --port= --password= > /tmp/table_name.txt

Rename Mysql Database

Either you can backup and restore the database into new database or follow below steps
mysql>create database new_database;
$ mysqldump -u user_name -d old_database -p | mysql -u user_name -d new_database -p
mysql> drop database old_database

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

How to install and configure MySQL Database

Linux supports a number of different solutions for installing MySQL.
1. Install MySQL using RPM Package
2. Install MySQL using Native package manager
3. Install MySQL using Generic Binaries

If you are new to MySQL then i would recommend using RPM, otherwise install manually using Generic Binaries. The recommended way installing MySQL on PRM-based(RPM VsNative) Linux distribution is using RPM package.

Install MySQL using RPM PackageUse below command to see all files in an RPM package
$ rpm -qpl MySQL-server-VERSION.linux2.6.x86_64.rpm

Run the below command to install
$ rpm -i MySQL-server-VERSION.linux2.6.x86_64.rpm

The RPM installation creates a login account named mysql.

By default the RPM places the data under /var/lib/mysql directory and creates the appropriate entries in /etc/ini.d to start the server automatically at boot time.

MySQL Installation Layout for Linux RPM
/usr/bin - Client programs and scripts
/usr/sbin - The mysqld server
/var/lib/mysql - Log files, databases
/usr/share/info - Manual in Info format
/usr/share/man - Unix manual pages
/usr/include/mysql - Include (header) files
/usr/lib/mysql - Libraries
/usr/share/mysql - Miscellaneous support files, including error messages, character set files, sample configuration files, SQL for database installation
/usr/share/sql-bench – Benchmarks

Install My SQL using Native package managerThe native package installation will automatically take care of downloading and installing the software. Also it will take care of dependencies for MySQL Installation.

Use yum command to install MySQL.
# yum install mysql mysql-server mysql-libs mysql-server

An init script will be installed into /etc/init.d/mysqld, this script is used to stop and start the server. A configuration file will be created under /etc/mysql/my.cnf

Use below commands to Stop and Start MySQL service
# service mysql start
# service mysql stop

If you want to start MySQL server automatically during the boot run below command.
# chkconfig --levels 235 mysqld on

Here are some popular MySQL Download links

Download MySQL Community Serverhttp://dev.mysql.com/downloads/mysql/
Download MySQL Clusterhttp://dev.mysql.com/downloads/cluster/
Download MySQL Workbench GUI Toolhttp://dev.mysql.com/downloads/workbench/5.2.html
Download MySQL Proxyhttp://dev.mysql.com/downloads/mysql-proxy/
Download MySQL Connectorshttp://dev.mysql.com/downloads/connector/

Regards
Satishbabu Gunukulahttp://www.oracleracexpert.com/

Error 1130: #HY000 Host ‘hostname’ is not allowed to connect to this MySQL server

The below are the most common errors, when connecting remotely to MySQL Server from MySQL Client.

Error 1130: #HY000 Host ‘hostname’ is not allowed to connect to this MySQL server

Cause: The host that you are using to connect MySQL Server does not have privilege

Solution: Grant required privileges
GRANT ON *.* TO ‘user’@'hostname' IDENTIFIED BY ‘password’ ;
For ex:- if you are connecting as root then use below syntax
Mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@’test-server’ IDENTIFIED BY ‘test’ ;

Now you should able to see a new user in mysql.user table.
Mysql> select host,user from mysql.user;
+------+---------------------------
| User | Host
+------+---------------------------
| root | test-server
+------+---------------------------

If you want to grant privilege to a host on specific database try below syntax.
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@’test-server’ IDENTIFIED BY ‘test’ ;

Error 1130: #HY000 Host ‘IP address’ is not allowed to connect to this MySQL server

Cause: The user host IP address is not allowed to connect to MySQL Server

Solution: Grant the privilege to host IP address
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘user’@’IPADDRESS’ IDENTIFIED BY ‘password’ ;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@’x.x.x.x’ IDENTIFIED BY ‘test’ ;

ERROR 1045 (28000): Access denied for user 'root'@hostname' (using password: NO)

Cause: The root user is password protected and you need to use password parameter in order to connect

Solution: - Try to connect MySQL Server using below command
# mysql -u root -h test-server –p

ERROR 1045 (28000): Access denied for user 'root'@hostname' (using password: YES)

Cause: The 'root'@hostname' doesn’t have privilege to connect MySQL Server

Solution: - Grant permission using below command.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@hostname' IDENTIFIED BY ‘password’

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

I was working on MySQL Server setup and modified the default location of MySQL datadir. After I restart the MySQL, I was unable to connect and receiving below error.

#mysql –u root
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

Cause:
When we restart mysql server, it creates a mysql.sock file under socket variable path. The default location is /var/lib/mysql/mysql.sock.

I have updated the new location in /etc/my.cnf file.

[mysqld]
datadir=/opt/mysql/data
socket=/opt/mysql/mysql.sock

I can see the new file under /opt/mysql/mysql.sock, but I still receive the same error and not able to connect to MySQL.

I have modified the socket path under [mysqld], but the parameter was missing under [client] section. When I was trying to connect using MySQL client, it could not able to find mysql.sock file.

Solution:
Add [client] section in /etc/my.cnf

[client]
socket=/opt/mysql/mysql.sock

I have restarted MySQL in order to take effect of new changes in my.cnf

# service mysql stop
# service mysql start

MySQL Client able to find mysql.sock file and I was able to connect.

[root@iwebsql etc]# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If you don’t want to restart mysql then use below command to tell client to use /opt/mysql/mysql.sock file

#mysql -u root –socket=/opt/mysql/mysql.sock

You may receive the same error, if mysqld didn't start. You can try below solution.

# /etc/init.d/mysqld start
# mysql –u root

Regards
Satishbabu Gunukula
http://www.oracleracexpert.com