You are missing some Flash content that should appear here! Perhaps your browser cannot display it, or maybe it did not initialize correctly.
Lazy Access to Command Line MySQL
Wednesday, May 19, 2010 - 7:36pm - Kurt Vanderwater
I’m a Command Line type of guy. I don’t appreciate have to move my hand back and forth to a mouse to get something done.
There are some great GUI based MySQL tools out there and probably my favorite is Sequel Pro on the Mac... But that isn’t the point of this blog today.
There are many times that I am at the Command Line and need to get something done. Being lazy (see title) and not using MySQL command line tools every day, I built a script to handle some defaults and remind me of some options.
I called the script (written in Bash) simplesql. This has been published on github for your access and enjoyment.
Some of the features of this script are as follows:
* An optional configuration file where I can store server name and aliases, along with usernames, passwords, and default database names.
* a quick access CLI mode “simplesql -o sql02”. This command uses the alias ‘sql02’ to lookup the servername, gets the user/passwd from the config file, and without other instructions, goes into edit mode. Behind the scenes this basically launhes “mysql -u b3sql02.domain.com -u root -ppassword” and I am in MySQL ready to do some adhoc Command Line work.
* a Dump, Import, Copy, Clone, & Export set of commands. Export will output CSV formatting for a table.
* run a ‘top’ like program to provide statistics for a particular server.
* A couple of Drupal specific commands. One is a dump without the watchdog, session, & cache data. The other is a mass updated to change the admin password
Security is a big issue and some of the features of this script are more geared for a smaller or mid-sized environment. I have left those features in the script as they were very useful to us until our growth demanded a higher focus on security. Today we do not use the external storage of password in the config file. That was a policy change but the feature should still work. Another security issue is that early on, we would have a common ‘admin’ password. So, the alter command came in very useful to do mass changes to that password with personnel turnover. Use it at your own risk ☺
Well, that is about it.... It has save me a lot of time and trips to the man pages over the years. I hope you might find it useful too.
I would also recommand Drush
Submitted by Wim Mostrey (not verified) on May 20, 2010 - 3:01am.
I would also recommand Drush for this kind of Drupal-related database maintenance. If not only for the fact that it reads the settings.php instead of using a custom config file where users can store any password (like the root mysql password).
medyum
Submitted by medyum (not verified) on May 21, 2010 - 9:30am.
I would also recommand Drush for this kind of Drupal-related database maintenance. If not only for the fact that it reads the settings.php instead of using a custom config file where users can store any password (like the root mysql password).
medyum
More drush = less code
Submitted by Simon Hobbs (not verified) on June 27, 2010 - 11:08pm.
You should write your script as a drush extension. If only to take advantage of the new site aliases feature. http://drupal.org/node/670460
Also, I understand the drush dump/sql-sync command handles exclusion list (no data from certain tables) because I asked Moshe about this at Drupalcon SF. I don't know the syntax though.
I must agree with you that it
Submitted by Francis (not verified) on September 4, 2010 - 1:07am.
I must agree with you that it does get irritating using the mouse and at times when it gets stuck up it infuriates you further. All PCs should come with a GUI. With technology, application and software moving ahead you wonder why the devices are still out dated.
Drupalcon DC Sponsorship
A proud sponsor of Drupalcon DC!
If you are running a drupal
If you are running a drupal site, drush provides some great commands for db access.
sql-cli: Open a SQL command-line interface using Drupal's credentials.
sql-conf: Print database connection details using print_r().
sql-connect: A string for connecting to the DB.
sql-dump: Exports the Drupal DB as SQL using mysqldump.
sql-query: Execute a query against the site database.
sql-sync: Copy source database to target database using rsync.