Uly.me

cloud engineer

mysql

MySQL Select Like

by ·

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';

MySQL Read Only

by ·

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;

Back to normal mode.

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.

Running MySQL commands from the Terminal

by ·

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"

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"

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

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

Change MySQL User Password

by ·

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';

Flush privileges for changes to take effect.

flush privileges;

WordPress Read Only

by ·

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;

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');

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.

MySQL Read Only

by ·

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

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

It’s very useful if creating a replica database.

MySQL Restore to another DB

by ·

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

MySQL SSL Connection

by ·

The standard way to connect to MySQL is:

mysql -h hostname -u user -p

Here’s how to connect to MySQL with SSL encryption.

mysql -h hostname -u user -p \
--ssl-ca=server-ca.pem \
--ssl-cert=client-cert.pem \
--ssl-key=client-key.pem

Generate the SSL keys from the MySQL server. Download it to the client.

MySQL Restore

by ·

Here’s how to restore a MySQL database from mysqldump.

mysql -u user -p
mysql> drop database databasename;
mysql> quit;
Bye
mysql -u user -p databasename < filename.sql

Drop database first, then import the SQL file.

MySQL Backup To S3 Bucket

by ·

Here’s my MySQL backup script to the S3 Bucket.

Just a couple of things about the script. It’s using …

  1. AWS CLI
  2. Mysqldump

They must be setup and configured to work properly.

#!/bin/bash
cd /root/database
TIMESTAMP=$(date +%Y-%m-%d)
S3FILE="s3://bucketname/sqlbackup/backup-$TIMESTAMP.sql"
/usr/bin/mysqldump dbname > dbname.sql
/usr/local/bin/aws s3 cp dbname.sql $S3FILE
sleep 3s
rm dbname.sql

Finally, set the S3 bucket with a 7 day retention. Backups older than 7 days are automatically deleted.