• Skip to main content

Uly.me

cloud engineer

  • Home
  • About
  • Search

mysql

Run MySQL in a Docker container

December 12, 2021

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

#!/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.

Filed Under: Cloud, Linux Tagged With: database, docker, environment, exec, mysql, password, select, tables

Database Clients on Linux

November 4, 2021

You can run database clients on your Linux distro by installing them.

MySQL

$ yum install mysql
$ apt install mysql

$ yum install mysql $ apt install mysql

Postgress

$ yum install postgresql postgresql-client
$ apt install postgresql postgresql-client

$ yum install postgresql postgresql-client $ apt install postgresql postgresql-client

MSSQL

$ yum install mysql-tools
$ apt install mssql-tools

$ yum install mysql-tools $ apt install mssql-tools

Filed Under: Linux Tagged With: apt, client, install, mssql, mysql, postgres, yum

MySQL Select Like

December 27, 2020

Here’s how to perform SQL searches using the like operator.

# Format
SELECT column1, column2 FROM table_name WHERE column LIKE pattern;
# Search for an entry starting with a 'joe.'
SELECT id,username,address FROM users WHERE username LIKE 'joe%';
# Search for an entry ending with a 'joe.' 
SELECT id,username,address FROM users WHERE username LIKE '%joe';
# Search for any entry with 'joe' from any position. 
SELECT id,username,address FROM users WHERE username LIKE '%joe%';
# Finally, using "_" as wildcards. Find any field with "j" in the second position.
SELECT id,username,address FROM users WHERE username LIKE '_j';

# Format SELECT column1, column2 FROM table_name WHERE column LIKE pattern; # Search for an entry starting with a 'joe.' SELECT id,username,address FROM users WHERE username LIKE 'joe%'; # Search for an entry ending with a 'joe.' SELECT id,username,address FROM users WHERE username LIKE '%joe'; # Search for any entry with 'joe' from any position. SELECT id,username,address FROM users WHERE username LIKE '%joe%'; # Finally, using "_" as wildcards. Find any field with "j" in the second position. SELECT id,username,address FROM users WHERE username LIKE '_j';

Filed Under: Linux, Misc Tagged With: like, mysql, pattern, search

MySQL Read Only

November 30, 2020

If you need to perform backup or replicate a database, you can lock up the database by doing a global read block to make it read-only.

The process is:

  1. Make the server read-only, so that it processes only retrievals and blocks updates.
  2. You can then perform the backup.
  3. Change the server back to its normal read/write state.

Read only.

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON;

Back to normal mode.

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

SET GLOBAL read_only = OFF; UNLOCK TABLES;

You can run these MySQL commands within MySQL or via a bash terminal. Check out my previous post.

Filed Under: Cloud, Linux Tagged With: global, mysql, read-only

Running MySQL commands from the Terminal

November 29, 2020

You can run MySQL commands from the terminal by using -e switch. Here are a few examples.

mysql -u username -p -e "create database mydb"
mysql -u username -p -e "use mydb"
mysql -u username -p database -e "select * from mytable"

mysql -u username -p -e "create database mydb" mysql -u username -p -e "use mydb" mysql -u username -p database -e "select * from mytable"

If you have .mycnf configured, you can omit the username and password.

mysql -e "create database mydb"
mysql -e "use mydb"
mysql -e "select * from mytable"

mysql -e "create database mydb" mysql -e "use mydb" mysql -e "select * from mytable"

To run multiple commands from a single line, separate the commands using a semicolon.

mysql -e "create database somedb; use mydb; select * from mytable;"

mysql -e "create database somedb; use mydb; select * from mytable;"

Filed Under: Linux Tagged With: commands, mysql, run, terminal

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 5
  • Go to Next Page »
  • Home
  • About
  • Search

Copyright © 2023