One of the challenges for a database administrator or database developer when working with a development server that has multiple instances of MySQL running is knowing exactly how many are running.
In some cases, this may have come about by accident but mostly having multiple instances of MySQL running is intentional. Whichever the case, it would be nice to be able to use a single command to find all of the MySQL processes.
Use the mysqlserverinfo utility to locate all of the MySQL processes running on a host.
mysqlserverinfo --show-servers --server=root:root@localhost \
--format=vertical# # The following MySQL servers are active on this host: # Process id: 3007, Data path: /usr/local/mysql/data # Process id: 8191, Data path: /Volumes/Source/source/temp_13001 # Process id: 8196, Data path: /Volumes/Source/source/temp_13002 # Process id: 8201, Data path: /Volumes/Source/source/temp_13003 # Process id: 8207, Data path: /Volumes/Source/source/temp_13004 # Process id: 8212, Data path: /Volumes/Source/source/temp_13005 # # Source on localhost: ... connected. ************************* 1. row ************************* server: localhost:3306 version: 5.1.50-log datadir: /usr/local/mysql/data/ basedir: /usr/local/mysql-5.1.50-osx10.6-x86_64/ plugin_dir: /usr/local/mysql-5.1.50-osx10.6-x86_64/lib/plugin config_file: /etc/my.cnf binary_log: my_log.000286 binary_log_pos: 237 relay_log: None relay_log_pos: None 1 row. #...done.
The mysqlserverinfo utility is normally used to find information about a particular server. We can see such results in the example above.
However, the utility also has an option,
displays a list of all of the MySQL server process ids that
are executing on the host. This quick glance can help diagnose
problems with multiple instances on the same machine.
The permissions required include the ability to read the mysql database and to have read access to the data directory.
Notice the output shows the data directory for each server. You can use this information to examine the files in that folder to discern more information such as what databases exist and find and examine the binary log, etc.
On POSIX systems, you can discover the command-line arguments such as the port number the server is using with the "ps -f PID" command. For example, to discover the complete information for PID 2487, you can do the following.
ps -f 2487UID PID PPID C STIME TTY TIME CMD 501 2487 1 0 10:58AM ttys001 0:00.41 /source/mysql-5.6/sql/mysqld --no-defaults \ --datadir=/source/temp_13002 --tmpdir=/source/temp_13002 \ --pid-file=/source/temp_13002/clone.pid --port=13002 \ --server-id=102 --basedir=/source/mysql-5.6 \ --socket=/source/temp_13002/mysql.sock --log-slave-updates \ --gtid-mode=on --enforce-gtid-consistency --log-bin \ --master-info-repository=TABLE --report-port=13002 \ --report-host=localhost