 |
|
| |
MySQL Reference for 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 privilages 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:
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 mysql |
| 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 |
| |
Colocate with Comentum for as little as $100 per month
- San
Diego Colocation
|
Bernard Kohan ©
2007
Website
Design and Development - San Diego, Chicago, Austin, Irvine, Los Angeles, Ft Lauderdale, Jacksonville
Website
Database Development - - San Diego, Chicago, Austin, Irvine, Los Angeles, Ft Lauderdale, Jacksonville
Website
Design and Flash Animation - San Diego, Chicago, Austin, Irvine, Los Angeles, Ft Lauderdale, Jacksonville
|
