MySQL - How to perform basic admin

MySQL - How to perform basic admin

How to display output

display in vertical : -E, --vertical

 

variables : --print-defaults

 

save output : -tee= save html : -H

 

How to check MySQL server is alive?

alive : mysqladmin -p ping

 

How to Find out current Status of MySQL server?

mysqladmin -u root -p extended-status mysqladmin -u root -p status

How to check MySQL version?

mysqladmin -u root -p version

How to stop MYSQL?

mysqladmin -p shutdown

How to set root password?

mysqladmin -u root password YOURNEWPASSWORD

How to check all the running Process of MySQL server?

mysqladmin -u root -p processlist

How to kill a process

>

mysql> SHOW PROCESSLIST; or mysql> SELECT * FROM information_schema.processlist ORDER BY id; mysql> Kill thread_id or mysqladmin kill id

How to create a job

mysql > CREATE EVENT IF NOT EXISTS test_event_01 ON SCHEDULE AT CURRENT_TIMESTAMP DO … mysql> SHOW EVENTS FROM …

http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

MySQL Backup and Recovery

This article provides a quick guide to performing backup and recovery of MySQL databases

Logical Backup (mysqldump)

Backup database

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Backup multiple databases

mysqldump -u root -ptmppassword --databases bugs sugarcrm > bugs_sugarcrm.sql

 

Restore a database


In this example, to restore the sugarcrm database, execute mysql with < as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the sugarcrm database before you can perform the restore

mysql -u root -ptmppassword mysql> create database sugarcrm; mysql -u root -ptmppassword sugarcrm < /tmp/sugarcrm.sql mysql -u root -p[root_password] [database_name] < dumpfilename.sql