MySQL

Users and Privileges

[edit] Show User Grants

mysql> select User,Host from mysql.user

mysql> SHOW GRANTS FOR user;

mysql> DROP USER user;

mysql> FLUSH PRIVILEGES;

mysql> USE database;

mysql> SHOW processlist;

mysql> grant all privileges on databasename.* to 'user'@'host' identified by 'password';

[edit] Update User Password

mysql> SET PASSWORD FOR 'user'@'host'=PASSWORD('newpass');

mysql> FLUSH PRIVILEGES;

[edit] USAGE

No privileges are granted. The USAGE privilege in the GRANT statement enables you to create an

account without giving it any privileges. It has the effect of setting all the global privileges

to 'N'. It is assumed that you will grant specific privileges to the account later.

[edit] Backup & Restore

# mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]

# mysql -u [username] -p [password] [database_to_restore] < [backupfile]

[edit] mysqldump flags

--opt: This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.

    * to set the date as the name: `date +%Y%m%d`.sql.gz 

To backup all databases:

# mysqldump --all-databases > alldatabases.sql

To only backup table structures:

# mysqldump --no-data --databases Customers Orders Comments > structurebackup.sql

To compress on the fly:

# mysqldump --all-databases | bzip2 -c >databasebackup.sql.bz2

# mysqldump --all-databases | gzip >databasebackup.sql.gz 

To set up a mysqldump via crontab, example shows everyday at midnight:

# crontab -e

0 0 * * *  /usr/bin/mysqldump --all-databases | gzip > /root/mysql/`date +%Y%m%d`.sql.gz

[edit] Password Recovery

 # mysqld_safe --skip-grant-tables &

 # log in as root

 mysql> GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password';

 mysql> FLUSH PRIVILEGES;

# mysqladmin processlist

mysql> show full processlist;

> explain

> show

> describe

> repair table [table name]

> check table [table name]

these only work with Isam tables

bash$ myisamcheck /path/to/table

bash$ myisamchk -r /path/to/table

------------------------------------------------------------------------------

This link has information on altering a table to add primary/foreign keys:

http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

This link has information on creating indexes on an existing table:

http://dev.mysql.com/doc/refman/4.1/en/create-index.html

This link has some information explaining unique/primary keys:

http://en.wikipedia.org/wiki/Primary_key 

------------------------------------------------------------------------------

[root@124720-www1 ~]# cat /etc/my.cnf

[mysqld] 

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

#tmpdir=/var/lib/mysqltmp

#old_passwords=1

#skip-grant-tables

#skip-networking

skip-locking

key_buffer = 64M

max_allowed_packet = 16M

table_cache = 2048

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 16

thread_concurrency = 8

query_cache_size = 32M

tmp_table_size=64M

max_heap_table_size=64M

back_log = 100

max_connect_errors = 10000

join_buffer_size=1M

open-files = 20000

interactive_timeout = 600

wait_timeout = 600

#max_connections=200

# The following directives should be commented out

# but included as they are things that get added

# very frequently on tickets. These are more in a

# need-this-feature basis.

# The below 2 cannot be set on the fly. If the customer already has

# InnoDB tables and wants to change the size of the InnoDB tablespace

# and InnoDB logs, then:

# 1. Run a full backup with mysqldump

# 2. Stop MySQL

# 3. Move current ibdata and ib_logfiles out of /var/lib/mysql

# 4. Uncomment the below innodb_data_file_path and innodb_log_file_size

# 5. Start MySQL (it will recreate new InnoDB files)

# 6. Restore data from backup

#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

#innodb_log_file_size = 100M

#innodb_buffer_pool_size = 384M

#innodb_additional_mem_pool_size = 20M

#log-slow-queries=/var/lib/mysqllogs/slow-log

#long_query_time=2

#log-queries-not-using-indexes

#log-bin=/var/lib/mysqllogs/bin-log

#log-slave-updates

#expire_logs_days = 14

server-id       = 1

[mysql.server]

user=mysql

#basedir=/var/lib

[mysqld_safe]

err-log=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

open_files_limit=65536

------------------------------------------------------------------------------

empulsegroup.com