Uly.me

cloud engineer

database

Run MySQL in a Docker container

How to run MySQL commands in a Docker container.

#!/bin/bash
set -a; source <(cat .env | sed -e '/^#/d;/^\s*$/d' -e "s/'/'\\\''/g" -e "s/=\(.*\)/='\1'/g"); set +a
docker exec wp_db mysql -uroot -p${MYSQL_PASSWORD} -e " \
use db1; \
select * from wp_options where option_name='siteurl'; \
select * from wp_options where option_name='home';" 2>/dev/null
  • The first command loads the content of .env to environment variables.
  • The MySQL password can then be used using the ${MYSQL_PASSWORD} variable.
  • I’m using sed to get around the special characters in the password.
  • The second command runs docker exec on wp_db container.
  • The last 3 commands are the actual sql commands.
  • We are selecting to use the db1 database.
  • Then we run select statements from the wp_options table.
  • Finally, 2>/dev/null suppresses errors and warning to null.

MySQL Restore to another DB

In order to restore a MySQL database to another database, use routines and triggers.

mysqldump -p user -p --routines --triggers db1 > db1.sql

To restore to another database, just use the normal command.

mysql -u user -p db2 < db1.sql

Yum RPMDB failed

If you are getting an error similar to the yum error below, yum may be broken in your system.

error: rpmdb: BDB0113 Thread/process 2196/139984719730496 failed: BDB1507 Thread died in Berkeley DB library
error: db5 error(-30973) from dbenv-&gt;failchk: BDB0087 DB_RUNRECOVERY: Fatal error, run database recovery
error: cannot open Packages index using db5 - (-30973)
error: cannot open Packages database in /var/lib/rpm
CRITICAL:yum.main:
 
Error: rpmdb open failed

Here’s the fix.

mv /var/lib/rpm/__db* /tmp
yum clean all

Run your yum commands. The errors should be gone.

Check DB Connection on AWS

When setting up your cloud infrastructure, you can check if your instances have access to the database by performing this command.

$ nc -zv 10.0.0.45 3306
$ nc -zv domain.com 3306
$ nc -zv endpoint.amazonaws.com 3306

If connection has succeeded, you’ll get a message like this in Linux and MacOS …

Connection to endpoint.amazonaws.com 3306 port [tcp/mysql] succeeded!

On Windows, you can use Telnet to test your DB connection.

C:\>telnet endpoint.amazonaws.com 3306

If connection has succeeded, the result is “no message.” If there’s a problem, you’ll get this ….

Connecting To endpoint.amazonaws.com...Could not open 
connection to the host, on port 3306: Connect failed

Install MariaDB on Ubuntu

MariaDB is a drop-in replacement for MySQL database. On most cases, you can just uninstall MySQL and install MariaDB and you are good to go. To keep up the two databases compatible, the MariaDB team are doing monthly merges with the MySQL code base making sure new features and fixes are kept up.

Uninstall MySQL

sudo apt-get purge mysql*
sudo apt-get autoremove

Install MariaDB

sudo apt-get install mariadb-server mariadb-client -y

Verify if MariaDB is running

sudo service mysql status