• Skip to main content

Uly.me

cloud engineer

  • Home
  • About
  • Archives

mysql

Run MySQL in a Docker container

December 12, 2021 by Ulysses

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 by Ulysses

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 by Ulysses

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 by Ulysses

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 by Ulysses

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

Change MySQL User Password

November 29, 2020 by Ulysses

This is a very simple command to run. Login to MySQL first and run the following the command line.

ALTER USER username IDENTIFIED BY 'password';

ALTER USER username IDENTIFIED BY 'password';

Flush privileges for changes to take effect.

flush privileges;

flush privileges;

Filed Under: Linux Tagged With: alter, change, mysql, password, user

WordPress Read Only

November 29, 2020 by Ulysses

Here’s how to create a WordPress site that’s read only. You will not be able to create, update and delete posts.

  1. Login to MySQL or MariaDB.
  2. Choose mysql database.
  3. Create a new user called ‘wpro’ for WordPress read only.
  4. Grant select permissions to all tables in ‘wordpress’ database.
  5. Flush privileges to commit your changes.
mariadb -u root -p
use mysql;
create user 'wpro'@'localhost' identified by 'yourpassword';
grant select on wordpress.* to 'wpro'@'localhost';
flush privileges;

mariadb -u root -p use mysql; create user 'wpro'@'localhost' identified by 'yourpassword'; grant select on wordpress.* to 'wpro'@'localhost'; flush privileges;

In MySQL or MariaDB, you have to terminate all commands with a semicolon.

Now edit your WordPress wp-config.php file. vim /var/www/wordpress/wp-config.php.

/** MySQL database username */
define('DB_USER', 'wpro');
 
/** MySQL database password */
define('DB_PASSWORD', 'yourpassword');

/** MySQL database username */ define('DB_USER', 'wpro'); /** MySQL database password */ define('DB_PASSWORD', 'yourpassword');

After saving the wp-config.php file, everything should work just like before, except that you will not be able to save, publish or delete posts, pages, or add or delete media files to your WordPress site. It’s working as intended.

Filed Under: Linux, WP Tagged With: create, grant, mariadb, mysql, read-only, select, user

MySQL Read Only

November 22, 2020 by Ulysses

Here’s how to make a MySQL database read only.

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

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

It’s very useful if creating a replica database.

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

  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Go to Next Page »
  • Home
  • About
  • Archives

Copyright © 2012–2022