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

MYSQL space management

Finding Database size

SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024
"Data Base Size in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

Finding all table size in the database

SELECT table_name AS "Tables", round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = "$DB_NAME"
ORDER BY (data_length + index_length) DESC;

MYSQL basic command

MySQL Commands List

Here you will find a collection of basic MySQL statements

General Commands

USE database_name	Change to this database. You need to change to some database when you first connect to MySQL mysql> SELECT DATABASE();
DESCRIBE table_name
SET PASSWORD=PASSWORD('new_password')
OPTIMIZE [table]
SHOW variables
SHOW DATABASES	Lists all MySQL databases on the system. To find out which database is currently selected, use the DATABASE() function
show tables  [FROM database_name]	Lists all tables from the current database or from the database given in the command
SHOW FIELDS FROM table_name
SHOW COLUMNS FROM table_name	These commands all give a list of all columns (fields) from the given table, along with column type and other info.
SHOW INDEX FROM table_name	Lists all indexes from this tables.
show open tables
show procedure status
show function status
show binary log	Lists the binary log files on the server
show master log	Lists the binary log files on the server

How to troubleshoot mysql database server high cpu usage/slowness

1. Firstly find out what's causing server CPU high usage
• vmstat 2 20top -b -n 5
2. check mysql error log , slow query log etc from /etc/my.cnf
• innodb_buffer_pool_size=20000M 
4. mysql > show engine innodb status\G

Other administration commands

• display in vertical : -E, --vertical
• variables : --print-defaults
• save output : -tee=
• save html : -H
• alive : mysqladmin -p ping
• status : mysqladmin -p ,
• How to Find out current Status of MySQL server?
• mysqladmin -u root -p extended-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


• : mysqladmin kill id
• How to create a job
• http://www.mysqltutorial.org/mysql-triggers/working-mysql-

Configuration Files

• my.cnf - usually located in /etc/my.cnf, /etc/mysql/my.cnf

Directories

• basedir ($MYSQL_HOME)
  - e.g. /opt/mysql-5.1.16-beta-linux-i686-glib23
• datadir (defaults to $MYSQL_HOME/data)
• tmpdir (important as mysql behaves unpredictability if full)
• innodb_[...]_home_dir
  - mysql> SHOW GLOBAL VARIABLES LIKE '%dir'