MySQL Install&More

gistfile1.txt

INSTALL
=======
1. get the wright source distribution (watch for _64 unless it matches your machine echitecture)
2. cd /usr/local
3. sudo tar -zxvf /path/to/mysql-VERSION-OS.tar.gz
4. sudo ln -s full-path-to-mysql-VERSION-OS mysql
5. cd ./mysql
6. sudo chown -R mysql .
7. sudo chgrp -R mysql .
8. sudo scripts/mysql_install_db --user=mysql
9. sudo chown -R root .
10. sudo chown -R mysql data
 
POST-INSTALL
============
1. run server: sudo mysqld_safe --user=mysql &
2. change the root pass: mysqladmin -u root password NEWPASSWORD
3. run mysql shell: mysql -uroot -pNEWPASSWORD 
 
 
Database
========
create database django_sbx default character set = 'utf8';
 
User
====
create user 'django'@'localhost' identified by 'django';
grant usage on *.* to 'django'@'localhost' identified by 'django';
grant all privileges  on django_sbx.* to 'django'@'localhost' identified by 'django';
#revoke all privileges on django.* from 'django'@'localhost';
# With DROP USER, you can remove an account and its privileges as follows:
DROP USER user;
 
Give all rights
===============
GRANT ALL PRIVILEGES ON *.* TO 'dbAdmin'@'localhost' IDENTIFIED BY 'newpasswd' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'dbAdmin'@'%' IDENTIFIED BY 'newpasswd' WITH GRANT OPTION;
 
 
Debugging
=========
SHOW GRANTS FOR 'django'@'localhost';
SHOW TABLE STATUS LIKE 'table_name';
SHOW InnoDB STATUS;
SHOW VARIABLES;
SHOW [FULL] PROCESSLIST;
DESC table_name
SHOW CREATE VIEW|TABLE name;
SHOW INDEX FROM mytable;
EXPLAIN SELECT...\G
\s | status
\h | help
 
BENCHMARKING
============
mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));
shell> cd sql-bench
shell> perl run-all-tests --server=server_name
 
DUMPING
=======
mysqldump [--add-drop-table] -uUSER -pPASSWORD DB_NAME [TABLE_NAME] > dump_file.sql
ssh -C USER@HOST "mysqldump -C --lock-tables=0 -uUSER -pPASS -hHOST --extended-insert --where='1=1' DATABASE TABLE" | mysql -C -uUSER -pPASS -hHOST DATABASE 
 
Shutdown MySQL
==============
shell> mysqladmin -u root -p shutdown
Enter password: (enter root password here)