Open Source Software Technical Articles

Want the Best of the Wazi Blogs Delivered Directly to your Inbox?

Subscribe to Wazi by Email

Your email:

Connect with Us!

Current Articles | RSS Feed RSS Feed

Use MySQL Utilities for better MySQL administration

  
  
  

Oracle, the company behind MySQL, provides MySQL Utilities, a collection of command-line scripts for accomplishing some common database administration tasks. We put the utilities through their paces on CentOS 6 and MySQL 5.6.

Since the MySQL Utilities collection is not included in the default MySQL server and client packages, you have to install it manually. First, download and install MySQL's Python connector. The MySQL Utilities are written in Python, and this connector allows them to connect to a database server. You can install the current version of the Python connector with the command rpm -ivh http://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-1.0.11-1.el6.noarch.rpm/from/http://cdn.mysql.com/.

Next, download and install the current version of MySQL Utilities with the command rpm -ivh http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.3.4-1.el6.noarch.rpm/from/http://cdn.mysql.com/. You should then find the newly installed utilities under your default shell path in the directory /usr/bin/. To see all the available scripts, use the command rpm -ql mysql-utilities |grep bin.

This simple installation, with only one dependency, makes it possible to use MySQL Utilities even on servers with no X environment.

Configuration

The MySQL Utilities don't require any special configuration, but to avoid having to always specify all connection parameters manually, you can save your MySQL connection details with the mysql_config_editor command. For instance, you can use mysql_config_editor set --login-path=local --host=localhost --user=root --password, where --login-path defines the name of the connection. This command saves the connection under the name "local." If you want to be able to access other servers, you have to save their details similarly under a different --login-path parameter.

The above information, including the password, is stored in the encrypted file ~/.mylogin.cnf, which has permissions of 600, meaning only your user can read it. The ~/.mylogin.cnf file is not a replacement for the similar ~/.my.cnf file; the latter is still needed to store your login details for the MySQL client /usr/bin/mysql. The secure permissions on ~/.mylogin.cnf, plus the fact that the data is encrypted, minimize security risks. Having your connection details stored securely is not only convenient but also helps you automate tasks and include MySQL Utilities in other scripts without the need to re-enter the password manually or store it in clear text.

Now you can use the information of this "local" instance by specifying it in the --server parameter of the scripts. Take for example mysqlserverinfo, the most basic MySQL Utilities script, which shows general information about the MySQL server. You can run this script with the "local" connection details like this: mysqlserverinfo --server=local.

Find disk usage with mysqldiskusage

One script you may find useful is mysqldiskusage, which computes the disk usage of MySQL databases:

/usr/bin/mysqldiskusage --server=local
# Source on localhost: ... connected.
# Database totals:
+---------------------+--------------+
| db_name             |       total  |
+---------------------+--------------+
| employees           | 448,502,697  |
| mysql               | 1,538,014    |
| performance_schema  | 489,543      |
+---------------------+--------------+

Total database disk usage = 450,530,254 bytes or 429.66 MB

You can add the option --format to get CSV and tab-separated output, which you can then use in automation tasks, feeding the output of mysqldiskusage to other scripts.

Copy databases with mysqldbcopy

Another script, mysqldbcopy, copies a database either on the same server under a different name or to a different server. Here is an example that copies the database employees from the local server to a remote server :

 mysqldbcopy --source=local --destination=server2 employees:employees2
# Source on localhost: ... connected.
# Destination on 192.168.204.202: ... connected.
# Copying database employees renamed as employees2
# Copying TABLE employees.departments
...
# Copying data for TABLE employees.employees
# Copying data for TABLE employees.salaries

The employees database is copied under the new name employees2 on the remote server, which has an IP address of 192.168.204.202.

This handy script saves time and effort you might spend with mysqldump manually exporting a database. If you explicitly need to export a database, such as for creating an offline backup, you can use a script called mysqldbexport. Its use is similar to that of mysqldump: run mysqldbexport --server=local dbname, where dbname is the database to be exported. To learn how to use more options, such as for locking the tables or creating a slave from the database dump, check the script's manual page with the command man mysqldbexport.

Clone users with mysqluserclone

You can also copy or clone a user with the script mysqluserclone. The command mysqluserclone --source=local --destination=server2 dolly@localhost server2:dolly@localhost clones the user dolly from the local server to the remote one (server2).

The mysqluserclone script not only copies the user's name and password, but also preserves its privileges, which saves you time and effort compared to performing the operation manually. This can be especially helpful when you have a user with complex rights and permissions and you need a fast way to create a new user with the same privileges.

Compare databases with mysqldbcompare

The script mysqldbcompare compares one database on different servers or different databases on one server. You may need such a comparison when you run MySQL replication and you want to check the data integrity between the servers. It also helps when you are debugging and you want to see the differences between the current live version of a database and a previous version, such as one from a backup.

For example, the command mysqldbcompare --server1=local --server2=local difftest:difftest2 --run-all-tests compares the databases difftest and difftest2 on the local server.

The powerful mysqldbcompare script can also tell you how to fix any differences it finds between two tables, as long as the two tables have the same primary keys. To see how to fix the differences, add the argument --difftype=sql, which provides ready-to-run SQL statements, as in this very simple example:

# Data differences found among rows:
UPDATE `difftest`.`test_table` SET `value2` = '7' WHERE `value1` = '5';

By default the statements are meant to be run on the first of the two compared servers, or the first database when the databases are on the same server. If you're going to run these commands, make sure to prevent updates to the relevant tables in the compared databases. You can lock a database table to read-only with the MySQL command LOCK TABLES table_name READ; Once the comparison is over, unlock the tables with the MySQL command UNLOCK TABLES;

Find MySQL processes with mysqlprocgrep

If you wish to look for certain MySQL processes and manage them, use mysqlprocgrep. This script allows you to refine search results with multiple parameters such as for how long a query is being executed (--age), the command of a query (--match-command), and on which database the query runs (--match-db).

To see how this script works, run mysqlprocgrep --server=local --age=60 --match-command='Sleep'. This lists all connections that have been inactive for more than 60 seconds and that are in sleep state:

mysqlprocgrep --server=local --age=60 --match-command='Sleep'
+------------------------+-----+-------+------------+------------+----------+-------+--------+-------+
| Connection             | Id  | User  | Host       | Db         | Command  | Time  | State  | Info  |
+------------------------+-----+-------+------------+------------+----------+-------+--------+-------+
| root:*@localhost:3306  | 8   | root  | localhost  | employees  | Sleep    | 391   |        | None  |
+------------------------+-----+-------+------------+------------+----------+-------+--------+-------+

The mysqlprocgrep script has two powerful options: --kill-connection to kill a connection and --kill-query to kill a query. These kill options work only after the search conditions are matched, and can be used to free resources on busy MySQL servers overloaded by poorly written applications.

Final words

The MySQL Utilities scripts can help you accomplish some common MySQL tasks easier and faster. Compare them with the usual ways you accomplish the same tasks and decide for yourself which is easier, faster, and less error-prone.




This work is licensed under a Creative Commons Attribution 3.0 Unported License
Creative Commons License.

Comments

What is usually obtained because of the gals ought to be anything remarkable in comparison with other sorts of fake chanel. It is a inborn characteristics connected with gals. Many people visualize sporting clothing or maybe shoes and boots that is certainly fully dissimilar by all the other gals. Whether or not many people take many gadgets many people visualize like dissimilarity likewise. Because of this , the women have on makers in addition to the vast majority of different fake rolex gadgets manufacturers have to take into consideration anything most current in addition to modern for them to store the shoppers. The women clutches usually are one among like gadgets which might be generally constructed remembering which the gals really should appreciate most of these. Based on the inclinations in addition to willpower on the gals to obtain modern, in addition to trendy vogue prada replica this gals gadgets makers include intended unique variations of carriers for ladies. One example is many women of all ages would like to acquire household leather carriers and many some others wish nylon having drops some including material carriers for example. All over again a lot of the household leather carriers are created from true household leather and many some others in contrast add some man-made replica chanel coco. Possibly many situations this carriers of which are created from straws can also be recommended by means of many replica chanel handbags. Performed people abide by this tendency connected with Jordan Kors? Enable modern in addition to extremely versatile Jordan Kors clutches are intended in addition to formulated with household leather. As a way to match that electric need to have replica chanel coco produce jordan kors women of all ages carriers having a variety of massive or maybe modest spaces intended for unique works by using. Many jordan kors hamilton go along with massive or maybe modest zipper purses on the inside of or maybe away from the clutches. So Jordan Kors fake chanel look at the choice of the potential customers though doing chanel outlet. You’re about to uncover almost any thought to be one of those modern chanel replica models on the net. Jordan Kors clutches are rolexful gadgets to help anyone’s clothing collection and are also favorite in each and every gathering. Jordan Kors clutches accompany various styles and designs. Recall, consider this gross sales on top quality on the net tendency suppliers and as well look at the good hip clutches dependant upon superstar models. The one thing is for certain somewhere around the structure on the tote: many real jordan kors carriers usually are most likely distinct. 
Posted @ Tuesday, May 13, 2014 3:41 AM by replica chanel coco
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics