Archives for November 2020

November 30, 2020

MySQL Read Only

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:

Make the server read-only, so that it processes only retrievals and blocks updates.
You can then perform the backup.
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.

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"

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

November 29, 2020

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;