Sometimes the simplest sysadmin tasks are the hardest to find straightforward answers to. Recently, I spent too much time trying to change the root password on a fresh mysql install.

Changing the root password for MySQL has security implications; there is no shortage of paranoid nerds describing how the world might end if you do what I’m about to do. If you understand the security implications of setting the root MySQL password, and still want to do it anyway, here’s how:

mysql -uroot

And, once logged in to the mysql console:

UPDATE mysql.user SET Password=PASSWORD('the password goes here') WHERE User='root';
FLUSH PRIVILEGES;

There are two methods:

DELETE FROM table_name

or

TRUNCATE TABLE table_name

The latter is more efficient, because it does not reference each row before deleting; it is a bulk operation. The former is expanded into DELETE * FROM table_name, which works on one row at a time. Therefore, use the latter.

PostgreSQL.

(Updated for 2015.)

To start MySQL on OS X:

mysql.server start

To stop MySQL on OS X:

mysql.server stop

Often as part of an hourly, daily, or weekly server backup bash script, you will wish to dump all MySQL databases to disk as a gzip file. This way, in the case that any of your databases suffer from corruption or user error, you will have a backup.

Simply put this command inside your cron-scheduled backup bash script, substituting the USERNAME, PASSWORD, and the destination path with your info:

#!/bin/bash
mysqldump -u USERNAME -pPASSWORD --all-databases --routines| gzip > /mysqlbackups/MySQLDB_`date '+%m-%d-%Y'`.sql.gz

There are many cases where you will want to dump a MySQL database. Examples include: backing up a website, migrating a website to a new server, moving from a development machine to a production server, and more.

Simply run this command, substituting the USERNAME, DATABASE_NAME, and FILENAME with your info. When you run this command, it will prompt you for the MySQL password for the USERNAME you provided:

mysqldump -u USERNAME -p DATABASE_NAME > FILENAME.sql

First, create a new, empty MySQL database from the MySQL console:

create database DATABASE_NAME;

Then, import the .sql file into it:

mysql -u root -p -h localhost DATABASE_NAME < FILE_TO_IMPORT.sql

The MySQL console allows you to directly execute SQL commands, without having to use a programming language adapter such as PHP, Ruby, or Python.

Run this command, replacing USERNAME with your MySQL username; you will be prompted for the password.

mysql -u USERNAME -p -h localhost