MySQL

Mysql Tuning

Download, run and do what it says. ( Mostly )

wget  http://mysqltuner.com/mysqltuner.pl

Create a Database and create a user for it

These are pretty simple commands and I use these frequently enough that I don't need to place them here. These are available for new admins to reference until they are seeing these in their sleep too.

Create a Database:

create database <database_name>;

Create a user that can connect from any hostname:

use database_name;
create user 'username'@'%' identified by 'password';

Create a user that can connect only from the specified hostname:

create user 'username'@'hostname' identified by 'password';

Grant all permissions ( except grant )

grant all privileges on database_name.* to 'username'@'%' identified by 'password';
flush privileges;

Dump / Backup a Mysql Table

This will dump the database specified in place of $1, and place it in a file specified in $2. This can be placed in an executable bash script and run as a cron job to backup automatically. Combine with date options for $2 and you will have complete iterative dumps so you can restore to a specific date or time.

mysqldump -h<host> -u<user> -p<pass> $1 --add-drop-table --complete-insert  --disable-keys --insert-ignore > $2

This will dump one database directly into another with no file in between. Don't overwrite the source DB at the Destination.

If the DB is on the localhost you can omit -h<host> after mysqldump.

mysqldump -h<host> -u<user> -p<pass> $Source_DB --add-drop-table --complete-insert  --disable-keys --insert-ignore | mysql -h<host -u<user> -p<pass> $Destination_DB

Repair MySQL master - slave replication

This is the full step-by-step procedure to resync a master-slave replication from scratch:

At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
And copy the values of the result of the last command somewhere.

Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump -uroot -p --all-database --flush-logs --delete-master-logs > /a/path/mysqldump.sql

Now you can release the lock, even if the dump hasn't end. To do it perform the following command in the mysql client:

UNLOCK TABLES;

Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Open a connection to mysql and type:

STOP SLAVE;

Load master's data dump with this console command:

mysql -uroot -p < mysqldump.sql

Sync slave and master logs:

You saved some data above: "And copy the values of the result of the last command somewhere." Use it below for the log file, and position.

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='<log file>', MASTER_LOG_POS=<position>;

Where the values of the above fields are the ones you copied before.

Finally type

START SLAVE;

And to check that everything is working again, if you type

SHOW SLAVE STATUS;

you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Borrowed from ServerFault.