In our previously published guide, we have shown the steps to install WordPress on IBM BlueMix. Of course we have shown basic example commands to check MySQL database from remote server. In this guide, we will discuss how you’ll be accessing Bluemix MySQL database from external server with root access. On CLI you need MySQL Client to work on MySQL.Definitely you can use localhost like Mac, GNU/Linux. We are not used with Microsoft Windows around sysadmin works – you should consult to their latest official guide for bash, we only can point towards cygwin for Microsoft Windows.
Accessing Bluemix MySQL Database From External Server
In our WordPress installation on IBM Bluemix guide, we have directed to select Compose for MySQL on Bluemix while creating MySQL database. At this moment, Bluemix has another flavour of MySQL which is managed by Cleardb. There are some differences between two options. You’ll get an IP with Cleardb MySQL instead of fully qualified domain name (FQDN) in case of Compose for MySQL. In this example, we are using Compose for MySQL.
If you click on the View credentials button of MySQL service box, where you’ll see a dialog with the service configuration and credentials in JSON format. Copy-paste and save it somewhere. It will have lines like :
---
1 2 | "uri_cli": "mysql -u admin -p --host sl-aus-syd-1-portal.1.dblayer.com --port 18006 --ssl-mode=REQUIRED", "uri_direct_1": "mysql://admin:ABCDEFGHIJK@sl-aus-syd-1-portal.2.dblayer.com:18006/compose", |
From the above example, our command to login to Bluemix hosted MySQL database from external server’s SSH is :
1 | mysql -u admin -p --host sl-aus-syd-1-portal.1.dblayer.com --port 18006 |
Password in the above case is ABCDEFGHIJ. You’ll receive usual MySQL server response :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | root@dotpro:~# mysql -u admin -p --host sl-aus-syd-1-portal.1.dblayer.com --port 18006 Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 6081 Server version: 5.7.19-log MySQL Community Server (GPL) Copyright (c) 2009-2017 Percona LLC and/or its affiliates Copyright (c) 2000, 2017, 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> |
Commands For MySQL Database Server
It is beyond scope of this article to discuss about MySQL server commands. All commands are available on official documentation of MySQL website and in our many older guides we talked about how to backup MySQL database, how to use cloud database etc. Obviously commands are same. We are providing just few basic examples in this guide.
If you run show databases; command you’ll get this kind of output :
1 2 3 4 5 6 7 8 9 10 11 | mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | compose | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.28 sec) |
To navigate inside the database named compose in the above example, we will run use compose; command. Which will return this confirmatory output :
1 2 3 4 5 | mysql> use compose; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed |
To view the tables of the database, we will run show tables; command :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> show tables; +-----------------------+ | Tables_in_compose | +-----------------------+ | wp_commentmeta | | wp_comments | | wp_links | | wp_options | | wp_postmeta | | wp_posts | | wp_term_relationships | | wp_term_taxonomy | | wp_termmeta | | wp_terms | | wp_usermeta | | wp_users | +-----------------------+ 12 rows in set (0.28 sec) |
We can take backup (create MySQL dump), restore from backup etc.
If you need to test database connection with PHP script, you can use this simple script :
1 | https://gist.github.com/chales/11359952 |
Obviously complex and secure method exists for on-premise :
1 | https://github.com/IBM-Bluemix/onprem-integration-demo |
