MySQL

Basic format

Will display in the format like

This script will backup each table in the given database into its own file.

So you want to run a select statement from the command line - use the -e switch

I was listening once to two programmers talk about MySQL. Both had taken the job in smaller business to get more "hands on" and "big picture" to developing high end web sites. They had both worked on big sites before - but only on a very small component. They where talking about MySQL and referred to it as being a Relational Model Database (RMDBS). It is not!

Well, not in it normal form. And definitely not in they we had implemented it.

To be a Relational Model Database, the database must enforce relations - referential integrity. Obvious really.

Get Size of Tables in a single MySQL Database

For a given "schema_name" (aka database)

I need to see every query that is being run on my MySQL database to make sure an attached device is working.

Edit the MySQL Configuration file

sudo vim /etc/mysql/my.cnf

And add the following line

log = /var/log/mysql/mysql.log

Sometimes this is already on the fie and commented out. Now restart MySQL

sudo service mysql restart

or

sudo /etc/init.d/mysql restart.

You can view the SQL commands as they come in live with this command;

Here is a simple script that backs up all accessible databases and sends them to an S3 bucket with the date as a prefix.

If your need to change the password of an existing MySQL user, follow these steps;

Log into MySQL

mysql -uroot

connect to the mysql table

use mysql;

change the password for the user (eg tom)

update user set password=PASSWORD("NEW-PASSWORD-HERE") where User='tom';

You can see what permissions a user has using this command

show grants for "tom";

or create a new user with