MYSQL Server Management for Administrator

1 => To take all database backup with one command

#mysqldump -uroot -pPassword --all-databases | gzip > /home/database_`date '+%m-%d-%Y'`.sql.gz

#mysqldump -uroot -pPassword --all-databases --events --routines >/srv/all_databases.sql

2 => To take selected database backup with one command

#mysqldump -uroot -pPassword --databases db1 db2 db3 | gzip > /home/database_`date '+%m-%d-%Y'`.sql.gz

3 => To import tar.gz database file in mysql

#zcat database.sql.gz | /opt/lampp/bin/mysql -uroot -pPassword

4 => To update table on remote system from local system

#mysqldump -uroot -pPassword databsaename  tablename | mysql -uroot -pPassword -h10.0.3.230 remotedatabasename

Note = If you get permission problem run below query on remote system.

grant all on database.* to 'root'@'%' identified by 'Password'

4 => To delete tables run below query

#drop tables `paper_comments`, `practice_test_ans`, `prs_test_paper`, `vc_subject`;

5 => To create user in mysql database

#CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

6 => To import table without checking foreign key write below query in table on

set foreign_key_checks=0;

after import run

set foreign_key_checks=1;

7 => To run mysql command in script without username password set bellow command

A => Create local login path to use in script

mysql_config_editor set --login-path=local --host=localhost --user=root --password

B => Then you can use in your shell script:

mysql --login-path=local -e "show databases"

instead of:

mysql -u username -p pass -e "show databases"

8 => To repair and recover mysql database with command

1 ) /etc/rc.d/init.d/mysql stop

2) Repair all SQL databases:
myisamchk -r /var/lib/mysql/*/*.MYI

3) Start mysql again:
/etc/rc.d/init.d/mysql start

http://forums.cpanel.net/f5/repair-all-database-mysql-69241.html

9 => To Change mysql data dirictory

#vim /etc/mysql/my.cnf

#datadir = /opt/var/lib/mysql

#rsync -av /var/lib/mysql   /opt/var/lib/mysql

#services mysqld restart