Posts tagged 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
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:
And, once logged in to the
UPDATE mysql.user SET Password=PASSWORD('the password goes here') WHERE User='root'; FLUSH PRIVILEGES;
There are two methods:
DELETE FROM table_name
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.
(Updated for 2015.)
CREATE DATABASE my_db;
To start MySQL on OS X:
To stop MySQL on OS X:
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
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
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