MySQL Command Line Administration in Linux

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