Search My Blog

Sunday, December 2, 2012

MySQL Commands for the Linux Command Line for Database Administration in Linux

In the past, I looked and looked for a quick list and how to on MySQL Commands for the Command Line in Linux and found nothing but long Manuals to read through. This is just what I've been looking for. Check it out...

Don

20 MySQL (Mysqladmin) Commands for Database Administration in Linux

mysqladmin is a command-line utility the comes with MySQL server and it is used by Database Administrators to perform some basic MySQL tasks easily such as setting root password, changing root password, monitoring mysql processes, reloading privileges, checking server status etc.

In this article we’ve compiled some very useful ‘mysqladmin‘ commands that are used by system/database administrators in their day-to-day work. You must have MySQL server installed on your system to perform these tasks.

20 mysqladmin commands examples

If you don’t have MySQL server installed or you are using older version of MySQL server, then we recommend you all to install or update your version by following our below article.

  1. Installation of MySQL 5.5.28 Server on RHEL/CentOS/Fedora

1. How to set MySQL Root password?

If you have fresh installation of MySQL server, then it doesn’t required any password to connect it as root user. To set MySQL password for root user, use the following command.

# mysqladmin -u root password YOURNEWPASSWORD

2. How to Change MySQL Root password?

If you would like to change or update MySQL root password, then you need to type the following command. For example, say your old password is 123456 and you want to change it with new password say xyz123.

mysqladmin -u root -p123456 password 'xyz123'

3. How to check MySQL Server is running?

To find out whether MySQL server is up and running, use the following command.

# mysqladmin -u root -p ping    Enter password:  mysqld is alive

4. How to Check which MySQL version I am running?

The following command shows MySQL version along with the current running status .

# mysqladmin -u root -p version    Enter password:  mysqladmin  Ver 8.42 Distrib 5.5.28, for Linux on i686  Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.    Oracle is a registered trademark of Oracle Corporation and/or its  affiliates. Other names may be trademarks of their respective  owners.    Server version          5.5.28  Protocol version        10  Connection              Localhost via UNIX socket  UNIX socket             /var/lib/mysql/mysql.sock  Uptime:                 7 days 14 min 45 sec    Threads: 2  Questions: 36002  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

4. How to Find out current Status of MySQL server?

To find out current status of MySQL server, use the following command. The mysqladmin command shows the status of uptime with running threads and queries.

# mysqladmin -u root -ptmppassword status    Enter password:  Uptime: 606704  Threads: 2  Questions: 36003  Slow queries: 0  Opens: 15  Flush tables: 1  Open tables: 8  Queries per second avg: 0.059

6. How to check status of all MySQL Server Variable’s and value’s?

To check all the running status of MySQL server variables and values, type the following command. The output would be similar to below.

# mysqladmin -u root -p extended-status    Enter password:  +------------------------------------------+-------------+  | Variable_name                            | Value       |  +------------------------------------------+-------------+  | Aborted_clients                          | 3           |  | Aborted_connects                         | 3           |  | Binlog_cache_disk_use                    | 0           |  | Binlog_cache_use                         | 0           |  | Binlog_stmt_cache_disk_use               | 0           |  | Binlog_stmt_cache_use                    | 0           |  | Bytes_received                           | 6400357     |  | Bytes_sent                               | 2610105     |  | Com_admin_commands                       | 3           |  | Com_assign_to_keycache                   | 0           |  | Com_alter_db                             | 0           |  | Com_alter_db_upgrade                     | 0           |  | Com_alter_event                          | 0           |  | Com_alter_function                       | 0           |  | Com_alter_procedure                      | 0           |  | Com_alter_server                         | 0           |  | Com_alter_table                          | 0           |  | Com_alter_tablespace                     | 0           |  +------------------------------------------+-------------+

7. How to see all MySQL server Variables and Values?

To see all the running variables and values of MySQL server, use the command as follows.

# mysqladmin  -u root -p variables    Enter password:  +---------------------------------------------------+----------------------------------------------+  | Variable_name                                     | Value                                        |  +---------------------------------------------------+----------------------------------------------+  | auto_increment_increment                          | 1                                            |  | auto_increment_offset                             | 1                                            |  | autocommit                                        | ON                                           |  | automatic_sp_privileges                           | ON                                           |  | back_log                                          | 50                                           |  | basedir                                           | /usr                                         |  | big_tables                                        | OFF                                          |  | binlog_cache_size                                 | 32768                                        |  | binlog_direct_non_transactional_updates           | OFF                                          |  | binlog_format                                     | STATEMENT                                    |  | binlog_stmt_cache_size                            | 32768                                        |  | bulk_insert_buffer_size                           | 8388608                                      |  | character_set_client                              | latin1                                       |  | character_set_connection                          | latin1                                       |  | character_set_database                            | latin1                                       |  | character_set_filesystem                          | binary                                       |  | character_set_results                             | latin1                                       |  | character_set_server                              | latin1                                       |  | character_set_system                              | utf8                                         |  | character_sets_dir                                | /usr/share/mysql/charsets/                   |  | collation_connection                              | latin1_swedish_ci                            |  +---------------------------------------------------+----------------------------------------------+

8. How to check all the running Process of MySQL server?

The following command will display all the running process of MySQL database queries.

# mysqladmin -u root -p processlist    Enter password:  +-------+---------+-----------------+---------+---------+------+-------+------------------+  | Id    | User    | Host            | db      | Command | Time | State | Info             |  +-------+---------+-----------------+---------+---------+------+-------+------------------+  | 18001 | rsyslog | localhost:38307 | rsyslog | Sleep   | 5590 |       |                  |  | 18020 | root    | localhost       |         | Query   | 0    |       | show processlist |  +-------+---------+-----------------+---------+---------+------+-------+------------------+

9. How to create a Database in MySQL server?

To create a new database in MySQL server, use the command as shown below.

# mysqladmin -u root -p create databasename    Enter password:
# mysql -u root -p    Enter password:  Welcome to the MySQL monitor.  Commands end with ; or \g.  Your MySQL connection id is 18027  Server version: 5.5.28 MySQL Community Server (GPL) by Remi    Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.    Oracle is a registered trademark of Oracle Corporation and/or its  affiliates. Other names may be trademarks of their respective  owners.    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.    mysql> show databases;  +--------------------+  | Database           |  +--------------------+  | information_schema |  | databasename       |  | mysql              |  | test               |  +--------------------+  8 rows in set (0.01 sec)    mysql>

10. How to drop a Database in MySQL server?

To drop a Database in MySQL server, use the following command. You will be asked to confirm press ‘y‘.

# mysqladmin -u root -p drop databasename    Enter password:  Dropping the database is potentially a very bad thing to do.  Any data stored in the database will be destroyed.    Do you really want to drop the 'databasename' database [y/N] y  Database "databasename" dropped

11. How to reload/refresh MySQL Privileges?

Read More...
http://www.tecmint.com/mysqladmin-commands-for-database-administration-in-linux/


News 12-01-12
Five free social networking tools for the desktop | TechRepublic
10 ways automated tools can help you meet the tech challenges of 2013 | TechRepublic
Austin Restaurant Features SI Black Diamond Venue
Why voice recognition will finally conquer the office - Computerworld
iGoogle
Linux Today - How To Upgrade From Linux Mint 13 (Maya) To 14 (Nadia) With apt
How To Upgrade From Linux Mint 13 (Maya) To 14 (Nadia) With apt | HowtoForge - Linux Howtos and Tutorials
Linux Today - Open Source CRM Zurmo Releases Version 0.8.0, Email and Theming Added
Open Source CRM Zurmo Releases Version 0.8.0, Email and Theming Added - SFGate
The War on Oil – Part 1 | Iranian.com
St. Optimus of Prime
Minecraft clock radio puts a creeper head next to your bed
Open source capactive charger resurrects an electric skateboard
Google snaps up Waterloo startup BufferBox | FP Tech Desk | Financial Post
How to Enable or Disable Location Sensing in Windows 7 and 8
How to Control Your Dreams: 9 steps - wikiHow
David Stern Fines Spurs for Day Off, Raising Question for All Bosses - NYTimes.com
Doctors Who Work for Hospitals Face a New Bottom Line - NYTimes.com
More Sleep May Help Some People Feel Less Pain
Troubled Firm Stops Making Generic Lipitor Pending Investigation
Do Older Adults Need Vitamins, Supplements?
Linux Today - 20 Best Android apps this week
20 Best Android apps this week | Technology | guardian.co.uk
News 12-01-12 #2
Ford Issues Urgent Voluntary Recall for 1.6-liter Escape, Fusion Models - WOT on Motor Trend
Linux Today - 20 MySQL (Mysqladmin) Commands for Database Administration in Linux
20 MySQL (Mysqladmin) Commands for Database Administration in Linux
Linux Today - Conditions in bash scripting (if statements)
Conditions in bash scripting (if statements) | Pinehead.tv
Recording audio with Chrome using HTML5
Plan 9 on the Raspberry Pi
Raspberry Pi Quadcopter
Dividing the West Bank, Deepening a Rift - NYTimes.com
Raspberry Pi powered quadrocopter - YouTube
Getting Plan 9 running on the Raspberry Pi | The Bendyworks Blog | Bendyworks | Ruby on Rails, iOS, & Clojure Consultants | Madison, WI
64 Rasberry Pis turned into a supercomputer
Christine Jorgensen - Wikipedia, the free encyclopedia
How to record audio in Chrome with native HTML5 APIs
Accelerometer-based game control using an iOS device courtesy of HTML5

No comments: