Linux system admins are very familiar with command line tools.
Many time admins need to administrate mysql server, they need to create database, manage database user, backup & restore mysql database, repair database & much more.
In this post I will explain some usefull command to administrate mysql server.
To know your installed mysql version
# mysql -V # mysql Ver 14.14 Distrib 5.5.46, for debian-linux-gnu (x86_64) using readline 6.3
# 1: Use of ‘mysqladmin’ utility tool
‘mysqladmin’ is very powerful tool, you can perform some handful task easyly.
here is some example of ‘mysqladmin’ command
create databasename Create a new database
debug Instruct server to write debug information to log
drop databasename Delete a database and all its tables
extended-status Gives an extended status message from the server
flush-hosts Flush all cached hosts
flush-logs Flush all logs
flush-status Clear status variables
flush-tables Flush all tables
flush-threads Flush the thread cache
flush-privileges Reload grant tables (same as reload)
kill id,id,… Kill mysql threads
password [new-password] Change old password to new-password in current format
old-password [new-password] Change old password to new-password in old format
ping Check if mysqld is alive
processlist Show list of active threads in server
reload Reload grant tables
refresh Flush all tables and close and open logfiles
shutdown Take server down
status Gives a short status message from the server
start-slave Start slave
stop-slave Stop slave
variables Prints variables available
version Get version info from server
How to use
# mysqladmin status
or Providing password
# mysqladmin -p status
You can pass lots of parameter to mysqladmin utility here is a short list
-u [database username] Mention username
-p [Database user password] Database Password
Note: If you do not mention user, default user will be root. And if you do not mention password that means you are trying to login with NO password.
# 2: MySQL User management
First you have to login to mysql console
# mysql -u root -p root_password
Create user
mysql> CREATE USER 'database_user'@'localhost' IDENTIFIED BY 'database_password';
Grant Privileges to a specific database
mysql> GRANT ALL PRIVILEGES ON database_name.database_table TO 'database_user'@'localhost';
Grant Privileges to a all database
mysql> GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';
Reload All privileges
mysql> FLUSH PRIVILEGES;
Show all privilages
mysql> SHOW GRANTS FOR 'database_user'@'localhost';
# 3: MySQL Backup & Restore
Database Backup
# mysqldump --skip-comments --compact --debug-check=TRUE --complete-insert=TRUE --extended-insert=FALSE --databases DATBASE_NAME -p > OUTPUT_FILE.$(date +%F).sql
Backup specific table
# mysqldump --skip-comments --compact --debug-check=TRUE --complete-insert=TRUE --extended-insert=FALSE DATBASE_NAME TABLE_NAME -p > OUTPUT_FILE.$(date +%F).sql
Restore Backup
mysql DATABASE_NAME < DATABSE.sql
# 4: MySQL Repair
Repair Mysql Table
# mysqlcheck --reapir database_name table_name
Repair Specific databse
# mysqlcheck --reapir --databases database_name
Repair All Databases
# mysqlcheck --repair --all-databases
# 5: MySQL reset root password
Although sometime admin needs to reset mysql root password, so here it is
Stop Mysql service
# /etc/init.d/mysql stop
Start mysql server as safe mode
# mysqld_safe --skip-grant-tables &
Login to mysql console
# mysql -u root
Now setup new password
mysql> use mysql; mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root'; mysql> flush privileges; mysql> quit
Restart mysql server & test
# /etc/init.d/mysql stop # /etc/init.d/mysql start # mysql -u root -p
So, It is short description , you can find more information from mysql official website I am sharing some important links here you can go through & learn lots of administration operation.
mysqladmin — Client for Administering a MySQL Server => https://dev.mysql.com/doc/refman/5.1/en/mysqladmin.html
mysqldump — A Database Backup Program => https://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
mysqlimport — A Data Import Program => https://dev.mysql.com/doc/refman/5.1/en/mysqlimport.html
myisamchk — MyISAM Table-Maintenance Utility => https://dev.mysql.com/doc/refman/5.1/en/myisamchk.html
innochecksum — Offline InnoDB File Checksum Utility => https://dev.mysql.com/doc/refman/5.1/en/innochecksum.html
mysqlcheck — A Table Maintenance Program => https://dev.mysql.com/doc/refman/5.1/en/mysqlcheck.html