MySQL Reference for Unix, Linux and OS X

Starting MySQL Server

Starting MySQL - (MySQL Install)

sudo usr/local/mysql/bin/mysqld_safe
enter your password
Press the keys: Ctrl+Z
bg

Starting MySQL - (OS X Server Install)

sudo mysqld_safe
enter your password
Press the keys: Ctrl+Z
bg


Shutting Down MySQL Server

Shutting Down MySQL - (MySQL Install)

usr/local/mysql/bin/mysqladmin -u root -p shutdown
enter your password

Shutting Down MySQL - (OS X Server Install)

mysqladmin -u root -p shutdown
enter your password


Setting Admin Password for MySQL Server

Setting MySQL Server Admin Password - (MySQL Install)

/usr/local/mysql/bin/mysqladmin -u root password <password>
enter your password
/usr/local/mysql/bin/mysqladmin -u root -h `hostname` password <password>
enter your password

Setting MySQL Server Admin Password - (OS X Server Install)

mysqladmin -u root password <password>
enter your password
mysqladmin -u root -h `hostname` password <password>
enter your password


Starting MySQL Client

Starting MySQL Client - (MySQL Install)

/usr/local/mysql/bin/mysql -u root -p
enter your password

Starting MySQL Client - (OS X Server Install)

mysql -u root -p
enter your password


MySQL Additional Security Implementation and Consideration

Use " mysql" configuration database
(created by MySQL) and delete default
users and users with no password.

use mysql
delete from user where user = ' ';
delete from user where user = 'root' and password = ' ';
flush privileges;

Delete "test" database if you are not
going to use it.

drop database test;


Granting Privilage to a User

Grand select and insert privilages to the
USER: "Bob_user" and Password:
password on all the tables (*) under the
employee database.

grant select, insert on employee.* to Bob_user@localhost identified by 'password';

Grand all privilages to the USER:
"Bob_user" and Password: password on
all the tables (*) under the employee database.

grant all on employee.* to Bob_user@localhost identified by 'password';


Showing Privilages for a User (I have not seen a statement for showing all privilages for all users)
Show privilages for USER: "Bob_user"

show grants for Bob_user@localhost;

Show privilages for root user.

show grants for root@localhost;


Revoking Privilage from a User

Revoking select and insert priviledges
from the USER: "Bob_user" on all
the tables (*) under the employee database.

revoke select, insert on employee.* from Bob_user@localhost;

Revoking all privilages from the USER:
"Bob_user" on all the tables (*) under
the employee database.

revoke all on employee.* from Bob_user@localhost;


Backing up and Restoring MySQL database(s)

To backup a database called "employee.db"
to the current working directory and call
it "employee.sql"

open a new shell and type:
/usr/local/mysql/bin/mysqldump -u root -p employee.db > employee.sql
enter your password

To restore the backup database called
"employee.sql" from the current working
directory back to mysql database and call it "employee.db".

login to mysql client:
/usr/local/mysql/bin/mysql -u root -p
enter your password
create a database called "employee.db":
create database employee.db;
open a new shell and type:
/usr/local/mysql/bin/mysql -u root -p employee.db < employee.sql

To backup all of the databases to a file
called "dbsbackup.sql" in the current
working directory.

open a new shell and type:
/usr/local/mysql/bin/mysqldump -u root -p -A > dbsbackup.sql
enter your password

To restore the backup database called
"employee.sql" from the current working
directory back to mysql database and call it "employee.db".

open a new shell and type:
/usr/local/mysql/bin/mysql -u root -p < dbsbackup.sql
enter your password


Setting PATH for mysql on OS X

To add a MySQL path so you don't need
to keep typing "usr/local/mysql/bin/"
each time.

edit the file: /etc/profile and update the path by adding:
usr/local/mysql/bin/ for example:
PATH="/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/mysql/bin"


Displaying MySQL Configuration, Running Processes and Status

Show MySQL Variables and Configuration

login to mysql client and type:
show variables;

Show MySQL Running Processes from mysql

login to mysql client and type:
show processlist;

Show MySQL Running Processes from shell#

login to mysql client and type:
usr/local/mysql/bin/mysqladmin -u root -p showprocesslist
enter your password

Show MySQL status

login to mysql client and type:
show status;


Setting MySQL Configuration and Variables

Setting MySQL Variables

use the above show variables; to display variables and
set a variable by typing:
set variable=value;
example:
set sql_safe_updates=1;

Setting MySQL configuration using "my.cnf"

MySQL will look inside the /etc for "my.cnf" file.
This file does not exist by default.
You can find an example of such a file under:
/usr/local/mysql/supportfiles/my-large.cnf

Or search for such a file by openning a new shell and type:
sudo find / -name my-large.cnf -print

Create the file "my.cnf" and copy it inside the /etc folder.
You can set the MySQL Client, Server and other Configuration
for example:
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
basedir = /usr/local/mysql/
datadir = /servers/raiddrive/databases/

Put the new "my.cnf" in /etc folder and shut down and restart mysql server.

You can display the current configuration and after update configuration by typing:
show variables;


Fixing database permissions issues with mysqls

Fix mysql data permission issues

sudo chown -R mysql /usr/local/mysql/data
sudo chgrp -R wheel /usr/local/mysql/data


Changing MySQL Root Password

Changing MySQL Root Password

su -l mysql
mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newrootpassword') WHERE User='root';
mysql> FLUSH PRIVILEGES;


Mac OS X Server 10.4: Issues connecting PHP to MySQL

When running MySQL and PHP on the same Mac OS X 10.4 server, you may find that PHP cannot connect to MySQL. When PHP is communicating with a MySQL server on the same host, it uses a socket file to communicate, and looks for it at /tmp/mysql.sock. On Mac OS X Server 10.4, MySQL creates this socket file at /var/mysql/mysql.sock. To resolve this issue, you can either change the location where MySQL creates its socket file, or modify the location where PHP looks for the file. Please note that the first option is less secure than the second.

I. To change the location where MySQL creates its socket file, do this:

  1. Open your preferred text editor and create a plain text file.
  2. Type this text (on two lines as shown):
    [mysqld]
    socket=/tmp/mysql.sock
  3. Save the file as: /etc/my.cnf

II. To change the location where PHP looks for the socket file, follow these steps:

  1. In terminal type:
    sudo cp /etc/php.ini.default /etc/php.ini
  2. Open /etc/php.ini in your preferred text editor.
  3. Find the [MySQL] section, and change the mysql.default_socket directive:
    ; Default socket name for local MySQL connects.
    If empty, uses the built-in ; MySQL defaults.
    mysql.default_socket = /var/mysql/mysql.sock
  4. Change the permissions on /var/mysql so that www can read the socket, by executing this in Terminal:
    sudo chmod 775 /var/mysql
For Information Contact:
Comentum Corp
6222 Ferris Sq.
San Diego, CA 92121
Phone: 619-990-1212

Hours: Mon. - Fri., 9 a.m. - 5 p.m. PST

Contact Us »


Mobile App Development

iPhone, Google Android, BlackBerry, Windows Mobile
In-house development team.
San Diego, CA, USA.

Learn More

Web App Estimator

Select and unselect modules such CMS or eCommerce for your web application and watch the cost update in real time.

Try It »