Posts tagged MySQL

How to change the root password on MySQL

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;

How to delete all rows in a MySQL or Oracle table

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.

How to dump all MySQL databases to a gzip file from inside a bash script

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

How to dump a MySQL database

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
more...

How to open the MySQL console

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
more...